Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Your table is rather small (below 1 million) and you dont really have MANY indices. Can you check that you have the proper indices on the referenced indices? A missing index on topic(topic_id) etc. may orce table scans there. You do also not say how many inserts you do (per minute) so it is very hard to judge whether your disc (of unknown spead) is to blame. –  TomTom Feb 5 at 9:25
    
Do a SET PROFILING=1 and insert to see whats happening.Also whats your file system?XFS by any chance? –  Mihai Feb 5 at 9:55
    
@TomTom: I removed foreign keys from the table. I helped just a little bit. I have ~60 queries/sec. –  Bald Feb 5 at 10:17
    
@Mihai: I am not sure what is my file system (I am using Freebsd). Profiler says that ''query end'' takes the longest. –  Bald Feb 5 at 10:39
    
Depends on you hardware(SAN for example) , sometimes innodb_flush_method = O_DIRECT can decrease performance, try to use the default value.Also, try to disable query cache query_cache_type = 0 –  altmannmarcelo Feb 5 at 10:49
show 4 more comments

1 Answer 1

THis is possibly tx flushing.

Check http://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.

share|improve this answer
    
Thanks. I know about innodb_flush_log_at_trx_commit but I must care about transactions. Maybe buying SSD is not such a bad idea ;) –  Bald Feb 5 at 12:19
    
If you care about tx efficiency all the way you need a bigger IOPS budget (if an IOTOP analysis shows that - please so that one). If you go that way, anything besides ssd is utterly stupid financially. THe next step up would be enterprise level SAS drives, and if you go IOPS/USD on those - dead. Like dead. By a factor of 100. SSD - a good one - is the best deal. Totally taking over the db space. –  TomTom Feb 5 at 12:24
    
Well, turned out that one of the HDD is corrupted. I disabled it and everything works just fine –  Bald Feb 11 at 20:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.