The InnoDB tuning pages have been given an overhaul in MySQL 5.5, so there is some good info there as well. I won't pretend to know how to tune solaris, but here's an article that goes over some solaris-specific tuning information. MySQL will automatically use multiple cores, so either your load of 25% is coincidence 1 or a potential misconfiguration on Solaris. Result: > 1 core used for the tests = positive result Let me know if I've missed anything importantĬhanged innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
innodb_buffer_pool_size is 80GB on primary, 10GB on a test (another instance is shut down).Yes, it's wrong but won't make MySQL use multiple cores
innodb_thread_concurrency is set to 8 on both.A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO).Short term, I'm unable to change the disk layout.Some of the system variables for InnoDB and threads are sub-optimal.I'm not asking about any form of parallelism.I understand the difference between an RDBMS "thread" and an OS "thread".That is, why doesn't MySQL use all cores available? (like other RDBMS) What setting or patch is required to allow more than one core to be used?.the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5).CPU usage goes up to 25% (one core is maxed out) and no higher.the 3 tables have a 2.5, 3.8 and 4.5 million rows.each has a concurrent (and different) ALTER TABLE.DROP KEY.ADD INDEX.It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB. Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this.
The biggest DB is 540 GB, the total is around 1.1TB and mostly InnoDB tables.Primary: 96GB RAM, 8 cores + single RAID 10 array.The servers are quite hefty with an OLAP/DataWarehouse (DW) type load: I'm more developer than DBA for MySQL so need some help Setup I've been presented with some dedicated MySQL servers that never use more than a single core.
0 kommentar(er)
