Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.
[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

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
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 7 '13 at 19:24

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

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

share|improve this answer
    
Relevant: dba.stackexchange.com/questions/20/… –  ceejayoz Jan 7 '13 at 17:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.