Tune up your database (MySQL)

[krusty]

Hi,
i have google the internet for tuning up a mysql database, because i have really performance problems with my database. The first step to tune up a database you should insert INDEX into every table. I am a really newbie with databases so i haven't any idea how to insert INDEX. Have anyone do something like that
Then i read that only the crevices which where used by SELECT statements are set up with INDEX. But if i look into the zabbix php scripts i saw that nearly every crevices was used for SELECT statements. Can anybody help me with my problem

[nikoula]

mysql

i advise you to read the mysql manual. you can find every things that you need.
you can have it online on mysql.com website, developper section.

[edeus]

I think you should be looking at how the mysql and the server itself is setup more than how databases are stored.

Without looking I am pretty certain that the tables are setup properly, everything is indexed by ID already.

If you are getting very large history tables I would suggest looking at fine tuning mysqld and in specific the table caching options. There are also innodb specific options to take note of (most guides online will tell you about myisam which isnt relevant to Zabbix).

Grab phpMyAdmin and install it. Once configured etc it is an easy way to find out running processes (inside mysql, ie what zabbix is doing) and memory usage (very important).

I find on my low end system (P3 800mhz, 384mb ram) once the history table reaches around 3-5 million rows mysql has trouble searching/indexing the table. If you have a lot of ram, look at the mysqld process and see if it is using "enough" and how much is left to use. Do a system find for "my-innodb-heavy-4G.cnf" - this file is a very good start for databases with large innodb requirements (ie Zabbix). Personally it is too much for my system, but it may suit you. It is located in /usr/local/share/mysql/ on FreeBSD. You need to copy/use values to the /etc/my.cnf or your system equivalent.

If you dont have a lot of ram spare (after taking into account other applications) make sure the HD the mysql db sits on is nice and fast - it would be doing a lot of read write operations. I am not sure if mysql ever goes into virtual memory/system swap - most programs these days dont touch the stuff unless it has to.

To summarise:
Tune MySQL server with options in the global my.cnf file.
Look at the --enable-static and --enable-somethingelse options and recompile mysql.
Get phpMyAdmin and find out what MySQL and Zabbix is doing.
Do an "OPTIMIZE" on all the Zabbix tables, this can clean up the database immensely if you have been running for over a month. I believe it works like a compact in Outlook. Dont quote me though
Worse case scenario, you can always export and reimport the DB. This would be similar to optimize, probably better.

If anyone can add other suggestions, or reword the above mentioned, it hopefully with help some people.