I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:
- Primary: 96GB RAM, 8 cores + single RAID 10 array
- Test: 32GB RAM with 4 cores
- The biggest DB is 540 GB, total is around 1.1TB and mostly InnoDB tables
- Solaris 10 Intel-64
- MySQL 5.5.x
Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.
Observations on test server
- 3 separate connections
- each have an concurrent (and different)
ALTER TABLE..DROP KEY..ADD INDEX
- the 3 tables have a 2.5, 3.8 and 4.5 million rows
- CPU usage goes up to 25% (one core is maxed out) and no higher
- the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)
- What setting or patch is required to allow more than one core to be used?
That is, why doesn't MySQL use all cores available? (like other RDBMS)
- Is it a consequence of replication?
- I understand the difference between a RDBMS "thread" and an OS "thread"
- I'm not asking about any form of parallelism
- Some of the system variables for innodb and threads are sub-optimal
(looking for a quick win)
- Short term, I'm unable to change the disk layout
- OS can be tweaked if needed
- A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)
- innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores
- innodb_buffer_pool_size is 80GB on primary, 10GB on test (another instance is shut down). This is OK for now.
- innodb_file_per_table = ON
- innodb_flush_log_at_trx_commit = 2
- innodb_use_sys_malloc = ON
- innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this)
- innodb_doublewrite = OFF
- File system = ZFS (And my sysadmin found this: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices)
- innodb_flush_method isn't showing as O_DIRECT when it should be
- will follow RolandoMySQLDBA's settings
Let me know if I've missed anything important
Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
Result: > 1 core used for the tests = positive result