MySQL 5.6 - Online DDL Operations

MySQL 5.6 supports several kinds of Online DDL operations on Innodb tables, so it is now possible to perform DML operations such as insert, update, delete and select operations on a table while DDL operation on the subject table is in progress. For more details I strongly advise you to read MySQL docs here and here. In this Blog I would demonstrate few quick examples:

a) Remove column from table 't1' -  DML operations can proceed while DDL is in progress i.e. ALTER TABLE .. DROP column..

mysql >show processlist;
+-----+------+-----------+------+---------+------+----------------+----------------------------------+
| Id  | User | Host      | db   | Command | Time | State          | Info                             |
+-----+------+-----------+------+---------+------+----------------+----------------------------------+
|  67 | root | localhost | test | Query   |    0 | init           | show processlist                 |
| 604 | root | localhost | test | Query   |    3 | altering table | alter table t1 drop column data2 |
+-----+------+-----------+------+---------+------+----------------+----------------------------------+
2 rows in set (0.00 sec)

mysql >insert into t1 values (null,'xxxx','yyyy');
Query OK, 1 row affected (0.00 sec)

mysql >select * from t1 where id=2;
+----+----------------------------------------------------+-------+
| id | data                                               | data2 |
+----+----------------------------------------------------+-------+
|  2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | a     |
+----+----------------------------------------------------+-------+
1 row in set (0.00 sec)

b) Add new column table 't1' -  DML operations can proceed as long as they do not access the new column that is being added to the table:

mysql>show processlist;
+-----+------+-----------+------+---------+------+----------------+---------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State          | Info                                                    |
+-----+------+-----------+------+---------+------+----------------+---------------------------------------------------------+
|  67 | root | localhost | test | Query   |    0 | init           | show processlist                                        |
| 604 | root | localhost | test | Query   |   38 | altering table | alter table t1 add column data2 varchar(20) default 'a' |
+-----+------+-----------+------+---------+------+----------------+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>insert into test.t1 values (null,'xxxx');
Query OK, 1 row affected (0.02 sec)

mysql>insert into test.t1 values (null,'xxxx','yyyy');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Replication checksums in MySQL 5.6

Replication checksums is one of the listed new features of MySQL 5.6 , but data integrity might break as documented here replication-checksum. In reality this feature is not completely new to MySQL, in MySQL 5.5, and earlier replication checksums can be used by using SSL replication as a workaround. You can learn more about SSL replication here. Similarly you can see my blog post about SSL connections with MySQL here.
Please note, by default replication checksums are enabled. However following variables can be used to enable/disable checksums with master/relay logs:

a) Binlog_checksum - Its a dynamic variable. The default value is CRC32.  This variable causes the master to write a checksum for each event in the binary log

When enabled binary log provides the following information:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

#130225 15:33:56 server id 1 end_log_pos 384 CRC32 0x280a5e52 Intvar SET INSERT_ID=1/*!*/; #130225 15:33:56 server id 1 end_log_pos 499 CRC32 0x37e25d0a Query thread_id=1 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1361806436/*!*/; insert into t1 values (null, 'mysql 5.6') /*!*/; # at 499 #130225 15:33:56 server id 1 end_log_pos 530 CRC32 0xf56d77c4 Xid = 17 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The extra/new information that we see the above output such as

CRC32 0x280a5e52 

On the slave host:
b) slave_sql_verify_checksum: Its ENABLED by default. However, as per online docs (here),  this option is disabled by default, so I take it as documentation bug. When this option is ENABLED, slave host would examine and verify checksums when reading the relay log.

show global variables like 'slave_sql_verify_checksum';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| slave_sql_verify_checksum | ON    |
+---------------------------+-------+
1 row in set (0.00 sec)

Innodb purging stalls

One of my clients reported an incident where they are getting 'Table Full' error against Innodb table. However, there seem to be ample free Innodb space available. So, when I looked at host server, I found they are running old mysql 5.0 release on 32bit platform, Innodb monitor output show:

History list length 2051529 
..
Main thread process no. 9701, id 1111792528, state: sleeping


Oh Innodb purge thread isn't running, and there is no change in "history length".
Note: Purge thread never progresses when the main thread is in a 'sleep' state.

