Home / server / Questions / 233162
Accepted
Shinnok
Asked: 2011-02-10 00:26:04 +0800 CST

Extreme fine tuning MySQL performance wise on a Linux server

  • 772

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.

linux mysql performance-tuning mysql5

3 Answers

  1. Best Answer

    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.

    • 2

  2. 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.

    • 0

  3. 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.

    • 0