MySQL Server won't restart

MySQL Server won't restart

Today I went to restart MySQL from my SSH console with the following command:

/etc/init.d/mysql restart

However even though the database server stopped it wouldn't restart.

I tried opening another console and running the status command.

/etc/init.d/mysql status

But this just told me it was stopped and a start command kept failing.

Even when I went into my VirtualMin website that manages my virtual server the service wouldn't restart.

I dug into the services and databases and tried accessing a database from VMIN and saw a message saying the system couldn't retrieve a list of databases. Further digging gave me this error:

can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Now I knew I had changed some settings in the my.cnf configuration file the other day for performance tuning. So I searched the web and found that I had added a command that wasn't supported by my older version i.e MySQL 5.0.51.

The command in question was:

skip-external-locking

Because Java had crashed and I couldn't access the file to edit it easily.

I quickly ran a:

CHMOD 777 /etc/mysql/my.cnf 

command to allow me to edit the file from FTP and then I swapped the new command with the older one which my version of MySQL supported:

skip-locking

I copied the file back and then hey presto a start command got the server back and running:

/etc/init.d/mysql status

I then made sure to CHMOD the file back so it couldn't be written by the website.

However on checking my website I only got to see the theme but NO articles.

It was a Wordpress site and the problem was probably WP-Super-Cache caching a page without data. I needed to run a REPAIR command on my wp_posts table to ensure all posts were visible again.

This is something I have seen many times before with hard reboots. The system comes back up but no articles appear. I always REPAIR and OPTIMIZE my wp_posts and wp_posts_meta table to rectify this.

This obviously locked the database up, as well as consuming 99% CPU whilst it ran - something which really annoys me, but afterwards the site was working.

So if you have been performance tuning your own MySQL database make sure you are not adding commands that your server doesn't support. A failed restart is a sure sign of unsupported configuration commands.

Apache Performance Tuning BASH Script

BASH Script to tune Apache Configuration Settings

As you might know a lot of the time I think the LAMP / Wordpress combo is a big bag of shite.

There are so many configuration options, at so many different levels that need tuning to get optimal performance, it is a nightmare to find the right information. There is also too many people offering various solutions for Wordpress / Linux / Apache / MySQL configuration.

Different people recommend different sizes for your config values and just trying to link up server load with page/URL/script requests to find out the cause of any performance issue is a nightmare in itself.

I would have thought there would have been a basic tool out there that could log server load, memory, disk swapping over time and then link that up with the MySQL slow query log, Apache error AND access logs so that you could easily tell when you had issues what processes were running, which URL's were being hit and how much activity was going on to identify culprits for tuning. I have even thought of learning PERL just to write one - not that I want to!

Even with all the MySQL tuning possible, caching plugins installed and memory limits on potentially intensive tasks it can be a nightmare to get the best out of a 1GB RAM, 40GB Virtual Server that is constantly hammered by BOTS, Crawlers and humans. I ban over 50% of my traffic and I still get performance issues at various times of the day - why? I have no FXXING idea!

Without throwing RAM at the problem you can try and set your APACHE values in the config file to appropriate values for your server and MPM fork type.

For older versions of Apache the Multi-Processing Module, non-threaded, pre-forking webserver is well suited as long as the configuration is correct. However it can consume lots of memory if not configured correctly.

For newer versions (2+) the Worker MPM is better as each thread handles a connection at a time and this is considered better for high traffic servers due to the smaller memory footprint. However to get PHP working on this setting apparently needs a lot of configuration and you should read up about this before considering a change.

Read about Apache performance tuning here Apache Performance Tuning.

To find out your current apache version from the console run

apache2 -v OR httpd -v (depending on your server type, if you run top and see apache2 threads then use apache2 otherwise use httpd)

You will get something like this.

Server version: Apache/2.2.9 (Debian) Server built: Feb 5 2012 21:40:20

To find out your current module configuration from the console run

apache2 -V OR httdp -V

Server version: Apache/2.2.9 (Debian)
Server built: Feb 5 2012 21:40:20
Server's Module Magic Number: 20051115:15
Server loaded: APR 1.2.12, APR-Util 1.2.12
Compiled using: APR 1.2.12, APR-Util 1.2.12
Architecture: 64-bit Server
MPM: Prefork threaded: no forked: yes (variable process count)
etc etc etc...

