MySQL to use all 20 cores I have

MySQLoptimization

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers have 20 cores, 64G ram.

Meanwhile I could see while executing longest SQL queries my MySQL server is using two cores maximum hence it is taking more than 38 seconds to execute my longest query. See the result below.

User@Host: dev_data @ localhost []

Query_time: 38.113460 Lock_time: 0.000514

Rows_sent: 10 Rows_examined: 48683733

How can I configure my MySQL server so that it uses all the 20 cores to handle the query? MySQL version is 5.6.

Atop result below.

enter image description here

Best Answer

tl;dr You can't force MySQL to use a lot of cores.

40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though.

MySQL can use a lot of CPU cores as of recent releases. But it doesn't use them to accelerate single queries; that sort of workload is not embarrassingly parallel. You can't get it to use a large number of cores for a single query. It simply doesn't work that way.

When it uses many cores, it uses them to handle simultaneous workload from many different connections. But of all those connections hit the same tables, it won't be able to use a lot of cores even if they are available.

At any rate, you should upgrade to version 5.7. It uses multithreading for more things than 5.6.

You will be able to improve performance with appropriate indexing for your query workloads. We can't advise you about that because you haven't offered any specifics about existing tables, indexes, queries, or simultaneous access by multiple clients.

Your server, with 20 cores and 64G of RAM, is overprovisioned in cores and RAM for MySQL, and for the sort of workload you mention. You didn't mention your server's I/O capabilities. I guess that replacing your mechanical disk drives with SSD drives using PCIe interfaces will make the most difference. But that's a guess based on little information.

My blunt advice: sell that massive server, or put it into service as a hypervisor host to use for lots of virtual machines for other things. It's overkill for MySQL.

Get a server with 16Gb and four fast cores. Use the extra money to provision it with as many PCIe SSD drives as you need for your application. Don't consider purchasing SSD drives smaller than about 0.2 terabyte; they are slower than the larger ones. Index your tables properly for your queries. For tables of the size you mention, don't worry about core utiization; instead worry about I/O.

You can probably try all this out for a few tens of dollars on a cloud virtual machine (Amazon, Rack Space, Digital Ocean) so you don't waste money on another overprovisioned server.

Related Solutions

Mysql – Insert-heavy InnoDB table won’t use all the CPU

You have to crank up innodb_io_capacity as well.

The default is 200. Raise it to 5000 for starters. I would go to 20000.

You may also want to make sure ib_logfile0 and ib_logfile1 are sufficiently large. The default value for innodb_log_file_size is 5M. I would raise that to 1G for starters.

A larger InnoDB Buffer Pool would also help, perhaps 4G.

To recap, use these additional settings:

[mysqld]
innodb_io_capacity=5000
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

After adding these settings to my.cnf, to resize ib_logfile0/ib_logfile1 do the following

service mysql stop
rm -f /var/log/mysql/ib_logfile[01]
service mysql start

The files ib_logfile0 and ib_logfile1 are recreated. Don't worry, I have done this many times.

You may have to do something out of the ordinary for InnoDB

Try the following:

Full Table Lock on the InnoDB table
Perform the Bulk Load
Release the Lock

MySQL the.cnf Optimization

Look at this line

[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G

The sum of all your MyISAM indexes is 131.6G

You should lower key_buffer_size to 140G. This will give back 110G.

I have a little suggestion for you. You may want to consider using a dedicate keycache for each large table. See my Nov 16, 2012 post Cache all mysql table

Look at this line

[!!] InnoDB data size / buffer pool: 2.2G/2.0G

innodb_buffer_pool_size could be raised to 2560M (That's 2.5G)

Look at these lines

read_buffer_size=2G
sort_buffer_size = 2G

Why is so big? If you change both of these to 32M, you can increase max_connections. In fact, do the following right now:

SET @ThrityTwoMeg = 1024 * 1024 * 32
SET GLOBAL read_buffer_size = @ThrityTwoMeg;
SET GLOBAL sort_buffer_size = @ThrityTwoMeg;
SET GLOBAL join_buffer_size = @ThrityTwoMeg;

Then, go rerun mysqltuner.pl and you will see a dramatic difference from 4.1G per thread

BTW go change join_buffer_size to 32M in my.cnf

Give it a Try !!!

UPDATE 2013-04-11 16:53 EDT

I just thought of another change you can make to increase SELECT speed against MyISAM tables. For every MyISAM you need to read a lot from, do this to the table:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

Believe it or not, this will bloat the table 80-100% in size, but SELECTs will go 20-25% faster. I have written about this many times. Here is just few of my posts:

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Best Answer

Related Solutions

Mysql – Insert-heavy InnoDB table won’t use all the CPU

MySQL the.cnf Optimization

UPDATE 2013-04-11 16:53 EDT

Related Question