Insert Statement Take too long to insert - MYSQL

Question

Following Insert query taking too long time to insert :

Query_time: 14.798782 Lock_time: 0.000062 Rows_sent: 0 Rows_examined: 0

SET timestamp=1523459374;
insert into patient_last_examined
 (patient_id,operator_id,section_name,created_date,status,save_or_review)
VALUES ('227350','72','Allergies','2018-04-11 11:09:20','0','1');

Table have 17k records before insertion and
Total Column : 13
Index Columns : id (PK), patient_id,section_name
Db Engine : Inno DB

Can you please suggest how to increase the performance?
(At timestamp=1523459374, 15-20 more insertion take place for other tables)

• Wilson Hauck: Please post A) SHOW CREATE TABLE patient_last_examined; and B) SHOW INDEX FROM patient_last_examined; Is query_time typically ~14 seconds to insert ONE row?
• Wilson Hauck: Additional information request, please. Post here or in pastebin.com RAM on your Host server complete my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) complete MySQLTuner.com report if readily available Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report for analysis of your configuration.
• Shilpa Garg: @WilsonHauck Please find pastebin.com/07PuHnVe
• Wilson Hauck: Additional information request, please. Post here or in pastebin.com RAM available on your Host server? OS you are using? Complete my.cnf-ini Text results of: B) SHOW GLOBAL VARIABLES; C) complete MySQLTuner.com report if readily available Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report for analysis of your configuration.
• Shilpa Garg: @WilsonHauck Sorry I can't share SHOW GLOBAL VARIABLES and MySQLTuner report

Answer 1

The table is MyISAM

You should really switch to InnoDB.

If something taking 14.7 (or more) seconds touches that table, then writes can occur until it finishes. The "something" could be mysqldump, a complex SELECT, etc, etc.

InnoDB avoids most such lengthy hangs.

• Shilpa Garg: ok i will try with this
• Shilpa Garg: I am sorry, Table is already InnoDB
• Rick James: @ShilpaGarg -- the pastbin said MyISAM for patient_last_examined ???
• Shilpa Garg: I am sorry for inconvenience, I have changed it already with InnoDB on another Server. But by mistaken provided you script of old server. And moreover I am facing this issue with InnoDB server
• Rick James: Do the length hangs happen with InnoDB?

Answer 2

Table_open_cache_hits 53758
Table_open_cache_misses 2768
Table_open_cache_overflows 870

What is the value of table_open_cache? It is possibly too small. And check ulimit in the OS.

Please provide SHOW VARIABLES LIKE 'query_cache%' -- I suspect you have a large cache, and the "pruning" of it (which happens on INSERTs) is clogging the system.

How much RAM do you have? Is there any swapping?

The STATUS has several hints of slow queries. What other slow queries are there? Especially touching the same table.

• Shilpa Garg: I will try to provide you these details by EOD

Answer 3

You just try these three steps to fast insertion:

• InnoDB supports transaction If you have multiple insertion then use transaction in your insertion.

  START TRANSACTION
  Your insertion query
  Commit;
  

  As, innoDB has to do a commit after each insertion statement which slowed down the insertion process.

• If using Transaction does not solve your problem then check innodb_fush_log_at_trx_commit.

Default setting of InnoDB is innodb-flush-log-at-trx-commit=1
In this case, InnoDB will write the log buffer to transaction log and flush to durable storage for every transaction. Here, for all transaction commit, InnoDB will write to log and then write to disk, if in case the slower disk storage, it will badly impact the performance, i.e. the number of InnoDB transaction per second will be reduced.;

So, if you want fast insertion then set

 innodb-flush-log-at-trx-commit=0; 
 sync_binlog=0;

When you set innodb_flush_log_trx_at_commit=0, InnoDB will write the modified data (in InnoDB Buffer Pool) to log file (ib_logfile) and flush the log file (write to disk) every second, but it will not do anything at transaction commit. So, your insertion will be fast.
Here, in case if there is a power failure or system crash, all the unflushed data will not be recoverable, since, it is not either written to log file or stored disk.

Or you may set

 innodb_flush_log_at_trx_commit=2

When you set innodb_flush_log_trx_commit = 2, InnoDB will write the log buffer to log file at every commit, but don't write data to disk. InnoDB flushes data once in every second. Option 2, even if there is a power failure or system crash, data will be available in log file and can be recoverable.

• InnoDB is very RAM dependent, you might find better result if you tweak the settings. What's your innodb buffer-pool size?
  Make sure you've set it to about 75% of your RAM.