Matt, Seong Uck Lee: slave

레이블이 slave인 게시물을 표시합니다. 모든 게시물 표시

2014년 7월 7일 월요일

MariaDB 10.0.12 Multi Threaded Slave

MariaDB 10.0 MTS

MariaDB 10.0 and MySQL 5.6 both have multi-threaded replication feature, but there's some differences.
Multi-threaded replication of MySQL 5.6 works based on schema(database) and MariaDB 10.0 works based on domain id of GTID.
MariaDB 10.0's multi-threaded replication based on GTID domain id is related with multi-source replication feature.
But we can control connection(session)'s domain id using gtid_domain_id system variable manually for slave multi-thread replication on single-master environment (Not multi-source replication).
Roughly, it is looks like MySQL 5.6 is automatic and MariaDB 10.0 is semi-automatic multi-threaded replication.

Both feature have some benefits as you think. But sometimes we want to allocate another slave sql thread for special purpose query like ALTER or heavy dml statement.
So MariaDB 10.0's semi-automatic multi-threaded replication is not so bad. On the other hand, sometimes schema based multi-threaded replication is useless because of the sharding. Usually sharded mysql instance has only one small database.

And MariaDB 10.0 has another multi-threaded replication feature. In MariaDB knowledge base, MariaDB 10.0 has two types of multi-threaded replication.
First thing is Out-of-ordered multi-threaded replication. it is GTID's domain id based replication written prior paragraph.
And second thing is In-ordered mutli-threaded replication. This type of multi-threaded replication is based on group commit of binary log on master side.
(In this blog, I will focus on second type of MTS based on group commit.)

Before going to multi-threaded replication on slave side, let's check MariaDB 10.0 group commit out first.

Binary Log Group Commit

This is not the first time MySQL binary log group commit is implemented. But this feature is removed in 5.0 because there's some issue and reborn in MySQL 5.5 and 5.6.
Whatever, MariaDB 10.0 group commit affect not only binary log flushing but also innodb transaction commit.
So InnoDB transaction might get slow when you activate group commit parameters. But not so much if you control it properly.

MariaDB 10.0 supports two system parameters for binary log group commit.
binlog_commit_wait_usec
MariaDB will flush group of binary log events of multiple transactions requested in short time period. But there's no transactions committed at the exact same time. So MariaDB have to wait a little for waiting some transactions combined together
But MariaDB does not know the proper time to wait because it varies SLA and target performance. So MariaDB supports binlog_commit_wait_usec system variable so that DBA can control the time the oldest transaction can tolerate.
binlog_commit_wait_count
DBA can control how often group commit happen with not only time but also the count of transactions. It's transactions' count not binary log events' count.

We can observe how many transactions are committed together by decoding mysql binary log.

First create table "test" with 3 integer columns. And set binlog_commit_wait_usec and binlog_commit_wait_count system variables.
And open 4 connections and run the insert at the same time. You can run 4 insert statement using CSSHX like tool easily.

MariaDB [test]> set global binlog_commit_wait_usec=5000; /* 5 milli-seconds */
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set global binlog_commit_wait_count=50;
Query OK, 0 rows affected (0.00 sec)

Client1 > insert into test values (1,1,1);
Client2 > insert into test values (2,2,2);
Client3 > insert into test values (3,3,3);
Client4 > insert into test values (4,4,4);

Each transaction will take more time(maximum 5 milli seconds in this case), and some trasnaction (lucky one) will take much less than 5 milli seconds.
On MariaDB 10.0, each transaction has "COMMIT ID" and a few transactions might have the same "COMMIT ID" if they committed within same group commit.
You can decode mysql binary log using mysqlbinlog or SHOW BINLOG EVENTS command and you can this "COMMIT ID" in the "BEGIN GTID ..." line. "COMMIT ID" is printed with "cid=".
Below sample is the result of above 4 clients' test.

