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


Transactional (ACID-compliant)
Non-Transactional


MySQL features the use of several storage engines


InnoDB (default as of MySQL 5.5)
MyISAM (default prior to MySQL 5.5)
ARCHIVE
BLACKHOLE
CSV
MEMORY
MRG_MYISAM
FEDERATED
NDB a.k.a. MySQL Cluster


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.