MySQL Performance

[imagize]

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.

[artur8ur]

[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…

[MarkRose]

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.