Ad Widget

Collapse

Performance Tuning MySQL

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

    Performance Tuning MySQL

    I had some time with an actual MySQL consultant on Friday and we went over MySQL performance tuning for my heaviest zabbix server (299 hosts, 18905 items, 7210 triggers, 5415 services). The server that I'm using is a 2x dual core with 6 15k SAS drives and a RAID array with 512MB of cache. Some of the recommendations:

    1. use a tmpfs tmpdir

    In /etc/my.cnf configure:

    tmpdir=/mytmp

    I'm using RHEL5 and in /etc/fstab i put:

    tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=27,gid=27 0 0

    You'll have to mkdir /mytmp and the numeric uid and gid values for your mysql user+group need to go on that line. Then you should be able to mount /mytmp and use tmpfs for mysql's temp directory. I don't know about the size and nr_inodes options there, I just saw those in linux tmpfs docs on the web and they seemed reasonable to me.

    2. Buffer cache/pool settings.

    In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. I had a long-running system and i sized it so that on a 16GB box that i should only have about 1GB of buffer cache available. If you use /usr/bin/free on a redhat system value in the "+/- buffer cache" row under the "free" column shows you how much buffer cache you have. We also setup innodb to use O_DIRECT so that the data cached in the innodb buffer pool would not be duplicated in the filesystem buffer cache. So, in /etc/my.cnf:

    innodb_buffer_pool_size=12000M
    innodb_flush_method=O_DIRECT

    3. Size the log files.

    I haven't applied this one yet because I changed this configuration, restarted mysql and mysql started complaining like I had corrupted the whole database and freaked me out. I got errors like:

    "Incorrect information in file: './zabbix/users.frm'"

    The correct way to resize this is documented here:

    http://dev.mysql.com/doc/refman/5.0/...-removing.html

    In /etc/my.cnf the value I'm going to try is:

    innodb_log_file_size=64M

    The value that I had was the default (8MB?) and that means that MySQL is constantly flushing from the logfiles to the table spaces. It is better to increase this size on write-mostly databases to keep zabbix streaming to the logfiles and not flushing into the tablespaces constantly. However, the penalty is slower shutdown and startup times. I have another zabbix server where this parameter was set to 512M and bouncing the mysql server leads to lots of downtown on the server.

    4. other parameters

    innodb_file_per_table

    Use file_per_table to keep tablespaces more compact and use "optimize table" periodically. And when you set this value in my.cnf you don't get an actual file_per_table until you run an optimize on all the tables. This'll take a long time on the large zabbix history* and trends* tables.

    log_slow_queries=/var/log/mysql.slow.log

    Turn on slow query logging.

    thread_cache_size=4

    I forgot to ask the consultant about this setting but it seemed to have a large effect when I set it initially (EDIT: reading up online it seems to affect the hit rate of Threads_created per Connection in 'show global status' -- with it set to 4 i had 3228483 Connections and 5840 Threads_created, which is a 99.2% hit rate -- higher number of Threads_created is worse).

    query_cache_limit=1M

    Consultant advised keeping this to 1MB or less. I had tuned it higher.

    query_cache_size=128M

    Consultant advised that this was a good value and not raising this any higher

    tmp_table_size=256M
    max_heap_table_size=256M

    I tuned these based on some other tuning docs, and the consultant concurred, but they shouldn't go any higher, and they should be set to the same value.

    table_cache=256

    This was also based on other tuning docs, but based on the number of tables in a zabbix database the consultant agreed, with 73 tables in zabbix plus temp tables and mysql tables and stuff that this was appropriate.

    max_connections = 400

    I jacked this up because i had a measured max conns of 70 and wanted headroom and configuring connections is reasonably cheap in mysql

    innodb_flush_log_at_trx_commit=2

    I had tried this before and not seen a huge benefit, but the consultant advised that since i didn't care about strict ACID that this would help a lot for high volume writes.

    join_buffer_size=256k
    read_buffer_size=256k
    read_rnd_buffer_size=256k

    The mysql consultant recommended keeping these values no higher than this, because they are per-thread buffers, and that this was based on performance analysis done by engineers at mysql. I had run into conflicting recommendations on the net to increase the size of the join_buffer_size in particular.

    Results: its only been about 40 mins so far, but the server has settled down with a load average bouncing between about 5.0 to 8.0 so far when before it was bouncing between 5.0 and 15.0. Idle CPU has jumped from only 20-40% to 40-50% (CPU was actually my limiting factor). I'll try to remember to post some zabbix graphs later after it settles down and fills up the innodb buffer pool and i get some idea of stable-state performance....
    Last edited by lamont; 02-05-2009, 21:53.

    #2
    Thanks for sharing this!

    Comment


      #3
      Originally posted by lamont View Post
      query_cache_limit=1M

      Consultant advised keeping this to 1MB or less. I had tuned it higher.

      query_cache_size=128M

      Consultant advised that this was a good value and not raising this any higher
      And considering zabbix constantly inserts new data into the main tables, the query cache gets constantly invalidated. So I assume it wouldn't help a lot.
      MySQL Administrator shows an 8% average query cache hit rate on our MySQL server that mainly hosts zabbix.

      Thanks for all the tips and pointers. If I had a dedicated server I'd tweak some of the variables too (like the innodb log flushing policy).

      Comment


        #4
        yesterday, i also bumped up the table_cache=512. afterwards my open_tables look like:

        Code:
        mysql> show global status like 'open%';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | Open_files    | 7     | 
        | Open_streams  | 0     | 
        | Open_tables   | 405   | 
        | Opened_tables | 9170  | 
        +---------------+-------+
        4 rows in set (0.17 sec)
        with open_tables at 405 it looks like table_cache=512 is probably a good value, i had it at 256 before and open_tables was pegged at 256 as well.

        ...

        bumping up the innodb log size was fairly important in reducing I/O utilization. my sar logs were showing wr_sec/sec of >30,000 uniformly before making the change:

        Code:
        10:05:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
        10:06:01     dev104-0    343.53      0.00  35462.17    103.23      0.17      0.48      0.28      9.66
        10:07:01     dev104-0    332.42      0.00  33918.43    102.03      0.13      0.39      0.23      7.51
        10:08:01     dev104-0    352.33      0.00  35738.79    101.44      0.13      0.38      0.21      7.53
        10:09:02     dev104-0    359.64      0.00  36840.64    102.44      0.14      0.39      0.22      7.89
        10:10:01     dev104-0    362.07     11.21  37009.71    102.25      0.14      0.39      0.22      7.94
        10:11:01     dev104-0    352.11      0.00  36520.25    103.72      0.14      0.39      0.22      7.62
        Afterwards it is less uniform due to the periodic flushing from the logfile to the database tablespaces but there's a lot less I/O:

        Code:
        23:46:01     dev104-0     69.28     63.60   9818.88    142.65      0.07      0.95      0.43      2.97
        23:47:01     dev104-0     86.98     11.41  11884.18    136.76      0.06      0.70      0.28      2.48
        23:48:01     dev104-0     51.06      8.19   5397.58    105.88      0.03      0.66      0.23      1.20
        23:49:01     dev104-0     41.08     10.12   3852.78     94.03      0.03      0.64      0.23      0.94
        23:50:01     dev104-0     38.03      6.39   3464.05     91.26      0.03      0.67      0.24      0.90
        23:51:01     dev104-0     40.76      7.99   3497.97     86.02      0.03      0.68      0.23      0.95
        23:52:01     dev104-0     33.95      8.53   3408.96    100.66      0.02      0.72      0.26      0.88
        23:53:01     dev104-0     45.13      5.85   5178.81    114.89      0.04      0.83      0.27      1.21
        23:54:01     dev104-0     76.01     11.74  11922.39    157.01      0.07      0.90      0.32      2.41
        23:55:01     dev104-0     71.65      8.51   9108.94    127.25      0.05      0.67      0.25      1.82
        (the log file size was the only config change in between those two datasets)
        Last edited by lamont; 05-05-2009, 22:00.

        Comment


          #5
          Originally posted by lamont View Post
          I had some time with an actual MySQL consultant on Friday and we went over MySQL performance tuning for my heaviest zabbix server (299 hosts, 18905 items, 7210 triggers, 5415 services). The server that I'm using is a 2x dual core with 6 15k SAS drives and a RAID array with 512MB of cache. Some of the recommendations:
          ----------------------snip--------------------------

          Just wanted to say thanks so much for posting this information as it's really helped me out and resolved multiple problem points for me.


          Kind regards

          Comment


            #6
            One thing that i still don't know how to due is tuning down the CPU consumption of mysql. All the tuning above helped, but i'm still primarily cpu-bound on zabbix, and I don't know what mysql is spending all its time doing. I've upgraded the server above to a dual-quad, but then threw more stuff on it, and now its pretty well saturating all the CPUs again. I upgraded mysql to the latest 5.0.x and it didn't make a lot of difference -- I haven't gotten around to testing 5.1.x or any dev versions yet.

            I still just don't know how to tell what is spending all the time in the CPU, and what requests might be CPU-heavy... Since it is a database, ideally I'd like it to look a bit more IO-bound...

            Comment


              #7
              Oh, and I found that 32GB of RAM helped a lot in perceived responsiveness since the history tables tended to stay in RAM, rather than having long delays printing out graphs from data on disk.

              If graphs for the past hours or day is snappy, but when you display a longer 3-day graph it grinds like crazy ('iostat -x 1' on linux shows 100% disk utilization while the graph is displaying), then you might want more RAM.

              Comment


                #8
                Originally posted by lamont View Post
                Oh, and I found that 32GB of RAM helped a lot in perceived responsiveness since the history tables tended to stay in RAM, rather than having long delays printing out graphs from data on disk.

                If graphs for the past hours or day is snappy, but when you display a longer 3-day graph it grinds like crazy ('iostat -x 1' on linux shows 100% disk utilization while the graph is displaying), then you might want more RAM.

                Wow, 32G of ram? I'm running with 8G and so far things seem pretty ok. How many hosts/items/triggers do you have? What is your required values/sec?

                Comment


                  #9
                  mysqltuner.pl

                  I've had some good "return on time investment" from running mysqltuner.pl:

                  Code:
                   >>  MySQLTuner 1.1.1 - Major Hayden <[email protected]>
                   >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
                   >>  Run with '--help' for additional options and output filtering
                  
                  -------- General Statistics --------------------------------------------------
                  [--] Skipped version check for MySQLTuner script
                  [OK] Currently running supported MySQL version 5.0.77
                  [OK] Operating on 64-bit architecture
                  
                  -------- Storage Engine Statistics -------------------------------------------
                  [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
                  [--] Data in InnoDB tables: 97M (Tables: 88)
                  [OK] Total fragmented tables: 0
                  
                  -------- Performance Metrics -------------------------------------------------
                  [--] Up for: 7d 17h 24m 57s (12M q [19.392 qps], 112K conn, TX: 2B, RX: 1B)
                  [--] Reads / Writes: 71% / 29%
                  [--] Total buffers: 906.0M global + 3.6M per thread (100 max threads)
                  [OK] Maximum possible memory usage: 1.2G (63% of installed RAM)
                  [OK] Slow queries: 0% (10/12M)
                  [OK] Highest usage of available connections: 27% (27/100)
                  [OK] Key buffer size / total MyISAM indexes: 8.0M/67.0K
                  [!!] Key buffer hit rate: 89.7% (684K cached / 70K reads)
                  [OK] Query cache efficiency: 25.6% (2M cached / 9M selects)
                  [!!] Query cache prunes per day: 2623
                  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 140K sorts)
                  [OK] Temporary tables created on disk: 18% (82K on disk / 436K total)
                  [OK] Thread cache hit rate: 99% (156 created / 112K connections)
                  [OK] Table cache hit rate: 21% (128 open / 586 opened)
                  [OK] Open file limit used: 0% (0/1K)
                  [OK] Table locks acquired immediately: 100% (10M immediate / 10M locks)
                  [OK] InnoDB data size / buffer pool: 97.5M/256.0M
                  
                  -------- Recommendations -----------------------------------------------------
                  General recommendations:
                      Enable the slow query log to troubleshoot bad queries
                      Increasing the query_cache size over 128M may reduce performance
                  Variables to adjust:
                      query_cache_size (> 512M) [see warning above]
                  Here's an example of suggestions it made that I've implemented:
                  Code:
                  # mysqltuner recommendations
                  skip-bdb
                  query_cache_size=512M
                  query_cache_limit=256M
                  join_buffer_size=1M
                  thread_cache_size=4
                  table_cache=128
                  innodb_buffer_pool_size=256M
                  tmp_table_size=128M
                  max_heap_table_size=128M
                  Note I'm not suggesting "run this script and blindly implement what it says"... I'm just saying a combination of reading about tuning MySQL + this script helped me out

                  I really like the 'tmpfs' idea as mentioned in this thread... I might go ahead and implement that one myself.

                  Comment


                    #10
                    Originally posted by elvar View Post
                    Wow, 32G of ram? I'm running with 8G and so far things seem pretty ok. How many hosts/items/triggers do you have? What is your required values/sec?
                    this is the zabbix server that is doing the most work (i have probably 1,200 production servers, and multiple zabbix servers monitoring them):

                    Number of hosts (monitored/not monitored/templates) 305 280 / 0 / 25
                    Number of items (monitored/disabled/not supported) 18675 17545 / 0 / 1130
                    Number of triggers (enabled/disabled)[true/unknown/false] 8087 7845 / 242 [7 / 20 / 7818]
                    Number of users (online) 2783 2
                    Required server performance, new values per second 289.6569

                    Comment


                      #11
                      Originally posted by ericgearhart View Post
                      I've had some good "return on time investment" from running mysqltuner.pl:
                      you should check those values against what i've got written above...

                      i started with mysqltuner.pl and another tuning script out there before getting some help directly from the consultant... in a lot of cases where mysqltuner.pl is suggesting different values, those are the values that i had and the consultant advised modifying those... check comment #3 in this thread for example... mysqltuner.pl will have you chasing higher values of those trying to get better hit rates in the query caches, which is not possible when you're load is dominated by inserts...

                      Comment


                        #12
                        Originally posted by lamont View Post
                        i started with mysqltuner.pl and another tuning script out there before getting some help directly from the consultant... in a lot of cases where mysqltuner.pl is suggesting different values, those are the values that i had and the consultant advised modifying those... check comment #3 in this thread for example... mysqltuner.pl will have you chasing higher values of those trying to get better hit rates in the query caches, which is not possible when you're load is dominated by inserts...
                        I noticed mysqltuner wasn't perfect... nothing beats reading a lot about exactly what values are being modified, but it doesn't hurt as a starting point.

                        Any tuning done by blindly following what a script says is a bad idea though...reading, reading, reading is your best friend when trying to performance tune anything, wouldn't you say?

                        Comment


                          #13
                          Originally posted by lamont View Post
                          I had some time with an actual MySQL consultant on Friday and we went over MySQL performance tuning for my heaviest zabbix server (299 hosts, 18905 items, 7210 triggers, 5415 services). The server that I'm using is a 2x dual core with 6 15k SAS drives and a RAID array with 512MB of cache. Some of the recommendations:
                          - what is your operating system ?
                          - which Zabbix version (1.6 vs 1.8), i saw a huge improvement ( load / 3)
                          - how many items/second ?
                          - how many sql queries/second ? (graph with item: mysqladmin extended-status | grep Com_)
                          - disk/mysql cache/hit ? (mysqladmin extended-status,
                          innodb_buffer_pool_read_requests / innodb_data_read

                          Comment


                            #14
                            Originally posted by lamont View Post
                            One thing that i still don't know how to due is tuning down the CPU consumption of mysql.

                            I still just don't know how to tell what is spending all the time in the CPU, and what requests might be CPU-heavy... Since it is a database, ideally I'd like it to look a bit more IO-bound...
                            First, thanks for sharing your mysql tuning experience, it has been a huge help!

                            Second, regarding CPU consumption of mysql. Up until a week ago, mysql on my zabbix server had always stayed pretty constant at around 18% - 19% of cpu. Now I have it down to 7%! Look at the graph below:

                            How this came about was that I had often wondered what would be the optimum number of StartPollers and StartTrappers. Too few Pollers/Trappers is not good, and too many is also not good. So how does one know where that 'happy medium' is. I created the following UserParameters, and created a 'stacked' graph. The graph clearly showed that most of my mysql threads were spending their day Sleeping (using up resources).

                            UserParameter=mysql.threads.sleep,mysqladmin -u admin -padmin processlist | grep Sleep | wc -l
                            UserParameter=mysql.threads.active,mysqladmin -u admin -padmin processlist | grep -v Sleep | wc -l

                            [Sorry, having trouble placing the graphs in the desired place. So they are both at the bottom. You'll see, one is 'mysql threads', the other 'mysql cpu'].

                            On the graph below, at point #1 I reduced both Pollers and Trappers from 50 to 30. The difference is obvious. And I still have plenty of sleeping mysql threads.
                            At point #2 (today) I reduced them further, from 30 down to 15. And still plenty of mysql threads to carry the workload. Oh, and nothing in the queue!

                            The graph shown below is from this UserParameter:

                            UserParameter=mysqld_cpu.pc,ps auxw|grep mysqld|grep -v grep| awk '{s+=$3} END {print s}'

                            I would appreciate any feedback. Try it and see if it helps.

                            MrKen
                            Attached Files
                            Last edited by MrKen; 21-04-2010, 03:59.
                            Disclaimer: All of the above is pure speculation.

                            Comment


                              #15
                              MrKen -

                              What an excellent tuning opportunity. Thanks for providing the setup you used!

                              Perfect timing for us as well... we are upgrading from 1.6.6 --> 1.8.2 on Friday.

                              With that said... on my Zabbix server I have:
                              StartPollers=5
                              StartTrappers=128

                              I know trappers is high, but it was set that way for our anticipated load of 12 globally located proxy servers handling 2,000 hosts. Currently we are only at 200 hosts. We will hit the 2,000 mark shortly after our 1.8.2 upgrade.

                              Just started collecting, but here's what I am seeing so far:
                              Attached Files
                              Last edited by tchjts1; 22-04-2010, 15:42.

                              Comment

                              Announcement

                              Collapse
                              No announcement yet.
                              Working...
                              X