Announcement

Announcement Module
Collapse
No announcement yet.

Mysql monitoring server, high performace, strange mysql values

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

  • Mysql monitoring server, high performace, strange mysql values

    Hi all,

    First of all, hi to everybody, first message here )

    I'm an Spanish Unix system administrator working for a supermarket company, now we are deploying a monitoring solution using zabbix with a php+mysql frontend.

    A few weeks ago we bought a brand new hp server with quad core and 8gb ram, we deployed there the database and frontend and we noticed an extremly slow web performance. We make some tunning with the procedure of "touch this value, restart mysql -> if it's faster, then it's ok, if not, don't touch that value".

    After that "tunning" we finally found a good balance between system performance and speed of web interface, now the server is running fine, so seems to be that everything is like is supossed to be.

    But i noticed, even when the performance is good, a few strange values on mytop and phpmyadmin. First of all, here you have the my.cnf values:

    (btw, server is debian running mysql 5)

    Quote:



    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram

    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    #
    # * Basic Settings
    #

    #
    # * IMPORTANT
    # If you make changes to these settings and your system uses apparmor, you may
    # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
    #

    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /var/lib/mysql
    tmpdir = /tmp
    language = /usr/share/mysql/english
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address = 10.1.2.50
    #
    # * Fine Tuning
    #
    key_buffer = 1500M
    max_allowed_packet = 16M
    thread_stack = 128K
    thread_cache_size = 8
    max_connections = 100
    table_cache = 1800M
    thread_concurrency = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit = 512M
    query_cache_size = 512M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    #log = /var/log/mysql/mysql.log
    #
    # Error logging goes to syslog. This is a Debian improvement )
    #
    # Here you can see queries with especially long duration
    log_slow_queries = /var/log/mysql/mysql-slow.log
    long_query_time = 1
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    # other settings you may need to change.
    #server-id = 1
    #log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 10
    max_binlog_size = 100M
    #binlog_do_db = include_database_name
    #binlog_ignore_db = include_database_name
    #
    # * BerkeleyDB
    #
    # Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
    skip-bdb
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    # You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
    #skip-innodb
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem

    innodb_buffer_pool_size = 1900M

    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M

    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition

    [isamchk]
    #key_buffer = 16M

    #
    # * NDB Cluster
    #
    # See /usr/share/doc/mysql-server-*/README.Debian for more information.
    #
    # The following configuration is read by the NDB Data Nodes (ndbd processes)
    # not from the NDB Management Nodes (ndb_mgmd processes).
    #
    # [MYSQL_CLUSTER]
    # ndb-connectstring=127.0.0.1


    #
    # * IMPORTANT: Additional settings that can override those from this file!
    # The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/



    And here you have the values shown on red on phpmyadmin:

    Quote:


    This MySQL server has been running for 12 days, 17 hours, 25 minutes and 46 seconds. It started up on Sep 17, 2008 at 02:38 PM.

    Query statistics: Since its startup, 1,533,007,810 queries have been sent to the server.

    Traffic Tip ø per hour
    Received 78 GiB 261 MiB
    Sent 157 GiB 527 MiB
    Total 235 GiB 788 MiB
    Connections ø per hour %
    max. concurrent connections 43 --- ---
    Failed attempts 18 k 58.60 5.87%
    Aborted 136 0.00 k 0.04%
    Total 305 k 997.57 100.00%

    Slow_queries 26 k
    Innodb_buffer_pool_pages_dirty 9,114
    Innodb_buffer_pool_reads 111 k
    Innodb_row_lock_time_max 165
    Innodb_row_lock_waits 2,719
    Handler_read_rnd 255 M
    Handler_read_rnd_next 1,776 M
    Created_tmp_disk_tables 162 M
    Key_reads 20 M
    Select_full_join 256 k
    Sort_merge_passes 134
    Opened_tables 210




    Database size is about 15gb. The problem that we have here is, as we are not databse administrator, we don't know if we could encounter a problem in the future, or if those values are ok for that traffic/queries.

    Someone can help? Thanks in advance for you help )

  • #2
    Hi,

    First: Are you using InnoDB or MyISAM tables? Or a mixture of both?

    I will assume that you are using InnoDB, because you showed InnoDB stats...


    Second: It is not easy to help someone, if you know not much about what he is doing... But I try as much as i can, based on the infos you have given:



    Quote:

    key_buffer = 1500M

    If you use use InnoDB only, you don't need such a big key_buffer, its for MyISAM Index buffering.


    Quote:

    table_cache = 1800M

    The table cache is for opened tables... I think the M here is an mistake.. you do not want to cache 1,800,000,000 open tables...


    Quote:

    query_cache_size = 512M

    A query cache of 512M is big. If it works for you, this is fine. I have had problems in the past with such a big cache, when the cache was filled with many small queries the system could hang for minutes on updates (cleaning the query cache).
    It worked for some days, and than out of nowhere the system stood still.


    Quote:

    query_cache_limit = 512M

    This is definitely a big value. Just, to remember, the default is 1MB. Depending on your Workload this might be helpful, but you are filling up your cache with one result, which will be droped after the execution of the next query... (First in, First out)

    Quote:

    innodb_buffer_pool_size = 1900M

    This is not to much, on a 8GB system. Again, this value depends heavily on your workload, so it might be good, but a typical value would be much bigger. For InnoDB the Data and Indexes are cached in the buffer (in contrast to MyISAM, where only indexes are cached in the key_pool).


    Since the buffer_pool is your only InnoDB configuration, here a list of other values to tune:
    Quote:


    innodb_data_file_path = ibdata1:XXG:autoextend
    # Set XX to a value of your choice.. You have 15 GB DB? Set it to 20GB
    # The reason is to create the InnoDB file in one chunk, to avoid fragmentation

    innodb_log_file_size = 512M
    # This defines how much data can be changed, before it has to be flushed to the Tablespace.
    # Big log files need a long "Reply" time after a crash, but if it is to small, the system has to wait till the logfile is flushed to the table space, when it is full.

    innodb_log_buffer_size = 16M
    # How much data can be cached in Memory before it has to be flushed to disk...

    innodb_autoextend_increment = 1024M
    # If your InnoDB table space gets full, increase it in a reasonable chunk, again to avoid fragmentation

    innodb_flush_method = O_DIRECT
    # Do not fill the system disk cache with data, that is cached by MySQL in the innodb_buffer




    Quote:

    Sort_merge_passes 134

    You might want to tune your sort buffer size...
    sort_buffer_size = 16M

    Quote:

    Opened_tables 210

    Your table cache is to big! Set it to 250.


    Quote:

    Slow_queries 26 k

    You do have queries which do not perform very well... Most time the problem is with the DB-Design or the query itself.. (No Index, or not using it...)
    Have a look on slow queries log, what appears there and check them using the explain syntax...

    Comment


    • #3
      Thanks for your reply, much appreciated!

      Regarding what are we doing, (don't know if that's the info that you want) this is a monitoring server based on zabbix.

      The problem is, as i told before, that i'm not bbdd admin, so, we perform the tunning using the method "touch it, if it works better, its good, if not, cancel the changes" )

      So, we are going to perform this changes on saturday, will update this thread with results )

      Thanks again )

      Comment


      • #4
        Hi

        to really know what you are doing, I would need to see your table structure and the application running... and maybe some example sql queries...
        But this is out of the scope for here.

        If your system is to slow, you can turn on the slow query log and post them here (with the affected tables).

        Even if the product is not written by you, you can tune the system by adding some indexes. In many products they are not optimal. And this is a good point for and DBA to tune his installation.


        I am looking forward to see the results of your test, based on my comments. So please post them here.


        Cheers

        artur

        Comment


        • #5
          Hi all,

          Just a quick update for artur, we plan to update the database values on april-may more or less when we have a test environment (now we didn't have it, so the changes must be done on the production server, and we want to change that ) )

          So, i did'nt forgot this thread, and when we have the results of the changes will update (again) the thread

          Regards from Zaragoza.

          Comment

          Working...
          X