MariaDB [test]> show binlog events in 'binlog.000015';
+-----------+-----+--------------+..+------------------------------------------------+
| Log_name | Pos | Event_type |..| Info |
+-----------+-----+--------------+..+------------------------------------------------+
| binlog.00 | 4 | Format_desc |..| Server ver: 10.0.11-MariaDB-log, Binlog ver: 4 |
| binlog.00 | 248 | Gtid_list |..| [0-1-1213] |
| binlog.00 | 287 | Binlog_check |..| binary-log.000014 |
| binlog.00 | 327 | Binlog_check |..| binary-log.000015 |
| binlog.00 | 367 | Gtid |..| BEGIN GTID 0-1-1214 cid=1786 |
| binlog.00 | 407 | Query |..| use `test`; insert into test values (3,3,3) |
| binlog.00 | 501 | Xid |..| COMMIT /* xid=1786 */ |
| binlog.00 | 528 | Gtid |..| BEGIN GTID 0-1-1215 cid=1786 |
| binlog.00 | 568 | Query |..| use `test`; insert into test values (1,1,1) |
| binlog.00 | 662 | Xid |..| COMMIT /* xid=1788 */ |
| binlog.00 | 689 | Gtid |..| BEGIN GTID 0-1-1216 cid=1787 |
| binlog.00 | 729 | Query |..| use `test`; insert into test values (4,4,4) |
| binlog.00 | 823 | Xid |..| COMMIT /* xid=1787 */ |
| binlog.00 | 850 | Gtid |..| BEGIN GTID 0-1-1217 cid=1787 |
| binlog.00 | 890 | Query |..| use `test`; insert into test values (2,2,2) |
| binlog.00 | 984 | Xid |..| COMMIT /* xid=1789 */ |
+-----------+-----+--------------+..+------------------------------------------------+

Binary log file (binlog.000015) has 4 transactions. (Each transaction start with "BEGIN GTID ...")
And all transactions have different GTID but same cid value "1786". This means all 4 transactions are (group) committed together.

MariaDB 10.0 Multi-Threaded Replication

MariaDB 10.0's In-ordered multi-threaded replication works based on binary log group commit.
Coordinator thread of slave side read all transactions which have same cid from relay log and distribute across multiple sql threads.
The reason slave can replay it parallel is all transactions within same "COMMIT ID" does not conflicted(changing same record). No confliction is certified on master MariaDB. (I think this is really cool idea. So I like MariaDB ^^).

So more transactions(group committed) on master more parallelism we can expect.

Advantages for not grouped transactions

We can get some benefits if there's no group committed transactions on master.
MariaDB could run "commit" of each transaction parallel even though all transactions have different "COMMIT ID".
We can get a performance gain when "commit" itself is heavy like (sync_binlog=1 & log_slave_updates) or (innodb_flush_log_at_trx_commit이=1) configuration.

MTS Performance Test

Hardware spec

Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz * 4 with HyperThreading
32G memory
2 SAS + Raid controller (R-1) with 512MB cache

MySQL Configurations

innodb_buffer_pool_size = 20G
binlog_commit_wait_usec = 1000
binlog_commit_wait_count = 50 ## only for MTS env.
slave_parallel_threads = 10 ## only for MTS env.
slave_parallel_max_queued = 524288 ## 512KB

Sysbench Configurations

20 ~ 50 clients
table rows : 10 million
run only update_nokey update statement

Test Result

15k Update statement / second

25k Update statement / second

