SAN

If you are having application performance issues and how cab we figure out it is related to disk / SAN issues? If write cache is enabled on operating system / disk for DAS or on SAN, writes from a database should be very fast and with a consistent response.

The best metric in perf mon is to measure the avg disk sec / write i.e the amount of time it took for a write to be completed by the disk or SAN. Generally a write should be with in 1ms - 5ms [microsoft recommendation] especially for transaction log.

If the write response is jumping all over the place and is not consistent over time, its time to look into SAN or disk performance.

SQL Server - Forced paramaterization

Sql server has a database option called parameterization. If the option is set to forced, sql server automatically parametrizes the queries and saves the query plan, so that it can be re-used for similar kind of queries. This is very helpful especially for ad-hoc kind of work load.

At my work, we have this option on as our work load is primarily ad hoc and this has helped in keeping down our cpu utilization. Recently our database cpu usage started hitting 70-80%. During that time, the queries with IN clause seem to be taking the longest time to execute.The query plan looked fine. On further analysis setting the time statistics on, it was the compilation that was taking nearly 3-4 seconds.

When the same query was executed against database with simple parameterization, the compile time was between 100ms to 200ms.

The query had more then 1500+ elements in the IN clause. The process of parameterization these 1000+ elements was taking nearly adding 3-4 seconds to the compile time.

There are couple of options to tackle this.
1. create a plan guide for this specific kind of query and override the forced parameterization to simple.
2. Rewrite the query so as to not to use the IN clause. Move the IN clause elements into a table and convert the IN clause to a join.

First option was not feasible as its the elements in the IN clause was completely user controlled and the plan guide had to match the query exactly. So if the elements in the IN clause change, the plan guide was not useful.

After rewriting the query to a join and deploying the fix, brought down the cpu usage to 30-40%.

So if your cpu usage is high on the database server with forced parameterization option on, look for queries with IN clause.

Dns and ftp

We provide our software as SAAS offering and use ftp for handling documents. All of a sudden, we started having ftp trouble at one of our data center.
Then it spread to other data centers also.

When ever we had a issue with ftp, it would take nearly 30 second for the login prompt to come up. From the get go we suspected a dns issue but we weren't sure which functionality of the ftp server was causing it.

We turned off the reverse dns look up, then turned off logging and still it didn't help. we got a memory dump during the one of the outage and saw some calls for dns lookup but couldn't make much head way with out symbol files.

We switched to another ftp server product and the problem disappeared.
Finally the ftp server support came through and asked us to turn off additional flags that were causing dns look up. After we turned it off, the problem disappeared.

We had turned on SMTP on the ftp server for monitoring unauthenticated login and it was causing reverse DNS look up issue and even after turning off this feature, a bug in the ftp server that would cause dns reverse lookup even though all the reverse dns look up features were turned off.

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.