J
J
jonasas2015-10-10 23:36:55
MySQL
jonasas, 2015-10-10 23:36:55

How to fix very slow insert in mysql?

There is a database in mysql. The main load is reading. Works under rather big load (160 requests per second).
Sometimes there is an insertion into the table (no more than 50 times a day). And the cold insertion operation takes about a minute! If you immediately make another insert, then it passes faster (a few milliseconds).
All tables in innodb, each with a couple of files.
Where should I look, what should I tweak?
Here is mysqltuner.pl analysis:

>>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.43-0+deb7u1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 96G (Tables: 342)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 74

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 21h 29m 57s (214M q [166.564 qps], 6M conn, TX: 456B, RX: 146B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 9.3G global + 18.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 12.0G (18% of installed RAM)
[OK] Slow queries: 0% (6K/214M)
[OK] Highest usage of available connections: 56% (85/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.4M
[OK] Key buffer hit rate: 100.0% (26M cached / 1 reads)
[OK] Query cache efficiency: 74.7% (148M cached / 198M selects)
[!!] Query cache prunes per day: 1326155
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 20M sorts)
[!!] Joins performed without indexes: 1047995
[OK] Temporary tables created on disk: 14% (1M on disk / 8M total)
[OK] Thread cache hit rate: 99% (574 created / 6M connections)
[OK] Table cache hit rate: 94% (937 open / 994 opened)
[OK] Open file limit used: 2% (49/2K)
[OK] Table locks acquired immediately: 100% (111M immediate / 111M locks)
[!!] InnoDB data size / buffer pool: 96.9G/8.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 1G) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 96G)

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question