How THP and Kernel Version effects MySQL Stability

During last year , i have faced multiple stall issue. MySQL Service was getting stalled any time, without leaving any clue in any logs. MySQL Service was getting restarted after 15 Minute of stall. We (Me and My Team ) were clueless.

Initially ,we were under impression, we may hit any bug. We gone through all MySQL bug tried multiple options.

1. Moving Redo Logs to magnetic Disk

2. optimizing flushing method/Thread

3. Optimizing all Database (Recreate using mysqldump and restore)

Still no Luck , we were still clueless.

We move forward and tried capturing all information using pt-stalk.

pt-stalk --user= --ask-pass --collect --daemonize --run-time=10 --sleep=10 --cycles=3 –dest= --log=

On Next failure we analyzed and still unable to find RCA. We were under impression that there are some queries which causing this behavior . We changed our focus and try to optimize all possible. Still we were facing random downtime. MySQL got stall and We need to restart as it stops responding.

We used oprofile with pt-stalk which lead us to issues with THP. We also got clue from Oliver's blog post

Debugging a MySQL stall

We disabled THP, one system got stable while anothe was still stalling randonly. We started using perf to get more deep in system calls.

% time     seconds  usecs/call     calls    errors syscall

------ ----------- ----------- --------- --------- ----------------

 70.81 1122.971901       27982     40132     14704 futex

 19.98  316.848873     1460133       217         6 restart_syscall

  5.70   90.315204       52448      1722           io_getevents

It lead us to futex Bug which was in kernel-2.6.32-504. According to Blooger

“The impact of this kernel bug is very simple: user processes can deadlock and hang in seemingly impossible situations. A futex wait call (and anything using a futex wait) can stay blocked forever, even though it had been properly woken up by someone.  If you are lucky you may also find soft lockup messages in your dmesg logs. If you are not that lucky (like us, for example), you'll spend a couple of months of someone's time trying to find the fault in your code, when there is nothing there to find.”
It was leaving is clueless every time. First we tested same by using strace. Which resumed MySQL process. Later we upgrdaed our Kernel version and MySQL service start working perfectly fine.
Detailed discussion about this bug is available at

https://groups.google.com/forum/#!searchin/mechanical-sympathy/futex/mechanical-sympathy/QbmpZxp6C64/BonaHiVbEmsJ



Learning

1. THP is not good for database.  
2. Linux expertise always for troubleshooting.
3. Start thinking outside of database for troubleshooting(MySQL always doesn't hit Bug :) )

How to scale inserts in MySQL with Innodb Engine

Usually we thinks/expect that inserts with large number of threads would to be faster but this is not always true(Due to MySQL limitations). The bottleneck might be the CPU, IO controller and OS. MySQL cannot fully use available cores/cpus  in server e.g. MySQL 5.0 (Innodb) is limited to 4 cores etc. We are working on scaling inserts, we have two data processing clusters each of which use 50 threads - so total 100 threads to  insert data into MySQL database (version 5.0.51). The issue, inserts are delayed by minute(s) and the backlog continues to grow... After examining innodb monitor status we found long list of transactions waiting for AUTO-INC lock: For example:


------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `alertxxxx/alertdata` trx id 0 685590474 lock mode AUTO-INC waiting ------------------ ---TRANSACTION 0 685590465, ACTIVE 10 sec, process no 8457, OS thread id 1169045824 setting auto-inc lock

Why AUTO-INC lock?

 When accessing the auto-increment counter, InnoDB engine uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. This basically causes all inserts into the same table to serialize. With single row inserts it is normally not too bad but could prevent scalability with multiple threads inserting Bug #16979. However, we can get better through put (inserts per second) with less  number of threads. So after dropping number of threads on both clusters by 50% initially - taking it to 20-20 sessions. The problem almost disappeared and when we further reduced number of threads to 10-10 sessions, the problem disappeared!

Beginning with MySQL 5.1.22 - new locking model introduced for handling Innodb auto-increment in InnoDB. There is a good article which talks about this MySQL Site and Blog

Similarly, if you want to achieve fast insert performance, it can be interesting to load files instead of the loading the inserts one by one : it is 3 to 4 times faster. If the goal is a huge amount of data already known at that time, it is probably the best option.

Optimization: We can’t use load files for all projects. We need to insert data row by row. For same we can optimize variables/Configuration.

1. Optimize database structure - Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster. Ensure columns have the right data types and require least amount of storage; you should try to use the most precise type in all cases. For example, if an integer column is used for values in the range from 1 to 99999, MEDIUMINT UNSIGNED is the best type. For more information about optimizing database structure click MySQL Site. If you store large strings (TEXT) or BLOB, compression may help there.

Use procedure analyse () function to check optimal data type based on table . But before changing data type, check your future plans, So it will not create a  problem in near future.

2. Innodb flush method - e.g. O_DIRECT, if used can help to avoid double buffering between the InnoDB buffer pool and the operating system's filesystem cache. MySQL reference manual explain this MySQL Site.

[ Warning]            O_DIRECT, serializes the writes in ext3. Howerver, impact can be lowered by using innodb_file_per_table)

3. Innodb thread concurrency - Keep the value low for this option variable (default 8 ok), however the correct value for this variable is dependent on environment and workload. This option variable is explained MySQL Site

4. Innodb buffer pool - Innodb maintains a buffer pool for caching data and indexes in memory. Making the pool larger can improve performance by reducing the amount of disk I/O needed, here is the detail about it.

5. Innodb log file size- The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash. As of MySQL 5.5, recovery times have been greatly improved and the whole log file flushing algorithm has been improved. In 5.5 you generally want larger log files as recovery is improved.

6. Filesystem selection and disk issues - 'xfs' is known to perform very well with MySQL. Also writing the redo logs, binary logs, data files in different physical disks is a good practice with a bigger gain than server configuration. RAID 10 is recommended for best performance, more detail about disk issue can be found at MySQL Site .

Calculate the size of innodb_buffer_pool_size and key_buffer_size

As a DBA sometime you will be working on Performance Optimization/Tuning/Configuration Optimization. You must be working to tune RAM of existing/New server. Keep in mind below points.

To working with transactional database you need to configure innodb_buffer_pool_size.  It will cache Indexes as well as data. Use below query to check the size.

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

If you are working with MyISAM engine, you can calculate size of key_buffer_size using the below query. It will provide you approximate size of key_buffer_size. It cache only MyISAM Indexes.

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;