Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Many months ago, while MySql 5.5 was still in beta this question was asked before. Now that it has been released and there are a few maintenance updates out, what do all y'all think?

share|improve this question

2 Answers 2

ISSUE #1) Semisynchronous Replication

A master with multiple slaves isn't all that great because only the first slave would replicate quickly and all subsequent slaves would sometimes switch into asynch mode depending on how long a query takes to run. In terms of the first slave, that is by design. I guess there are no guarantees for subsequent slaves running in a timely fashion.

On the other hand, simple master-slave, circular rep, and multimaster rep work dynamite fast IMHO.

ISSUE #2) Multiple InnoDB Buffer Pools

I have a client with 3 DB Server Configured as Follows:
Dual HexaCore (12 CPUs)
192GB RAM
2 TB SAS RAID10

Client has 480GB of data spread across 780 databases.
Circular Replication is running amongst the three DB servers.

Definitely one of the most powerful features I have activated for InnoDB is the following:

innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=65536
innodb_buffer_pool_instances=1
(default) innodb_bufer_pool_size=162G

MySQL 5.5 works like a dream for this client. All Client's Customer Data are Virtually Cached in Memory. All 12 CPUs are fully engaged due to heavy InnoDB activity. Everything dynamite fast. InnoDB Dirty Pages for the Buffer Pool are so low and paged out so quickly.

I did try setting the buffer pool this way at first (160GB)

innodb_buffer_pool_instances=64 (maximum value)
innodb_buffer_pool_size=2560M (2.5 GB)

This created a lot of thread locking. I suspect this is due to having related data cached in different buffer pools. Trying to access those segments of data resulted in some kind of mutexing amongst buffer pools. A coworker at my job (who is an Oracle Certified Master) told me that Oracle has a similar infrastructure you can use and thread locking is also a problem so they do not market its usage.

All the client's thread locking issues totally disappeared when I set the following:

innodb_buffer_pool_instances=1
(default) innodb_bufer_pool_size=162G

My Conclusion on Multiple InnoDB Buffer Pools

I was looking forward to using this feature but came up disappointed. Having multiple buffer pools do not buy you a whole lot if you have large datasets that are related but spread across the buffer pools, introducing increased thread locking. I wish you could assign data to specific buffer pools just like you could preload MyISAM key caches. Only then would multiple buffer pools be very handy.

If you have very small tenant databases but lots of them, multiple buffer pools are OK. There would still be thread locking issues, but not as severe.

IMHO I would shy away from multiple buffer pools for large installations. A giant buffer pool with a lot of threads work more effectively. I would definitely focus on using more innodb threading and engaging more CPUs, which is a distinct advantage over MySQL 5.0/5.1.

share|improve this answer
    
uhh, how the HECK are you generating 162GB innodb_buffer_pools you must be running some kind of bad ass machine.12 CPU's ?? how do I get that kind of hardware!? –  Zak Apr 7 '11 at 21:39
    
The company I work for does DB/Web Hosting. I have seen all kinds of crazy hardware we have set up for clients. I think the RAM upgrade alone on one of the DB servers was like $20,000. I can find out the exact manufacturer and comment back on it. –  RolandoMySQLDBA Apr 8 '11 at 1:57
    
BTW keep in mind there is only one buffer pool that 162GB. I tried using 160GB using 2.5G per buffer pool and running 64 buffer pools. As I mentioned before, the thread locking was unbearable. So, I went back to one monolithic buffer. –  RolandoMySQLDBA Apr 8 '11 at 2:03

Re: issues with innodb_buffer_pool_size and innodb_buffer_pool_instances:

I think there is a disconnect with how these variables are related. innodb_buffer_pool_size is the total buffer pool available. That is then divided amongst the # of threads referenced in innodb_buffer_pool_instances.

In the poorly-performing example above, 2.5GB was divided amongst 64 instances, which leaves 40MB per instance. The mimimum recommended (if you're using these options) is 1GB per instance. I could totally see how 40MB per instance would've resulted in poor performance.

In our production environment, we're running servers with 3 instances, 3GB pool, for 1GB per instance, without excessive paging. We're also running 4 instances, 10GB pool, also with no excessive paging (newer hardware, why not take advantage of that memory).

Hope that helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.