I’ve now downloaded the tuning-primer.sh script and here follows the output:
– MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery -MySQL Version 4.1.22-standard i686Uptime = 0 days 12 hrs 10 min 58 secAvg. qps = 6Total Questions = 275029Threads Connected = 2Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.htmlVisit http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL’s Enterprise Monitoring and Advisory ServiceSLOW QUERIESCurrent long_query_time = 10 sec.You have 22 out of 275041 that take longer than 10 sec. to completeThe slow query log is NOT enabled.Your long_query_time may be too high, I typically set this under 5 sec.WORKER THREADSCurrent thread_cache_size = 128Current threads_cached = 18Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = 500Current threads_connected = 3Historic max_used_connections = 21The number of used connections is 4% of the configured maximum.You are using less than 10% of your configured max_connections.Lowering max_connections could help to avoid an over-allocation of memorySee “MEMORY USAGE” section to make sure you are not over-allocatingMEMORY USAGEMax Memory Ever Allocated : 161 MConfigured Max Per-thread Buffers : 2 GConfigured Max Global Buffers : 74 MConfigured Max Memory Limit : 2 GPhysical Memory : 1010.15 MMax memory limit exceeds 90% of physical memoryKEY BUFFERCurrent MyISAM index space = 0 bytesCurrent key_buffer_size = 32 MKey cache miss rate is 1 : 785168Key buffer fill ratio = 3.00 %Your key_buffer_size seems to be too high.Perhaps you can use these resources elsewhereQUERY CACHEQuery cache is enabledCurrent query_cache_size = 32 MCurrent query_cache_used = 3 MCurrent query_cache_limit = 1 MCurrent Query cache Memory fill ratio = 11.07 %Current query_cache_min_res_unit = 4 KQuery Cache is 10 % fragmentedRun “FLUSH QUERY CACHE” periodically to defragment the query cache memoryIf you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.Your query_cache_size seems to be too high.Perhaps you can use these resources elsewhereMySQL won’t cache query results that are larger than query_cache_limit in sizeSORT OPERATIONSCurrent sort_buffer_size = 1 MCurrent record/read_rnd_buffer_size = 1020 KSort buffer seems to be fineJOINSCurrent join_buffer_size = 1.00 MYou have had 0 queries where a join could not use an index properlyYour joins seem to be using indexes properlyOPEN FILES LIMITCurrent open_files_limit = 2558 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHEYou are not ‘101’ or 'root’I am unable to determine the table_count!Current table_cache value = 1024 tablesYou have 85 open tables.The table_cache value seems to be fineTEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 16414 temp tables, 99% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Perhaps you should increase your tmp_table_size and/or max_heap_table_sizeto reduce the number of disk-based temporary tablesNote! BLOB and TEXT columns are not allow in memory tables.If you are using these columns raising these values might not impact yourratio of on disk temp tables.TABLE SCANSCurrent read_buffer_size = 1020 KCurrent table scan ratio = 22717 : 1You have a high ratio of sequential access requests to SELECTsYou may benefit from raising read_buffer_size and/or improving your use of indexes.TABLE LOCKINGCurrent Lock Wait ratio = 1 : 23You may benefit from selective use of InnoDB.If you have long running SELECT’s against MyISAM tables and performfrequent updates consider setting ‘low_priority_updates=1’
Does this mean anything to anyone? …I’ve run the flush cache query command that it suggested yesterday but I still had to restart mysql again (
Here’s the output from my mysqladmin status command:
Uptime: 43995 Threads: 1 Questions: 276045 Slow queries: 22 Opens: 160 Flush tables: 1 Open tables: 85 Queries per second avg: 6.274
Looks like the ‘slow queries’ grows throughout the day… I restarted MySQL about 11 hours ago…
I think the problem only occurs when I get more than thousand visitors per hour… so probably a loading issue…
I have just one web user of the database - is this normal practice? Every page initiates a database connection using this same user - should I create different users for different pages? Would this elleviate some contention?
My host told me that they could see ‘locked queries’ how do I view these myself?
Many thanks - hope you can help )
Hope you can help