Related BUG 13847885. Quoting:
 Deleting a huge amount of data from InnoDB tables within a short time could cause the purge operation that removes delete-marked records to stall. This issue could result in unnecessary disk space use, but does not cause any problems with data integrity. If this issue causes a disk space shortage, restart the server to work around it. This issue is only likely to occur on 32-bit platforms
Bug fixed in 5.0.97, 5.1.63, 5.5.23


Solution: Restart MySQL and upgrade MySQL version

MySQL - working with xml data

MySQL (5.1 and onward) provide XML functions; such as ExtractValue() that can be helpful to read required elements from xml; instead of returning entire xml back to the client application we can return one (or more) xml elements containing required data.

ExtractValue(xml_fragment, xpath_expression)

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
In the following example, we use ExtractValue() with column content as the first argument. For the second argument, we employ an XPath expression that means “the content of the element having the name attribute "bankName"”:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName" from xml_test where id=2;  
 +----------+  
 | bankName |  
 +----------+  
 | xyz      |  
 +----------+  
 1 row in set (0.00 sec)  

 We can read more than one attribute:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName", ExtractValue(content, '//variable[@name="productType"][1]') "productType" from xml_test where id=1;  
 +----------+-------------+  
 | bankName | productType |  
 +----------+-------------+  
 | xyz      | cvv=***     |  
 +----------+-------------+  
 1 row in set (0.00 sec)  

Similarly we can extract all attributes of a particular xml element 

 SELECT ExtractValue(content,'//dictionary[@name="client_b"]/*') as "all attributes" from xml_test where id=2;  
 +----------------+  
 | all attributes |  
 +----------------+  
 | abc cvv=***    |  
 +----------------+  
 1 row in set (0.00 sec)  

MySQL - capturing diagnostic data

Sporadic problems such as occasional server stalls can be difficult to diagnose. It is important to capture good diagnostic data when the problem is clearly happening. And it gets frustrating when you don't know when next it will hit your database. So, we need to find a way to know when problem  happens,Let's call it 'diagnostic trigger'. We need to find good indicator e.g. sudden sever's load spike and/or spike in status varaibles such as Threads_connected, Threads_running.
 Thanks to perconna Toolkit, pt-stalk - Watches for a trigger condition to become true, and then collects data to help in diagnosing problems. I performed few quick test using pt-stalk:
Installation:
At the time of writing this article percona-toolki2.1.2 is the latest release:
wget http://www.percona.com/downloads/percona-toolkit/2.1.2/percona-toolkit-2.1.2-1.noarch.rpm

You might have to install following required by percona-toolkit:
yum install perl-Time-HiRes.x86_64
yum install perl-TermReadKey.x86_64

Quick Examples:

A) Collect diagnostic data when at least 40 threads are running,

# pt-stalk  --function status --variable Threads_running \
--threshold 40 --prefix=mysql_trouble --prefix=mysql_trouble --cycles=2-- \
-uroot -pxxxx
....
2012_06_23_10_13_11 Check results: Threads_running=1, matched=no, cycles_true=0
2012_06_23_10_13_12 Check results: Threads_running=1, matched=no, cycles_true=0
2012_06_23_10_13_14 Check results: Threads_running=1, matched=no, cycles_true=0
2012_06_23_10_13_15 Check results: Threads_running=40, matched=yes, cycles_true=1
2012_06_23_10_13_16 Check results: Threads_running=41, matched=yes, cycles_true=2
2012_06_23_10_13_16 Collect triggered
2012_06_23_10_13_16 Collector PID 16113
2012_06_23_10_13_16 Sleeping 300 seconds after collect 

And the stored diagnostic data is available in its default location:

# ls -lh /var/lib/pt-stalk/
total 572K
-rw-r--r-- 1 root root 4.5K Jun 23 11:08 mysql_trouble-df
-rw-r--r-- 1 root root  153 Jun 23 11:08 mysql_trouble-disk-space
-rw-r--r-- 1 root root  16K Jun 23 11:08 mysql_trouble-diskstats
...
-rw-r--r-- 1 root root  15K Jun 23 11:08 mysql_trouble-top
-rw-r--r-- 1 root root  386 Jun 23 11:08 mysql_trouble-trigger
-rw-r--r-- 1 root root 8.1K Jun 23 11:08 mysql_trouble-variables
-rw-r--r-- 1 root root  992 Jun 23 11:08 mysql_trouble-vmstat
-rw-r--r-- 1 root root  245 Jun 23 11:08 mysql_trouble-vmstat-overall

