Mysql 5.0.92 - Percona Mysql Server, VMWare, CentOS, 32 GB RAM, 4 CPU, SAN.
In my tests with O_DIRECT set as the InnoDB flush method it was found to be resulting in a slow down for IO bound queries while running with SAN as the MySQL backend storage. A lot of our batch jobs or features like reports use temporary tables that are IO bound.
Some extracts from the Mysql Documentation in support of these findings
[["
innodb_flush_method If set to fdatasync (the default), InnoDB uses fsync() to flush both the data and log files. If set to O_DSYNC, InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. If O_DIRECT is specified (available on some GNU/Linux versions, FreeBSD, and Solaris), InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB uses fsync() instead of fdatasync(), and it does not use O_DSYNC by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is always async_unbuffered and cannot be changed. Different values of this variable can have a marked effect on InnoDB performance. For example, on some systems where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three." ]]
It was observed in my testing that running with defaults gave me at least a 60% improvement. Here are some performance metrics:
With O_DIRECT
[root@host]# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 21533436 489548 8243880 0 0 2 66 2 13 1 0 99 0 0
0 1 0 21456184 489556 8243964 0 0 13734 4272 2204 2598 6 2 67 24 0
0 1 0 21426052 489564 8243972 0 0 2598 7415 1923 1849 13 2 67 18 0
0 1 0 21403608 489564 8244136 0 0 1130 7932 1885 1739 14 2 68 16 0
1 0 0 21379676 489564 8244084 0 0 1632 8526 1979 1943 13 2 68 17 0
1 0 0 21360580 489572 8244216 0 0 1165 8336 1909 1821 13 2 68 17 0
0 1 0 21340988 489576 8244144 0 0 1114 8719 1963 1946 12 2 68 18 0
0 1 0 21323752 489580 8244336 0 0 694 8711 1940 1854 12 2 68 18 0
0 1 0 21305896 489580 8244248 0 0 778 8207 1888 1791 13 2 68 18 0
0 0 0 21287792 489580 8244444 0 0 787 8398 1916 1799 12 2 68 18 0
0 1 0 21270556 489584 8244392 0 0 611 8661 1926 1853 13 2 68 17 0
0 0 0 20929308 489652 8246276 0 0 0 393 989 91 0 0 100 0 0
Without O_DIRECT (Mysql Defaults)
[root@host]# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 18700144 489492 8238128 0 0 2 66 2 13 1 0 99 0 0
0 1 0 18681236 489492 8238844 0 0 16 11651 2162 2086 18 3 69 10 0
1 0 0 18662140 489500 8239296 0 0 0 10927 2073 1933 18 2 69 11 0
0 0 0 18643356 489500 8239652 0 0 16 11022 2084 1963 18 3 68 11 0
0 1 0 18624508 489500 8239648 0 0 0 11665 2162 2072 17 3 69 11 0
1 0 0 18606032 489508 8240008 0 0 0 11210 2113 2006 18 2 68 12 0
1 0 0 18587308 489512 8240132 0 0 0 11120 2097 1983 18 3 68 11 0
1 0 0 18567964 489516 8240564 0 0 0 10951 2067 1944 18 2 68 11 0
1 0 0 18550604 489520 8241068 0 0 0 11098 2105 1982 17 3 68 12 0
1 0 0 18531012 489520 8241008 0 0 0 11286 2113 2005 19 3 69 9 0
0 1 0 18512412 489528 8241360 0 0 0 11349 2123 2015 17 3 68 12 0
0 0 0 18282208 489536 8245508 0 0 10 2454 1183 404 3 1 94 2 0
Some of the key differences seen in VMSTAT are highlighted above. Mainly seen in bi, bo and wa.
It is worth testing this further with other tools specifically for IO against SAN or just measuring stats for reads/writes before and after the changes, but as documented by MySQL we just need to try it out ourselves and use what works best on a given platform/environment.
Useful tools we could use to further test this and more:
1. http://googlux.com/bonnie.html
2. http://vegan.net/tony/supersmack/
No comments:
Post a Comment