There are lots of people giving "suitable" configuration settings for the various apache settings but one thing you need to do if you run TOP and notice high memory usage and especially high virtual memory usage is try and reduce disk swapping.

I have noticed that when Apache is consuming a lot of memory that your virtual memory (disk based) will be high and you will often experience either high server loads and long wait times for pages to load OR very small server loads e.g 0.01-0.05, an unresponsive website and lots of MySQL Server Gone Away messages in your error log file.

You need to optimise your settings so that disk swapping is minimal which means trying to optimise your MySQL settings using the various MySQL tuning tools I have wrote about as well as working out the right size for your Apache configuration values.

One problem is that if you use up your memory by allowing MySQL to have enough room to cache everything it needs then you can find yourself with little left for Apache. Depending on how much memory each process consumes you can easily find that a sudden spike in concurrent hits uses up all available memory and starts disk swapping.

Therefore apart from MySQL using the disk to carry out OR caching large queries you need to find the right number of clients to allow at any one time. If you allow too many and don't have enough memory to contain them all then the server load will go up, people will wait and the amount of disk swapping will increase and increase until you enter a spiral of doom that only a restart fixes.

It is far better to allow fewer connections and serve them up quickly with a small queue and less waiting than open too many for your server to handle and create a massive queue with no hope of ending.

One of the things you should watch out for is Twitter Rushes caused by automatically tweeting your posts to twitter accounts as this can cause 30-50 BOTS to hit your site at once. If they all consume your memory up then it can cause a problem that I have wrote about before.

Working out your MaxClients value

To work out the correct number of clients to allow you need to do some maths and to help you I have created a little bash script to do this.

What it does is find out the average size of an Apache thread then restarts Apache so that the correct "free size" value can be obtained.

It then divides the remainder by the Apache process size. The value you get should be roughly the right value for your MaxClients.

It will also show you how much disk swapped or virtual memory you are using as well as the size of your MySQL process.

I noticed on my own server that when it was under-performing I was using twice as much disk space as RAM. However when I re-configured my options and gave the system enough RAM to accommodate all the SQL / APACHE processes then it worked fine with low swapping.

Therefore if your virtual memory is greater than the size of your total RAM e.g if you are using 1.5GB of hard disk space as virtual memory and only have 1GB of RAM then it will show an error message.

Also as a number of Apache tuners claim that your MinSpareServers should be 10-25% of your MaxClients value and your MaxSpareServers value 25-50% of your MaxClientsValue I have also included the calculations for these settings as well.

#!/bin/bash
echo "Calculate MaxClients by dividing biggest Apache thread by free memory"
if [ -e /etc/debian_version ]; then
 APACHE="apache2"
elif [ -e /etc/redhat-release ]; then
 APACHE="httpd"
fi
APACHEMEM=$(ps -aylC $APACHE |grep "$APACHE" |awk '{print $8'} |sort -n |tail -n 1)
APACHEMEM=$(expr $APACHEMEM / 1024)
SQLMEM=$(ps -aylC mysqld |grep "mysqld" |awk '{print $8'} |sort -n |tail -n 1)
SQLMEM=$(expr $SQLMEM / 1024)
echo "Stopping $APACHE to calculate the amount of free memory"
/etc/init.d/$APACHE stop &> /dev/null
TOTALFREEMEM=$(free -m |head -n 2 |tail -n 1 |awk '{free=($4); print free}')
TOTALMEM=$(free -m |head -n 2 |tail -n 1 |awk '{total=($2); print total}')
SWAP=$(free -m |head -n 4 |tail -n 1 |awk '{swap=($3); print swap}')
MAXCLIENTS=$(expr $TOTALFREEMEM / $APACHEMEM)
MINSPARESERVERS=$(expr $MAXCLIENTS / 4)
MAXSPARESERVERS=$(expr $MAXCLIENTS / 2)
echo "Starting $APACHE again"
/etc/init.d/$APACHE start &> /dev/null
echo "Total memory $TOTALMEM"
echo "Free memory $TOTALFREEMEM"
echo "Amount of virtual memory being used $SWAP"
echo "Largest Apache Thread size $APACHEMEM"
echo "Amount of memory taking up by MySQL $SQLMEM"
if [[ SWAP > TOTALMEM ]]; then
      ERR="Virtual memory is too high"
