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)

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.

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.

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.