CODEWITHSUNDEEP
mysql
Trentj
Trentj

Reputation: 254

Application hangs on 16GB InnoDB mysqldump using "--single-transaction --quick"

Days of searching but still no solution.

My situation

I am using this command to dump a 16+Gb (growing) database (all but 2 tables are InnoDB)

The dump is run via cron once a day and sits in a small perl script

mysqldump -uuser -pxxx --single-transaction --quick DBNAME > DBBACKUP.sql

Now the website has 2 main tables that are very heavy read/write (low load when the dump is actually happening in the morning however)

The DB has 54 tables (most under 10-20 MB)

Table 1 is 71 Million rows (9GB) InnoDB table

Table 2 is 25 million rows (3GB) InnoDB table

Now when the dump runs, the application (whist accessing Table 1 or 2) is fine, until around 3 minutes into the dump when I expect Table 1 starts to backup, the website when submitting data to be inserted into Table 1 hangs for around 9 minutes, then works again, and then Table 2 starts to be dumped and the application that uses Table 2 starts to hang.

--single-transaction should not lock tables

--quick should not buffer in memory

Lastly, the CPU and MEM %'s are very low while the dump is happening.

So has anyone seen this and know of a solution or have any suggestions at all?

SYSTEM SPECS

Processor: Dual Intel Xeon E5506 Quad Core
RAM: 12 Gb DDR3 
HD1: 4 147 Gb SAS drives in a RAID 10 array
HD2: 500 Gb SATA Backup Drive
OS: Linux OS - CentOS 5 64-bit

MY.CNF

max_connections = 500
safe-show-database
skip-locking
key_buffer = 128M
max_allowed_packet = 16M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 64M
thread_concurrency = 8
wait_timeout = 30
innodb_file_per_table
innodb_log_file_size = 10485760
open_files_limit = 8192
ft_min_word_len = 3
log-error=/var/log/mysql/mysql-error.log
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 7

[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

Thank You.

Upvotes: 1

Views: 1662

Answers (2)

CrandellWS
CrandellWS

Reputation: 2804

As mentioned here you could setup replication (master/slave) and backup the slave (with or without locking) and have no fear of changes, thus getting around the locking issue.

You can find a guide to setup replication here:

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Upvotes: 0

Trentj
Trentj

Reputation: 254

OK Looks like I found the issue.

Basically, my DB has 4 myiisam table and around 20 innodb tables, the myissam table are under 2 Mb in size (total) but were causing the locks :(

So I am lucky enough to be able to dump the innodb tables (as above excluding the myissam tables) separately (and keep consistency) from the myissam tables, and no more locking :)

If anybody has other solutions feel free to post.

Upvotes: 2

Related Questions