In this test I ran 25k update statements(per second), there's no replication delay on MTS replication.
I did not test how many update statements(per second) can be replicated without delay, because it's depend on query characteristics and performance.
And with MTS configuration, Slave server takes a lot of CPU cycles. (But it's fair enough, because Coordinator and SQL threads have to synchronize their status every time.)
And this is not a big deal on stand-by slave as we usually use this replication topology.

Group commit vs Master performance

There's one more thing we have to consider, transactions commit performance will be down when binlog_commit_wait_usec and binlog_commit_wait_count is set greater than 0.
Because each transactions have to wait until group conditions are met unless binlog_commit_wait_usec and binlog_commit_wait_count are 0. (Intermittent performance drop, I did not want to tune this because I think this performance drop is nothing to do with group commit performance.)

2014년 6월 22일 일요일

Memcached Replication (3)

This is the last article about Memcached Replication.If you missed previous two article, Read below article first for understanding.

http://seonguck.blogspot.kr/2014/06/memcached-replication-1.html

http://seonguck.blogspot.kr/2014/06/memcached-replication-2.html

Install

Download KMC source first from GitHub.
https://github.com/kakao/mysql_5.6.14_kmc

To build KMC, you need to install basic library needed for MySQL 5.6.
Especially there's a lot of system which lack of libaio-devel and ncurses-devel and cmake.
And you need to install libmemcached library and development package.

libmemcached-1.0.4-1.el5.remi.x86_64.rpm
libmemcached-devel-1.0.4-1.el5.remi.x86_64.rpm

Now you can build KMC, you should run cmake with a few options.

# cd mysql-5.6.14_kmc
# mkdir Release
# cd Release
# cmake .. \
'-DBUILD_CONFIG=mysql_release' \
'-DCMAKE_INSTALL_PREFIX=/usr/local/mysql' \
'-DWITH_INNODB_MEMCACHED=ON' \
'-DENABLED_LOCAL_INFILE=OFF' \
'-DHAVE_QUERY_CACHE=OFF' \
'-DOPTIMIZER_TRACE=OFF' \
'-DENABLE_DEBUG_SYNC=OFF' \
'-DENABLED_PROFILING=OFF' \
'-DWITH_ARCHIVE_STORAGE_ENGINE=OFF' \
'-DWITH_EMBEDDED_SERVER=OFF' \
'-DENABLE_DTRACE=OFF'

Of course you don't need to all options, but you should put "DWITH_INNODB_MEMCACHED" for Memcached plugin of MySQL.
After cmake, just run make && make install. After installing you can find built MySQL executables in the directory you specified as INSTALL_PREFIX.
Before starting MySQL server, you should run $MYSQL_HOME/scripts/mysql_install_db script to create default dictionary schema as normal MySQL server.

Creating Memcached related schema.

After starting MySQL server, you should create Memcached related schema to activate Memcached plugin.
Initialization script is located in your MySQL home directory. Just run it.
Actually this procedure also need to original MySQL Memcached plugin not only for KMC.

mysql> source $MYSQL_HOME/share/innodb_memcached_config.sql
mysql> use innodb_memcache
mysql> show tables

Check below three tables have created in innodb_memcache database.

cache_policies
config_options
containers

Now we need to initialize KMC basic schema.

mysql> USE innodb_memcache;
mysql> INSERT INTO `containers` VALUES ('default','kmc','kmc_template','k','v','f','c','e','PRIMARY');

mysql> CREATE DATABASE kmc;
mysql> USE kmc;

mysql> DROP TABLE IF EXISTS `kmc_template`;
mysql> CREATE TABLE `kmc_template` (
`k` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`v` mediumblob,
`f` int(11) NOT NULL DEFAULT '0',
`c` bigint(20) unsigned NOT NULL DEFAULT '0',
`e` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 STATS_PERSISTENT=0;

## Don't need this default row anymore, But I am not sure.
mysql> INSERT INTO `kmc_template` VALUES ('1','DO-NOT-REMOVE',0,0,0);

On this procedure, table name must start with "kmc_" prefix.

Configurations

Lastly change my.cnf configuration file for KMC. Below options are not only for KMC but also memcached performance.
We don't use KMC as InnoDB or MyISAM engine together. So I changed InnoDB options minimally.
This is not so strict options(except kmc_connect_string and binlog-format), so you can change it for your system standardizations.

## InnoDB ------------------------
...
innodb_data_file_path = ibdata1:64M:autoextend
...
innodb_log_file_size = 32M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

## Memcached --------------------
daemon_memcached_option = '-m 20480 -p 11211 -c 80000 -t 8 -s /tmp/memcached.sock'
innodb_api_enable_binlog = 1
innodb_api_trx_level=0 ## READ-UNCOMMITTED
innodb_api_bk_commit_interval=1
daemon_memcached_r_batch_size=1
daemon_memcached_w_batch_size=1
innodb_api_enable_mdl=OFF

## Replication:Binary log -----------
server-id = 1
## Replication query is always idempotent
slave_exec_mode = IDEMPOTENT
## Memcached connection string :: with socket file
kmc_connect_string = '--SOCKET="/tmp/memcached.sock" --BINARY-PROTOCOL --NOREPLY --TCP-NODELAY --TCP-KEEPALIVE'
binlog-checksum = NONE
sync_binlog = 0
master_info_repository = FILE
relay_log_info_repository = FILE
sync_master_info = 0
sync_relay_log = 0
sync_relay_log_info = 0
slave_checkpoint_group = 100000
slave_checkpoint_period = 1000
max_binlog_size = 100M
expire_logs_days=1
expire_max_log_files = 15
binlog-format = ROW

daemon_memcached_option is Memcached plugin startup options and kmc_connect_string option is for SQL thread of slave MySQL server.
Now starting MySQL server and check the basic function on.
And you can check replication setup and the way to check replication status out is same as original MySQL server.

Testing Memcached and Replication

MySQL Memcached plugin support both binary and text mode protocol, so we can simply test memcached operation with telnet program.

[root@matt001 ~]# telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.

set matt 0 0 9
Seong Uck
STORED

get matt
VALUE matt 0 9
Seong Uck
END

delete matt
DELETED

get matt
END

quit
Connection closed by foreign host.

If you want to check whether socket file(/tmp/memcached.sock) is working correctly, you can use netcat utility. In this case you can't use telnet.

[root@matt001 ~]# nc -U /tmp/memcached.sock
set matt 0 0 9
SEONG UCK
STORED

get matt
VALUE matt 0 9
SEONG UCK
END

quit
[root@matt001 ~]#

If you want to check the replicated data, run GET operation on slave memcached after run SET on master memcached.
Of course you can inspect the binary log file through mysqlbinlog utility.

Memcached replication feature(Added feature) does't have unit test yet. So you should be carefull.

2014년 6월 21일 토요일

Memcached Replication (2)

Modifications

As written in the first article about Memcached Replication, need to change some of MySQL 5.6 source code.
Below diagram will explain how MySQL Memcached plugin works and what I modified.

(Blue line is original behavior of MySQL 5.6 Memcached plugin's Caching and Innodb-only mode, Bold brown line means my modifications)

And the number of the diagram means ...

InnoDB API will transfer the input data to InnoDB engine(Table) as configured with innodb_api_bk_commit_interval, daemon_memcached_r_batch_size, daemon_memcached_w_batch_size system variables. And after that InnoDB API will write changes to binary log. But this is not happened on Cache-only mode. I modified MySQL code as memcached changes is also written to binary log but not apply that changes to InnoDB engine on Cache-only mode Memcached plugin. On original MySQL 5.6 Memcached plugin, dictionary table(innodb_memcache DB) and container table needs for Caching and Innodb-only cache mode not for Cache-only mode. But after modification dictionary table and container table is gotten to need for binary log writting. Of course we will not store memcached data to container table, but need it's template for binary log writting.
On slave side, SQL thread have to apply relay log gotten by IO thread to MySQL Memcached plugin NOT InnoDB tables. But on original MySQL replication, SQL thread directly access InnoDB API not Memcached. I need a way to access to Memcached plugin. I implemented it as SQL thread bring up another memcached client (using libmemcached client library). I can use Non-blocking mode mode with libmemcached client library for fast relay log applying. Actually in original MySQL 5.6, libmemcached.so shared library also exist on your $MYSQL_HOME/lib/plugin, So I renamed it as "libmemcachedserver.so" so that install libmemcached RPM separately on the same system.
Repication SQL thread act as Memcached client have to run as fast as possible, so modified SQL thread's memcached client connect to Memcached socket file rather than 11211 TCP port. And worked as Non-blocking mode for fast replication. Actually SQL thread will apply relay log event as sync-mode on every 100th request so that slave can check replication apply error.

Another modifications

Originally, Memcached plugin never open both of TCP port and Unix domain socket at the same time, But I need both channel.
Copy Memcached plugin's status metric to MySQL server's status variables so that we can monitor memcached status with MySQL monitoring tool.InnoDB master thread will copy memcached status metrics to MySQL status variables every second.

mysql> show global status like '%kmc%';
+----------------------------------+--------------+
| Variable_name | Value |
+----------------------------------+--------------+
| Innodb_kmc_connection_structures | 2004 |
| Innodb_kmc_curr_connections | 2002 |
| Innodb_kmc_curr_items | 70638969 |
| Innodb_kmc_pointer_size | 64 |
| Innodb_kmc_threads | 8 |
| Innodb_kmc_total_connections | 5090 |
| Innodb_kmc_total_items | 778120599 |
| Innodb_kmc_bytes | 18648687816 |
| Innodb_kmc_bytes_read | 460650586044 |
| Innodb_kmc_bytes_written | 497924594841 |
| Innodb_kmc_cmd_get | 7781206118 |
| Innodb_kmc_cmd_set | 778120599 |
| Innodb_kmc_evictions | 498675673 |
| Innodb_kmc_get_hits | 1043938514 |
| Innodb_kmc_get_misses | 6737267604 |
| Innodb_kmc_limit_maxbytes | 21474836480 |
+----------------------------------+--------------+

Fix related memory leak of MySQL 5.6.14 and expiration time bug of Memcached plugin.
Remove every file sync call for fast processing (if possible).
Add expire_max_log_files system variables so that we can control total binary logs' size by file count.(This features only use binary log file's suffix number, so if you use frequent "PURGE LOGS" or other commands which switch binary log file, expire_max_log_files system variables will not work as expected)
Add Binlog_purge_failed system status variable for monitoring binary log purge

0 : Okay
1 : Set when MySQL server attempt to remove ACTIVE state binary log.
2 : SET when MySQL server attempt to remove USE state binary log.

Add kmc_connect_string system variable so that you can change how SQL thread connect to it's local memcached plugin. You can change it whenever you want because it is dynamic variable. But You have to "STOP/START SLAVE" for applying change.

Usable Memcached Operations

Unfortunately on KMC(Kakao MemCached), Some of memcached operations are not usable. It's because of Memcached replication characteristic (And have not tested it).

Usable operations

GET
SET
ADD
DELETE
REPLACE

Not implemented or Not tested

INCREMENT
DECREMENT
CAS

Performance

GET operations' performance is same as Original Memcached. But SET/ADD/DELETE operations need to written to disk (Binary log file). And there's a lot of complex processing are involed even though it's not sync mode. So SET/ADD/DELETE performance is dramatically lower than original memcached server.
But general purpose of Memcached server, SET/ADD/DELETE operations is not so many. Once data item is cached on Memcached, then GET operation is performed all the time.
If not (If there's a lot of SET/ADD/DELETE), that memcached server has no effect becuase cache ratio is low. And in this case Memcached server only add overhead (I think).

I also modified SQL thread work as parallel based on row, But MySQL 5.6 multi threaded replication make a lot of sync overhead(CPU overhead) among slave threads and coordinate thread.

MySQL Memcached plugin can process more SET/ADD/DELETE operations than normal MySQL's SQL statements, but you have to consider replication.
SQL thread of slave side is working as single thread, and this might be bottleneck.
On Intel X86 commodity server, I think 10k SET/ADD/DELETE operations are limit. it's the limit of replication (No replication delay status)
So I added expire_max_log_files system variable and Binlog_purge_failed system status variable. If you have enough memory, allocate some of memory for binary log directory using RamFS or RamDisk for SET/DELETE/ADD operation's performance. Added system variable and status variable will help RamFs usage management.

Some limitations

Container table name must start with "kmc_".
Can't use CAS, INCR and DECR operations.
KMC(Kakao MemCached) use two upper bytes of Memcached flags field for originated server_id. So this is not compatible with your memcached client.
KMC will convert your DELETE operation to SET operation with 1 second expire (because we can't use flags field on DELETE operation).

Download

https://github.com/kakao/mysql_5.6.14_kmc

Memcached Replication (1)

Purpose

A lot of applications have data which have to be stored persistently. And theses data is stored on RDBMS or NoSQL solutions.
Sometimes these applications need a lot of data search operations, but current RDBMS or NoSQL solutions can't serve this requirement.
RDMBS or NoSQL solutions do really complicated internal processing for applications' request, and they have to store data to disk slowest component of computer.
So we use memory cache solution like Redis or Memcached. But they also have weak points especially Memcached doesn't have repilcation features.
Redis, this cache solution also have some weak points, this is not why I am focusing Memcached in this article.
We use a lot of Memcached on our service already, and I have to make Memcached can replicate data to remote Memcached(like slave of mysql) without migrating to Redis.

Some people doesn't feel any needs for Memcached replication.
But replication feature is necessary for multi-idc synchronization. And multi-idc synchronization is need for disaster recovery or IDC location aware services.
Redis has replication features, but Memcached server has not. Redis can serve complex data types but Memcached not. On the other hand, Memcached has it's own advantages.
(I don't want to metion about "Why Memcached is better than Redis, and Why Redis better than Memcached", What I want to say in this article is there's still a lot of people use Memcached server).
Some company made mysql server's binary log parser and relay it to Memcached server of multi IDC. Because usually cached data items are originated from database server.
And this way, solve a lot of complexity of application.

Also our company(Kakao corp) has same needs for DR or location aware services.
But above binary log parsing method need a few change of sql statement of applications. It's not fully transparent from application.

MySQL 5.6 Memcached plugin

MySQL 5.6 is released during I am looking for memcached replication method, First time I saw the MySQL Memcached plugin it seems that it doesn't have no usability.
But after a few days, Suddenly I thought we use MySQL Server's Memcached plugin as standalone memcached server and easily have memcached replication. Because Memcached data change will be written to mysql binary log through MySQL(InnoDB API). MySQL Memcached plugin has below three cache policy.

Innodb-only
Caching
Cache-only

First of all, Memcached plugin act as whole memory operation. So we could not use Inoodb-only and Caching policy because memcached data will be stored in innodb finally on this two policy. We have to use Cache-only policy. But unfortunately Memcached data is never written to binary log because memcached data is not stored in InnoDB on Cache-only policy.
And this is only the story for binary log writting. But on slave side SQL thread will relay binary log contents to InnoDB only, Not memcached plugin.

If I changed MySQL as writting data change to binary log on master and apply change to memcached plugin on slave, Memcached replication can be possible.
This is not so easy task, but it would be best way to implement it. We don't have to implement whole replication features on Memcached and MySQL replication features is really stable.