GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL setup my.cnf tools

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL setup my.cnf tools

    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.

    Quote:


    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!

  • #2
    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

    Comment


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

      Comment


      • #4
        Hi Mark and thanks to answer me.

        Quote:

        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!

        Comment


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

          Regarding the key_buffer_size, that is only used by MyISAM. If you're using InnoDB, you should also be looking at innodb_buffer_pool_size.

          Comment


          • #6
            Quote:

            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  Hi again

                  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!

                  Comment


                  • #10
                    Only secondary indexes on an InnoDB table will be reported with the index size, so it is not included.

                    As far as monitoring MySQL goes, there are tools out there that can help you out with this. We use a combination of Nagios, for alerting us when something bad goes happen and tracking our uptime and Cacti for recording and graphing historical data about MySQL. There's some work involved setting them up, especially Nagios, but if you're considering rolling your own, you might want to look into those as an alternative or inspiration.

                    Comment


                    • #11
                      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.

                      Comment

                      Working...
                      X