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)
And here you have the values shown on red on phpmyadmin:
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 )
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 )
Comment