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.

I have a dedicated server on which I run MySQL + Apache. I run a chat script which polls every 3 seconds. Now last night my MySQL server died due to the load. Am wondering how to maximize my MySQL threads as well as Apache threads.

Can someone point me to URLs which can help me achieve the same?

Thank you for your time.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The canonical place for MySQL performance information is MySQL Performance blog. There is also the High Performance MySQL book by Jeremy Zawodny.

You might like to look at some form of connection pooling in your application, where it will reuse existing database connections, rather than creating a new connection for every new session. This will reduce the overhead of opening a new TCP connection and any processing MySQL does. It should also reduce the number of idle connections MySQL is running, as each connection requires a certain amount of memory just to exist.

Another thing you can do to massively reduce the load on your database is to integrate memcached. Memcached is an in-memory distributed key-value store; like a network-attached hash map. You would modify your application to poll memcached and if there was no data, to check the database. When you change the data, you write it to the database and invalidate the data in memcached. This means that you'd be polling the significantly quicker memcached rather than MySQL.

Once you've implemented these features, and only when you have, you can start looking at tweaking MySQL parameters. There's a huge potential to screw things up royally and end up with a database that ends up slower than it was before if you do not know what you're doing. I would have at least read the MySQL Performance book before you try this.

If you are able to spend more money, the first improvement would be to split your database onto a separate server. If that's not enough, you can start looking at using a master and slave replication setup, where writes go to the master server, but reads are spread between the master and the slave. This works particularly well for read-heavy workloads, which is fairly common.

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.