1

Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good in 2 weeks) as a MASTER replicator, locked all tables for reading, dumped the data which resulted in a 34GB .sql file and unlocked the tables. Afterwards I copied the file over to the other server and ran mysql import through mysql-client using the following code 

cat default-start-import.sql newdatabase.sql default-end-import.sql | mysql -uroot -p newdatabase

With the contents of default-start-import.sql and default-end-import.sql being the following

default-start-import.sql

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

default-end-import.sql

COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

The problem is, this 34GB dump has been running for 2 days and by looking at the processlist and the amount of tables I have, I'd say its at about 50% import. And this supposedly slave server is not doing anything else at the moment. It's idling.

The CPU usage my MySQL is at 9% at worst and memory usage is at 44% at worst. Looking at the queries it takes about 1-2 seconds to insert a row. I have tables with millions of rows. If each row takes a second this import will take years to complete. I've set vm.swappiness to 10 in sysctl to reduce hard drive swapping and the server runs with steady 2.4 load, which is largely - fine. I wouldn't say it's overburdened.

I've disabled slow query log

I've set innodb_flush_method = O_DIRECT I've given innodb a buffer of 20 gigabytes

Is there anything else I could do to speed up the process. This feels like an insane amount of timewaste. It's just 34GB, I'm sure there are organizations that bring up slave servers with hundreds of gigabytes of databases in no time and here I am struggling on 30gb database. How?!?

Improve this question
6
  • 1
    Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL server being loaded? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide SET GLOBAL suggestions.
    – Wilson Hauck
    Commented Apr 3, 2020 at 16:19
  • 1
    pastebin.com/xw32wc0L - Memory. The MySQL server runs on 2TB HDDs. pastebin.com/GtFDpeBz - output of SHOW GLOBAL STATUS aaaand pastebin.com/9Qhjuca3 - is the mysqltunner report. I accepted @pifor 's answer and have since completed the task but any information regarding is very helpful the CPU this server is running with is a modest Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz 4-core 8-thread CPU. We've since preordered a Intel Xeon box but we're waiting delivery but due to COVID we're going to get invoiced if we dont stop the servers.
    – Sk1ppeR
    Commented Apr 3, 2020 at 16:51
  • Please also post C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; You may have to ask your HOST provider to provide the following reports from OS Command Prompt, htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count,
    – Wilson Hauck
    Commented Apr 3, 2020 at 19:00
  • Workload analysis can proceed after C) and D) are posted. Do you have flexibility to use Skype TALK (in English)? My Skype ID is [email protected]
    – Wilson Hauck
    Commented Apr 3, 2020 at 19:28
  • Well pastebin.com/LVF7DNiD - is the output of SHOW GLOBAL VARIABLES and pastebin.com/VHb1w1CX - is the output of SHOW FULL PROCESSLIST. Like I said, this server is basically idling and was idling while I was importing the database. After I imported the database I turned it into a slave. imgur.com/a/3EqVBoK - this is a full screenshot of the top command few hours after the backup was complete. Before that while it was importing the load was 2.9 - 3.5 at worst. While looking at the "SHOW PROCESSLIST" during backup it was executing 1 insert query per sec and nothing else.
    – Sk1ppeR
    Commented Apr 3, 2020 at 20:06

2 Answers 2

Reset to default
3

Large databases should be backed up physically (i.e by copying the physical database files) instead of logically (i.e by exporting in SQL or something similar). If you use Oracle MySQL Community Edition, you should use xtrabackup. If you use MariaDB there is a integrated feature in the last releases: https://mariadb.com/kb/en/mariabackup/.

Improve this answer
1
  • Okay, I'll look into it. Thing is the database is on a Debian Wheezy and xtrabackup doesn't support it on the download page at least. I'll try with the Linux-generic download. Hope that works. Thanks for the info.
    – Sk1ppeR
    Commented Apr 3, 2020 at 11:15
1

Suggestions to consider for your my.cnf [mysqld] section

performance_schema=0  # from ON to conserve CPU cycles
key_buffer_size=200M  # from 2G to support 201M of MyISAM indexes
query_cache_size=0  # from 256M because query_cache_type = OFF conserve RAM
thread_concurrency=6  # from 16, you only have 4 cores/8 threads, leave 2 free
innodb_thread_concurrency=6  # to match thread_concurrency
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function every SECOND
innodb_log_file_size=1G  # from 50M to support ~ 1 HR logging
innodb_log_buffer_size=500M  # from 8M to support ~ 30 minutes in RAM
innodb_io_capacity=900  # from 200 to use more of IOPS capacity
innodb_change_buffer_max_size=50  # from 25 (percent) for higher INSERT rate per second

view my profile, Network profile for free downloadable Utility Scripts and contact info there are more opportunities to improve performance for every day use.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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