trafic is not real high on any of them typically, not high enough to cause what i'm seeing now
i upgraded from mysql 5.6
and used this in my.cnf
Code: Select all
[mysqld]
sort_buffer_size = 5M
read_rnd_buffer_size = 5M
tmp_table_size = 4G
max_heap_table_size = 4G
max_connections = 160
# wait_timeout = 2000
# interactive_timeout = 2000
key_buffer =1G
max_allowed_packet=268435456
thread_cache_size = 160
table_open_cache = 42997
join_buffer_size = 3M
table_definition_cache = 46258
low_priority_updates=1
concurrent_insert=ALWAYS
query_cache_type=1
# * Query Cache Configuration
#
query_cache_limit = 3M
query_cache_size = 2G
query_cache_min_res_unit = 512
# for a 2 Gig RAM droplet
# tmp_table_size = 2048M
# max_heap_table_size = 2048M
innodb_buffer_pool_size = 20096M
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 8
# default-storage-engine=MyISAM
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=10000
before whn using myysql when under low loads it averaged 0.23 to 0.56 when running my sitemap crawler it averaged 0.76-1.14
now its pretty steady in the 2.0-4.0 range under nearly no trafic
apache connection handling seems to have changed too despite no settings changes
we tried clearing the my.cnf and using defaults.. still high (it fluctuates wildly though every so often it goes down into a normal range very briefly
i couldnt let it run 24 hours like it was with cpu spikes off the charts
so i ran the tuning script before the 24 hours and copied over some of the old values it suggested raising
its currently
Code: Select all
[mysqld]
query_cache_size = 2G
tmp_table_size = 4G
max_heap_table_size = 4G
sort_buffer_size = 8M
thread_cache_size = 160
table_open_cache = 42997
innodb_buffer_pool_size = 9096M
innodb_buffer_pool_instances =8
read_rnd_buffer_size = 8M
key_buffer =1G
ps the tuning script suggested innodb_buffer_pool_instances =19
but i had read that that should be set to the number of the cores?