MariaDB 10.0.10 incredibly lazy
Hi! I believe, it must be some improper setting in configuration. The problem is, that the INSERT performance (and overall performance too) is surprisingly poor, but there's more: 4-core CPU actually isn't used. While I'm trying to feed the table with new data, I can see, that just one core (sometimes) works on 5% load; as if MariaDB was refusing to work. It's not the problem of slow hardware. Besides: I use LOAD DATA INFILE to speed-up the data insertion.
I used my-huge.cnf as base configuration template:
The non-listed options have been left commented-out, with their default values. I would to add, that - as you can see - I use Aria engine as default, and tables (for future use) have been created with "TRANSACTIONAL=1". The main table is big (around 1 billion = 10^9 rows), but has only 3 columns. The machine has plenty of RAM (16 GB) - perhaps increasing some of the buffer sizes could help? But which ones? And is the problem actually buffer-related? To give you picture, how slowly it works: before switching from MySQL to MariaDB 10.0.10 I made a dump of the databases. Restoration (with standard mysqldump utility) of the main one - I mean the one with the large table - took more than 24 hours! Any helpful hints are appreciated.
[client]
#password = your_password
port = 3306
socket = /var/run/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
default-storage-engine = aria
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeoutThe non-listed options have been left commented-out, with their default values. I would to add, that - as you can see - I use Aria engine as default, and tables (for future use) have been created with "TRANSACTIONAL=1". The main table is big (around 1 billion = 10^9 rows), but has only 3 columns. The machine has plenty of RAM (16 GB) - perhaps increasing some of the buffer sizes could help? But which ones? And is the problem actually buffer-related? To give you picture, how slowly it works: before switching from MySQL to MariaDB 10.0.10 I made a dump of the databases. Restoration (with standard mysqldump utility) of the main one - I mean the one with the large table - took more than 24 hours! Any helpful hints are appreciated.
-
Do you notice any higher than normal "iowait" values when using the "sar -u" command? Thank you. 0 -
I have to confess: never used "sar" before. Tried it now, but it complained: "Cannot open /var/log/sa/sa23: No such file or directory". "touch /var/log/sa/sa23" also won't give that much, since now it responds: "Invalid system activity file: /var/log/sa/sa23". I was using mostly "htop" to examine CPU load. Well, as I wrote, it turned out, that 4-core CPU is barely used. Actually it's idle almost all the time. [COLOR="silver">- - - Updated - - - When used now (database isn't busy with anything), "sar -u 2" reports something like this: Linux 3.7.0 (intel1) 05/23/2014 _x86_64_ (4 CPU) 10:27:11 AM CPU %user %nice %system %iowait %steal %idle 10:27:13 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:15 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:17 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:19 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:21 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:23 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:27:25 AM all 0.00 0.00 0.12 0.00 0.00 99.88 0 -
One more: during conditional query (select count(8) from table where...), sar reported: Linux 3.7.0 (intel1) 05/23/2014 _x86_64_ (4 CPU) 03:07:58 PM CPU %user %nice %system %iowait %steal %idle 03:08:00 PM all 8.01 0.00 0.51 15.50 0.00 75.98 03:08:02 PM all 7.91 0.00 0.64 15.05 0.00 76.40 03:08:04 PM all 8.07 0.00 0.76 15.64 0.00 75.54 03:08:06 PM all 7.55 0.00 0.51 15.36 0.00 76.57 03:08:08 PM all 7.40 0.00 1.02 15.31 0.00 76.28 03:08:10 PM all 12.80 0.00 1.14 10.14 0.00 75.92 03:08:12 PM all 16.94 0.00 1.38 6.52 0.00 75.16 "htop" showed only one core (out of four) busy, and only at 33% load. How can I persuade MariaDB to use full hardware's power? 0
Please sign in to leave a comment.
Comments
3 comments