GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Mysql Performance issue

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

  • Mysql Performance issue

    Hi,

    Thanks for contribution support.

    Am a newbie to MySQL DB, i run a web server using Mysql DB / Apache / PHP. Currently my web server has around 50,000 post.

    Past few months MySQL server seem to be using more Memory and very high CPU, also tables are getting fragmented very often.

    Some times DB gets hung and it need to be optimized in daily basis.

    Here is more detail about my server

    My.cnf

    [mysqld]
    Datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    skip-external-locking
    #skip-bdb
    skip-innodb
    # Disabling symbolic-links is recommended to prevent assorted security risks;
    # to do so, uncomment this line:
    # symbolic-links=0
    slow_query_log=/home/XXX/XXX/log_slow_query.txt
    max_user_connections = 25
    max_connections=500
    wait_timeout=20
    interactive_timeout=20
    connect_timeout = 20
    key_buffer_size = 256M
    query_cache_type=1
    query_cache_size = 400M
    query_cache_limit = 350M
    tmp_table_size = 1500M
    max_heap_table_size = 350M
    thread_cache_size = 24
    #open_tables = 256
    table_cache = 2000
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    thread_concurrency = 8
    #long_query_time=1
    myisam_sort_buffer_size = 64M
    #join_buffer_size = 256K

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [isamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M

    [myisamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M


    Apache version 2.2.3
    Mysql Version 5.1.54
    Php Version 5.2.17 with eaccelerator
    OS: CentOS 5.5 64 Bit
    Mem: 5G
    CPU: Xeon Quad Core


    Any help will be appreciated.

    Thanks in Advance.

    Bharath

  • #2
    More information is required. If there would be a single my.cnf that solves all problems, it would ship with mysql.

    Comment


    • #3
      Thanks gmouse

      Can i know information required?

      Comment


      • #4
        Tables, queries, query frequency, your daily optimization tricks, why cpu usage is high, etc.

        Comment


        • #5
          Basically i don't know about those details, i just connected through one of the content management application.

          i use mysqltuner script to analyse. hope this help you to solve my prob. if you can help with command i can produce output.

          -------- General Statistics --------------------------------------------------
          [--] Skipped version check for MySQLTuner script
          [OK] Currently running supported MySQL version 5.1.54
          [OK] Operating on 64-bit architecture

          -------- Storage Engine Statistics -------------------------------------------
          [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
          [--] Data in MyISAM tables: 514M (Tables: 20)
          [!!] Total fragmented tables: 1

          -------- Performance Metrics -------------------------------------------------
          [--] Up for: 1d 7h 2m 50s (1M q [13.245 qps], 41K conn, TX: 6B, RX: 208M)
          [--] Reads / Writes: 91% / 9%
          [--] Total buffers: 1006.0M global + 6.4M per thread (500 max threads)
          [!!] Maximum possible memory usage: 4.1G (86% of installed RAM)
          [OK] Slow queries: 0% (343/1M)
          [OK] Highest usage of available connections: 5% (27/500)
          [OK] Key buffer size / total MyISAM indexes: 256.0M/231.1M
          [OK] Key buffer hit rate: 99.9% (2B cached / 1M reads)
          [OK] Query cache efficiency: 76.8% (1M cached / 1M selects)
          [OK] Query cache prunes per day: 0
          [OK] Sorts requiring temporary tables: 0% (485 temp sorts / 143K sorts)
          [!!] Temporary tables created on disk: 49% (70K on disk / 142K total)
          [OK] Thread cache hit rate: 99% (28 created / 41K connections)
          [!!] Table cache hit rate: 8% (49 open / 607 opened)
          [OK] Open file limit used: 1% (88/8K)
          [OK] Table locks acquired immediately: 99% (490K immediate / 493K locks)

          Comment


          • #6
            try this:
            query_cache_size = 10M
            query_cache_limit = 1M
            myisam_sort_buffer_size=8M
            tmp_table_size = 16M
            max_heap_table_size = 16M

            The "Temporary tables created on disk" is very high and needs attention. If you can not determine the queries that cause this, find a consultant who can.

            Comment


            • #7
              Thanks, Already i use those with high numbers.

              query_cache_size = 40M
              query_cache_limit = 350M
              myisam_sort_buffer_size=64M
              tmp_table_size = 1500M
              max_heap_table_size = 350M

              Comment


              • #8
                Which is why I use them with lower numbers. Assigning more memory to a job does not mean the job will be done faster or more efficiently.

                Comment


                • #9
                  After changing that value, mysqltuner report as follows

                  -------- General Statistics --------------------------------------------------
                  [--] Skipped version check for MySQLTuner script
                  [OK] Currently running supported MySQL version 5.1.54
                  [OK] Operating on 64-bit architecture

                  -------- Storage Engine Statistics -------------------------------------------
                  [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
                  [--] Data in MyISAM tables: 514M (Tables: 20)
                  [!!] Total fragmented tables: 1

                  -------- Performance Metrics -------------------------------------------------
                  [--] Up for: 1m 22s (193 q [2.354 qps], 14 conn, TX: 949K, RX: 24K)
                  [--] Reads / Writes: 98% / 2%
                  [--] Total buffers: 282.0M global + 6.4M per thread (500 max threads)
                  [OK] Maximum possible memory usage: 3.4G (71% of installed RAM)
                  [OK] Slow queries: 0% (1/193)
                  [OK] Highest usage of available connections: 1% (5/500)
                  [OK] Key buffer size / total MyISAM indexes: 256.0M/231.1M
                  [OK] Key buffer hit rate: 98.3% (638K cached / 10K reads)
                  [OK] Query cache efficiency: 36.3% (61 cached / 168 selects)
                  [OK] Query cache prunes per day: 0
                  [!!] Sorts requiring temporary tables: 12% (4 temp sorts / 31 sorts)
                  [!!] Temporary tables created on disk: 46% (12 on disk / 26 total)
                  [OK] Thread cache hit rate: 64% (5 created / 14 connections)
                  [OK] Table cache hit rate: 78% (25 open / 32 opened)
                  [OK] Open file limit used: 0% (45/8K)
                  [OK] Table locks acquired immediately: 100% (153 immediate / 153 locks)

                  -------- Recommendations -----------------------------------------------------
                  General recommendations:
                  Run OPTIMIZE TABLE to defragment tables for better performance
                  MySQL started within last 24 hours - recommendations may be inaccurate
                  Enable the slow query log to troubleshoot bad queries
                  When making adjustments, make tmp_table_size/max_heap_table_size equal
                  Reduce your SELECT DISTINCT queries without LIMIT clauses
                  Your applications are not closing MySQL connections properly
                  Variables to adjust:
                  sort_buffer_size (> 2M)
                  read_rnd_buffer_size (> 2M)
                  tmp_table_size (> 16M)
                  max_heap_table_size (> 16M)

                  Comment


                  • #10
                    Look at the long-term load, and at problematic moments. Do not run some tool that looks after 1 minute and does not have all the information that you can have.

                    Comment


                    • #11
                      I Agree that, will wait for a day and see how that goes.

                      Appreciate your help.

                      Thank you

                      Comment


                      • #12
                        here is the update

                        -------- General Statistics --------------------------------------------------
                        [--] Skipped version check for MySQLTuner script
                        [OK] Currently running supported MySQL version 5.1.54
                        [OK] Operating on 64-bit architecture

                        -------- Storage Engine Statistics -------------------------------------------
                        [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
                        [--] Data in MyISAM tables: 522M (Tables: 20)
                        [!!] Total fragmented tables: 4

                        -------- Performance Metrics -------------------------------------------------
                        [--] Up for: 1d 1h 23m 21s (2M q [24.366 qps], 35K conn, TX: 8B, RX: 433M)
                        [--] Reads / Writes: 67% / 33%
                        [--] Total buffers: 282.0M global + 6.4M per thread (500 max threads)
                        [OK] Maximum possible memory usage: 3.4G (71% of installed RAM)
                        [OK] Slow queries: 0% (2K/2M)
                        [OK] Highest usage of available connections: 5% (28/500)
                        [OK] Key buffer size / total MyISAM indexes: 256.0M/241.7M
                        [OK] Key buffer hit rate: 99.9% (14B cached / 12M reads)
                        [OK] Query cache efficiency: 46.7% (793K cached / 1M selects)
                        [!!] Query cache prunes per day: 79599
                        [OK] Sorts requiring temporary tables: 4% (6K temp sorts / 168K sorts)
                        [!!] Temporary tables created on disk: 45% (68K on disk / 148K total)
                        [OK] Thread cache hit rate: 99% (31 created / 35K connections)
                        [!!] Table cache hit rate: 3% (146 open / 4K opened)
                        [OK] Open file limit used: 2% (183/8K)
                        [!!] Table locks acquired immediately: 79%
                        [!!] Connections aborted: 20%

                        -------- Recommendations -----------------------------------------------------
                        General recommendations:
                        Run OPTIMIZE TABLE to defragment tables for better performance
                        Enable the slow query log to troubleshoot bad queries
                        When making adjustments, make tmp_table_size/max_heap_table_size equal
                        Reduce your SELECT DISTINCT queries without LIMIT clauses
                        Increase table_cache gradually to avoid file descriptor limits
                        Optimize queries and/or use InnoDB to reduce lock wait
                        Your applications are not closing MySQL connections properly
                        Variables to adjust:
                        query_cache_size (> 10M)
                        tmp_table_size (> 16M)
                        max_heap_table_size (> 16M)
                        table_cache (> 2000)

                        After a day, i see my webserver started dropping connection.

                        Comment


                        • #13
                          Have you tried turning on the slow-query-log and analyzing the output so that you can identify the heavy queries?

                          So many temp tables created on disk sounds like you a combination of joins/order by's that are not using indexes and/or tables containing BLOB's (which unfortunately makes MySQL to always write the temporary tables to disk).

                          And the high CPU load also indicates that it could be performing a lot of filesorts or joins that doesn't use indexes.

                          Either way your main concern should not be the individual MySQL variables but instead the queries and the tables with indexes etc.

                          Could you provide the output from:

                          SHOW STATUS;

                          and attach it to this thread so that we can get some more information?

                          Comment


                          • #14
                            Hi Sterin,

                            Here is the result of Show status.

                            Thank You.

                            Comment


                            • #15
                              Sorry forgot to mention GLOBAL:

                              SHOW GLOBAL STATUS;

                              Comment

                              Working...
                              X