B) Collect data when at least 20 queries running in the 'copying to tmp table' State.

pt-stalk  --function processlist --variable State \
--match Copying --threshold 20 --prefix=mysql_trouble --cycles=2 \
-- -uroot -pxxxxx
..
2012_06_23_11_54_19 Check results: State=21, matched=yes, cycles_true=1
2012_06_23_11_54_20 Check results: State=21, matched=yes, cycles_true=2
2012_06_23_11_54_20 Collect triggered
2012_06_23_11_54_21 Collector PID 9154
2012_06_23_11_54_21 Sleeping 300 seconds after collect 

 

You will probably like to run this tool as daemon - Here’s a sample configuration file for triggering when there are more than 40 threads running at once:

# Config for pt-stalk
variable=Threads_running
cycles=2  # trigger if problem seen twice in a row
dest=/var/log/mysql_diagnostics
threshold=40
daemonize
--
--user=monitoring
--password=xxxx

Syntax to run pt-stalk as daemon:
# pt-stalk --config /etc/pt-stalk.cnf
 For more information about configuration files click here

MySQL - How to scale inserts

Usually we expect inserts with large number of threads would to be faster but this is not always ture. The bottleneck might be the CPU, IO controller ...  MySQL can not fully use available cores/cpus e.g. MySQL 5.0 (Innodb) is limited to 4 cores etc. One of my clients had a problem scaling inserts, they have two data processing clusters each of which use 40 threads - so total 80 threads insert data into MySQL database (version 5.0.51). The issue, inserts are delayed by minute(s) and the backlog continue 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 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 cause 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, you can reach better through put (inserts per second) with less 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 here and here

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:


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 here. If you store large strings (TEXT) or BLOB, compression may help there is an article about it here 

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 here.
[ 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 here

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 good article that explains here and here.

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. Article about selecting innodb log file size is here

6 Innodb_flush_log_at_trx_commit -. The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.

7. 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 here and here

Continue...

Data compression

Data compression not only significantly reduces the storage required for the database, but also improves throughput by reducing the I/O workload, at a modest cost in processing overhead. The storage cost savings can be important, but the reduction in I/O costs can be even more valuable. There are three possible ways to achieve that

1. Use compress/uncompress function in the database
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_compress

2. Innodb-plugin offers table compression
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-background.html

3. Compress/uncompress data in client not in the database. This will offload more stuff – CPU and network. . This approach uses CPU cycles for compression and uncompression on the client machine rather than the database server, which might be appropriate for a distributed application with many clients, or where the client machine has spare CPU cycles.

We performed benchmarks (using table structre mentioned on my previous post: binary-log-synchronization) to evaluate the impact of using above mentioned data compression techniques (1 & 2 only), test data  was loaded using 8 parallel database sessions  into original table, compressed table and as well as into original table but using compress function to compress "content" column.

Table Format	Time to load 80k records	Data file size
Original Table	0m57.515s	1.6GB
Compressed Table: ROW_FORMAT =COMPRESSED	1m9.613s	648MB
Original Table using compress() function	0m35.883s	156MB

The results show that inserting data in compressed format (i.e. using compress() function) is the fastest approach and it gives better compression than using innodb compressed table. Using compress() function we have the control to compress only selected columns whereas innodb compressed table compress the entire table.

We also perfomed benchmarks to read data from original table, compressed table and original table but using uncompress() function (to uncompress compressed data), total 800k lookups were perfomed using id=<>

Table	Time to perform 800k lookups
Compressed Table: row_format= compressed	1m26.426s
Original Table using uncompress() function	1m26.197s
Original table	6m29.504s

The test results show that lookups performed againts compressed tables are at least 6 times faster than original table. The reason for which is that compressed table is much smaller than original table thus most of the data can fit into memory and hence reducing disk I/Os.