Friday, January 25, 2008

MySQL and threads: my observation and experience

First: Here's to give an idea of how much traffic our site handles:

I just read the post by Monty Taylor about MySQL and threads which was in response to Curt Monash's post on why not to use MySQL.

As Director of Database Infrastructure for a top 14 Internet destination (according to Alexa), I just wanted to pitch-in with a quick comment.

Monty says, "Try running an top-10 web property with 100+ Oracle databases with a team of 1 DBA and see how long it remains running. I’d be happy to take the challenge of running the same thing on MySQL."

I couldn't agree more. I am a single person at my work working on MySQL. Our MySQL instances handle close to 30,000 queries per second at peak hours. We run MySQL on Solaris 10 with 3Par as our SAN and couldn't be more happier. We are running 64 concurrent InnoDB threads with average of 1000 max connections per server and the results are truly pleasing. For sure, I feel desperate to get some assistance but for now, it is just me managing all our installations. I have been doing that since June 2006 and without MySQL, it would not be possible. So far, I haven't yet come across a similar situation where 1 guy was managing a top 15 web property (traffic-wise) using Oracle.

And what about Curt's remaining points about why not to use MySQL? I will let Roland, a good friend of mine, address that.


Roland Bouman said...

Hi Frank!

Thanks for this great post, it's amazing and truly fulfilling to see people like you, enjoying their job and running the biggest websites on the planet.

And thanks for the support and setting the records straight, I appreciate it a lot ;)

mike said...

Ha! Yeah, Oracle, MSSQL, all of those big guys seem to have a ton of administrative overhead. MySQL is way too simple (I even get annoyed with the few extra steps in Postgres) I am just waiting for replication and the other scaling options to become more mature...

I don't know if this is too forward but I would love to see your server config (my.cnf, etc.) or at least any details about your architecture (is it 1 master, or multi-master, is it using mysql cluster, do you have a connection pooling middle tier, etc...)

I enjoy reading about it and seeing options that work...

Thanks! said...

What version of MySQL?

Have you used a modified innodb_thread_concurrency value?

I've been thinking of playing with this but we're still on 4.1.22 and most of the big changes for this went into the 5.1.x series.

Hopefully these will go gold soon.


Frank said...

Hi Roland,

Thank you! It definitely fulfills me challenge and curiosity wise to work on such a high trafficked website.

It was really awesome to see you all pumped up :) Reminded me of good'ole days when Planet MySQL started.

Frank said...

Hi Mike, Interestingly enough, we have been moving away from replication and increasing reliance on memcache. On most our boxes, query cache is turned off. Most of our scalability has come from optimizing our schemas to better fit what MySQL/InnoDB combination have to offer. Solaris 10 and Sun boxes are truly amazing in their performance. Plus, we achieve all this while using RAID 5 on SAN. If this experience has taught me one thing that is this: understanding your application needs and optimizing them for underlying database and can do wonders. It makes absolutely no sense for us to even consider Oracle or any other dbms at this point.

Regarding my.cnf file, I will see what can be done. Although, everything should be tuned based on your specific environment. There is really no magic there though. Other than may be that I leave multiple gigs of memory for connections, and try very hard to keep disk IO at the minimum.

If you still want to look at my generic my.cnf, email me and I will send it to you.

Frank said...

Hi Kevin,

Yes, we have innodb_thread_concurrency set to 64. If your MySQL/InnoDB instance needs it, then increasing this can do wonders.

Currently, we have MySQL versions 4.1.11 to 4.1.22 deployed in production. I would love to go to MySQL 5.1.x but it will be quite some time before that is achieved.

mike said...

I can't seem to find your email -

mine is mike503 AT Any info (configs, server hardware stats, etc) would be great.

I have never really trusted replication (I used it in earlier days on sub-par hardware and it sucked, havent't relied on it since) and mysql cluster doesn't seem to be well-suited for my needs either. some sort of self-healing replication (parts of it might be an option now with maatkit/the google replication tools/ibpool maybe)

I try to build memcached/those kind of cache concepts into my stuff nowadays, even if not using a cache yet.

I guess the disk hardware, RAM info would be helpful to know...