Mysql – InnoDB: very bad performance on INSERT/UPDATE

innodbMySQLoptimizationperformance

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, Core i7 but no SSD drive.

mysqltuner says:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.14
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 203M (Tables: 23)
[--] Data in InnoDB tables: 1G (Tables: 96)
[--] Data in MEMORY tables: 5M (Tables: 4)
[!!] Total fragmented tables: 37

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 54m 18s (3M q [93.602 qps], 131K conn, TX: 22B, RX: 713M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 4.0G global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 4.2G (12% of installed RAM)
[OK] Slow queries: 0% (535/3M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/98.7M
[OK] Key buffer hit rate: 99.7% (10M cached / 28K reads)
[OK] Query cache efficiency: 66.2% (1M cached / 2M selects)
[!!] Query cache prunes per day: 290517
[!!] Sorts requiring temporary tables: 16% (43K temp sorts / 261K sorts)
[OK] Temporary tables created on disk: 17% (20K on disk / 119K total)
[OK] Thread cache hit rate: 99% (7 created / 131K connections)
[OK] Table cache hit rate: 26% (413 open / 1K opened)
[OK] Open file limit used: 0% (125/100K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 1.3G/3.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_size (> 32M)
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)

I already tried to optimize MySQL configuration but it didn't help so much.
my.cnf:

[mysqld]
innodb_buffer_pool_size = 3GB
innodb_flush_method = O_DIRECT

innodb_additional_mem_pool_size=16M
innodb_autoextend_increment = 128M
innodb_log_file_size = 500M
innodb_log_buffer_size = 750M

query_cache_type = 1
query_cache_limit = 32M
query_cache_size  = 32M

tmp_table_size = 220M
max_heap_table_size = 220M

sql_mode=
event-scheduler=1
long_query_time=2

Most of queries on my slow query log are really simple. Tables are medium so that's wired. However most problematic query is INSERT to table that has ~900k records (InnoDB):

CREATE TABLE `topic_marking` (
    `topic_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `user_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `forum_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `mark_time` INT(10) UNSIGNED NOT NULL,
    UNIQUE INDEX `topic_id_2` (`topic_id`, `user_id`),
    INDEX `forum_id` (`forum_id`),
    INDEX `topic_id` (`topic_id`),
    INDEX `user_id` (`user_id`),
    CONSTRAINT `topic_marking_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`topic_id`) ON DELETE CASCADE,
    CONSTRAINT `topic_marking_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE,
    CONSTRAINT `topic_marking_ibfk_3` FOREIGN KEY (`forum_id`) REFERENCES `forum` (`forum_id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

As you can see there is a lot of indexes and foregin keys. I thought that might be a problem so I decieded to remove all foreign keys and leave only two indexes (topic_id_2 and forum_id). Well, that helped a little bit but didn't eliminate a problem.

What else can I do to improve perfomance except buying additional SDD drive?

I have ~50 queries/sec.
Profiling simple UPDATE query gives results like that:

starting
0.000028
checking permissions
0.000004
Opening tables
0.000017
init
0.000014
System lock
0.000026
updating
0.000033
end
0.000003
Waiting for query cache lock
0.000002
end
0.000066
query end
7.456112
closing tables
0.000019
freeing items
0.000013
logging slow query
0.000054
cleaning up
0.000011

So query_end takes like 7 seconds! What does it mean?

Best Answer

THis is possibly tx flushing.

Check https://stackoverflow.com/questions/6937443/query-end-step-very-long-at-random-times for something similar.

There, adding

innodb_flush_log_at_trx_commit = 0

helped.

If you run a typical 7200RPM large but slow disc, your IOPS budget is VERY limited. Your only real steps here are lying (like up - not commiting, or having the disc physically in write back mode, confirming a write before it happens) or getting more IOPS (which is where SSD really shine - you can easily get a SSD with 60.000 IOPS - that is 400 times the budget.

Lying is not advisable unless you have batteries in place to cover power failures - possibly with a raid controller that has a battery on top, too. If that is an invetment, a SSD is the absolutely most cost effective way to do things.

You most liekly just kille the IO budget. SOmeone with more linux exerperience can step in and tell you how to measure that - i think there is a command iotop that can do that.

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Best Answer

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Related Question