else
      ERR="Virtual memory is ok"
fi
echo "$ERR"
echo "Total Free Memory $TOTALFREEMEM"
echo "MaxClients should be around $MAXCLIENTS"
echo "MinSpareServers should be around $MINSPARESERVERS"
echo "MaxSpareServers should be around $MAXSPARESERVERS"

If you get 0 for either of the last two values then consider increasing your memory or working out what is causing your memory issues. Either that or set your MinSpareServers to 2 and MaxSpareServers to 4.

There are many other settings which you can find appropriate values for but adding indexes to your database tables and ensuring your database table/query caches can fit in memory rather than swapped to disk is a good way to improve performance without having to resort to more caching at all the various levels Wordpress/Apache/Linux users love doing.

If you do use a caching plugin for Wordpress then I would recommend tuning it so that it doesn't cause you problems.

At first I thought WP SuperCache was a solution and pre-caching all my files would speed things up due to static HTML being served quicker than PHP.

However I found that the pre-cache stalled often, caused lots of background queries to rebuild the files which consumed memory and also took up lots of disk space.

If you are going to pre-cache everything then hold the files as long as possible as if they don't change there seems little point in deleting and rebuilding them every hour or so and using up SQL/IO etc.

I have also turned off gzip compression in the plugin and enabled it at Apache level. It seems pointless doing it twice and PHP will use more resources than the server.

The only settings I have enabled in WP-Super-Cache at the moment are:

• Don’t cache pages with GET parameters. (?x=y at the end of a url) 
• Cache rebuild.
• Serve a supercache file to anonymous users while a new file is being generated. 
• Extra homepage checks. (Very occasionally stops homepage caching)
• Only refresh current page when comments made. 
• Cache Timeout is set to 100000 seconds (why rebuild constantly?)
• Pre-Load - disabled.

Also in the Rejected User Agents box I have left it blank as I see no reason NOT to let BOTS like googlebot create cached pages for other people to use. As bots will most likely be your biggest visitor it seems odd to not let these BOTS create cached files.

So far this has given me some extra performance.

Hopefully the tuning I have done tonight will help the issue I am getting of very low server loads, MySQL gone away errors and high disk swapping. I will have to wait and see!

Strictly TweetBOT Version 1.1.2 Released

Version 1.1.2 of the Wordpress Plugin - Strictly TweetBot released

New features and bug fixes include:

• Fixed bug with some words in the TweetShrink function.
• Added new short versions of words for the TweetShrink function.
• I have improved the performance of the loop that controls the tweets by only getting the permalink for the new post once and not on every loop iteration as before.
• I have added an option to make an HTTP request to the new post before tweeting so that if caching plugins are installed and their caching options are enabled correctly the page will get cached before any Twitter rush from BOTS visiting the post. I have proved that at least 50+ (growing all the time) requests to any link in a new Twitter post can concurrently occur when a Tweet is posted.
• I have added an option to use a different user-agent when making this HTTP request so that you can easily identify the request in your access log files for debugging etc.
• I have also added a new configuration test to the "Config Test" button which tests the HTTP Cache test by posting the last article posted and returning a status code for it.

Caching new posts before any Twitter Rush

The main feature of this release is the ability to fire off an HTTP request to the post being Tweeted about before any Tweets are sent.

The idea is that if you are using any number of caching systems (properly configured obviously - and every caching system is different) the first page request will be from the TweetBot and hopefully cause that page to be cached by the system you are using e.g WP Super Cache, W3 Total Cache, Apache Caching modules etc.

The aim is that by caching the post before any Tweets are sent that any Twitter Rush that occurs when your link hits Twitter does not overload it by having 50+ BOTS all concurrently hitting the page and competing to be the first visit to cause the page to be cached for future visitors.

Obviously if you are logged in as admin when writing your post then this might not cause a cache in some systems. For example WP Super Cache has an option to prevent logged in users from seeing cached pages. Therefore if you are logged in when you write a post this automated HTTP request might not cache the page if you have set the option on NOT to serve cached pages for logged in users.

However if you are auto-blogging and importing posts from feeds using WP Robot, WP-O-Matic or any other system then you will not be logged in and this HTTP request should create a cached page for BOTS to hit.

