MySQL Tweaking for performance

Question

[mysqld]
myisam_repair_threads=4
key_buffer = 64M
myisam_sort_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16K
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 160M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1


[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Does adding the quick help with 10GB mysql database dumps every night?

I also added myisam_repair_threads=4 would that help fix corrupted tables issues sometimes it seems like dumping big databases causes corruptions

score 0 · Answer 1 · answered Jan 07 '13 at 17:21

0

Does adding the quick help with 10GB mysql database dumps every night?

Yes, most likely. It'll probably drastically reduce the RAM usage.

answered Jan 07 '13 at 17:21

ceejayoz

32,469
7
81
105

Relevant: http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database – ceejayoz Jan 07 '13 at 17:21

score 0 · Accepted Answer · answered Jan 07 '13 at 18:03

Here is the official explanation of quick:

This option is useful for dumping large tables. It forces mysqldump to 
retrieve rows for a table from the server a row at a time rather than 
retrieving the entire row set and buffering it in memory before writing it out.

So for large tables this will help, if you can't fit the table into memory.

But, important to note here, if you are using MyISAM it will lock the table each time is reads a row (it also locks it when dumping the row-set). InnoDB uses row-level locking instead of table-level locking so it will back up much easier. If you have a large table, it should probably be using InnoDB rather than MyISAM. This will make a big difference when doing backups.

I'm surprised using mysqldump causes corruptions, I would look into other sources that may be causing this.

Can you switch from MyISAM to InnoDB? -- I am surprised too but the drives/ram are new so I'm just wondering if since i started backing up the big dbs it is now causing corruptions — Mike Janson, Jan 07 '13 at 19:24

MySQL Tweaking for performance

2 Answers2