Best MySQL setup for my hardware?

Question

MySQL

Good time!
Please help me to tune mysql server to get good performance.
The server will be used only for the Netup UTM5 billing database and
has the following hardware characteristics:

HP ProLiant DL380 G7 / 583917-B21 <br/>
2 x Intel® Xeon® CPU E5630<br/>
16 GB ECC 1333 MHz<br/>
4 x 600 GB SAS RAID 1+0

# lsb_release -a<br/>
Distributor ID: Ubuntu<br/>
Description: Ubuntu 10.04.2 LTS<br/>
Release: 10.04<br/>
Codename: lucid<br/>
<br/>
# uname -a<br/>
Linux db 2.6.32-30-server #59-Ubuntu SMP Tue Mar 1 22:46:09 UTC 2011 x86_64 GNU/Linux<br/>

# aptitude show mysql-server<br/>
Package: mysql-server<br/>
State: installed<br/>
Automatically installed: no<br/>
Version: 5.1.41-3ubuntu12.10<br/>

# time dd if=/dev/zero of=testfile.bin bs=64k count=16384; rm testfile.bin<br/>
16384+0 records in<br/>
16384+0 records out<br/>
1073741824 bytes (1.1 GB) copied, 2.09754 s, 512 MB/s<br/>
<br/>
real 0m2.327s<br/>
user 0m0.000s<br/>
sys 0m1.480s

The current data size in the database is about 90GB.

Answer 1

I have vpn 400 MHz / 160 MB RAM Please help me tune the server for optimal performance. Throw off the configs and tell me where to put them.
But to the point: There are a million articles on optimizing muscle settings, which describe step by step which parameters are responsible for what, and which parameters should be changed by “poke method” to check the change in speed, and which definitely need to be assigned for tangible changes. Isn't it interesting to try it all on your own skin?

Answer 2

Have you already tried running it? Have performance issues? If you haven't tried it, then it's time. MySQL is well configured by default and in many cases there will simply be no problems. Now, if the tests show problems, then it will be necessary to understand.
On good, it makes sense to add memory. I hope you have InnoDB? Buffer pool helps to speed up a lot. But it depends on requests. I suspect that billing writes a lot to the database, and reads little in normal mode. But the construction of reports is not only very long, but also can significantly interfere with other requests. There is, for example, such a problem as flushing the cache.
Then watch slow query, buffer state, mysqltop and you can already think of something based on the results.
Well, get in touch, if that :)
www.percona.com/mysql-consulting/performance-audit/

Answer 3

Complete transition to InnoDB
and optimization
www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
(the material is old, elders tell me something as powerful, but newer?)
and there was / is a script- assistant in the configuration of muscle servers, but it's better to read the mana and do everything right
here a small digression into today's near-muscular state
vimeo.com/20439614
for the very lazy, they will do you good here -> mariadb.org/
and of course: experiments on a separate machine, before updating full backup.

Answer 4

I don't know what kind of magic tips you're expecting...
try the automatic hints: launchpad.net/mysql-tuning-primer and github.com/rackerhacker/MySQLTuner-perl.
then enable slow query logging and parse forge.mysql.com/projects/project.php?id=333

Answer 5

I'm not asking for the whole config!
The question is that, perhaps, someone will tell you what parameters allow you to achieve good database performance on such hardware of one particular application, namely NetUp UTM.

Answer 6

Look at the example configs that come with mysql, and then read about the options you are interested in at mysql.com

Answer 7

mysqltuner.pl

Answer 8

For some reason, I was sure that the database, like other software, is configured for the project, and not for hardware.