As I said, it all depends on the caching system you are using and how you configure it.

Remember every caching system is different and it all depends on the options you configure in the admin area of your own particular caching system. So make sure it is configure correctly to allow for the first visit to any post to be cached.

So version 1.1.2 of Strictly-Tweetbot is now live and you can download it from my main site or from the Wordpress plugin repository whenever you want.

Another MySQL Tuning Tool

MySQL Configuration and Tuning

One thing I really don't like about MySQL compared to MS SQL is the number of configurable options and the lack of data management  views to help you diagnose performance issues. Yes there are tools available but I haven not come across anything as good as the Activity Monitor for an overview of your servers performance in MySQL.

Being able to quickly see the primary cause of performance issues in graph form, high CPU, memory, I/O, blocking, the processes causing the blocking and those effected by the blocks, performance intensive queries and so on in a visual format is very useful.

Another great thing about MS SQL is the amount of Data Management Views and the reports you can create to find the most expensive queries, those with missing indexes, those that require tuning or index rebuilding, query plan re-use or under-user etc are all very useful tools.

Therefore when I come across anything that is semi useful for performance tuning MySQL I will make a note of it and list it on this blog so that other Microsoft developers using LAMP, WAMP etc can benefit from them as well.

One tool I came across tonight which has gone into my rackspace server alongside other MySQL configuration analysers is the MySQL Performance Tuning Primer Script which along with others of a similar ilk will analyse your database settings from a SHOW /*!50000 GLOBAL */ STATUS command and then make recommendations that you can use in your MySQL.cnf file.

To install this script you will need to do the following.

1. Open a SSH console window up.
2. Move to the right folder e.g cd /usr/local/src/
3. Use WGet to load the file to your server e.g wget http://day32.com/MySQL/tuning-primer.sh
4. Grant execute permission to the script e.g chmod u+x tuning-primer.sh
5. Try running the script e.g ./tuning-primer.sh

You might get an error like I did on the first attempt which said:

Error: Command line calculator 'bc' not found!

If you don't know bc is (which I didn't) it is the command line arbitrary precision calculator from GNU and it is obviously used within the shell script we are trying to run.

Therefore you will need to install this as well by using apt-get. So run this:

apt-get install bc

Which will install the bc app.

Now try again e.g

/usr/local/src# ./tuning-primer.sh

And you should get something like this:

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
Could not auto detect login info!
Found potential sockets: /var/run/mysqld/mysqld.sock
Using: /var/run/mysqld/mysqld.sock
Would you like to provide a different socket?: [y/N] n
Do you have your login handy ? [y/N] : y
User: [enter your username e.g root or the password for the DB in question]
Password: [enter your password]

You will then get a report like the following and the option to create a new MySQL configuration file if you require it.


Would you like me to create a ~/.my.cnf file for you? [y/N] : n


-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.51a-24+lenny5-log x86_64

Uptime = 7 days 23 hrs 32 min 26 sec
Avg. qps = 10
Total Questions = 7286512
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2 sec.
You have 178496 out of 7286569 that take longer than 2 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 15
The number of used connections is 15% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 153 M
Configured Max Per-thread Buffers : 262 M
Configured Max Global Buffers : 114 M
Configured Max Memory Limit : 376 M
Physical Memory : 1.01 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 221 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 3110
Key buffer free ratio = 14 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 40 M
Current query_cache_used = 20 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 50.83 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 89958 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 200 tables
You have a total of 146 tables
You have 200 open tables.
Current table_cache hit rate is 6%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 985542 temp tables, 44% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 998 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1205
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables consider setting 'concurrent_insert=2'.

Then you can take the recommendations and change the MySQL configuration file yourself in /etc/mysql/my.cnf before restarting the MySQL server so that the changes take effect e.g.


/usr/local/src# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Just another MySQL report in the same ilk as mysqltuner.pl and mysqlreport but still not as good as anything I have seen on MS SQL 2005-2008.

Low Server Usage, Long Page Loads with APACHE Caching

Disabling Apache Caching improves LAMP Server Performance


On one of my virtual servers which runs LAMP on only a 1GB RAM box with a couple of quite high traffic sites I have been experiencing an intermittent problem which had the following symptoms.

