0 like 0 dislike
asked in MySQL Database Forum by (4k points)  

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.

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.

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!

My final my.conf looks like this:

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.
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.
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

# * 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
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            =
# * 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
# 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.
# * 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.

## Produce informations about wrong informations in tables.


# * 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

max_allowed_packet      = 16M

#no-auto-rehash # faster start of mysql but no tab completition

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).
# ndb-connectstring=

# * IMPORTANT: Additional settings that can override those from this file!
!includedir /etc/mysql/conf.d/


Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (4k points)  

OK I found out some more information.

First off, I decided to look at what was turned on/off. I ran a 'mysqladmin -uroot -p variables'


Variable_name		       |Value
auto_increment_increment       |1
auto_increment_offset	       |1
automatic_sp_privileges	       |ON
back_log		       |50
basedir			       |/usr/
binlog_cache_size	       |32768
bulk_insert_buffer_size	       |8388608
character_set_client	       |latin1
character_set_connection       |latin1
character_set_database	       |latin1
character_set_filesystem       |binary
character_set_results	       |latin1
character_set_server	       |latin1
character_set_system	       |utf8
character_sets_dir	       |/usr/share/mysql/charsets/
collation_connection	       |latin1_swedish_ci
collation_database	       |latin1_swedish_ci
collation_server	       |latin1_swedish_ci
completion_type		       |0
concurrent_insert	       |1
connect_timeout		       |5
datadir			       |/var/lib/mysql/
date_format		       |%Y-%m-%d
datetime_format		       |%Y-%m-%d %H:%i:%s
default_week_format	       |0
delay_key_write		       |ON
delayed_insert_limit	       |100
delayed_insert_timeout	       |300
delayed_queue_size	       |1000
div_precision_increment	       |4
engine_condition_pushdown      |OFF
expire_logs_days	       |0
flush			       |OFF
flush_time		       |0
ft_boolean_syntax	       |+ -><()~*:""&|
ft_max_word_len		       |84
ft_min_word_len		       |4
ft_query_expansion_limit       |20
ft_stopword_file	       |(built-in)
group_concat_max_len	       |1024
have_archive		       |YES
have_bdb		       |NO
have_blackhole_engine	       |YES
have_compress		       |YES
have_crypt		       |YES
have_csv		       |YES
have_dynamic_loading	       |YES
have_example_engine	       |NO
have_federated_engine	       |YES
have_geometry		       |YES
have_innodb		       |YES
have_isam		       |NO
have_merge_engine	       |YES
have_ndbcluster		       |DISABLED
have_openssl		       |DISABLED
have_ssl		       |DISABLED
have_query_cache	       |YES
have_raid		       |NO
have_rtree_keys		       |YES
have_symlink		       |YES
hostname		       |AS1
init_connect		       |
init_file		       |
init_slave		       |
innodb_autoextend_increment    |8
innodb_buffer_pool_awe_mem_mb  |0
innodb_buffer_pool_size	       |367001600
innodb_checksums	       |OFF
innodb_commit_concurrency      |0
innodb_concurrency_tickets     |500
innodb_data_file_path	       |ibdata1:10M:autoextend
innodb_data_home_dir	       |
innodb_doublewrite	       |OFF
innodb_fast_shutdown	       |1
innodb_file_io_threads	       |4
innodb_file_per_table	       |ON
innodb_flush_log_at_trx_commit |0
innodb_flush_method	       |
innodb_force_recovery	       |0
innodb_lock_wait_timeout       |50
innodb_locks_unsafe_for_binlog |OFF
innodb_log_arch_dir	       |
innodb_log_archive	       |OFF
innodb_log_buffer_size	       |8388608
innodb_log_file_size	       |5242880
innodb_log_files_in_group      |2
innodb_log_group_home_dir      |./
innodb_max_dirty_pages_pct     |90
innodb_max_purge_lag	       |0
innodb_mirrored_log_groups     |1
innodb_open_files	       |300
innodb_rollback_on_timeout     |OFF
innodb_support_xa	       |OFF
innodb_sync_spin_loops	       |20
innodb_table_locks	       |ON
innodb_thread_concurrency      |8
innodb_thread_sleep_delay      |10000
interactive_timeout	       |28800
join_buffer_size	       |131072
key_buffer_size		       |268435456
key_cache_age_threshold	       |300
key_cache_block_size	       |1024
key_cache_division_limit       |100
language		       |/usr/share/mysql/english/
large_files_support	       |ON
large_page_size		       |0
large_pages		       |OFF
lc_time_names		       |en_US
license			       |GPL
local_infile		       |ON
locked_in_memory	       |OFF
log			       |OFF
log_bin			       |OFF
log_error		       |
log_queries_not_using_indexes  |OFF
log_slave_updates	       |OFF
log_slow_queries	       |OFF
log_warnings		       |1
long_query_time		       |10
low_priority_updates	       |OFF
lower_case_file_system	       |OFF
lower_case_table_names	       |0
max_allowed_packet	       |134217728
max_binlog_cache_size	       |4294967295
max_binlog_size		       |1073741824
max_connect_errors	       |10
max_connections		       |100
max_delayed_threads	       |20
max_error_count		       |64
max_heap_table_size	       |16777216
max_insert_delayed_threads     |20
max_join_size		       |18446744073709551615
max_length_for_sort_data       |1024
max_prepared_stmt_count	       |16382
max_relay_log_size	       |0
max_seeks_for_key	       |4294967295
max_sort_length		       |1024
max_sp_recursion_depth	       |0
max_tmp_tables		       |32
max_user_connections	       |0
max_write_lock_count	       |4294967295
multi_range_count	       |256
myisam_data_pointer_size       |6
myisam_max_sort_file_size      |2147483647
myisam_recover_options	       |OFF
myisam_repair_threads	       |1
myisam_sort_buffer_size	       |8388608
myisam_stats_method	       |nulls_unequal
ndb_autoincrement_prefetch_sz  |32
ndb_force_send		       |ON
ndb_use_exact_count	       |ON
ndb_use_transactions	       |ON
ndb_cache_check_time	       |0
ndb_connectstring	       |
net_buffer_length	       |16384
net_read_timeout	       |30
net_retry_count		       |10
net_write_timeout	       |60
new			       |OFF
old_passwords		       |OFF
open_files_limit	       |1134
optimizer_prune_level	       |1
optimizer_search_depth	       |62
pid_file		       |/var/run/mysqld/mysqld.pid
port			       |3306
preload_buffer_size	       |32768
profiling		       |OFF
profiling_history_size	       |15
protocol_version	       |10
query_alloc_block_size	       |8192
query_cache_limit	       |4194304
query_cache_min_res_unit       |4096
query_cache_size	       |268435456
query_cache_type	       |ON
query_cache_wlock_invalidate   |OFF
query_prealloc_size	       |8192
range_alloc_block_size	       |2048
read_buffer_size	       |131072
read_only		       |OFF
read_rnd_buffer_size	       |262144
relay_log_purge		       |ON
relay_log_space_limit	       |0
rpl_recovery_rank	       |0
secure_auth		       |OFF
secure_file_priv	       |
server_id		       |0
skip_external_locking	       |ON
skip_networking		       |OFF
skip_show_database	       |OFF
slave_compressed_protocol      |OFF
slave_load_tmpdir	       |/tmp/
slave_net_timeout	       |3600
slave_skip_errors	       |OFF
slave_transaction_retries      |10
slow_launch_time	       |2
socket			       |/var/run/mysqld/mysqld.sock
sort_buffer_size	       |2097144
sql_big_selects		       |ON
sql_mode		       |
sql_notes		       |ON
sql_warnings		       |OFF
ssl_ca			       |
ssl_capath		       |
ssl_cert		       |
ssl_cipher		       |
ssl_key			       |
storage_engine		       |MyISAM
sync_binlog		       |0
sync_frm		       |ON
system_time_zone	       |CDT
table_cache		       |512
table_lock_wait_timeout	       |50
table_type		       |MyISAM
thread_cache_size	       |80
thread_stack		       |131072
time_format		       |%H:%i:%s
time_zone		       |SYSTEM
timed_mutexes		       |OFF
tmp_table_size		       |33554432
tmpdir			       |/tmp
transaction_alloc_block_size   |8192
transaction_prealloc_size      |4096
tx_isolation		       |REPEATABLE-READ
updatable_views_with_limit     |YES
version			       |5.0.45-Debian_1
version_comment		       |Debian etch distribution
version_compile_machine	       |i486
version_compile_os	       |pc-linux-gnu
wait_timeout		       |28800

