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.

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help

Setup

The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:

  • Primary: 96GB RAM, 8 cores + single RAID 10 array
  • Test: 32GB RAM with 4 cores
  • The biggest DB is 540 GB, total is around 1.1TB and mostly InnoDB tables
  • Solaris 10 Intel-64
  • MySQL 5.5.x

Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.

Observations on test server

  • 3 separate connections
  • each have an concurrent (and different) ALTER TABLE..DROP KEY..ADD INDEX
  • the 3 tables have a 2.5, 3.8 and 4.5 million rows
  • CPU usage goes up to 25% (one core is maxed out) and no higher
  • the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)

Questions

  1. What setting or patch is required to allow more than one core to be used?
    That is, why doesn't MySQL use all cores available? (like other RDBMS)
  2. Is it a consequence of replication?

Other notes

  • I understand the difference between a RDBMS "thread" and an OS "thread"
  • I'm not asking about any form of parallelism
  • Some of the system variables for innodb and threads are sub-optimal
    (looking for a quick win)
  • Short term, I'm unable to change the disk layout
  • OS can be tweaked if needed
  • A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)

Edit 1

  • innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores
  • innodb_buffer_pool_size is 80GB on primary, 10GB on test (another instance is shut down). This is OK for now.
  • innodb_file_per_table = ON

Edit 2

To test

  • innodb_flush_method isn't showing as O_DIRECT when it should be
  • will follow RolandoMySQLDBA's settings

Let me know if I've missed anything important

Cheers

Update

Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
Result: > 1 core used for the tests = positive result

share|improve this question
    
@Dtest: innodb_file_per_table = ON. SHOW ENGINE INNODB STATUS\G is command line only? –  gbn Sep 12 '11 at 16:10
    
@Dtest: I got no output in SQLyog and would need to ask someone to run this from command line –  gbn Sep 12 '11 at 17:56
1  
webyog.com/forums/index.php?showtopic=1290 should work without the \G. Also, I think SHOW INNODB STATUS is deprecated in favor of SHOW ENGINE INNODB STATUS in 5.5 (I get an error running the former in command-line. –  Derek Downey Sep 12 '11 at 18:00
1  
While all the other answers are good, since you are a developer, I would recommend taking a look at Shard Query code.google.com/p/shard-query It may help you, especially in a datawarehouse environment. –  Jonathan Sep 14 '11 at 8:10
    
Thanks, it is one option we've thought about. I=m also taking on the DBA role too. –  gbn Sep 14 '11 at 8:33

3 Answers 3

up vote 33 down vote accepted

I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.

I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrecy_tickets to open for a given MySQL instance setup.

Once you set innodb_thread_concurrency to 0, you can set innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) to the maximum value of 64. This should engage more cores.

share|improve this answer
    
Will try this. I was going to set innodb_thread_concurrency to 0 anyway based on stuff I've read too –  gbn Sep 12 '11 at 16:09
    
+1 for innodb_thread_concurrency = 0 –  randy melder Sep 12 '11 at 17:01
    
That did it. Thank you –  gbn Sep 13 '11 at 14:26
    
@gbn - Coming from the #1 guy in DBA.SE, a thank you is a confidence booster and very much appreciated. Thank You and you're welcome !!! –  RolandoMySQLDBA Sep 13 '11 at 15:00

MySQL will automatically use multiple cores, so either your load of 25% is coincidence1 or a potential misconfiguration on Solaris. I won't pretend to know how to tune solaris, but here's an article that goes over some solaris-specific tuning information.

The InnoDB tuning pages have been given an overhaul in MySQL 5.5, so there is some good info there as well. From the InnoDB disk IO tips:

If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it equal to more than 80% of physical memory.

Some other things to check:

  • Switching the innodb_flush_method to O_DIRECT is worth testing. If this helps, you might need to mount the filesystem with forcedirectio option

  • Change the innodb_flush_log_at_trx_commit from 1 to 0 (if you don't mind losing the last second on mysql crash) or 2( if you don't mind losing the last second on OS crash).

  • Check the value of innodb_use_sys_malloc. This article has more information on the variable.

    At that time, there were no memory allocator libraries tuned for multi-core CPUs. Therefore, InnoDB implemented its own memory allocator in the mem subsystem. This allocator is guarded by a single mutex, which may become a bottleneck.

    But there are some caveats at the end of the section about what it means to turn the variable on (it is on by default in 5.5).

    Note that when the InnoDB memory allocator is disabled, InnoDB will ignore the value of the parameter innodb_additional_mem_pool_size.

  • It is possible that replication is causing some of the problem. I realize you're not interested in parallelism, but from the description of this worklog:

    Currently, replication does not scale well on multi-core machines. The single slave thread execute replication events one by one and may not cope with a load produced by concurrent multiple client connections served by separate master server's CPU.

Ultimately, InnoDB might not be the best engine for datawarehousing, because of the disk-based operations that occur. You could consider altering the datawarehouse table(s) to be Compressed MyISAM.

1By coincidence, I mean there is a bottleneck that prevents your load from increasing above 25%, but isn't necessarily a forced single-core issue.

share|improve this answer
    
Thanks. Settings section added to question. The problem is several intensive queries using a single core: not memory or thread settings yet. More threads still run on the same core –  gbn Sep 12 '11 at 15:29
    
@gbn thanks for the update, still looking. I was thinking it was a 'coincidence'. I'm wondering if it's a solaris-only issue (developers.sun.com/solaris/articles/mysql_perf_tune.html ), but don't know much about that system. –  Derek Downey Sep 12 '11 at 15:31
    
Your answer is still quite valid under most circumstances, especially the last comment. Nothing here to really add. So, +1 !!! –  RolandoMySQLDBA Sep 12 '11 at 15:41
1  
@Dtest: I'll chuck that article over to the Solaris admin too Some good stuff there –  gbn Sep 12 '11 at 17:25

A single connection will only use a single core. (OK, InnoDB uses other threads, hence cores, for some I/O processing, but that is not significant.)

You had 3 ALTERs, so you were not using much more than 3 core's worth.

Alas, not even PARTITION uses multiple cores.

Until recently, multiple connections would max out after 4-8 cores. Percona's Xtradb (included in MariaDB) makes better use of multiple cores, but still only one per thread. They max out at about 32 cores.

share|improve this answer

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.