my.cnf MySQL perfomance for 5GB MYISAM table

Question

I Have a dedicated server - Intel Xeon L5320 with 8GB of RAM and 2 x 500GB 7200RMP HDD

I need to optimize mysql to cope with a large 5Gb MyISAM table + around 25 - 30 smaller databases currently it looks like this:

key_buffer = 3G
thread_cache_size = 16
table_cache = 8192
query_cache_size = 512M

As it is the server really struggles and I get continues tmp disk full warnings could you please help me out / suggest the best my.cnf configuration for my server and or any other settings changes that would improve performance.

Thanks in advance

score 1 · Answer 1 · answered Nov 26 '12 at 16:30

1

If /tmp is filling up, you are running some large, inefficient queries somewhere which are falling back to FILESORT. Well-written, efficient queries should typically not need this -- turn on slow query logging (if it isn't already) and check the log to see what needs optimizing.

answered Nov 26 '12 at 16:30

I dont think that is the be all and end all tbh I have set up mysql's tmp folder to fix this - I think the 5GB myisam table is optimized pretty well. The tmp disk is set to 4GB could this do with increasing? is my my.cnf as good as it could be - are there any other tweeks that could greatly improve perfomance? – Adrian Brown Nov 26 '12 at 17:19
1

MySQL configuration can only do so much for inefficient queries. **Look at your slow query log.** – Nov 26 '12 at 19:47

score 1 · Accepted Answer · edited May 23 '17 at 12:06

I recommend you use mytop and mysqltuner to analyze using mysql resources (RAM and CPU).

Too enable the option to log slow queries:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

And check out this post about ntpd service:

MySQL high CPU usage

Finally I leave you in a setting that I have a dedicated server for a high rate of transactions.

max_allowed_packet=16M
key_buffer_size=8M
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=512M
join_buffer_size=40M
table_open_cache=1024
query_cache_size=40M
table_definition_cache=256
innodb_additional_mem_pool_size=10M
key_buffer_size=16M
max_allowed_packet=32M
max_connections = 300
query_cache_limit = 10M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

Greetings.

my.cnf MySQL perfomance for 5GB MYISAM table

2 Answers2