Optimizing MySQL queries with memory tables

In this query optimization tip, I will show you how we can make our queries faster using MySQL’s memory storage engine.

I have some report queries in my call center application (using which we capture the calls that we received in our call center).

The main table to be used was callflow:

I have to calculate some stats from this table, like the total duration of the call.

One of the report query was:

select date(receivedon), sum(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) Total, count(rcv_call_id) No_of_calls,
max(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) maximum ,
min(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) Minimum
from
(select FkCallID rcv_call_id, receivedon from callflow where FlowType='R' and
ReceivedOn >= '2014-01-01 00:59:03' and
ReceivedOn <= '2014-01-31 00:59:03') rcv,
(select FkCallID clsd_call_id, finishedon from callflow where FlowType='C' and
FinishedOn >= '2014-01-01 00:59:03' and
FinishedOn <= '2014-01-31 00:59:03') clsd where rcv_call_id=clsd_call_id group by date(receivedon);

This query returns total number of calls for a day, total time in seconds for the calls, max time taken for a call and min time taken for a call.

This query was taking around 30 seconds to execute which is too much.

So I decided to use MySQL’s memory tables for above query. The above query has two derived tables and I am going to use memory tables for those two derived tables.

 

1) First I will create the memory tables for the derived tables and going to add index on the column which will be used in where condition.

CREATE TABLE rcv ENGINE=MEMORY
SELECT FkCallID rcv_call_id, receivedon from callflow  where  FlowType='R' and
ReceivedOn >= '2014-01-01 00:59:03' and
ReceivedOn <= '2014-01-31 00:59:03' ;

ALTER TABLE rcv ADD INDEX (rcv_call_id);
 
CREATE TABLE clsd ENGINE=MEMORY select FkCallID clsd_call_id, finishedon from callflow  where FlowType='C'  and
FinishedOn >= '2014-01-01 00:59:03' and
FinishedOn <= '2014-01-31 00:59:03';

ALTER TABLE clsd ADD INDEX (clsd_call_id);

 

2) Using these memory tables, I will rewrite the query:

select date(receivedon), sum(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) Total, count(rcv_call_id) No_of_calls,
max(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) maximum ,
min(TIME_TO_SEC(TIMEDIFF(finishedon,receivedon))) Minimum
from rcv, clsd where rcv_call_id=clsd_call_id group by date(receivedon);

 

3) After running the query and getting the result, I will drop the temporary memory tables:

drop table rcv;
drop table clsd;

 

My query after using the memory tables gave results in 87 ms which is lightning fast compared to 30 secs it was taking previously with derived tables.

But remember to adjust the max_heap_table_size system variable, as this value restrict the maximum size of memory tables. Default is 16MB, so adjust it as per your need to take the benefit of memory tables.

Phabricator & Code Auditing

We wanted to implement code quality auditing in our software development lifecycle. We came across awesome Phabricator platform which makes code auditing much easier.

In this document I will show, how to install Phabricator in Ubuntu Linux and configuring it to Audit codes for some particular user(s) in an external SVN repository.

 

Installation & Setup:

We use Ubuntu 12.04 in our production and there is a script for Ubuntu installation. You can download the script from the link: http://www.phabricator.com/rsrc/install/install_ubuntu.sh

As we are using Ubuntu 12.04, so I changed the following line in the install_ubuntu.sh script:

Copy the install_ubuntu.sh script to the folder where you want to install Phabricator. I want to install Phabricator in /usr/local/phabricator, so I copied the script in /usr/local

Make the script executable and run the script as root user (or using sudo, I hate sudo and prefer to go to the root login)

# chmod +x install_ubuntu.sh

# ./install_ubuntu.sh

Change ownership of phabricator folder, so that Apache webserver has access to it

# chown -R www-data:www-data /usr/local/phabricator/

Now I am going to create a virtual host for my phabricator site

# pico /etc/apache2/sites-available/phabricator

# a2ensite phabricator

# service apache2 reload

If required change the MySQL database configuration for Phabricator

# /usr/local/phabricator/bin/config set mysql.user mysql_username

# /usr/local/phabricator/bin/config set mysql.pass mysql_password

# /usr/local/phabricator/bin/config set mysql.host mysql_host

# /usr/local/phabricator/bin/storage upgrade

Open the new Phabricator site, and create the initial administrator account.

Configure strict-mode for MySQL:

Open /etc/mysql/my.cnf file and add the following line under [mysqld] section:

sql-mode = STRICT_ALL_TABLES

After that restart MySQL

# service mysql restart

Disable apc.stat in the /etc/php5/conf.d/apc.ini by adding the following line:

apc.stat=0

Add timezone to PHP’s config file:

/etc/php5/apache2/php.ini

date.timezone = Asia/Calcutta

(Set your correct timezone, for me it is Asia/Calcutta)

If you are planning to user LDAP/Active Directory authentication with your Phabricator instance, you have to install PHP LDAP module.

# apt-get install php5-ldap

Restart Apache

# service apache2 restart

Also we have to install subversion

# apt-get install subversion

Create a local repository directory:
# mkdir -p /data/repo

