Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.14
-
None
-
CentOS 7
Description
We have an old MySQL 5.0 database running on CentOS 5 server which we want to move to CentOS 7 and MariaDB 10.1.
We have successfully installed and configured the new system to currently run as a slave of the old server (we will make it master once we are sure everything is working smoothly) but there were some problems.
We have a table like this one with ~70,000,000 records:
CREATE TABLE `calculated_data` ( |
`object_id` int(10) unsigned NOT NULL, |
`d` date NOT NULL, |
`hour` tinyint(4) NOT NULL default '-1', |
`kms` double unsigned NOT NULL, |
PRIMARY KEY (`object_id`, `d`, `hour`), |
CONSTRAINT `my_table__object_id__f_ix` FOREIGN KEY (`object_id`) REFERENCES `objects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
We purge the old data from the table daily using this query:
DELETE FROM `calculated_data` WHERE `object_id` > 0 AND `d` < DATE_SUB(NOW(), INTERVAL 365 DAY); |
We are not interested in `object_id` but we include it in the query so that the engine uses the primary key.
Our old server executes the query in 81 seconds which is perfectly acceptable, the new shiny one does it in 5314 seconds. What can be the problem?
There was another issue. New data is being inserted into the old server at 100 records per second and it works just fine, but the new server was replicating at ~20 records a second maximum. We tried to tune the flush methods and slave parallelism settings to no success (see the commented out settings), we somewhat solved the issue by changing innodb_flush_log_at_trx_commit = 1 to innodb_flush_log_at_trx_commit = 2, but I'm not sure it was the right thing to do.
Settings:
[mysqld]
|
|
|
datadir=/var/lib/mysql
|
socket=/var/lib/mysql/mysql.sock
|
log-error=/var/log/mariadb/mariadb.log
|
pid-file=/var/run/mariadb/mariadb.pid
|
|
|
bind-address = 127.0.0.1
|
|
|
max_allowed_packet = 16M
|
|
|
#This is MyISAM-only
|
key_buffer_size = 32M
|
read_buffer_size = 1M
|
|
|
sort_buffer_size = 4M
|
join_buffer_size = 2M
|
thread_cache_size = 12
|
thread_pool_size = 16
|
query_cache_limit = 0
|
query_cache_size = 0
|
query_cache_type = 0
|
table_open_cache = 5000
|
open_files_limit = 10000
|
max_connections = 100
|
|
|
character_set_server = utf8
|
collation_server = utf8_general_ci
|
|
|
innodb_buffer_pool_size = 5G
|
innodb_buffer_pool_instances = 5
|
innodb_log_buffer_size = 32M
|
innodb_log_file_size = 256M
|
innodb_log_files_in_group = 2
|
innodb_thread_concurrency = 10
|
innodb_file_per_table = 1
|
innodb_flush_method = O_DIRECT
|
#this is not safe (next line) but without it the slave syncs max at 20 inserts per second, very slow
|
innodb_flush_log_at_trx_commit = 2
|
#innodb_flush_log_at_trx_commit = 1
|
|
|
sync_binlog = 1
|
|
|
max_relay_log_size = 256M
|
relay_log_purge = 1
|
relay_log = db-relay-bin
|
relay_log_index = db-relay-bin.index
|
#slave_parallel_threads = 6
|
#slave_domain_parallel_threads = 4
|
#slave_parallel_max_queued = 1M
|
log_slow_slave_statements = 1
|
replicate_do_db = db
|
server_id = 2
|
|
|
slow_query_log = 1
|
slow_query_log_file = /var/log/mariadb/mariadbs.log
|