Ad Widget

Collapse

Performance Tuning MySQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    #31
    Hi,

    My two cents: ZABBIX 1.8.x is much better talking about performance. In ZABBIX 1.6 (and before) that didn't have any Cache or Buffer capability ... in 1.8.x the new features are working like a charm.

    All performance problems we have had with 1.6.x are gone with 1.8.x

    []s,
    Luciano
    --
    Luciano Alves
    www.unirede.net
    Twitter|Facebook|Blog|Youtube|Zabbix Performance Tuning
    Porto Alegre | São Paulo | Rio de Janeiro | USA

    Comment


      #32
      Originally posted by lamont View Post
      Don't suppose you've replicated this for 1.8?

      Anyway the only point of my "doesn't make any sense" comment was that it didn't make any sense to say that they were taking up resources while they were sleeping. Sleeping is sleeping, and shouldn't take any resources.

      Clearly, they *aren't* sleeping, and they're waking up and tripping all over each other. It looks vaguely like there might a thundering herd issue or something like that.
      First, sorry for the late reply.

      1. No, haven't upgraded the Production server yet. I do have a 1.8.3 set up but it is totally dedicated to Zabbix_Trapping on about 60 items once daily, so replication can't be done yet.

      2. Sleeping threads taking up resources. Mmm, maybe a bad choice of words. What I meant was that in the past I had jacked up the StartPollers/Trappers as that was the 'standard advice' for resolving such issues as slow server performance, slow housekeeper, gaps in graphs, . . .
      However, what I found after tuning my.cnf with the advice from this forum thread, was that I didn't really need all those StartPollers/Trappers. I returned them to the default values and everything was fine; fast housekeeper, low cpu load, in fact everything was running so much better than before.

      3. A thundering herd? Maybe, not sure yet. Despite the fact that everything is running smoothly, I have noticed that for each additional StartPoller/Trapper there is a slight increase in cpu load. But, with such a finely tuned database I have no need for anymore than the default StartPollers/Trappers anyway.
      In mysqladmin, I see that the Sleeping threads are only in that state for a short time anyway, usually less than 60 seconds.

      For now I shall just leave things as they are.

      MrKen
      Disclaimer: All of the above is pure speculation.

      Comment


        #33
        Originally posted by Samd View Post
        I wonder if you can comment on how your UserParameter and monitoring work, since "ps auxw" is supposed to give you average CPU usage over the entire lifetime of the process. Therefore, a drastic change in performance shouldn't be noticed immediately. It will get watered down, inside the "average" calculation. Yet - your graph displays a discontinuity. This doesn't make sense. Unless, perhaps, a process got killed at that moment or something similar, so it's eliminated from the calculation.
        You are correct! 'ps auxw' does give an average over the lifetime of the process. However, for me that is fine as I can see the trending data. This UserParameter will not tell you that suddenly the cpu usage of a particular process has just gone through the roof. (I have no triggers on these items)

        The UserParameter was not devised by me, it came from a rather old forum thread http://www.zabbix.com/forum/showthread.php?t=210

        If you want to be able to trigger when a process starts using excessive cpu, there are other threads that, I think, run a script to identify the offending process. Here's one, give it a try (I haven't yet) http://www.zabbix.com/forum/showthre...ht=CPU+Process

        MrKen
        Disclaimer: All of the above is pure speculation.

        Comment


          #34
          Old post, but VERY interesting

          Hi everyone,

          Thanks a lot for all the tips !

          I just spend few hours to think and test mySQL settings for better tuning.
          Links I have used are:
          http://www.mysqlperformanceblog.com/...zation-basics/
          http://www.mysqlperformanceblog.com/...fer_pool_size/
          http://dev.mysql.com/doc/refman/5.0/...iguration.html
          http://dev.mysql.com/doc/refman/5.0/...variables.html

          I'm runing Zabbix 1.8.4 on SLES 10 virtual machine (2vCPU, 4GB RAM), DB is 40GB (340 hosts, 12k items). Required server performance, new values per second: 183.66.

          I have changed the following settings: in () old values or defaults:
          Code:
          innodb_buffer_pool_size = 3G (16M)
          innodb_log_file_size = 64M (5M)
          innodb_flush_method=O_DIRECT
          innodb_flush_log_at_trx_commit=2 (1)
          tmp_table_size=256M (32M)
          max_heap_table_size=256M (16M)
          table_cache=256 (64)
          Does it really improve performance ? YES (and reduce a lot I/O and MBs on disks)
          Have a look on pictures:
          CPU "2011-07-07_141032.png",
          Memory "2011-07-07_141312.png",
          Disks I/O "2011-07-07_141607.png"

          My storage admin is very happy now !

          Regards,
          Bernard
          Attached Files

          Comment


            #35
            Hey, why didn't you use Zabbix for the graphs?
            Alexei Vladishev
            Creator of Zabbix, Product manager
            New York | Tokyo | Riga
            My Twitter

            Comment


              #36
              Still waiting for "vfs.dev.read/write[device,bytes]" for linux ...

              Comment


                #37
                Originally posted by bernard View Post
                Still waiting for "vfs.dev.read/write[device,bytes]" for linux ...
                I see, sorry for this.
                Alexei Vladishev
                Creator of Zabbix, Product manager
                New York | Tokyo | Riga
                My Twitter

                Comment


                  #38
                  Thx for sharing, we just started tuning the DB

                  Comment


                    #39
                    Maybe someone who has a large environment and running zabbix on mysql can check this server http://www.percona.com/software/percona-server/ vs a mysql and run some benchmark and show results?

                    Comment


                      #40
                      Originally posted by bernard View Post
                      Hi everyone,

                      Thanks a lot for all the tips !

                      I just spend few hours to think and test mySQL settings for better tuning.
                      Links I have used are:
                      http://www.mysqlperformanceblog.com/...zation-basics/
                      http://www.mysqlperformanceblog.com/...fer_pool_size/
                      http://dev.mysql.com/doc/refman/5.0/...iguration.html
                      http://dev.mysql.com/doc/refman/5.0/...variables.html

                      I'm runing Zabbix 1.8.4 on SLES 10 virtual machine (2vCPU, 4GB RAM), DB is 40GB (340 hosts, 12k items). Required server performance, new values per second: 183.66.

                      I have changed the following settings: in () old values or defaults:
                      Code:
                      innodb_buffer_pool_size = 3G (16M)
                      innodb_log_file_size = 64M (5M)
                      innodb_flush_method=O_DIRECT
                      innodb_flush_log_at_trx_commit=2 (1)
                      tmp_table_size=256M (32M)
                      max_heap_table_size=256M (16M)
                      table_cache=256 (64)
                      Does it really improve performance ? YES (and reduce a lot I/O and MBs on disks)
                      Have a look on pictures:
                      CPU "2011-07-07_141032.png",
                      Memory "2011-07-07_141312.png",
                      Disks I/O "2011-07-07_141607.png"

                      My storage admin is very happy now !

                      Regards,
                      Bernard
                      Dear Benard,

                      After I apply some config in my.cnf like you and I have a problem with zabbix GUI. So many error about sql in the log in screen. How can i fix it. I monitored over 300 host and 4k items, used zabbix 1.8.4 and the queue delay over 20 minutes after several of time .

                      Thanks and Best Regards,

                      Comment

                      Announcement

                      Collapse
                      No announcement yet.
                      Working...
                      X