MySQL slow/down under heavy load

mysql5

MySQL server is keep crashing on a dedicated server running also apache/php. I am not a MySQL PRO so I need some experts suggestions.

Innodb and MyISAM engines both are used in the website but Innodb is heavily used.

From WHM phpmyadmin I found following red options

Innodb_buffer_pool_pages_dirty      18       
Innodb_buffer_pool_reads    31 k     
Handler_read_rnd    128 k
Handler_read_rnd_next   3,013 k
Created_tmp_disk_tables     1,534    
Sort_merge_passes   10 
Opened_tables   807 
Table_locks_waited      8

Here is MySQL Configuration

max_connections = 500
safe-show-database
skip-locking
key_buffer = 700M
max_allowed_packet = 32M
table_cache = 512
sort_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 512M
query_cache_limit=1024M
thread_concurrency = 8
connect_timeout = 8
wait_timeout = 120
interactive_timeout = 15
wait_timeout = 500
innodb_buffer_pool_size=1024M
open_files_limit = 8192
tmp_table_size = 64M
long_query_time = 1
log_slow_queries
innodb_autoinc_lock_mode = 2
set-variable = innodb_lock_wait_timeout=10

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

Error Log

100930  9:44:12 [Warning] /usr/sbin/mysqld: Forcing close of thread 283  user: 'rdf'

100930  9:44:19 [ERROR] /usr/sbin/mysqld: Sort aborted
100930  9:44:19 [ERROR] /usr/sbin/mysqld: Sort aborted
100930  9:44:21 [ERROR] /usr/sbin/mysqld: Sort aborted

100930  9:44:53 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.

100930 10:37:16 [ERROR] Cannot find or open table XXXX from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?

I see lots of errors cannot find or open table. I haven't touch MySQL configuration.

Best Answer

Personally, I think some of those values are way out of whack, but that shouldn't cause crashes.

http://blog.mysqltuner.com/ has a script that might give you some tuning hints, though, they suggest the server is running for 48+ hours.

When you say crash, do you mean that mysqld terminates, or is unresponsive? Is the system unresponsive or just mysql? check dmesg, /var/log/messages, core dumps, etc. Barring hardware issues, we'll assume that mysql is locking up. You don't mention uptime for those values above, nor the version of mysql you're using, but, we'll assume a relatively short uptime. The tmp tables created on disk might mean that your temporary table size is too small, or, you have queries that can't be cached in memory.

First thing I see is table_cache is lower than opened tables. This isn't necessarily a huge issue, but can cause a lot of churn. show status like 'open_tables'; If that is capped at your table_cache, you might want to increase table_cache.

If you have a relatively recent version of mysql, /var/lib/mysql/hostname.err or /var/log/mysql/mysql.err may contain some additional info. If you can do a 'show processlist' during an event, or mysqladmin -u root -ppassword processlist > /var/tmp/pl (to some area that isn't cleared on reboot if your server needs to be rebooted), you might be able to check what is happening at the crash. How about your mysql slowquery log? Longquery at 1 may create a ton of extra logging, but, if you find queries that are taking 300+ seconds that should take 4, you might have an indication of which table is hanging.

InnoDB isn't the cure for all that ails you. Programmers that don't understand mysql will use InnoDB because it uses row level locking versus table level locking. Lock a smaller portion of the table and you should see concurrency rise. The theory is sound, but, things you do with InnoDB don't work the same way as they do with MyISAM.

select count(*) from innodbtable where condition='asdf';
select count(*) from myisamtable where condition='asdf';

In the first case, InnoDB will lock the table and do a complete scan. In the second case, MyISAM won't lock the table (and will answer from the index if condition is contained within the index).

select count(varname) from table where condition='asdf';

If varname is not a key, this will force a tablescan to make sure that varname isn't null. If varname is a key, it still checks all key values to see if any are null.

Doing a select on a table that involves a text field automatically creates a disk temp table - you might be able to alter queries to avoid that.

If you can post a little more information about the application running, what you mean by crash, output from show variables/show status, contents of error logs, etc, I'm sure someone here with a bit more knowledge might be able to debug it further.

I'm guessing you restarted the server with a reboot/powercycle. In your /var/lib/mysql directory, there should be some files like:

ibdata1
ib_logfile0
ib_logfile1

I would assume that one of those files contains some bad data.

You might restart mysql with innodb_force_recovery=4 in the [mysqld] section to see if the innodb file is able to be repaired. If not, load one of your hotcopies or recent dumps. You might not be able to drop that table without some magic, but, should be able to rename the table and reimport from your backups.

Related Topic