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 only takes a minute to sign up.
Sign up to join this community
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?
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:
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.
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.
