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)
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.
* Basic Settings
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
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
* Query Cache Configuration
query_cache_limit = 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
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
Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
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.
* Security Features
Read the manual, too, if you want chroot!
chroot = /var/lib/mysql/
For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
innodb_buffer_pool_size = 1900M
* 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).
* IMPORTANT: Additional settings that can override those from this file!
The files must end with ‘.cnf’, otherwise they’ll be ignored.
And here you have the values shown on red on phpmyadmin:
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
Slow_queries 26 k
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 )