0

I would like to know all of the key points where MySQL can be fine tuned performance wise on a Linux server through my.cnf or through other methods, libraries and tools. Tips and tricks and shady stuff welcome too.

Since a target server architecture is important you can wrap your answers around this example target architecture:

1 GB of RAM (non ecc)

2.2GHz Dual Xeon

File System ext3 or reiserfs (welcome for suggestions)

7200 RPM HDD

A couple of Wordpress installations with at least 5-10 requests per second.

A pretty common setup, if you ask me, nothing special here.

Thus i'd like to get your input on how to tune a MySQL install on such a target server to the point where you can't really do much from a MySQL Server stand point to gain performance.

3 Answers 3

2

You can still do a lot in MySQL alone to gain performance on this setup, I'm using MySQL primer on my servers to fine-tune all memory related parameters (you can get it from here).

Also the filesystem is important but not critical for MySQL, I've been using it both in reiserfs and ext3 (try to add noatime,nodiratime in /etc/fstab if using ext3) with almost identical results.

Very important as well is how much memory you have available, the more the better of course!

My recommendation, stick to a server scenario where you're comfortable with then start your mysql and start finetuning with the primer, your 5-10 query/sec scenario looks quite light and will give you lots of room to improve things.

1
  • Tuning Primer is definitely worth a run. I'll return with an outcome once i've done that.
    – Shinnok
    Feb 9, 2011 at 11:39
0

The most effective way to tune MySQL is to optimize your queries. You should enable a slow query log to get a list of slow queries. If your queries are all faster than 1 sec (a minimum border for stock MySQL), you should install Percona Server or MariaDB, they have a microslow patch. You can also try mysqlreport, it's a very handy tool. Use this guide to interpret its results and make further decisions.

3
  • 1
    Using Wordpress, the user generally doesn't have the opportunity to optimize the queries. One would hope that such a well-supported application has its queries well-tuned by default.
    – Joe
    Feb 9, 2011 at 10:30
  • Why not, I've had a project where I rewrote Worpress queries to optimize them. Stock 2.6.X Wordpress did not perform well on big datasets because its queries were just not tuned well enough. I haven't evaluated 2.8.X or 3.0.X queries but I think nothing changed, probably because nobody really needed to serve tens of thousands blog posts like our project did.
    – Alex
    Feb 9, 2011 at 10:40
  • Modifying the source of web apps running on the server is out of the question. My question is specific to MySQL Server tuning and not web application source code tuning. :-)
    – Shinnok
    Feb 9, 2011 at 11:37
0

This question is impossible to answer here. There's just too much to learn. You could start by reading the MySQL manual in some detail. Then go buy a book or two on Mysql tuning and on Linux performance tuning. These won't (can't) provide you with the answers you're looking for - they'll show you how settings affect behaviour and how to measure the resultant effect on the application. There is no generic solution to the problem - although both Linux and MySQL have good default setups out-of-the-box.

Once you fully understand everything in those books, then you'll be able to start applying changes to your system. You'll need to gather statistics and review each change to measure its effectiveness - but you also need to understand the issues of local minima in goal searching.

You might find mysqltuner helpful - but you need to be in a position to judge the impact of its recommendations (particularly with regard to oscillating recommendations) and to measure the effect on your system.

You must log in to answer this question.

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