Accelerate MySQL insert / update 5-10 times

A bit of theory. On UNIX operating systems, there is a file system partition that is physically located in RAM, but allows you to work with it as with a regular disk drive. The access speed of a hard disk drive is approximately 1 ms. Memory access speed - 0.001 ms. Let's try to apply this to the MySQL database in order to get the most out of insert / update operations.

First, check the speed of random writing to the hard disk:

time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare 1000 files, 102Kb each, 99Mb total real 0m47.682s 

Now the same for shared memory (/ run / shm or / dev / shm):

 time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare 1000 files, 102Kb each, 99Mb total real 0m0.083s 

Compare the results and see that the creation time of 1000 files decreased 574 times. Good. So, we should expect an increase in the speed of writing to the database.
')
Perform the following:

1) Check the size and free space for / run / shm

 df -h | grep -P "Filesystem|shm" Filesystem Size Used Avail Use% Mounted on none 16G 782M 15,2G 4.8% /run/shm 

2) Check how much space the database takes

 sudo ls -lh /var/lib/mysql/ | grep ibdata1 -rw-rw---- 1 mysql mysql 4.5G  14 05:21 ibdata1 

It means that we can transfer the base to / run / shm

3) Stop MySQL:

 sudo service mysql stop 

4) Create directories and copy data:

 sudo mkdir /run/shm/mysql-lib sudo chown mysql:mysql /run/shm/mysql-lib sudo cp -rp /var/lib/mysql/* /run/shm/mysql-lib/ 

5) Rule config:

 sudo nano /etc/mysql/my.cnf [mysqld] #datadir = /var/lib/mysql datadir = /run/shm/mysql-lib 

6) AppArmor rule:

 sudo nano /etc/apparmor.d/usr.sbin.mysqld #/var/lib/mysql/ r, #/var/lib/mysql/** rwk, /run/shm/mysql-lib/ r, /run/shm/mysql-lib/** rwk, 

7) We start MySQL

 sudo service mysql start 

* If the service does not start - see /var/log/mysql/error.log

Now the fun part. Checking what happened.

I conducted the test on the hard disk in advance, so I immediately give the results.

  HDD Shared mem Insert 10 000 000  202  66  (bulk = 1000) Update 100 000  122  16  (id = rnd) 

Update was performed at a random interval [1 000 000 - 9 000 000] for the primary key (id). Extreme values ​​are dropped so that the engine “digs” inside the table.

Significant increase in speed on INSERT and even greater on UPDATE.
Less for insertion, because MySQL recalculates indexes and organizes data.

In MySQL settings set:
innodb_buffer_pool_size = 1024M
If you put less, then the speed UPDATE for HDD naturally falls.

innodb_flush_log_at_trx_commit = 2
As such, we do not have transactions here and this does not affect the speed. Nevertheless, we leave this value equal to 2.

With such a scheme, it is crucial to write a binary log and make a backup regularly. We can reduce recording costs as much as possible only by specifying a separate hard disk for the binary. Sequential write speed on the hard drive is much higher than random. Therefore, we put into the system an additional hard disk, mount it, for example, in / mnt / hddbin /, and specify the path for the binary log in my.ini: log_bin = /mnt/hddbin/mysql-bin.log

Do not forget to add scripts to reboot and shutdown the system. Check out the / etc / rc * folder. This is usually 0 (system shutdown) and 6 (reboot). The manual how to add scripts is easy to find in google. The script stops MySQL before restarting or shutting down the system, then copies the / run / shm / mysql-lib folder to the hard disk. When the system is turned on, the script restores the data from the hard disk to the / run / shm / mysql-lib folder and then starts MySQL.

Also add a simple bash or perl script to monitor free memory in / run / shm. You can connect Zabbix.