Why SSD loaded 100% with INSERT in Mysql?

0 like 0 dislike
10 views
Good day!
There is a server (64GB ram) with a SSD disk (samsung 500gb), the server is not loaded.
As soon as you start to do INSERT to a mysql table via PHP, then the disk just "dies" - 100% IO load process [jbd2/sda3-8], with the SELECT all works fine with multiple sites.
What to look for and where to look?
Here is recorded the output of iotop
https://drive.google.com/file/d/13xa80U9DvCjGxAu5z...

Config mysql
[client] port=3306 socket=/var/run/mysqld/mysqld.sock [mysqld_safe] socket=/var/run/mysqld/mysqld.sock [mysqld] user=mysql pid-file=/var/run/mysqld/mysqld.pid socket=/var/run/mysqld/mysqld.sock port=3306 basedir=/usr datadir=/var/lib/mysql tmpdir=/tmp lc-messages-dir=/usr/share/mysql log_error=/var/log/mysql/error.log symbolic-links=0 skip-external-locking key_buffer_size = 128M max_allowed_packet = 32M #table_open_cache = 256 #sort_buffer_size = 1M #read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 #query_cache_size= 16M query_cache_size = 0 #tuner query_cache_type = 0 #tuner query_cache_limit = 1M #tuner join_buffer_size = 128M #tuner sort_buffer_size = 128M #my initiative read_rnd_buffer_size = 128M #my initiative tmp_table_size = 4GB #tuner max_heap_table_size = 4GB #tuner innodb_buffer_pool_instances = 8 #my initiative https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances recommend to install no more than the physical cores # Should be calculated according to the formula ( innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of the buffer pool size) innodb_buffer_pool_size = 48GB # tuner innodb_log_files_in_group = 2 #tuner innodb_log_file_size = 6GB #tuner #innodb_additional_mem_pool_size = 128MB #my initiative - DEPRECATED innodb_log_buffer_size = 1GB #my initiative and can be up to 15% of the size of the logs, A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. #innodb_use_native_aio = 0 innodb_file_per_table #The open_files_limit should typically be set to at least 2x-3x #that of table_cache if you have heavy MyISAM usage. #FORMULA : 10 + max_connections + (table_open_cache * 2) table_open_cache = 5000 #tuner open_files_limit = 5000 #tuner #max_connections=200 max_connections=100 max_user_connections=50 wait_timeout=10 interactive_timeout=28800 # for Scripts and Scrapers! (Was 10) long_query_time=5 !includedir /etc/mysql/conf.d/

asked by | 10 views

3 Answers

0 like 0 dislike
caching on the ssd disconnected ?

view manuals on the subject of cache and RAM
\rhttps://dev.mysql.com/doc/
answered by
0 like 0 dislike
wtfowned perhaps you have this table too much to index and INSERT query, the indices start date, as this process is very resource intensive.
answered by
0 like 0 dislike
0. I wouldn't move from innodb to myisam, usually go to the opposite. In Myisam a lot of problems solved in the innodb.
1. To do multiple single inserts = evil. Transaction and the pieces 1/10/20/etc lines more correctly.
2. What is the value of innodb_flush_log_at_trx_commit ? poprobute put the value of 2, about risks, see the link.
3. Also, there are innodb_doublewrite . That would reduce the load on the disk, you can try to disable it, but then again, at your own risk.
answered by

Related questions

0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
asked May 11 by itsfost
110,581 questions
257,150 answers
0 comments
477 users