mysql 5.7 is working slow on ubuntu 18.04 LTS
I am currently using ubuntu 18.04 server ( with 8GB RAM , 100 GB HDD & Intel I3 processor ) for project deployment, & am using mysql 5.7 database server , but whenever I tries to insert data in database it has been inserting too slow as compare to Ubuntu 16.04 with same configuration .
- Ubuntu 16.04 -> Mysql 5.7.24 -> insert data rate ( 250k-350k / Min )
- Ubuntu 18.04 -> Mysql 5.7.24 -> insert data rate ( 10k-15k / Min )
AS I check performance of the system while inserting the data into mysql then I found
- Ubuntu 16.04 -> CPU utilization 60-70 %
- Ubuntu 18.04 -> CPU utilization 7-8 %
Ubuntu 18.04 restrict mysql application from utilizing maximum CPU , Please let me know the solution for above.
2 Answers
The Linux Disk I/O elevator algorithm was the culprit.
$ cat /sys/block/{device-name}/queue/scheduler
noop deadline [cfq]change the io mode to deadline, if use the ssd,should use noop
echo deadline > /sys/block/{device-name}/queue/schedulerAppend the choice of elevator algorithm to this kernel boot command entry. The changed linux kernel boot command will look like:
linux /vmlinuz-4.4.0-31-generic root=/dev/mapper/ashish--devbox--vg-root ro elevator=deadlineWhile implementing ubuntu 18.04, Ubuntu has made a notable improvement in CPU usage. The Ubuntu has greatly improved and reduced CPU usage. They’ve also fixed hundreds of bugs and made hundreds of other small improvements.
Just because of that MySQL taking too much time to perform it's operation This issue has been solved with following MySQL parameters, which force MYSQL to utilize more CPU thread to perform the operation.
Just solved it. By adding the following line to mysqld.cnf has solved the problem:
innodb_read_io_threads=4
innodb_write_io_threads=8 #To stress the double write buffer
innodb_buffer_pool_size=20G # 70-80% available Memory
innodb_buffer_pool_load_at_startup=ON
innodb_log_file_size = 32M #Small log files, more page flush
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
skip-innodb_doublewrite #commented or not depending on test
