Mysql – Possible to make MySQL use more than one core

innodbMySQLmysql-5.5performancetuning

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

Best Answer

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.

Related Solutions

Sql-server – way to make SQL Server’s Database Tuning Advisor use more than one core

I expect the analysis is carried out serially, one query at a time, as doing so in parallel would be unreliable.

DTA produces recommendations by creating hypothetical indexes and evaluating the impact on a query's execution plan. If analysis were carried out on multiple queries at a time, the index created for one query could influence the analysis of another.

A hypothetical index is created using the undocumented command CREATE INDEX WITH STATISTICS_ONLY and as the name implies this creates just the statistics for the index, without building the physical structure.

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before