1. Long page load wait time - Spinning browser window with nothing happening
2. Very low server load average < 10 despite large number of requests to website
3. Large amount of disk swapping - where the hard drive is used to temporarily store data

This also coincided with these errors in the log files

[Thu Feb 02 16:30:57 2012] [error] (103)Software caused connection abort: cache: error returned while trying to return mem cached data

Running a TOP command would return something like this

op - 16:42:06 up 3 days, 21:29,  2 users,  load average: 0.12, 0.39, 0.67, 1.39
Tasks: 219 total,   1 running, 218 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1060972k total,   931768k used,   129204k free,     9668k buffers
Swap:  2097144k total,   328740k used,  1768404k free,   362284k cached

An APACHE restart always fixed this issue temporarily however on running

/etc/init.d/apache2 restart

I noticed it was carrying out an htcacheclean command and it was always on this clean commands completion that the page load would suddenly spin into action before terminating as the web-server stopped.

Both websites that ran on the server were using Wordpress systems that utilised caching plugins, namely WP-SuperCache and WP-Widget-Cache as well as .htaccess file rules that set future expiry headers on certain file types e.g JavaScript, CSS, Images etc.

I also ban over 55% of all traffic through rules that block known bad BOTS, spammers, hackers and known bad IP addresses. If you are not writing articles in Chinese or Russian and have no wish to pay for the bandwidth caused by BOTS like Yandex and Baiduspider to visit constantly then 403 them! They both pay no attention to the Robots.txt file so in my book can be labelled as "Bad BOTS" anyway.

As the main symptom was very low server loads AND hanging pages that were cured by running htcacheclean it made sense to try something recommended by someone I was in contact with at Tiger Technologies who recommended disabling the APACHE caching.

He recommended this because I was already utilising other caching technologies at the Wordpress plugin level and the errors in the log file suggested some sort of problem with APACHE's caching system. Apparently this is not a default option to be enabled and although I have no recollection of enabling it as I didn't set the server up it was obviously turned on.

After checking all the config files to ensure I wasn't making specific use of these Apache modules I disabled the following Apache Modules: disk_cache file_cache mem_cache with the following command in my console.

a2dismod

These all had to be disabled first before I could disable the main cache module that they all depended on.

After disabling all these caching modules I ran the following command to check that the syntax of all my Apache config files were correct and still in working order.

apache2ctl configtest


This returns "Syntax OK" if everything is okay.

A restart of Apache now didn't run the htcacheclean command and afterwards the problem seemed to dissipate.

One thing I have noticed coming from a Windows environment is just the pure amount of places that you can enable caching from the web-server level to MySQL, Wordpress, Plugins that make use of static files, PHP Accelerators such as APC and memcache for those with RAM to spare.

There are an awful lot of configuration possibilities with a LAMP setup and it seems that APACHE's own caching system was causing the problem probably due to the amount of memory it required for a busy site which was the cause of the large amount of disk swapping that was going on.

Maybe if I had a large amount of RAM to spare this wouldn't have been a problem but as I have shown in previous articles related to Twitter Rushes caused by large concurrent BOT visits after Tweeting links if you don't have lots of RAM and haven't lowered your MaxClients setting to an appropriate level from the default of 256 to 12-25, a 1GB RAM server can run out of memory very quickly indeed.

However whilst the Twitter Rush / MaxClients problem is easily detected by high server loads, a look at the visitor log file after Tweeting plus some maths to divide your RAM by the average amount of memory used by a page load plus any other applications that constantly run such as MySQL. This configuration issue was a bit harder to work out due to the fact I was experiencing very low server loads and thought I had done the right thing by utilising various caching techniques.

The Apache caching was obviously causing my server an issue and as always being able to methodologically diagnose a problem by stepping through the various possibilities ruling them out one by one is a technique that always works. 

Speeding up batch SQL Processes that use temporary table variables

Problems with TABLE variables and how to improve performance for SQL Batch Jobs


We recently moved our production system to SQL 2008. Everything seemed okay until we realised after a fortnight that a particular MS Agent job had not been completing correctly.

One of the steps within the job had been failing due to a missing SQL Login. This job transferred large amounts of data from daily tables to historical ones and a large backlog had now been created numbering in the tens of millions.

