I have had a few conversations in other threads about doing some fine tuning and performance tweaks to MySQL as I have reason to believe that MySQL is what is causing some slowdowns on my systems. I have done some implementations and experimenting on the suggestions given in the forums and I have also been looking into other possibilities and researching them. As I am just now learning about the inner workings of Zabbix, I would appreciate it if anyone could tell me if these tweaks help, hinder, or if I just did a serious no-no. I am just listing some of the changes I have made based off of what I have found as well as some items that keep popping up that I am still trying to get more information on.
I have not committed any changes to my production server, just my test Zabbix server so if I did just really screw something up...meh who cares? Thats what the test system is there for, right?
While I know about 3x more on MySQL then I did a few weeks ago, I am still in the starting stages of learning. I would really appreciate any comments, suggestions, and/or further information on MySQL improvements.
I went to a few sites and I saw a lot of similar suggestions. These are just the few sites that I bookmarked.
http://www.cyberciti.biz/tips/enable...rformance.html
I did try to modify a few settings based on my server memory. I wanted to ensure that I had plenty left over for the kernel and other programs while allowing MySQL to use a size able amount as well. My test server has 1.2GB of memory
key_buffer = 256 (1/5 of my servers memory)
query_cache_size = 256MB (1/5 of my servers memory)
query_cache_limit = 4MB
table_cache = 512
The next two were commented out in my my.conf and I have not done enough research yet to figure out if it they are worth turning on
#sort_buffer_size = 32M
#myisam_sort_buffer_size = 32M
These next three are not in my my.conf. I want to find out more before I add them.
tmp_table_size = 64MB
delay_key_write = 1
wait_timeout = 60
I commented the next line out as I have a cron process that backs up the MySQL database nightly and copies it to a different server. Therefore I believe that this is unneeded. One website said this was a noticeable gain on large databases that are frequently updating. I have not found a site that can show any type of graph or proof on this but it seems to have been mentioned on several sites. There is also a nifty warning in my.conf that I followed and commented out the expire_logs_days and max_binlog_size
#log-bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
#expire_logs_days = 10
#max_binlog_size = 100M
I have read several places that state I can safely turn off InnoDB and see a speed increase (such as link 2 states) but in this thread (http://www.zabbix.com/forum/showthread.php?t=250 ) from 2005 Alexei states that it increases parallelism and does not lock tables. From what I understand, locking tables can do bad things if a process freezes. So I did some more research and found this ( http://www.zabbix.com/forum/showthread.php?t=7771 ). I am still reading through those links (its a bit of information to go through
) but I have been playing with the suggested changes.
innodb_file_per_table
innodb_buffer_pool_size=350M
innodb_log_buffer_size=8M
innodb_support_xa=0
nnodb_flush_log_at_trx_commit=0
innodb_checksums=0
innodb_doublewrite=0
The query_cache stuff suggested I had already done up there ^ .
I also found this thread ( http://www.zabbix.com/forum/showthread.php?t=6316 ) and followed a few suggestions
max_allowed_packet = 128M (was set to 16M)
innodb_buffer_pool_size=350M (From the other thread I have it set to 350M which is about 1/4 of my physical memory but in this thread alj recommended that 70% (~896M) of my physical memory be put here! That sounds way high from other places I found; can someone verify?)
thread_cache_size = 80
I did all of these edits to my.conf, so again if I screwed something up or a change should be placed elsewhere please let me know. Any comments / criticism / suggestions are welcome but instead of posting "don't do that. its stupid" I would really appreciate an explanation or a link with details that I can gather knowledge from.
Thanks guys! I really do appreciate any help!
cstackpole
My final my.conf looks like this:
I have not committed any changes to my production server, just my test Zabbix server so if I did just really screw something up...meh who cares? Thats what the test system is there for, right?
While I know about 3x more on MySQL then I did a few weeks ago, I am still in the starting stages of learning. I would really appreciate any comments, suggestions, and/or further information on MySQL improvements.
I went to a few sites and I saw a lot of similar suggestions. These are just the few sites that I bookmarked.
http://www.cyberciti.biz/tips/enable...rformance.html
I did try to modify a few settings based on my server memory. I wanted to ensure that I had plenty left over for the kernel and other programs while allowing MySQL to use a size able amount as well. My test server has 1.2GB of memory
key_buffer = 256 (1/5 of my servers memory)
query_cache_size = 256MB (1/5 of my servers memory)
query_cache_limit = 4MB
table_cache = 512
The next two were commented out in my my.conf and I have not done enough research yet to figure out if it they are worth turning on
#sort_buffer_size = 32M
#myisam_sort_buffer_size = 32M
These next three are not in my my.conf. I want to find out more before I add them.
tmp_table_size = 64MB
delay_key_write = 1
wait_timeout = 60
I commented the next line out as I have a cron process that backs up the MySQL database nightly and copies it to a different server. Therefore I believe that this is unneeded. One website said this was a noticeable gain on large databases that are frequently updating. I have not found a site that can show any type of graph or proof on this but it seems to have been mentioned on several sites. There is also a nifty warning in my.conf that I followed and commented out the expire_logs_days and max_binlog_size
#log-bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
#expire_logs_days = 10
#max_binlog_size = 100M
I have read several places that state I can safely turn off InnoDB and see a speed increase (such as link 2 states) but in this thread (http://www.zabbix.com/forum/showthread.php?t=250 ) from 2005 Alexei states that it increases parallelism and does not lock tables. From what I understand, locking tables can do bad things if a process freezes. So I did some more research and found this ( http://www.zabbix.com/forum/showthread.php?t=7771 ). I am still reading through those links (its a bit of information to go through
) but I have been playing with the suggested changes.innodb_file_per_table
innodb_buffer_pool_size=350M
innodb_log_buffer_size=8M
innodb_support_xa=0
nnodb_flush_log_at_trx_commit=0
innodb_checksums=0
innodb_doublewrite=0
The query_cache stuff suggested I had already done up there ^ .
I also found this thread ( http://www.zabbix.com/forum/showthread.php?t=6316 ) and followed a few suggestions
max_allowed_packet = 128M (was set to 16M)
innodb_buffer_pool_size=350M (From the other thread I have it set to 350M which is about 1/4 of my physical memory but in this thread alj recommended that 70% (~896M) of my physical memory be put here! That sounds way high from other places I found; can someone verify?)
thread_cache_size = 80
I did all of these edits to my.conf, so again if I screwed something up or a change should be placed elsewhere please let me know. Any comments / criticism / suggestions are welcome but instead of posting "don't do that. its stupid" I would really appreciate an explanation or a link with details that I can gather knowledge from.
Thanks guys! I really do appreciate any help!
cstackpole
My final my.conf looks like this:
Code:
# # For reference the Zabbix server has 1.2 GB of ram # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [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 # 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 = 127.0.0.1 # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 128M thread_stack = 128K thread_cache_size = 80 #max_connections = 100 table_cache = 512 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_type = 1 query_cache_limit = 4M query_cache_size = 256M # # * 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 = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian! #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 innodb_file_per_table innodb_buffer_pool_size=350M innodb_log_buffer_size=8M ## Produce informations about wrong informations in tables. ###innodb_log_file_size=1M innodb_support_xa=0 innodb_flush_log_at_trx_commit=0 innodb_checksums=0 innodb_doublewrite=0 # # * 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 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 256M # # * 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! # !includedir /etc/mysql/conf.d/
Thats quite the difference.
Comment