I'm using this settings now, on phpmyadmin advisor it says:
and I'm using Memcached so I'm good?
Thanks !
MySQL Tuning.
- Thread starter Slavik
- Start date
Thanks !
Deebs
Well-known member
Oracle has disabled the Query Cache in 5.6 and have asked the community on their views to removing it completely as it does not scale on hardware available today. Keep it off and forget it ever existed.I'm using this settings now, on phpmyadmin advisor it says:
and I'm using Memcached so I'm good?
Thanks !
Floren
Well-known member
You are right, for 5.6. However, based on my own experience, disabling the cache on MySQL/MariaDB 5.5.x will result in performance issues. I did a comparison today on MariaDB 5.5.38 and MariaDB 10.0.11, both with cache off and identical compile options. On 5.5 the load increased 8 times, while on 10 it was practically null. So I would be careful to disable the cache on 5.5. Even if I compiled the packages with jemalloc 3.6.0 as system library, both releases did not accepted either libevent 1.4.x or 2.0.x as system libraries and forced the use of bundled ones instead. The compile options were truly identical and ran through cmake 2.8.12.2, which is the required version for MariaDB.
Settings used:
IMO, MariaDB 10 is the right choice as we speak, performance wise.
Last edited:
Deebs
Well-known member
Different workloads will behave differently but the engineers at both Oracle and Percona state that it hurts performance, I myself see an improvement due to not waiting on a horrible global mutex and it is simply quicker to re-execute the query. The cache was great with single core/threaded servers back in the day but the fact is that it simply does not scale and most of the time the cache is being invalidated due to the amount of underlying updates/inserts being made.
Not one of the big sites use the query cache as a cache but use some other technology and move the caching closer to the end recipient of the data. I personally hope that Oracle remove it and all the code from a 5.7 milestone release.
Brent W
Well-known member
I just made the jump to MariaDB 10.0.11
How does my.cnf look? 4GB DigitalOcean droplet.
How does my.cnf look? 4GB DigitalOcean droplet.
Code:
[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#innodb=ON
skip-federated
##skip-pbxt
##skip-pbxt_statistics
skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 150
key_buffer_size = 32M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 4096
table_open_cache = 2048
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0
#query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB
default-time-zone = '-06:00'
log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log
# innodb settings
##innodb_extra_rsegments = 4
innodb_purge_threads=1
#innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2048M
#innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2
# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1
userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M
[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0
#innodb_lazy_drop_table=1
innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192
[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
[mariadb-10.0]
# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb
## MariaDB 10 InnoDB plugin only
#innodb_buffer_pool_load_now=1
#innodb_buffer_pool_dump_now=1
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
Last edited:
Brent W
Well-known member
Or DigitalOcean Droplet
That would be the correct name.
Regarding my.cnf, I just follow my phpmyadmin recommendation
MattW
Well-known member
You could take a look at
innodb_io_capacity
and try tuning that based on the SSD speed of the VPS you are on?
http://www.reddit.com/r/linux/comments/1dxd75/
For your ssd server @MattW what is the best value for that? I tried reading a lot of articles but didn't answer me
MattW
Well-known member
This is the HDD in our servers : http://ark.intel.com/products/75678/Intel-SSD-DC-S3500-Series-120GB-2_5in-SATA-6Gbs-20nm-MLC
So I took their random write value and used that (4600)
So I took their random write value and used that (4600)
Similar threads
