MySQL setup my.cnf tools

[ittec]

Hi all

its my first time here, so hi to all. I am reader of mysqlperformance blog and I have some subjects about MySQL to discuss, to find feedback with other people.

I will explain a real case. This post is to know if most of the people that use MySQL usually, uses tools like MySQL Tunner or Tunning primer.sh

MySQL Tunner, by my experience, give to you a very restrictive recommendations. I think it try to limit the use of global RAM to 80% or system and when you have a configuration that exceed this rule, it give you a warning.

[B]Quote:[/B]

Total buffers: 666.0M global + 168.2M per thread (325 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 54.0G (2701% of installed RAM)

I had a BD with MySQL on Centos5, MySQL 5.0.45 with setup based on MySQL Tunner recommendations. This DB had a 14 GB of dates and use MyISAM. But lately, performance getting worse day by day, and waiting % was very high. In this situation, the consumption of RAM by MySQL was very low, because my.cnf was setup, as I say before, with recommendations of Tunner.

So we changed yesterday my.cnf to try to improve performance. And was a success. Almost, because some tables of DB are now corrupted. So we gain performance but lost stability.

With a more restrictive setup, I noticed that the tables was ok, no corrupted.

So this is my dilemma. Any idea or advice will be good. Thanks!

[ittec]

Hi again

I didn’t see any responses. If any people have something to tell me, any advice, please, feel free to answer me anyway )

Thanks

[MarkRose]

Make sure your key_buffer is big enough to hold all your indexes.

[ittec]

Hi Mark and thanks to answer me.

[B]Quote:[/B]

Make sure your key_buffer is big enough to hold all your indexes.

Actually now I have another big doubt. How I can get total size of MySQL indexes? I know MySQL only uses cache to indexes, and with the help of MySQL tunner I can get the value of total MyISAM indexes, but I don’t know how can I get the total value by myself.(because when you speak about key_buffer we assume that the only storage engine is MyISAM, isn’t it?

Thanks!

[ittec]

[B]Quote:[/B]

Try SHOW TABLE STATUS. That will give you the data length and index length for all the tables in the selected database.

Hi again

but…if I do Show table status Im only getting index of one database. So I think if is possible to get the total value of index on MySQL server of one shot, as MySQL tunner does.

Best regards.

[vgatto]

If you’re using MySQL 5 and above, you can get it in one shot using the information_schema database:

SELECT SUM(INDEX_LENGTH) FROM information_schema.tables;

Otherwise, your best bet is to use SHOW DATABASES to loop through each database and get the results of SHOW TABLES.

You can also try to examine the size of the index files (.MYI) on disk, but that will only capture the size of MyISAM indexes. InnoDB keeps indexes and data in the same file, so there’s no way to tell the size of things just using filesystem information.

[ittec]

Hi

thanks vgatto, nice info ) I will take a look on my server to get some light about indexes, then I could monitor as mysql stats.

Best regards

[ittec]

Hi again :o

I have checked the results of query information_schema.tables. The result was:

SUM(INDEX_LENGTH)36153664

So Index on my server are only 35 MB more or less. But, in those bytes are counted Innodb index also? The results of MySQLTunner shows that MyISAM index is

Key buffer size / total MyISAM indexes: 64.0M/34.5M

and InnoDB

InnoDB data size / buffer pool: 5.3M/32.0M

So I don’t know if InnoDB data size is a part of those 36153664

Thanks!

[ittec]

Actually I do

But I working with Zabbix instead of Nagios. Is easy to get MySQL stats through Zabbix. But first I have to know this kind of things, like separate MyISAM indexes of InnoDB indexes.

Anyway, thanks a lot guys! Now I have less doubts than one week ago.

Keep talking.Best regards.