Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the difference of running MySQL on 4cores vs 6cores vs 8cores and so on.

Do different storage engines use CPU differently?

share|improve this question
    
Related: dba.stackexchange.com/questions/5666/… –  gbn Sep 18 '11 at 14:55
    
its related but don't address the behaviors of different storage engines toward multi core CPUs. –  Rick James Sep 19 '11 at 15:17
1  
Indeed. Which is why there is no "close as duplicate" vote... –  gbn Sep 19 '11 at 16:33
    
This is a wonderful community, I am still learning how to use this site. –  Rick James Sep 19 '11 at 19:17
    
Hi buddy take a look here: mysql-cluster-blog.com you find at least something –  user16618 Dec 31 '12 at 1:57
add comment

2 Answers

up vote 13 down vote accepted

When it comes to MySQL, there is no comparison among storage engines except that it falls into two basic categories:

MySQL features the use of several storage engines

As for the storage engines listed, the only ones that have ACID-compliance are InnoDB and NDB. Why is this importatnt to mention? Two reasons:

  • Other storage engines are simply not benefited with the presence of more cores, other than basic disk I/O, CPU usage, and overall throughput.
  • The code for each non-transactional storage engine, which dictates basically 14 internal operations regardless of storage engine, was not designed to leverage the accessing of multiple cores.

InnoDB under MySQL 5.5, InnoDB Plugin), and Percona Server's XtraDB have options you can set in order to access multiple cores (Percona Server has been doing so longer). In fact, Percona injects about 30,000 lines of code specifically for performance enhancement of InnoDB with each new GA release of MySQL source code. We can be sure Oracle has included its own enhancements from their own think tank to run within InnoDB for multicore operation (since MySQL 5.1.38).

With the need to perform MVCC on data in conjunction with row/page locking, transaction performance can now be instrumented, measured, and configured.

If there is one thing I have learned about using multiple cores, it is that you must tune InnoDB effectively and not just rely on InnoDB out of the box.

UPDATE 2011-09-20 08:03 EDT

With regard to InnoDB benefiting from all cores, we need to keep things in persepctive. The cores must also tend to other matters (OS, Disk, Memory, Applications, Monitoring, etc.) in the Database Server. For those with modest budgets, many tend to have a Database Server also provide NFS, monitoring from Munin, app support for JBoss, PHP, and the list goes on. If you want MySQL, more specifically InnoDB, to use more cores, the Database Server must be dedicated exclusively to MySQL and the OS/Disk/Memory must tend to MySQL only. Given this perspective, InnoDB will engage more cores withtout a doubt.

As for InnoDB Plugin, it was mentioned simply to show earlier initiatives to have a better InnoDB on the part of MySQL (eh, Oracle. Sorry, still doesn't roll off the tongue yet). New variables to summon more core activity became evident from MySQL 5.1.38.

For example, innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. The default and max settings being so different (4 - 64) shows that InnoDB is as multithreaded and core intensive as you configure it !!!

Addressing the needs of the MySQL community to access more cores with InnoDB was lead by Percona. Consequently, MySQL started to follow suit. I have to admit that Oracle (yuck) made the necessary improvements for more core activity.

share|improve this answer
    
InnoDB under MySQL 5.5 tuned as you suggested above can benefit from all cores? {bit confused about InnoDB plugin} –  Rick James Sep 20 '11 at 8:51
    
@Rick - Further addressed your comment in my answer –  RolandoMySQLDBA Sep 20 '11 at 12:02
    
Here it seems to be a completely different story and MyISAM seems to fall flat on face when it comes to utilizing multi cores but on other side at dba.stackexchange.com/questions/5974/best-of-myisam-and-innodb MyISAM has advantages. So, it seems to be a tie to decide which way to go. –  Rick James Sep 21 '11 at 15:39
1  
It all depends on the purpose you have in using MyISAM or InnoDB. What and how much are you willing to cache? Do you rely on MySQL or other caching mechanisms (such as varnish and memcached) for data retrieval? Is your hardware properly scaled up for InnoDB? Is 98% of your SQL SELECTs? Are the table in the best format for high-speed reads? Answering these questions beforhand should guide us as to storage engine selection, appropriate configuration, hardware selection, even reaching deeper things like high availability, DB topology, read/writing splitting, and this list can go on. –  RolandoMySQLDBA Sep 21 '11 at 15:50
add comment

I find talking about storage engines using cores can be misleading for beginners. Provided that a program is sufficiently multi-threaded, the operating system will schedule it across as many cores as possible.

The specific problem that limits cpu-scaling is when internal locking code (mutexes) have contention and block threads from running concurrently. All storage engines will require mutexes, but certainly there are some hot ones in MyISAM.

If we ignore mutex contention for a second and get back to your main question: how important is it to have many cores? -

I like having lots of cores for workloads that serve user facing requests. Having many can reduce variance between query times. Think of this as like lining up at the super market with 12 aisles open versus just 2.

Update: I wrote a blog post on why vertical scalability (multi-cores) is important.

share|improve this answer
2  
+1 for mentioning the elephant in the room: mutex contention –  cerd Feb 11 at 4:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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