Due to the use of our batch delete process that removed data that had been transferred in small chunks (500 rows) to reduce locking the data had got to a stage where new records were being added almost as fast as we were removing them.

When I looked at the query that was doing the insert and then delete I saw that it was using a temporary table variable to hold the temporary data whilst it was being transformed.As this was now 20 million plus records it was a problem on it's own due to it's limitations.

This is a common SQL Performance bottleneck and one I have come across a few times now.

SQL's TABLE variables are very good for small datasets (a few hundred at most) and are very useful for array like usage within stored procedures. However when you move to large amounts of records they just become a performance nightmare.

Unlike proper temporary tables or fixed permanent tables you cannot add indexes to TABLE variables and with large record sizes this is usually a must. The constant table scans the process must have been having to do to find rows must have been a major cause of the slow performance.

I rewrote the stored proc to make use of a temporary table, added a clustered index on the columns I was using for my joins to the real table and I used a batch DELETE process using the TOP (@X) statement to remove old records.

This has sped the process up immensely but the automatic creation of indexes and creation and dropping of tables requires permissions higher than those that the website login I use for my site has.

Therefore to ensure everything ran smoothly I had to use the WITH EXECUTE AS 'login' statement to allow the user to impersonate a login with higher privileges.

A cut down example of the proc is below and it shows the following:

1. Impersonating logins with higher permissions to allow for DDL statements e.g CREATE and DROP.
2. Checking TEMP DB for existing temporary tables and indexes and dropping them if they exist.
3. Deleting large numbers of records in batches to prevent blocking.
4. Ensuring a SARGABLE where clause is used instead of DATEDIFF to get yesterdays data

CREATE PROCEDURE [dbo].[usp_sql_job_data_transfer] 
 @Success BIT = 0 OUTPUT,
 @RowsCopied INT = 0 OUTPUT,
 @RowsDeleted INT = 0 OUTPUT

