0

I am importing a huge (~900GB ) dump in a mysql 5.5 server;

The system has 48GB of RAM.

Here are my InnoDB settings

innodb_read_io_threads          = 16
innodb_write_io_threads         = 16  #To stress the double write buffer
innodb_buffer_pool_size         = 48000M
innodb_log_file_size            = 1G #Small log files, more page flush
innodb_log_files_in_group       = 2
innodb_file_per_table           = 1
innodb_log_buffer_size          = 1G
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 0
innodb_file_format              = BARRACUDA
innodb_fast_shutdown = 1

Here is some vmstat output in MBs

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0   6324    290      6    206    3    0  7988 29188 1723 1576  7  1 82 10  0
 1  0   6321    294      5    198    5    0 11436 29152 1935 1762  7  1 83  9  0
 0  1   6318    285      5    202    5    0  9364 23940 1516 1350  8  1 82  9  0
 0  1   6316    289      5    195    3    0  8136 30500 1636 1607  5  1 82 12  0
 0  1   6316    288      5    196    0    0   384  5620  296  342  0  0 82 17  0
 0  2   6315    287      5    197    0    0   992   984  173  232  1  0 69 30  0
 0  2   6315    287      5    197    0    0     0     0   85  149  0  0 67 33  0
 1  0   6314    289      5    192    1    0  3384  9788  699  597  2  0 80 18  0
 0  1   6313    294      4    184    3    0  5360 10872  651  689  4  1 82 14  0
 0  1   6310    289      4    185    4    0 10580 16796 1333 1276  7  1 82 10  0
 0  1   6308    289      4    181    4    0  6272 23212 1233 1208  5  1 83 12  0
 0  2   6307    290      4    178    2    0  4220  9956  696  740  3  0 72 25  0
 0  2   6306    286      4    182    0    0  2712  3656  317  322  1  0 68 31  0
 0  2   6305    283      4    182    1    0  4000 17172 1020 1094  3  1 81 15  0
 1  0   6302    291      4    171    4    0  8712 25940 1479 1430  8  1 79 12  0

The imported database should be about ~580G and now I am in about ~320G (the import has been running for a couple of days).

Why is the system swappping so heavily?

Is there something I can do (in the middle of the import) to speed it up?

Why I see processes in the b(locked) column of vmstat? could the import itself being blocked by something?

I have globally disabled foreign key checks.

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (2.63 sec)
  • How are you importing it ? Just a dump? Paste the command please – ninjabber Dec 20 '18 at 8:12
  • mysql> use thedatabaseIwanttheimportin and then mysql> source /path/to/dump.sql – pkaramol Dec 20 '18 at 8:27
  • 1
    Show processlist, Show Engine InnoDB status. Please paste them too. Probably grep for locks , writes and reads from the status. – ninjabber Dec 20 '18 at 8:45
  • Setting the InnoDB buffer pool size to 100% of system memory as you have done here is not viable. Swap thrashing is inevitable with this misconfiguration. – Michael - sqlbot Dec 20 '18 at 13:53
0

The system has 48GB of RAM.
innodb_buffer_pool_size = 48000M
Why is the system swappping so heavily?

NO, NO, NO! 48G will lead to swapping and possibly crashing. Use 36G.

You have asked to fill up RAM with the buffer_pool, leaving no room for the OS, MySQL, other caches, etc.

Was the .sql file produced by mysqldump? With what parameters?

  • mysqldump with no parameters at all – pkaramol Dec 20 '18 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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