Tag Archives: MySQL - Page 2

Deciding whether to send reads to slave or master

Posted by on October 4, 2008 No comments

There are quite a few articles out there that talk about how to give your application some context and send reads to one server, and writes to another. There are even some mentions of marking your connection “dirty” and then sending all reads to the write server.

As a first try at scaling things, I recently made a change to our web application’s data access layer where reads went to a group of readonly slaves. However, if a write was made to a database, a value was put into the user’s session, saying that the database was dirty, and causing all subsequent reads to go to the master server.
Read more »

Can more queries equal a healthier MySQL server?

Posted by on August 29, 2008 2 comments

This week was an ugly one for my monster database servers. It should have been triumphant, but oddly enough, I think it shows how prone to mistuning InnoDB on MySQL 5.0 is with multiple cores.

This server is a multi-core, high concurrency server. The application has been designed a little bit naively in that it just throws almost all queries at the main db server. Several bits have been designed to scale by not doing that, but unfortunately, huge amounts of functionality were built around those apps to prevent them from scaling.

As a result, we’ve had to scale up the central database server and its redundant systems significantly. We started with the Proliant DL380 G4 with two Xeon 3.4Ghz CPU’s and 12GB of RAM, and plenty of disks in an external RAID. As more traffic was added, we moved up to the DL580 servers with 4 Xeon 3.4Ghz and 64GB of RAM. This worked well, but still more traffic, and more data, was coming and the app wasn’t ready to change significantly. We finally landed on the latest DL580 server, with 1GB of total battery backed write cache, 14 SAS disks, 128GB of RAM, and two quad core Xeon CPU’s.
Read more »

Query Cache defeats Serverzilla

Posted by on July 15, 2008 2 comments

So a few days ago, my big mean MySQL server started having problems that were very hard to explain. It was slowing down, taking a minute to run queries that usually take a few seconds, and Linux load averages were in the teens, despite having quiet disks (less than 0.1% cpu IO wait time) and plenty of RAM (128G for about 200G of data total…).

The developers were stumped. The other systems guys were stumped. So was I. But it still seemed ok. We found all sorts of things to point fingers at, but nothing made sense.
Read more »

The Thread_Concurrency myth

Posted by on June 17, 2008 No comments

Just a few weeks ago, I found out that thread_concurrency’s purported magical effects at correcting MySQL’s concurrency limitations (especially in 4.1) were something of a myth. It was a post on mysql’s lists that alerted me to this. Apparently it only works on Solaris, Linux’s threading library ignores this parameter completely. This is not to be confused with innodb_thread_concurrency, which is quite useful in controlling the flow of transactions through InnoDB. I think the problem really lies in the fact that the default my.cnf example configs tell us to set thread_concurrency to the number of CPU’s*2. They fail to mention that this only matters on Solaris, though the manual is quite clear.
Read more »