Re: MySQL 5.5.8 - Configuration Settings Suggestions

[Aftab Khan]

>Still I am getting load issues by re-setting the suggested values for read/sort variables.

If you find default values are too small then try to increase them by small increment, I would say run benchmark on test bed to determine optimal values for connection buffers.


>I do not have script for mysqldumpslow and for your kind information we are storing the general log and slow log in table instead of file.

Assuming your slow query log table name is 'slow_log'
You can use following SQL to find TOP 5 slow queries:


SELECT substr(sql_text, 1, 40) q,
count(*) nb,
sum(query_time) sum,
min(query_time) min,
max(query_time) max,
avg(query_time) avg,
min(lock_time) min_lock,
max(lock_time) max_lock,
avg(lock_time) avg_lock,
min(rows_sent) min_rows_sent,
max(rows_sent) max_rows_sent,
avg(rows_sent) avg_rows_sent,
min(rows_examined) min_rows_examined,
max(rows_examined) max_rows_examined,
avg(rows_examined) avg_rows_examined
FROM mysql.slow_log
WHERE 1=1
group by substr(sql_text,1,40) order by sum(query_time) desc limit 5


Perhaps you can change slow log destination to 'FILE', for example:

SET GLOBAL log_output='FILE';