Possible to make MySQL use more than one core?

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.

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

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