If I read this statement right, then apprently the my.conf did not enable InnoDB.


storage_engine		 |MyISAM

Also, from what I have found using InnoDB probably isn't the best thing unless you know you are going to have a huge database. This website hasn't really helped convince me that InnoDB is the way to go ( http://www.daniweb.com/forums/thread40911.html Started a year ago, but last updated less then a day ago so the posting is still active).

Another thing is wait_timeout. The second article I posted in the previous post suggested 60. It looks like mine is currently set to 28800.  Thats quite the difference.

delay_key_write: According to this website ( http://www.petefreitag.com/item/441.cfm ) "the option makes index updates faster because they are not flushed to disk until the table is closed."
Please correct me if I am wrong but this sounds very dangerous to me. First on small tables, I doubt it would do much at all. Second on large tables, if the database crashed, server died, network hiccup, ect then data could be lost and / or corrupted. Unless someone can give me a better reason, I think I am just going to go with the default (apparently its ON) and leave this one alone and just as it is.

Last item (for now anyway  ) is the sort_buffer_size. I have found several sites that claim something along lines of 'if you do a lot of sorting on your tables or if you know you are going to be sorting a lot, then increase this somewhere between 10MB and 256MB. Otherwise, don't sweat it.' Could someone tell me if Zabbix does enough big sorts that increasing this would be good?

0 like 0 dislike
answered by (4k points)  

Hm, guys. I've read that if you'll read directly from MyISAM files you should get speed increase up to 5-7 times from Mysql Perfomance article. What do you think about this?

Related questions

0 like 0 dislike
1 answer
asked Mar 31 in MySQL Database Forum by nijamutheen j (13.3k points)  
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Mar 30 in MySQL Database Forum by nijamutheen j (13.3k points)  
0 like 0 dislike
6 answers
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.