0

I have a web application which uses a MySQL database hosted on a virtual server. I've been using this server when I started the application and when the database was really small. Now it has grown and the server is not able to handle the db, causing frequent db errors. I'm planning to get a server and I need suggestions for that.
Like I said, the db is now 9 GB, and is growing considerably fast. There are a number of tables with millions of rows, which are frequently updated and queried. The most frequent error the db shows is Lock wait timeout exceeded. Previously there used to be "The total number of locks exceeds the lock table size" errors too, but I could avoid it by increasing Innodb buffer pool size.
Please suggest what configurations should I look for in the server I should buy. I read somewhere that the db should ideally have a buffer pool size greater than the size of its data, so in my case I guess I'd need memory gt 9 GB. What other things should I look for in the server?

Just tell me if I should give you more info about the

2 Answers 2

2

You could use the perl script named MySQLTuner to get some ideas of variables to tweak, and thus hardware to upgrade if necessary.
However you should understand the settings before modifying them or you risk aggravating the problem.

The script himself contains a very insightful tip:

*WARNING
It is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's output, or if you don't understand the recommendations, you should consult a knowledgeable DBA or even a system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.
*WARNING

You'll find great articles on the subject on Percona's MySQL Performance blog, a list of books dealing with that subject and even screencast on their percona.tv website.

1

Have you examined the Lock wait?

You can do so by activating the InnoDB lock monitor:

CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;

Then tail your MySQL error log.

To disable the monitor drop the table.

HTH

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .