innodb thread concurrency

there is lot of material on the web about how the right number for innodb thread concurrency is kind of a black magic.

recently we changed most of the tables from myisam to innodb for one of our customers. the innodb thread currency was set to 4.

the customers called complaining of slowness and show processlist displayed lots queries seemingly waiting.

and these queries were pretty much optimized.This variable can be set dynamically.
as it was a 16 core machine, we changed it to 18 and there was a complete change in show processlist behavior.

So in summary, if you have optimized queries and lots of queries are being executed and cpu is not being used very much i.e in our case, only about 25% of the 16 core was being used where as once we increased the thread concurrency number, the cpu jumped to 50 to 60% ply with innodb thread concurrecny setting.

jconsole,jmap and tomcat as windows service

1. Install a service using sc.exe - that will open a command prompt under local service account by executing this on cmd window

sc create debugservice binpath= "cmd /K start" type= own type= interact

2. sc start debugservice

[on a remote desktop session, this service has to be started only in console session for the command prompt window to pop up]

will print message that [SC] StartService FAILED 1053: but will start a command prompt under local system account.

3. now navigate to %JAVA_HOME%/bin on the command prompt that got opened and type in
jmap -dump:format=b,file=D:\temp\test.hprof
this will dump the heap to D:\temp folder.


Be careful dumping heap on a live system as it will lock up the system for the duration of heap dump.

4. similarly jconsole can be started using that command prompt and connecting to the pid of the tomcat.

Technical explanation:
a. as tomcat service is installed under local system account, jconsole / jmap cannot connect to the service when these are started under the windows logged in user.
b. So the command prompt interactive service is installed as a local system service and when that service is started, it opens the command prompt under local system account.
c. And hence any process started from that command prompt will be started under local system account and hence be able to access any local system services.

limit and order by

lets consider a simple table

CREATE TABLE test
(                            
  id int(11) NOT NULL default '0',                   
  lastname varchar(30) NOT NULL default ''           
) ENGINE=MyISAM;

and populate with following data.

Mysql provides a neat function where in you can limit the number of rows returned by the query.
So if you execute
  select  id,lastname from test limit 2;

will return 2 rows. but is it the expected result? For the expected result an order by clause is required .

for eg. in the above scenario, by adding order by lastname clause with limit, the user can control the order in which mysql limit the number of row.

the corresponding - one of the equivalent in sql server is:

select * from (select id,lastname,row_number() over(order by lastname) as rowcounter from test_limit) as temp where temp.rowcounter between 1 and 2

date and time

date and time is the biggest the difference between the databases.SQL server 2005 has datetime (with microsecond) and smalldatetime(no microsecond) data type where as  mysql has date, time, datetime  and timestamp datatypes to handle date time components.

In mysql, if a blank value is inserted into datetime data type, it is stored as 0000-00-00 00:00:00 where as in sql server is stored as 1900-01-01 00:00:00.000. And if the application had been developed around mysql and logic in the application to handle the peculiarities with datetime in mysql will have to be thrown out of the window if the application needs to support sql server.

as mysql has no concept of microseconds where as sql server datetime has microseconds. If the application does not handle microsecond, the application will have to be rewritten to handle it.

ad hoc queries and sql server and mysql

when ever a query is fired to a database, it is parsed and various plans are generated. The best or good enough plan is chosen by the engine and is used to execute the query.

Once a plan is generated, it is maintained in the plan cache by the sql server for future re use in case same query is executed where as mysql generates the plan every time the query is executed. The caveat in the sql server for ad hoc queries, is that the queries must match text to text, case sensitive for the query plan to be reused.

For eg:
query 1:
    SELECT col1 FROM table1 WHERE col2 = 100;
query 2:
    SELECT col1 FROM table1 WHERE col2 = 200;

The above 2 queries will generate 2 query plans and will be cached in the plan cache as 2 different plans even though the only difference is in the predicate value.

This leads to couple of interesting issue.

1. SQL server 2005 introduced TokenAndPermUserStore cache which affects the performance for ad hoc queries.

 http://support.microsoft.com/default.aspx/kb/927396

This issue seems to have been fixed in sql server 2005 sp3 and sql server 2008.

2. The size of plan cache grows streadily as sql server caches all the plans until it hits the memory pressure.

The sql server can be forced to use the same plan for similar type of queries by
1. By changing database setting from simple parameterization to forced parameterization. This will replace the literal value in the queries to parameter. So when a new query with different predicate is fired it will reuse the parameterized query plan. The plan cache will have the parametrized query plan and  the shell query plans for similar query holding a pointer to the parameterized query plan. I will elobrate on this in one of the follow up blog.
 additional inforamtion on forced parameterization.

   http://technet.microsoft.com/en-us/library/ms175037.aspx 

2. Using prepared statements in java.  This will help in creating plans for parameterized queries and will be reused by the sql server. The only drawback of using prepared statement is that it adds an additional network round trip but doesn't increase the plan cache for similar queries.

We have implemented the forced parameterization and it boosted the app performance by 30 % and reduced the TokenAndPermUserStore cache growth from rate of 2 to 2.5 MB per 10 minutes to 600 kb per 10 minutes.

The first step

Mysql had been the only database we were supporting for a long time.This had served the company very well for a long time but was falling short for some potential customers as they did not support mysql in their data centers.


So we decided to support SQL server in addition to mysql. I will blog about the various issues we faced when adding new database support, the differences in sql syntax and how we were able to accomplish the support for SQL server in a relatively short time.

Our application is a three tier application with middleware being developed in java. The application server connects through jdbc to the database for DML operations. This helped us in supporting the new database vendor as we could intercept all the sql's passing through the jdbc driver and manipulating most of the sql's to conform with SQL server syntax.