Crash Recovery / Bulk Import Advice

[Eric Holmberg]

I am evaluating MySQL Server 6.0.5-alpha and doing some bulk data imports into Falcon tables. I have about 800 Million records in several different tables that take up about 60 GB of data.

I have 12GB of memory and have allocated 10GB to the Falcon tables. Data import performance is very good until I hit the largest table at which point the memory usage goes up to over 20GB (of the 12GB of physical memory) and hard drive thrashing ensues.

Once the hard drive trashing occurs, MySQL takes about 1 hour to shut down and if I kill it, the restart results in a server crash. The only way out of this scenario is to delete all of the falcon data files and start over.

If I kill the import job using "kill #" in the mysql-client and then attempt a server shutdown, the server will eventually shutdown gracefully after an hour or so. At this point, when I attempt a restart, it fails as mysql thrashes on the Falcon data files for 15 to 45 minutes. I have to repeat this 2 or 3 times and then mysql finally restarts.

Server configuration:
* OS: Ubuntu 07.10 x86_64
* Processor: Quad-core Intel Xeon E5310 processor
* RAM: 12 GB of RAM

Falcon configuration:
mysql> show variables like '%falcon%';
+----------------------------------+-----------------+
| Variable_name | Value |
+----------------------------------+-----------------+
| falcon_checkpoint_schedule | 7 * * * * * |
| falcon_consistent_read | ON |
| falcon_debug_mask | 0 |
| falcon_debug_server | OFF |
| falcon_debug_trace | 0 |
| falcon_direct_io | 1 |
| falcon_gopher_threads | 5 |
| falcon_index_chill_threshold | 4 |
| falcon_initial_allocation | 0 |
| falcon_io_threads | 2 |
| falcon_large_blob_threshold | 160000 |
| falcon_lock_wait_timeout | 50 |
| falcon_max_transaction_backlog | 150 |
| falcon_page_cache_size | 5767168000 |
| falcon_page_size | 4096 |
| falcon_record_chill_threshold | 5 |
| falcon_record_memory_max | 6815744000 |
| falcon_record_scavenge_floor | 25 |
| falcon_record_scavenge_threshold | 67 |
| falcon_scavenge_schedule | 15,45 * * * * * |
| falcon_serial_log_block_size | 0 |
| falcon_serial_log_buffers | 10 |
| falcon_serial_log_dir | |
| falcon_serial_log_priority | 1 |
| falcon_support_xa | OFF |
| falcon_use_deferred_index_hash | OFF |
+----------------------------------+-----------------+

I have also tried falcon_page_cache_size = 2G and falcon_record_memory_max = 8G without any real changes in performance.

Should I configure the falcon_serial_log_priority, serial buffers, or scavenging parameters?

Latest Source
--------------
I didn't see any matching bug reports. The 3rd attempt at downloading the latest bzr repository source has failed (it downloads for several hours before getting an aborted connection). I don't see any way to download the bzr repo using HTTP/FTP.

Bug Report
----------
I could try to come up with some tests to reproduce this, but I'd like to know that this would be useful as I don't have a lot of free time to spend on this and I can't download the latest source, so the bug reports would be against an older version of the code.

Crash Recovery
--------------
Is there any way to recover from a crash? It seems like the falcon crash recovery is interfering with the mysql restart process, so it's a never-ending cycle of crash, thrash, crash...

Improved Import Performance
---------------------------
I've seen some suggestions in this forum to try setting AUTOCOMMIT=1 for the bulk imports. This makes some sense that the large imports are creating huge transactions that then need to be rolled back if I abort the import. I'll try setting this value and re-import all of the data and see what happens.

Any useful thoughts here would be appreciated.

Thanks,
Eric



Edited 1 time(s). Last edit at 08/09/2008 12:05PM by Eric Holmberg.