WITH EXECUTE AS 'admin_user'
AS
  BEGIN

  SET NOCOUNT ON
  SET DATEFORMAT YMD

  DECLARE @Stamp datetime, 
   @InsertDate datetime,
   @TmpRows int,
   @Rows int,  
   @RowsInserted int,
   @error int
   

  -- check for existance of old temp table, a local temp table wont persist but we may need to use a global one (Example usage)
  IF object_id('tempdb..#JobHits') IS NOT NULL
    BEGIN
    -- drop it
      
    DROP TABLE #JobHits
    END 
 
  -- create temp table   
  CREATE TABLE #JobHits(
   JobFK int, 
   Stamp Datetime, 
   ViewHits int, 
   SearchHits int
  )

  -- job runs after midnight and we want a SARGABLE WHERE clause
  SELECT @Stamp = CONVERT(datetime,CONVERT(varchar,getdate(),23))

   
  SELECT @TmpRows = 0, @RowsInserted = 0, @RowsDeleted = 0  

  -- insert into my temp table the search hits by date (SQL 2005)
  -- if this proves to be still slow or causing blocks then try inserting in batches of 1000 OR 20 min chunks
  INSERT INTO #JobHits
  (JobFK, Stamp, ViewHits, SearchHits)
  SELECT JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23)),0, count(jobFk) 
  FROM JOBS_DATA with (nolock)
  WHERE HitType = 'S' 
   AND Stamp < @Stamp
  GROUP BY JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23))

  SELECT @TmpRows = @@ROWCOUNT, @Error = @@ERROR
  
  IF @Error <> 0
    GOTO HANDLE_ERROR

  -- insert into my temp table view hits
  INSERT INTO #JobHits
  (JobFK, Stamp, ViewHits, SearchHits)
  SELECT JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23)),count(jobFk), 0
  FROM JOBS_DATA with (nolock)
  WHERE HitType = 'V' AND 
   Stamp < @Stamp -- SARGABLE WHERE CLAUSE - note I am not using DATEDIFF
  GROUP BY JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23))

  SELECT @TmpRows = @TmpRows + @@ROWCOUNT, @Error = @@ERROR


  -- if an error occurred jump to the error handler
  IF @Error <> 0 
    GOTO HANDLE_ERROR
  --If no error but no rows then its just a bad day for the site with no hits
  ELSE IF @TmpRows = 0  
    GOTO EXIT_PROC
  ELSE
    BEGIN
     
    -- add an index to aid lookups and searching 

    -- ensure no record exists already in Temp DB
    IF object_id('tempdb..clidx_#JobHits') IS NOT NULL 
      BEGIN
      
     DROP INDEX clidx_#JobHits ON #JobHits

      END

       
    -- Add a clustered index to help searching - cover the main join column JobFk and group by column Stamp 
    CREATE CLUSTERED INDEX [clidx_#JobHits] ON #JobHits
    (
     [JobFK] ASC,
     [Stamp] ASC    
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    
    
    END
  
  
  -- join my temp table to the main table to get info about the company the job belonged to
  INSERT INTO JOBS_DATA_HISTORY
  (JobFk,Stamp,ViewHits, SearchHits, ClientFK)
  SELECT a.JobFK, a.Stamp, Sum(a.ViewHits), Sum(a.SearchHits), j.ClientFK
  FROM  #JobHits as a
  JOIN  JOBS as j with (nolock)
   ON  a.JobFk = j.JobPK
  GROUP BY a.JobFK, a.Stamp, j.ClientFk

  SELECT @RowsInserted = @@rowcount, @error = @@ERROR
  

  --if we are here then there must be > 0 rows otherwise we would have exited earlier
  IF @RowsInserted=0 or @Error<>0
    GOTO HANDLE_ERROR
 

  -- Now we have copied our data we need to delete it all from the daily table
  -- as this table is being used by the site still we delete in batches to prevent blocking locks
  SELECT @Rows = 1
  
  -- loop until no more rows are left
  WHILE @Rows > 0
    BEGIN 
    -- delete data in table using the TOP command to ensure we only delete on our indexed column in batches
    DELETE TOP(1000)
    FROM JOBS_DATA
    WHERE Stamp < @Stamp -- indexed column in JOBS_DATA
      
    SELECT @Rows = @@ROWCOUNT, @RowsDeleted = @RowsDeleted + @Rows, @Error = @@ERROR

    -- should i wait for a second in between batches? cannot find a definitive answer
    -- if problems occur (which they haven't so far) then try this
    --WAITFOR DELAY '00:00:01' 
    
    IF @Error <> 0 
      GOTO HANDLE_ERROR
    END

  --if no rows were deleted then something went pete tong
  IF @RowsDeleted=0
    GOTO HANDLE_ERROR
  END

-- clean up
CLEAN_UP:
    
   -- add an index to aid lookups and searching (not sure if when table is dropped the index record remains or not so being safe - check this!)
  IF object_id('tempdb..clidx_#JobHits') IS NOT NULL 
    BEGIN
  -- drop our clustered index
  DROP INDEX clidx_#JobHits ON #JobHits

    END
    
  IF object_id('tempdb..#JobHits') IS NOT NULL
    BEGIN
  -- drop our temp table  
  DROP TABLE #JobHits
    END 
  
  
  -- jump over error handler and exit 
  GOTO EXIT_PROC
  

--handle error
HANDLE_ERROR:

  SELECT @Success = 0

  IF @FromProc = 0
    SELECT @Success as success, COALESCE(@RowsCopied,0) as rowscopied,COALESCE(@RowsDeleted,0) as rowsDeleted

  -- exit
  RETURN 0



--handle a successful exit
EXIT_PROC:
  SELECT @Success = 1, @RowsCopied = @RowsInserted

  -- exit
  RETURN 1

Proving the benefits of SARGABLE Clauses and converting DATEDIFF to BETWEEN in SQL

Increasing performance with date range searches in SQL

I often find myself wanting to retrieve data from a table for the current day only.

There are many ways to do this but one of the easiest and the one I often find people using is to use
a non SARGABLE WHERE clause that makes use of the DATEDIFF function to only return records for the current date e.g

SELECT *
FROM BLAH
WHERE DATEDIFF(DAY,Stamp,GETDATE())=0

The problem with this is related to performance and whilst you might see negligible effects on small datasets
when you are dealing with datasets containing millions of rows you want the best performance possible.

If you don't know what a SARABLE clause is here is the definition:

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

Therefore because we have wrapped the Stamp (Date) column in the DATEDIFF function the clause becomes NON SARGABLE and an INDEX SCAN will be carried out instead of the performance benefits
that an INDEX SEEK would provide.

Obviously if you don't have an index on the data column in question it won't make the slightest bit of difference but lets test this theory.

First we want some code that will give us the start and end date of the current day.

I am using code for SQL 2005 and below here as in SQL 2008 there are specific DATE datatypes
that don't contain time parts but running a simple:

SELECT GETDATE()

-- returns
2011-10-27 15:01:39.000

As we want to replace the DATEDIFF with a BETWEEN @STARTDATE AND @ENDDATE we need to create the minimum date for the day and the maxiumum date for the day.

We can do this using some basic CAST conversions that first create the date for today at midnight e.g 2011-10-27 00:00:00:000 and then once we have that we can add the appropriate number of milliseconds to the date to give us the maximum end date e.g 2011-10-27 23:59:59.997.

To calculate the number of milliseconds we need to add is simple we multiple 60 seconds * 60 minutes * 24 hours * 1000 milliseconds and then deduct 3 (as 997 is the biggest value allowed in TSQL) e.g:

SELECT (60 * 60 * 24 * 1000)-3

The code to get the start and end date for the current day in TSQL is below.

DECLARE @StartDate DATETIME,
 @EndDate DATETIME
  
-- quickly convert our current datetime into the current date at midnight e.g 2011-10-27 00:00:00.000
-- by casting the date as a varchar(12) cutting it off at the space to skip the time part then casting it back to a datetime
SELECT @StartDate = CAST(CAST(GETDATE() as varchar(11)) as datetime)

-- add 86399997 milliseconds to get a nice 23:59:59:997 datetime
SELECT @EndDate = DATEADD(MS,86399997,@StartDate)    

-- run to test
SELECT  @StartDate, @EndDate 

And you will get 2011-10-27 00:00:00.000 and 2011-10-27 23:59:59.997.

Now to prove that doing a search this way is better for performance we pick a large table, ensure there is an index on the date column we are using and write a test harness.

Remember to turn on the "Include Actual Execution Plan" option on, and make sure that we clean out all the cache buffers before running our test.

Using some simple datediff calculations to time each SELECT we can run the following code.

SET DATEFORMAT YMD
SET NOCOUNT ON

DECLARE @START DATETIME, 
 @END DATETIME,   
 @DUR INT
  
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT @START = GETDATE()

SELECT Racedatetime
FROM RACES
WHERE DATEDIFF(DAY,Racedatetime,GETDATE())=0 -- only get data for current day

SELECT @END = GETDATE(),
 @DUR = DATEDIFF(MS,@START,@END)

SELECT  'Query 1 with non SARGABLE DATEDIFF function took ' + CAST(@DUR as varchar) + ' milliseconds to run'


SELECT @START = GETDATE()

-- hardcoding values for an example but put our code from above to get the start and end of the current day here
SELECT Racedatetime
FROM RACES
WHERE Racedatetime BETWEEN '2011-10-27 00:00:00:000' AND '2011-10-27 23:59:59:997'

SELECT @END = GETDATE(),
 @DUR = DATEDIFF(MS,@START,@END)

SELECT  'Query 2 with SARGABLE WHERE Clause took ' + CAST(@DUR as varchar) + ' milliseconds to run'

Viewing the execution plan for the query you can see the difference in execution plans between the two queries.

The Non SARGABLE Query that used the DATEDIFF made use of an INDEX SCAN to obtain the result as it had to loop through the dataset running the DATEDIFF function on each Racedatetime column to see if the result was 0 (today).

The SARGABLE Query didn't have to do this and made use of the correct INDEX and no function needed to be applied to each column.

You can see the benefit in the query cost between the two SELECT statements in the following screenshot of the execution plan.



Statement one that used a NON SARGABLE DATEDIFF clause took 673 milliseconds to run (on a table with 567,031 records) and had a cost of 96% and the statement that used a SARGABLE BETWEEN clause took 30 milliseconds to run and had a cost of 4%



Hopefully you can see from this example why it pays to make your queries as optimal as possible.

For more SQL Tips you can read an old Top SQL Performance Tips article I wrote some time back which still has lots of useful tips on performance tuning your queries.


And for analysing bottle necks and problematic queries you should view my SQL performance tuning script which will identify 14+ different areas which could be improved from query plan re-use, fragmented indexes, high CPU, and missing indexes plus lots more.