# Mysql monitoring server, high performace, strange mysql values **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 30, 2008, 1:07am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946 "2008-09-30T01:07:16Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![grodrigo](https://avatars.discourse-cdn.com/v4/letter/g/958977/32.png) [@grodrigo](https://forums.percona.com/u/grodrigo)\ **Post date:** [September 30, 2008, 1:07am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946/1 "2008-09-30T01:07:16Z")
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) | [B]Quote:[/B] | | [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: | [B]Quote:[/B] | | 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 ) ---
**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\ **Post date:** [October 1, 2008, 6:00am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946/2 "2008-10-01T06:00:57Z")
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: | [B]Quote:[/B] | | key\_buffer = 1500M | If you use use InnoDB only, you don't need such a big key\_buffer, its for MyISAM Index buffering. | [B]Quote:[/B] | | 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... | [B]Quote:[/B] | | 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. | [B]Quote:[/B] | | 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) | [B]Quote:[/B] | | 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: | [B]Quote:[/B] | | 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 | | [B]Quote:[/B] | | Sort\_merge\_passes 134 | You might want to tune your sort buffer size... sort\_buffer\_size = 16M | [B]Quote:[/B] | | Opened\_tables 210 | Your table cache is to big! Set it to 250. | [B]Quote:[/B] | | 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... ---
**Author:** ![grodrigo](https://avatars.discourse-cdn.com/v4/letter/g/958977/32.png) [@grodrigo](https://forums.percona.com/u/grodrigo)\ **Post date:** [October 3, 2008, 2:02am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946/3 "2008-10-03T02:02:55Z")
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 ) ---
**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\ **Post date:** [October 3, 2008, 3:29am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946/4 "2008-10-03T03:29:06Z")
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 ---
**Author:** ![grodrigo](https://avatars.discourse-cdn.com/v4/letter/g/958977/32.png) [@grodrigo](https://forums.percona.com/u/grodrigo)\ **Post date:** [December 18, 2008, 2:49am UTC](https://forums.percona.com/t/mysql-monitoring-server-high-performace-strange-mysql-values/946/5 "2008-12-18T02:49:46Z")
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.