MySQL settings for 8xcore/16GB RAM

[vlangber]

Hi!

I work for a company developing a CMS in .Net. We’ve used SQL Server so far, but we have recently added MySQL support as well. Now we want to test how it performs compared to the SQL Server version in production use, but we feel a bit lost when it comes to optimizing all the different settings in MySQL.

Do you have suggestions on values for the most important settings on a server with 2x quad core Shanghai processors and 16GB RAM, with 15K SCSI disks? We have 95-99% reads from the database. We intend to use MyISAM.

[istvan.podor]

Thats a pretty strong server.

Don’t use myisam. 2 reasons:

• Not ACID compliant and you can easily loose data
• Can hold back your beautiful machine.

Innodb use row-level locking which means you can execute as much query per second as much your disks (which you got a lot of) can proceed. So I suggest to use Innodb and spend time on learning how to tune it.

Istvan

[vgatto]

In general, I don’t use MyISAM for anything, but if you really have 95%+ reads, table locking won’t be an issue unless you have a pretty high query volume. InnoDB tables are considerably larger than MyISAM, and that means less I/O and more data in memory. Unless you’re doing a lot of sequential reads by primary key and can benefit from InnoDB’s clustered index, then MyISAM will likely be faster.

I’ve heard it can be a real pain to recover a MyISAM table in the event of a crash, though. Other than that, I’d say there is much less community support for MyISAM than there is for InnoDB. Patches, consultants and database geeks who post around here are definitely more InnoDB focused.

Once again, I haven’t really used MyISAM in forever, but the main tuning level is the key_buffer_size. MyISAM is only going to keep indexes in the key buffer, not data, so you have to make sure to leave some memory for the OS file system cache, and not just throw all 16GB at MySQL.