1

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 are having 20 core, 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

migrated from stackoverflow.com Jun 27 '16 at 22:35

This question came from our site for professional and enthusiast programmers.

  • 2
    Wrong question. Examining 48 million rows likely will take a lot of time regardless of number of cores - disk IO and the raw number of rows will be more significant. Chances are your data can be better indexed. – ceejayoz Jun 25 '16 at 19:20
  • 2
    show us the query, table structure, indexe and a explain of your query – Bernd Buffen Jun 25 '16 at 19:32
  • @Drew - if you can use subqueries, than each subquery can use his own thread or optimize the query, so thats not neccessary to use them. but you 100% right, i cant do it to use all cores – Bernd Buffen Jun 25 '16 at 19:46
  • Subqueries, UNIONs, PARTITIONs -- MySQL will not use multiple cores for a single thread. – Rick James Jul 4 '16 at 5:39
  • @RickJames If I understand, Subqueries, UNIONs, PARTITIONs will make use of more cores? – Horaciux Sep 13 '18 at 21:14
8

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.

  • Currently I am using "digital oceans" largest drop let. The application being used in symphony frame work 64GBMemory 20 CoreProcessor 640GBSSD Disk 9TBTransfer Our plan was to test the server using highest data we are expected to have in cloud server so that we can reduce and increase drop let according to our requirement. – nisamudeen97 Jun 26 '16 at 2:55
  • 1
    Oh, cool. Here's the thing. Scaleable web apps usually have a centralized DBMS server machine and multiple web server machines. So, scale down that dbms virtual machine to the 4GB droplet for now.. Then provision two 1GB or 2GB machines and put your web app on them. You can always scale up later when you add users to your system. – O. Jones Jun 26 '16 at 11:10
  • The fact of the matter is most answers on this topic will just be buzz word compliant. – Drew Jun 27 '16 at 22:13
1

In addition to what Ollie says...

"Rows_sent: 10 Rows_examined: 48683733" -- Is that a table scan? A giant GROUP BY? Those are things to work on avoiding in a production server.

For Data Warehousing applications, build and maintain "Summary table(s)" that have subtotals by day (or week) so that the query might hit 400K rows instead of 46M rows. That, alone, will make the query run 10x faster.

If you would like to discuss this further, open a new question with details about that query.

Yet another suggestion: If you have lots of reads, especially long ones, consider a Master-Slave topology. With that, you can scale out reads 'infinitely'. This scale-out can be independent of the scale-out of web servers as Ollie mentioned. This allows for lots and lots of "simultaneous" web users. (That is how the 'heavy hitters' do it.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.