Mysql monitoring server, high performace, strange mysql values

[grodrigo]

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 )

[artur8ur]

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...

[grodrigo]

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 )

[artur8ur]

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

[grodrigo]

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.