Maria 10.0.15 high CPU/disk usage without queries
I just migrated the server from mysql 5.5 to mariadb 10.0.15 It is a web hosting server with about 100 sites on different platforms. So, there are about 100 databases. Most tables myisam, some innodb (mostly joomla 3)
Before with mysql CPU load was less than 10%, disk uses by mysqld was also not very high. No, with top and top -mio i see pretty high numbers. mysqld takes from 20 to 30% cpu and 80-100% disk
About configuration. It is xeon 4 cores+HT (8 cores seen by OS) with 32GB ram, 2 3TB SATA disks in software mirror.
os is FreeBSD 10.1 stable
I tried using thread of pools, one thread per connection - all the same The weird think what i show processlist i ass about 20 haning connection without any question. This is is normal, some sites leave connections for a while (mod_perl, php) the weirdness is that thare no queies while 20-30% load of CPU.
What is mariddb doing?
my.cnf [mysqld] #thread-handling= one-thread-per-connection thread_handling=pool-of-threads thread_pool_size=4 thread_cache_size = 45 init-connect='SET NAMES cp1251' default-storage-engine = MyISAM port= 3306 socket= /hoster/tmp/mysql.sock skip-external-locking skip-networking slow_query_log=1 slow_query_log_file=/hosts/MYSQLDB/slow.log # GLOBAL max_connections=800 max_allowed_packet = 50M key_buffer_size = 512M table_open_cache = 32768 query_cache_size = 256M query_cache_limit = 32M # PER THREAD read_buffer_size = 2M # PER REQUEST sort_buffer_size = 32M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 128M join_buffer_size=2M server-id = 1 innodb_data_home_dir = /hosts/MYSQLDB innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /hosts/MYSQLDB innodb_buffer_pool_size = 384M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 3 innodb_lock_wait_timeout = 50
Answer Answered by Artem Kuchin in this comment.
I'll comment by own question :)
I futher got stats on users and clients and found that there are two users which are doing select statements like crazy. Stats show liek 10M lines selected in minutes. However, i still do not see their queries using show processlist however, full query log show all their crazy select queries. Two questions arise: 1) Why show proccesslist is always empty? 2) Why i did not see such load before?
I wild guess is that mariadb and new freebsd version/setup are times faster than myssql on older config and i just cannot catch the queries, they are all executed extremely fast. And this "fastness" must come with higher cpu load. CPU is the same. So, if more work is done per seconds then i must see higher load. Probably before hdd or memory or mysql itself something was the bottleneck and CPU was very much underloaded, now, that bottlenecks are gone CPU got its load. And indeed i see that all queries now exected faster and sites are actually more responsive.
