Originally Posted by David Dreezer
try this script, run it from command line and see what it tells you, it's written by a MySQL developer and is a great help.

http://www.day32.com/MySQL/

Ok, I tried the script and here is what it told me...(I'll try to colorize as I can):

-- MYSQL PERFORMANCE TUNING PRIMER --


MySQL Version 4.1.22-standard i686

Upptime = 4 days 22 hrs 49 min 38 sec<BR>
Avg. qps = 24
Total Questions = 10315695
Threads Connected = 2

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 10 sec.
You have 301 out of 10315707 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
[color:"black">Current thread_cache_size = 128
Current threads_cached = 50
Current threads_per_sec = 0
Historic threads_per_sec = 0[/color]
[COLOR:#00BB00]Your thread_cache_size is fine[/color]

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 2
Historic max_used_connections = 53
The number of used connections is [COLOR:#BB0000]10% 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 memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 479 M
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 192 M
Configured Max Memory Limit : 2 G
Physical Memory : 994.54 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 192 M
Current key_buffer_size = 150 M
Key cache miss rate is 1 : 417
Key buffer fill ratio = 30.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 11 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 36.47 %
Current query_cache_min_res_unit = 4 K
Query Cache is 22 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 10940 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pas.

Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 2558 files

The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 952 tables
You have 464 [color:"black"]open
tables.[/color]

The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 99298 temp tables, 0% were created on disk

Effective in-memory tmp_table_size is limited to max_heap_table_size.

Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 82 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 685
You may benefit from selective use of InnoDB.

If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'