Edit the repository.default-local-path key to the new local repository directory.

Go to Config -> Current Settings -> repository.default-local-path

Set the Base URI of Phabricator install:

# /usr/local/phabricator/bin/config set phabricator.base-uri 'http://phabricator.mkcl.org/'

Also we have to start Phabricator daemons

# /usr/local/phabricator/bin/phd start

 

 

 

Configuring external SMTP

We need to use external SMTP server for sending mails, for that we will use PHPMailer.

Go to Config

Click on PHPMailer

In PHPMailer configuration, enter your external SMTP server’s details.

Now I am going to change the Mail Settings. Go to Config -> Mail

Edit metamta.mail-adapter, here select PhabricatorMailImplementationPHPMailerAdapter

 

 

 

 

Configuring Active Directory Authentication:

We are going to use Active Directory authentication for Phabricator user login, so in this section I will show Active Directory integration.

Login as Admin user, and go to the Auth Application.

Click on Add Authentication Provider

Select LDAP from the Provider list.

Enter LDAP hostname, LDAP Port and Base Distinguished Name

I am binding to LDAP with users’ LDAP username and password (details of LDAP binding is available in Authentication Provider creation page)

 

 

 

 

User Login

In this example I will use two users, one is pranabs who is our code Auditor and second one is websafe who is our developer.

As we have configured LDAP authentication, so user can login with their Active Directory login. I will show the process for our first user pranabs.

After LDAP authentication is successful, some additional information is also required for that user (as I configured simple direct binding). After entering Email and Real Name click on the Register Phabricator Account button.

This registration request has to be approved by the admin, after that the user can login.

Also the user will get one Email in his/her registered Email ID for verifying the Email address that is entered at the time of registration.

Click on the link received in the Email and verify the Email address.

The Admin will receive Email for approving the new registered user.

Also the admin will see the count of the number of new users to approve in his/her login

Admin has to go to the link People -> Approval Queue and click on the Approve button to approve the new user.

Once the new account is approved, the user will receive Email alert that his/her account has been approved.

 

 

 

 

Configuring external SVN repository

We have few existing SVN repositories, so instead of using hosted repository of Phabricator I planned to use the external repositories.

To add the SVN repository, login as admin and go to the Repositories link under Administration section:

Click on Create New Repository

As we are going to use existing external repository, so I am selecting Import an Existing External Repository.

The type of our repository is Subversion, so I am selecting Subversion.

Give a name to the new repository, also give a Callsign. Callsign is a short unique identifier for the repository and mainly it will be used for repository related operations.

Next enter the root of the repository. I can access my repository using http, so I entered http link for my repository root.

If the repository needs authentication, we have to create Credential for the repository. Click on the Add Credential button.

Enter the required details and select the visibility and who can edit this credential.

Select the just added Credential and click on Continue button.

Select access policy for this repository.

All done, we can now start importing the repository data. Select Start Import Now and click on Save button.

The background daemon will start reading updates from the repository; we can see the status of import.

To view the repository commits and contents go to the Diffusion link

Here we can see the list of configured repositories (on which we have access permission)

Commit details:

We can see the details of a commit, including the file content.

 

 

 

 

Creating Audit Rule

As per our requirement, the commits are to be audited for a particular SVN repository. As I wrote earlier, for this example I have two users: pranabs (auditor) and websafe (developer). To send Audit requests for commits done by websafe to pranabs, we have to specify some rule or condition that will create the Audit action. For that I will use Herald rule (https://secure.phabricator.com/book/phabricator/article/herald/ ).

Go to Herald and click on Create Herald Rule link

Our requirement is for Commits, so I selected Commits.

I will create Object type rule as this rule will be for PranabTestRepo repository that we added for this example.

Now I have to enter the object name, in my case it is rPT (Callsign for my repository PranabTestRepo).

Next I have to enter the name for the Herald rule, also I have to create the rule triggering condition and the action that will be taken if the condition is satisfied.

So my condition is: if the Author is websafe, then the first action will be Trigger an Audit by pranabs and second action will be sending Email to pranabs and websafe about this Audit.

Once we created our rule, we can test the rule by going to Test Console

Here I entered one commit ID to test

Test Result: I can see my rule is successfully applied, but here no action is taken as this is a test run.

 

 

 

 

Auditing:

Suppose our developer websafe did some changes in the code and commit the changes.

Our Auditor pranabs will receive Email alert in his mailbox for the Audit request.

When auditor pranabs logins to Phabricator, he can see the Audit request.

Once Auditor opens the Audit request, details of that commit can be seen. Also Auditor can view the difference of the commit with the previous version of a file.

If something wrong with the commit, the auditor can Raise Concern for the commit.

The developer websafe will receive Email alert that Auditor has raised some concern about his audit.

Also the developer websafe can see the commit under the Problem Commits section.

Suppose our developer websafe has worked out on the issues that were raised by the auditor for a commit. Now the auditor can approve the commit.

Once the commit is accepted by the Auditor, the developer websafe receives Email alert.

Now the commit’s Status will be shown as Audited.

It’s simple enough, but it is very useful for post-push code review.