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)

[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 )

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

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 )

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

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 :smiley:

Regards from Zaragoza.