Possible to make MySQL use more than one core?

Question

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than 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, the 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 a test server

• 3 separate connections
• each has a 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 an 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 a test (another instance is shut down). This is OK for now.
• innodb_file_per_table = ON

Edit 2

• innodb_flush_log_at_trx_commit = 2
• innodb_use_sys_malloc = ON
• innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this)
• innodb_doublewrite = OFF
• File system = ZFS (And my sysadmin found this: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices)

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

• Rick James: This question is getting stale -- New versions of ALTER can now add/drop indexes much faster. Further note that the question was asking about throwing more cores at an I/O-bound process -- It won't help.

Answer 1

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_concurrency_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.

• Peter VARGA: No offense, but can it be with MySQL 8 and 8 years later almost all questions and answers regarding the performance of InnoDB are not really applicable anymore?
• RolandoMySQLDBA: @AlBundy No offense taken, but please keep in mind the shocking number of people and companies still using MySQL 5.5 (which just went EOL Dec 2018). So, answers posted for tuning 5.5 will still be of value as long as 5.5 is actively used.
• Peter VARGA: Of course, but I meant explicitly for MySQL 8 the posts don't really apply.
• nl-x: I can confirm that on Mysql 5.7.30 on Windows 10, I still can effectively use only 1 out of 2 physical CPU's. Even though there are many threads/connections with many queries. I will try the inno_db settings as mentioned here to see if that helps.
• Hassan Naqvi: I have MySQL 8 running on Ubuntu. I tried to implement this method but it didn't work. MySQL still using single core.

Answer 2

MySQL will automatically use multiple cores, so either your load of 25% is coincidence¹ 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.

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

• Rick James: Now, replication is (optionally) multi-threaded on the Slave. InnoDB has improved since this Answer was written. I would not advise using MyISAM, especially not if compressed.

Answer 3

Note: This Answer is about a single connection using multiple cores. The OP's Question was ambiguous; it incorrectly assumed that MySQL as a whole could not use more than one core. The other Answers correctly point out that the 3-Alter test case is really I/O-bound, hence failing to prove Title question.

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.

(Update in 2015:) Multiple connections with 5.6 maxes out at about 48 cores. 5.7 promises to be even better. (So says Oracle benchmarks.) But still no use of multiple cores for a single connection.

Update (after going to Oracle's OpenWorld): the new version 8.x will not have any parallelism.

Further update -- 8.0.17 has cases where it will use multiple cores on a very few selected queries. (That is, don't get excited.)

Answer 4

IMHO and in the described use-case, you will never use more than one core. The reason is that your workload is IO bound, not CPU bound. As your 3 connections are creating a new Index, each of those needs to read the whole table from disk: this is what is taking time, not computing the Indexes.

Answer 5

Consider that your bottleneck could be your filesystem's IO performance.

In addition to the settings suggested by @RolandoMySQLDBA, I also set the noatime mount settings in /etc/fstab for the partition holding my mysql data directory (/data01/mysql in my case, with /dev/sdb1 mounted to /data01).

By default linux records the access time for EVERY disk read or write which negatively affects IO performance, especially for high IO applications like databases. This means that even reading data from a file triggers a write to disk... WAT!

To disable this, add the noatime mount option in /etc/fstab for the desired mount point as follows (example in my case):

/dev/sdb1 /data01 ext4 defaults,noatime 0 2

Then remount the partition:

mount -o,remount /data01

This should boost the read/write performance of applications using that partition. BUT... nothing beats holding all your data in memory.