# Performance Issue on UPDATEs **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 14, 2010, 2:58am UTC](https://forums.percona.com/t/performance-issue-on-updates/1558 "2010-11-14T02:58:22Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![manavid](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@manavid](https://forums.percona.com/u/manavid)\ **Post date:** [November 14, 2010, 2:58am UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/1 "2010-11-14T02:58:22Z")
Hi All, We recently encountered performance issues on UPDATEs on MySQL 5.1 as well as Percona 5.1. When using Lucid deb package provided from Percona average update query takes about 50ms to complete on a table with ~500K records. I compiled Percona from source with following flags: CFLAGS=“-O2 -DBIG\_JOINS=1 -march=nocona -mfpmath=sse -msse2 -mmmx"CXXFLAGS=”-O2 -DBIG\_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti - Our server is a Dual Xeon CPU 3.40GHz with 4GB of RAM. Ubuntu 10.04 is installed. The compiled version takes average 25ms to complete update query. Here is output of the profiling: ±--------------------------±---------+| Status | Duration |±--------------------------±---------+| starting | 0.000081 || checking permissions | 0.000010 || Opening tables | 0.000019 || System lock | 0.000006 || Table lock | 0.000007 || init | 0.000100 || Searching rows for update | 0.004254 || Updating | 0.066596 || end | 0.000013 || query end | 0.000005 || freeing items | 0.042667 || logging slow query | 0.000004 || cleaning up | 0.000006 |±--------------------------±---------+ Same query takes 1ms per update on my macpro workstation. I created a VM with Parallels installed Ubuntu 10.04 with 4 cpus and 4g ram and average update is 2ms, Percona was installed from deb package. On server turning off the query cache won’t increase the performance. Please let me know how I can provide more information to troubleshoot this performance issue. Thank you Moe ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [November 14, 2010, 2:52pm UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/2 "2010-11-14T14:52:40Z")
I’m guessing that you are running InnoDB tables? If so then how big is your InnoDB\_buffer\_size? And what is your innodb\_flush\_logs\_at\_trx\_commit setting? Basically providing us with the output from: SHOW VARIABLES LIKE ‘innodb%’; is a good start. ---
**Author:** ![manavid](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@manavid](https://forums.percona.com/u/manavid)\ **Post date:** [November 14, 2010, 3:56pm UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/3 "2010-11-14T15:56:47Z")
Thank you for your reply. Yes I’m running InnoDB. The buffer size is set at 1G. innodb\_flush\_log\_at\_trx\_commit was set to 1. I Noticed no innodb\_flush\_method was shown in SHOW VARIABLES LIKE ‘innodb%’; I set it to O\_DSYNC and average update dropped from 25ms to 6ms. Changing innodb\_flush\_method to O\_DIRECT didn’t make any changes. Here is the output of the SHOW VARIABLES LIKE ‘innodb%’; ±---------------------------------------±-----------------------+| Variable\_name | Value |±---------------------------------------±-----------------------+| innodb\_adaptive\_checkpoint | estimate || innodb\_adaptive\_flushing | OFF || innodb\_adaptive\_hash\_index | ON || innodb\_additional\_mem\_pool\_size | 8388608 || innodb\_auto\_lru\_dump | 0 || innodb\_autoextend\_increment | 8 || innodb\_autoinc\_lock\_mode | 1 || innodb\_buffer\_pool\_size | 1073741824 || innodb\_change\_buffering | inserts || innodb\_checkpoint\_age\_target | 0 || innodb\_checksums | ON || innodb\_commit\_concurrency | 0 || innodb\_concurrency\_tickets | 500 || innodb\_data\_file\_path | ibdata1:10M:autoextend || innodb\_data\_home\_dir | || innodb\_dict\_size\_limit | 0 || innodb\_doublewrite | ON || innodb\_doublewrite\_file | || innodb\_enable\_unsafe\_group\_commit | 0 || innodb\_expand\_import | 0 || innodb\_extra\_rsegments | 0 || innodb\_extra\_undoslots | OFF || innodb\_fast\_checksum | OFF || innodb\_fast\_recovery | OFF || innodb\_fast\_shutdown | 1 || innodb\_file\_format | Antelope || innodb\_file\_format\_check | Barracuda || innodb\_file\_per\_table | ON || innodb\_flush\_log\_at\_trx\_commit | 1 || innodb\_flush\_log\_at\_trx\_commit\_session | 3 || innodb\_flush\_method | O\_DSYNC || innodb\_flush\_neighbor\_pages | 1 || innodb\_force\_recovery | 0 || innodb\_ibuf\_accel\_rate | 100 || innodb\_ibuf\_active\_contract | 1 || innodb\_ibuf\_max\_size | 536854528 || innodb\_io\_capacity | 200 || innodb\_lock\_wait\_timeout | 50 || innodb\_locks\_unsafe\_for\_binlog | OFF || innodb\_log\_buffer\_size | 8388608 || innodb\_log\_file\_size | 5242880 || innodb\_log\_files\_in\_group | 2 || innodb\_log\_group\_home\_dir | ./ || innodb\_max\_dirty\_pages\_pct | 75 || innodb\_max\_purge\_lag | 0 || innodb\_mirrored\_log\_groups | 1 || innodb\_old\_blocks\_pct | 37 || innodb\_old\_blocks\_time | 0 || innodb\_open\_files | 300 || innodb\_overwrite\_relay\_log\_info | OFF || innodb\_page\_size | 16384 || innodb\_pass\_corrupt\_table | 0 || innodb\_read\_ahead | linear || innodb\_read\_ahead\_threshold | 56 || innodb\_read\_io\_threads | 4 || innodb\_recovery\_stats | OFF || innodb\_replication\_delay | 0 || innodb\_rollback\_on\_timeout | OFF || innodb\_show\_locks\_held | 10 || innodb\_show\_verbose\_locks | 0 || innodb\_spin\_wait\_delay | 6 || innodb\_stats\_auto\_update | 1 || innodb\_stats\_method | nulls\_equal || innodb\_stats\_on\_metadata | ON || innodb\_stats\_sample\_pages | 8 || innodb\_stats\_update\_need\_lock | 1 || innodb\_strict\_mode | OFF || innodb\_support\_xa | ON || innodb\_sync\_spin\_loops | 30 || innodb\_table\_locks | ON || innodb\_thread\_concurrency | 0 || innodb\_thread\_concurrency\_timer\_based | OFF || innodb\_thread\_sleep\_delay | 10000 || innodb\_use\_purge\_thread | 1 || innodb\_use\_sys\_malloc | ON || innodb\_use\_sys\_stats\_table | OFF || innodb\_version | 1.0.12-11.5 || innodb\_write\_io\_threads | 4 |±---------------------------------------±-----------------------+ Thank you very much for your help. Moe ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [November 14, 2010, 4:49pm UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/4 "2010-11-14T16:49:09Z")
If you are willing to trade a bit of robustness for speed you can set: innodb\_flush\_log\_at\_trx\_commit = 2 Or you can buy a better RAID controller with a battery backed up cache. The reason why you had better performance on your workstation is probably due to that some OS fake the sync even though the hardware havn’t actually had time to write it to disk yet. And you should increase the log\_buffer to something like: innodb\_log\_file\_size = 32M The default log buffer size that you have now is very small, but you shouldn’t go higher than what I suggest since it will not give you so much benefit while the restore/repair time in case of accidental shutdown will instead increase a lot. ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [November 15, 2010, 6:47am UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/5 "2010-11-15T06:47:12Z")
Do not compare a server’s speed with a Mac OS laptop that lies about whether your data has actually been written to disk. ---
**Author:** ![manavid](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@manavid](https://forums.percona.com/u/manavid)\ **Post date:** [November 15, 2010, 4:15pm UTC](https://forums.percona.com/t/performance-issue-on-updates/1558/6 "2010-11-15T16:15:52Z")
I do appreciate for your responses. Thank you very much. Moe