# MySQL Performance **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 27, 2008, 4:28pm UTC](https://forums.percona.com/t/mysql-performance/944 "2008-09-27T16:28:40Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![imagize](https://avatars.discourse-cdn.com/v4/letter/i/3be4f8/32.png) [@imagize](https://forums.percona.com/u/imagize)\ **Post date:** [September 27, 2008, 4:28pm UTC](https://forums.percona.com/t/mysql-performance/944/1 "2008-09-27T16:28:40Z")
Hello, This server has dual intel xeon 5430 cpu, 8GB ram and 2x15k rpm sa-scsi hard drives on raid1. It is a web server and thus apache and mysql run on the same machine for now. We have innodb databases which are several gigabytes big (mediawiki with 12000 articles and some vbulletin forums). | [B]Quote:[/B] | | [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock query\_cache\_limit = 1M query\_cache\_size = 64M query\_cache\_type = 1 max\_user\_connections = 350 max\_connections = 500 # interactive\_timeout = 10 # wait\_timeout = 10 # connect\_timeout = 10 thread\_cache\_size = 128 key\_buffer\_size = 128M join\_buffer = 64MB max\_allowed\_packet = 16M table\_cache = 1024 record\_buffer = 1M innodb\_buffer\_pool\_size = 1024M innodb\_log\_buffer\_size = 16M innodb\_log\_file\_size = 128M read\_buffer\_size = 4M read\_rnd\_buffer\_size = 4M sort\_buffer\_size = 8M myisam\_sort\_buffer\_size = 8M max\_heap\_table\_size = 128M tmp\_table\_size = 128M max\_connect\_errors = 10 thread\_concurrency = 8 safe-show-database long\_query\_time = 1 server-id = 1 #[mysql.server] #user = mysql #basedir = /var/lib [safe\_mysqld] err-log = /var/log/mysqld.log pid-file = /var/lib/mysql/mysql.pid open\_files\_limit = 8192 #[mysqldump] #quick #max\_allowed\_packet = 16M [mysql] no-auto-rehash [isamchk] key\_buffer = 64M sort\_buffer = 64M read\_buffer = 16M write\_buffer = 16M [myisamchk] key\_buffer = 64M sort\_buffer = 64M read\_buffer = 16M write\_buffer = 16M [mysqlhotcopy] interactive-timeout | | [B]Quote:[/B] | | mysqlreport \_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_ Buffer used 96.63M of 128.00M %Used: 75.50 Current 114.92M %Usage: 89.78 Write hit 54.80% Read hit 99.87% \_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Total 29.04M 428.8/s DMS 11.28M 166.5/s %Total: 38.82 Com\_ 11.15M 164.7/s 38.40 QC Hits 3.97M 58.6/s 13.66 COM\_QUIT 2.65M 39.1/s 9.12 -Unknown 3.59k 0.1/s 0.01 Slow 1 s 4.00k 0.1/s 0.01 %DMS: 0.04 Log: OFF DMS 11.28M 166.5/s 38.82 SELECT 9.93M 146.6/s 34.20 88.08 INSERT 1.22M 18.0/s 4.19 10.79 UPDATE 108.28k 1.6/s 0.37 0.96 DELETE 15.42k 0.2/s 0.05 0.14 REPLACE 3.94k 0.1/s 0.01 0.03 Com\_ 11.15M 164.7/s 38.40 set\_option 4.27M 63.1/s 14.72 change\_db 2.65M 39.1/s 9.12 begin 2.60M 38.3/s 8.94 \_\_ SELECT and Sort \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Scan 78.21k 1.2/s %SELECT: 0.79 Range 1.96M 28.9/s 19.69 Full join 1.30k 0.0/s 0.01 Range check 0 0/s 0.00 Full rng join 19 0.0/s 0.00 Sort scan 45.10k 0.7/s Sort range 47.62k 0.7/s Sort mrg pass 6 0.0/s \_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Memory usage 52.94M of 64.00M %Used: 82.72 Block Fragmnt 8.89% Hits 3.97M 58.6/s Inserts 9.51M 140.4/s Insrt:Prune 7.93:1 122.7/s Hit:Insert 0.42:1 \_\_ Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Waited 246 0.0/s %Total: 0.00 Immediate 14.20M 209.7/s \_\_ Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_ Open 1024 of 1024 %Cache: 100.00 Opened 6.86k 0.1/s \_\_ Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Max used 405 of 500 %Max: 81.00 Total 2.65M 39.1/s \_\_ Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Disk table 16.63k 0.2/s Table 49.73k 0.7/s Size: 128.0M File 17 0.0/s \_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Running 3 of 7 Cached 121 of 128 %Hit: 99.98 Created 405 0.0/s Slow 11 0.0/s \_\_ Aborted \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Clients 754 0.0/s Connects 1.55k 0.0/s \_\_ Bytes \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_ Sent 25.75G 380.2k/s Received 5.00G 73.8k/s \_\_ InnoDB Buffer Pool \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Usage 1.00G of 1.00G %Used: 100.00 Read hit 99.89% Pages Free 0 %Total: 0.00 Data 61.18k 93.35 %Drty: 0.42 Misc 4356 6.65 Latched 1 0.00 Reads 215.45M 3.2k/s From file 242.12k 3.6/s 0.11 Ahead Rnd 6677 0.1/s Ahead Sql 472 0.0/s Writes 24.87M 367.2/s Flushes 512.00k 7.6/s Wait Free 0 0/s \_\_ InnoDB Lock \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Waits 29 0.0/s Current 0 Time acquiring Total 52818 ms Average 1821 ms Max 51005 ms \_\_ InnoDB Data, Pages, Rows \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Data Reads 277.29k 4.1/s Writes 1.46M 21.5/s fsync 1.04M 15.3/s Pending Reads 0 Writes 0 fsync 0 Pages Created 21.70k 0.3/s Read 597.76k 8.8/s Written 512.00k 7.6/s Rows Deleted 845.75k 12.5/s Inserted 987.67k 14.6/s Read 52.30M 772.1/s Updated 259.29k 3.8/s | Any assistance or advice would be greatly appreciated, thanks. ---
**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\ **Post date:** [September 28, 2008, 2:46pm UTC](https://forums.percona.com/t/mysql-performance/944/2 "2008-09-28T14:46:20Z")
| [B]Quote:[/B] | | Any assistance or advice would be greatly appreciated, thanks. | Well,… its not easy to answer such an unspecific question… Do you have actual performance problems, or do you just want to increase general page load time? | [B]Quote:[/B] | | \_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Memory usage 52.94M of 64.00M %Used: 82.72 Block Fragmnt 8.89% Hits 3.97M 58.6/s Inserts 9.51M 140.4/s Insrt:Prune 7.93:1 122.7/s Hit:Insert 0.42:1 | That is not to much… a good ratio would be n:1 where n is much bigger than 1… In your case only 42% of the cached statements are reused… Try increasing the cache size,… | [B]Quote:[/B] | | \_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Running 3 of 7 Cached 121 of 128 %Hit: 99.98 Created 405 0.0/s Slow 11 0.0/s | That is a big cache… I don’t know your Data Access pattern… but the cache is used to buffer the recreation of new threads… If your typical client access pattern is not to create dozens of new connections, without releasing some in the meantime, you can decrease this cache… But this would be very uncommon for a web application… A “normal” pattern would me more like: 3 new, 1 release, 2 new, 2 release, 2 new, 1 release, 1 new, 2 release… So you need only your cache to smooth your fluctuations… About 8 threads… or maybe up to 16… | [B]Quote:[/B] | | innodb\_buffer\_pool\_size = 1024M | For a machine with 8GB RAM, this is not to much... and since you have more Data than that (Pool 100% used) you could try to increase your buffer\_pool to gain some performance... For any other advice, I do need more information… ---
**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [September 28, 2008, 6:54pm UTC](https://forums.percona.com/t/mysql-performance/944/3 "2008-09-28T18:54:20Z")
If you find your system is getting slow, have you tried running PHP with APC compiled in? It’ll make a massive difference, if you configure things properly.