Solved

How to force MySQL to use 4 cores on a Quad Core Processor

Posted on 2012-04-04
4
1,331 Views
Last Modified: 2012-04-04
I am looking to make MySQL server take full advantage of my CPU. It is alleged that the 64bit version does take advantage, however it is only using 1 of my 4 cores available (64bit 5.1 MySQL Server for Windows).

Is there a configuration option that needs to be set in the my.ini file or is there something else that needs to be "set" in order for this to work.

I have also tried elevating the task to no avail, and all 4 cores are checked off for it to use, however it is acting like a 32 bit application, even though it is most definitely the 64bit distribution.
0
Comment
Question by:microvb
  • 2
4 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 37808120
that could be licensing feature, the default for free open source is 1 cpu, to use more they want You to buy enterprise version at oracle.com
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37808412
No there should be no additional licensing involved.  http://dev.mysql.com/doc/refman/5.1/en/faqs-general.html#qandaitem-B-1-1-8  states that "MySQL is fully multi-threaded, and will make use of multiple CPUs, provided that the operating system supports them.".

I'm able to use multi-core everytime, even on my desktop, but I only use linux boxes so I can't tell what to check on a Windows installation.  What I do notice though, is most of the time I only see 1 cpu being utilized, with additional cpus being used every now and then during high loads.  @microvb, just how much load is your instance experiencing right now?
0
 
LVL 3

Author Comment

by:microvb
ID: 37809017
@johanntagle

Some of the queries i need to process take a long amount of time due to extra calculations / etc being performed within the query. I was able to dramatically reduce some of the queries with a bit of index tweaking, however it seems that no single query can cause the server to use more than 1 core ...  so 1 core per query, where I would like to have it automatically roll into the other cores for queries that would ordinarily take a long time, lots of calculations / etc.

(this one query was going to take 45 minutes, reduced down to 3 seconds through indexes).
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37809072
Ah yes you are bound to one core per sql.  That is by design.  one way around it to use table partitioning and rewrite  the query to multiple ones.  i haven't had the need to do it so I really cannot explain it well, but you can google for "mysql intra-query parallelism"
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question