MySQL performance

[tryme]

Hello,

I need some help/advice for my server.

Here are the server specs:

1 x Quad Core Xeon 5320
6 GB memory
2 x 73GB SAS (1 is only for MySQL databases)
Linux debian 2.6.18-6-686-bigmem
MySQL version 5.0.32

I’m running free forum server with ~20k databases (each 27 tables). Only MyISAM.

I would like to know if my.cnf is well optimized for such server. Here is:

[B]Quote:[/B]

skip-locking safe-show-database skip-innodb query_cache_limit = 1M query_cache_size = 48M query_cache_type = 1 set-variable = key_buffer=16M set-variable = join_buffer=1M set-variable = read_buffer_size=4M set-variable = sort_buffer_size=4M set-variable = tmp_table_size=128M set-variable = table_cache=25 set-variable = max_connections=50 set-variable = thread_concurrency=4 set-variable = thread_cache_size=160 set-variable = max_allowed_packet=1M set-variable = connect_timeout=3 long_query_time = 3 log_slow_queries = /var/log/mysql/mysql-slow.log [safe_mysqld] err-log=/var/log/mysqld.log [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M table_cache = 2048 sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 2M thread_cache_size = 40 thread_concurrency = 2 tmp_table_size = 128M

Here is raport from mysqlreport:

[B]Quote:[/B]

__ Key ____________________________________________________________ _____ Buffer used 14.16M of 16.00M %Used: 88.48 Current 1.87M %Usage: 11.69 Write hit 50.04% Read hit 86.00% __ Questions ___________________________________________________________ Total 178.08M 211.2/s DMS 91.62M 108.7/s %Total: 51.45 QC Hits 44.89M 53.2/s 25.21 Com_ 21.12M 25.0/s 11.86 COM_QUIT 20.27M 24.0/s 11.38 +Unknown 185.34k 0.2/s 0.10 Slow 3 s 95 0.0/s 0.00 %DMS: 0.00 Log: ON DMS 91.62M 108.7/s 51.45 SELECT 61.89M 73.4/s 34.75 67.55 UPDATE 23.52M 27.9/s 13.21 25.67 DELETE 2.63M 3.1/s 1.48 2.88 INSERT 1.99M 2.4/s 1.12 2.17 REPLACE 1.59M 1.9/s 0.89 1.73 Com_ 21.12M 25.0/s 11.86 change_db 20.36M 24.2/s 11.43 optimize 691.80k 0.8/s 0.39 create_tabl 33.71k 0.0/s 0.02 __ SELECT and Sort _____________________________________________________ Scan 19.62M 23.3/s %SELECT: 31.70 Range 1.78M 2.1/s 2.87 Full join 543.78k 0.6/s 0.88 Range check 0 0/s 0.00 Full rng join 2.49k 0.0/s 0.00 Sort scan 3.36M 4.0/s Sort range 4.83M 5.7/s Sort mrg pass 926 0.0/s __ Query Cache _________________________________________________________ Memory usage 30.91M of 48.00M %Used: 64.40 Block Fragmnt 13.13% Hits 44.89M 53.2/s Inserts 61.73M 73.2/s Insrt:Prune 6.39:1 61.8/s Hit:Insert 0.73:1 __ Table Locks _________________________________________________________ Waited 57.45k 0.1/s %Total: 0.04 Immediate 158.39M 187.9/s __ Tables ____________________________________________________________ __ Open 25 of 25 %Cache: 100.00 Opened 90.58M 107.4/s __ Connections _________________________________________________________ Max used 23 of 50 %Max: 46.00 Total 20.27M 24.0/s __ Created Temp ________________________________________________________ Disk table 757.90k 0.9/s Table 1.27M 1.5/s Size: 128.0M File 1.86k 0.0/s __ Threads ____________________________________________________________ _ Running 1 of 1 Cached 22 of 160 %Hit: 100 Created 23 0.0/s Slow 0 0/s __ Aborted ____________________________________________________________ _ Clients 9 0.0/s Connects 0 0/s __ Bytes ____________________________________________________________ ___ Sent 3.85G 4.6k/s Received 2.38G 2.8k/s __ InnoDB Buffer Pool __________________________________________________ Usage 0 of 0 %Used: 0.00 Read hit 0.00% Pages Free 0 %Total: 0.00 Data 0 0.00 %Drty: 0.00 Misc 0 0.00 Latched 0 0.00 Reads 0 0/s From file 0 0/s 0.00 Ahead Rnd 0 0/s Ahead Sql 0 0/s Writes 0 0/s Flushes 0 0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 0 0/s Writes 0 0/s fsync 0 0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 0 0/s Written 0 0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s

My big problem is the load average on peak hours.

If you need more information, please tell me.

Thanks for your help.

Best Regards,
tryme

[artur8ur]

Hi,

[B]Quote:[/B]

I would like to know if my.cnf is well optimized for such server. Here is: [B]Quote:[/B] skip-locking set-variable = key_buffer=16M

Just a hint: You can remove this "set-vatiable = " part of the statements... It's needed only for "old" MySQL Server...

I guess the following statistics are gathered after running for some time

[B]Quote:[/B]

Here is report from mysqlreport: [B]Quote:[/B] __ Key […] __ Query Cache _________________________________________________________ Memory usage 30.91M of 48.00M %Used: 64.40 Block Fragmnt 13.13% Hits 44.89M 53.2/s Inserts 61.73M 73.2/s Insrt:Prune 6.39:1 61.8/s Hit:Insert 0.73:1

Your query cache is to small... most of the inserts kick out cached queries.. Increase the size of your cache...

[B]Quote:[/B]

[B]Quote:[/B] __ Tables ____________________________________________________________ __ Open 25 of 25 %Cache: 100.00 Opened 90.58M 107.4/s

Your table cache is to small... increase the table_cache variable... Set it to 1024, and have a look how many slots are used,.. you can decrease it than to a number around that... You might need to increase open_files_limit, too.

[B]Quote:[/B]

[B]Quote:[/B] __ Threads ____________________________________________________________ _ Running 1 of 1 Cached 22 of 160 %Hit: 100 Created 23 0.0/s Slow 0 0/s

Your thread cache is to big.. The tread cache is the number of "unused" threads to cache, not a limit to how many threads are allowed in total. This cache is used to decrease the number of threads which are created per second,.. to smooth the fluctuations.. I think a cache of 8 should be enough for your... max 24.

Tuning of other variables like sort_buffer_size, read_buffer_size, read_rnd_buffer_size, bulk_insert_buffer_size… but this depends on the your data structure, queries, workload…