Possible to make MySQL use more than one core?

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

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

• 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 = ZF

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.

StackExchange Database Administrators Q#5666, answer score: 142