high cpu and other issues after upgrading to mariadb 10

Support/Development for MySQL, MariaDB, and other database systems
soaringeagle
New Forum User
New Forum User
Posts: 3
Joined: Thu Oct 22, 2015 3:09 am
Location: earth

high cpu and other issues after upgrading to mariadb 10

Unread post by soaringeagle »

i have a dedicated server, 8 cores 32 gigs ram hosting about a dozen sites but 1 is very large
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
on upgrading to mariadb the 8 core cpu readings were spiking at 14.00+!
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
any idea what to do to reduce this out of control cpu loads
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?
prupert
Forum Regular
Forum Regular
Posts: 573
Joined: Tue Aug 01, 2006 2:45 pm
Location: Netherlands

Re: high cpu and other issues after upgrading to mariadb 10

Unread post by prupert »

What is the load bottleneck? RAM, CPU, disk (iowait)? Figure that out, and start from there.

By the way, your MySQL server configuration looks pretty wacko. Of course, I do not know your exact work load, but I dare to question how you came up with these settings. :-)
Lemonbit Internet Dedicated Server Management
soaringeagle
New Forum User
New Forum User
Posts: 3
Joined: Thu Oct 22, 2015 3:09 am
Location: earth

Re: high cpu and other issues after upgrading to mariadb 10

Unread post by soaringeagle »

prupert wrote:What is the load bottleneck? RAM, CPU, disk (iowait)? Figure that out, and start from there.

By the way, your MySQL server configuration looks pretty wacko. Of course, I do not know your exact work load, but I dare to question how you came up with these settings. :-)
cpu was the biggest issue, often off the charts high...it was very volitle though fluctuating wildly
sometimes setling down to about double what it was under5 mysql 5.6 then shooting way up

at 1 point..cpu, ram, and swap all maxed out and i lowered swappability

as for my settings. started with the recomended for my main site then tweaked it using the recommendations of the tuning scripts
and some research
Post Reply