Sometimes some of our service, there's a lot of DELETE but some case free space of removed rows can not be reused (Especially AutoIncrement primary key).
We have looking for online defragmentation feature for InnoDB long time.
MySQL 5.6 Facebook patch have this feature (I found this a few weeks ago), but not in MariaDB.
So I ported Facebook InnoDB defragmentation feature to MariaDB 10.0.
You can see more detailed explanation and patched source code from below facebook github and kakao tech blog.
https://www.github.com/facebook/mysql-5.6
http://kakao-dbe.blogspot.kr/2014/07/defragment-innodb-table-on-mariadb-100.html
Basic test (Defragmentation efficiency)
After insert 1 million rows, removed some rows(50% from tb_t50, 30% from tb_t30, 20% from tb_t10) from target tables.
And compare three table's data pages count after run "ALTER TABLE tb_txx DEFRAGMENT" command.
CREATE TABLE `tb_t50` (
`fdpk` int(11) NOT NULL AUTO_INCREMENT,
`fd1` char(100) NOT NULL,
`fd2` char(100) NOT NULL,
PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `tb_t30` (
`fdpk` int(11) NOT NULL AUTO_INCREMENT,
`fd1` char(100) NOT NULL,
`fd2` char(100) NOT NULL,
PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `tb_t10` -- // ==> Actually this table name must be tb_t20 )
`fdpk` int(11) NOT NULL AUTO_INCREMENT,
`fd1` char(100) NOT NULL,
`fd2` char(100) NOT NULL,
PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Test scenario
- Insert 1048576 rows to tb_t50, tb_t30, tb_t10(fdpk column value is auto incremented from 1)
- Restart MariaDB server for clearing InnoDB buffer pool
- Check loaded page count from InnoDB buffer pool after running below query from tb_t50
select count(*)
from tb_t50 use index(primary)
where fdpk between 1 and 1000000
order by fdpk;
select count(*)
from information_schema.innodb_buffer_page
where table_name='`test`.`tb_t50`';
- Delete 50% of rows from tb_t50, 30% of rows from tb_t30, 20% of rows from tb_t10
delete from tb_t50 where fdpk%2=0;
delete from tb_t30 where fdpk%3=0;
delete from tb_t10 where fdpk%5=0;
- Restart MariaDB server
- Run select query from tb_t50 to load disk data page to InnoDB buffer pool
- Checking loaded page count from InnoDB buffer pool for tb_t50
- Doing 6) ~ 7) for tb_t30 and tb_t10
Test result
According to the first graph, pages will be merged automatically when removing 50% of rows from pages.
But 1/3 and 1/5 case, pages are not merged automatically and stay intactly.
Average row count per page is decreased in tb_t20 and tb_t30 table (According to second graph).
But after defragmentation, average row count per page is getting same among three tables (4th graph).
And loaded page of InnoDB buffer pool is getting smaller than before(before deletion) (3rd graph).
Defragmentation Performance and Processing time
Below graph shows disk read iops according to innodb_defragment_frequency system variable.
- innodb_defragment_frequency = 100
- innodb_defragment_frequency = 1000
- innodb_defragment_frequency = 10
- innodb_defragment_frequency = 50
innodb_defragment_frequency is used to determine how many times merge method will be call per each second.
So greater value set to this variable more pages would be merged per second. So total defragmentation time is getting low.
But InnoDB defragment thread will read more pages if you set higher value to innodb_defragment_frequency system variable.
If you have fast ssd or whole data could be loaded into InnoDB buffer pool you should increase this variable.
Unless you should decrease this value.
Warning
You can not use this InnoDB defragmentation feature to shrink InnoDB tablespace's disk size.
This feature merge pages and free some page to reserved space of table, not to operating system.
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.)
I ran the test both of group-commit activated and deactivated MariaDB. As you can see, there's some performance regression.
But in this test, this transaction waiting time affected throughput greatly because I made only 50 client threads. If there's 25k update statement (/second) with 5000 client thread, performance difference would be close.
And If we can choose proper waiting time and group-commit count(binlog_commit_wait_usec and binlog_commit_wait_count) this performance gap would not be a problem.
With MTS slave, we can see multiple sql thread replay binary event parallel.
MariaDB [(none)]> show processlist;
+-----+-------------+..+---------+-------------------..-+-----------------------------------------...---+
| Id | User |..| Command | State .. | Info ... |
+-----+-------------+..+---------+-------------------..-+-----------------------------------------...---+
| 3 | system user |..| Connect | Waiting for prior .. | COMMIT ... |
| 4 | system user |..| Connect | init .. | COMMIT ... |
| 5 | system user |..| Connect | init .. | UPDATE sbtest set c='491483753-416518378...74 |
| 6 | system user |..| Connect | init .. | UPDATE sbtest set c='909812426-309814605...27 |
| 7 | system user |..| Connect | Waiting for prior .. | COMMIT ... |
| 8 | system user |..| Connect | Waiting for prior .. | COMMIT ... |
| 9 | system user |..| Connect | init .. | UPDATE sbtest set c='112858395-197504108...38 |
| 10 | system user |..| Connect | freeing items .. | NULL ... |
| 11 | system user |..| Connect | init .. | UPDATE sbtest set c='55183839-759991643-...4- |
| 12 | system user |..| Connect | Waiting for prior .. | COMMIT ... |
| 14 | root |..| Sleep | .. | NULL ... |
| 18 | system user |..| Connect | Waiting for master.. | NULL ... |
| 19 | system user |..| Connect | Slave has read all.. | NULL ... |
| 155 | root |..| Query | init .. | show processlist ... |
+-----+-------------+..+---------+-------------------..-+-----------------------------------------...---+
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.
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
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
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.
Sometimes peoples said "Overhead during dropping InnoDB table".
Dropping table need to scan buffer pool (Especially they need to scan twice in older version).
But I think a real overhead comes from file system.
So you might be already use XFS or EXT4 to avoid this.
Simply I tested 50GB file remove (unlink system call on Linux).
< Linux iostat during file remove (unlink on ext3 filesystem) >
My server has only 6 SAS disk (4==> RAID 1+0 used for mysql data directory, and remained 2 disk used as mysql log) and 1GB Raid controller cache.
In my test, removing 50GB file took 25 seconds. During this time, there's huge disk read iops as you can see above chart. And the whole time during file unlink, disk utilization is 100%.
As you can imagine, If you run drop table which has huge file size MySQL server can't handle use requests until file remove completely. Actually this is not so weird things. it's because of EXT3 file system architecture.
On the other hand, EXT4 has several features to overcome this kind of fragmentations.
- Multiblock allocator
When ext3 appends to a file, it calls the block allocator, once for each block. Consequently, if there are multiple concurrent writers, files can easily become fragmented on disk. However, ext4 uses delayed allocation which allows it to buffer data and allocate groups of blocks. Consequently the multiblock allocator can make better choices about allocating files contiguously on disk. The multiblock allocator can also be used when files are opened in O_DIRECT mode. This feature does not affect the disk format.
- Delayed allocation
ext4 uses a performance technique called allocate-on-flush also known as delayed allocation. That is, ext4 delays block allocation until it writes data to disk. (In contrast, some file systems allocate blocks before writing data to disk.) Delayed allocation improves performance and reduces fragmentation by using the actual file size to improve block allocation.
I quoted this from wiki about EXT4 (http://en.wikipedia.org/wiki/Ext4)
XFS also has this kind of optimization. So XFS and EXT4 have fewer fragmented blocks than EXT3. Sometimes MySQL server performance is dropped when removing binary log file internally (Default size of binary log file is 1GB, So we changed it to 100MB).
If you can't change file system to EXT4 or XFS, then you can use linux hard link + truncate command.
< Linux iostat during file remove (truncate file 1GB each 2seconds on ext3 filesystem) >
Tested program does just truncate last 1GB amount of contents of 50GB file and sleep 2 seconds. After that truncate last 1GB amount of contents of remained 49GB. so on...
Disk utilization is really stable. And this time user requests are never blocked.
InnoDB will just call unlink system call when you drop table. And unlink system call never drop when the target file has another hard link(man unlink). So you can make another hard link for huge ibd file before and run drop table, then InnoDB drop only just one link.
After that, you can truncate the hard linked file little by little.
shell> link /mysql_data/db1/huge_table.ibd /mysql_data/huge_table.ibd.dropped
mysql > drop table huge_table;
shell> ## doing ftruncate "huge_table.ibd.dropped" little by little
I'm doing this can be possible as MySQL builtin features.
Still I'm doing read and modify mysql code (Not done yet).
https://github.com/SunguckLee/MariaDB/commit/87f05c2619c714007d40b9c07e151cb51ef6eca6
* Why
Recently MariaDB 10.0 include LIMIT ROWS EXAMINED features.
I think this features will prevent our services from abnormal behavior(A sudden change of QEP or Operation miss, ... something else..) of MariaDB.
I got new idea from LIMIT ROWS EXAMINED features.
We often use "SELECT COUNT(*) .." query for counting rows matched condition.
We can limit row count with LIMIT clause when we SELECT row itself, But we can't during counting rows.
So, I added small features on MariaDB 10.0.10.
* What : LIMIT ROWS MATCHED
This new features works before "ORDER BY" and "GROUP BY" and some aggregate function like "COUNT()" and "SUM()".
So we can count rows matched WHERE condition with LIMIT ROWS MATCHED syntax.
Below example is simple usage of LIMIT ROWS MATCHED syntax. (in this example LIMIT ROWS MATCHED works same as just LIMIT clause)
MariaDB [test]> CREATE TABLE test (fd_pk INT NOT NULL auto_increment, fd1 INT, fd2 INT, PRIMARY KEY(fd_pk));
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> INSERT INTO test VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [test]> SELECT * FROM test;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+-------+------+------+
5 rows IN SET (0.00 sec)
MariaDB [test]> SELECT * FROM test LIMIT ROWS MATCHED 2;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+-------+------+------+
2 rows IN SET (0.00 sec)
But if you use COUNT(*) aggregate function, the result looks different.
MariaDB [test]> SELECT COUNT(*) FROM test LIMIT 3;
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
1 row IN SET (0.01 sec)
MariaDB [test]> SELECT COUNT(*) FROM test LIMIT ROWS MATCHED 3;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row IN SET (0.00 sec)
And LIMIT ROWS MATCHED feature is processed before LIMIT. So sometimes, it looks like LIMIT is not working when you use both of LIMIT n and ROWS MATCHED n.
MariaDB [test]> SELECT * FROM test LIMIT 3 ROWS MATCHED 2;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+-------+------+------+
2 rows IN SET (0.00 sec)
We can use LIMITED ROWS MATCHED syntax nested fashion.
MariaDB [test]> SELECT * FROM (
SELECT * FROM test WHERE fd2 BETWEEN 2 AND 4 LIMIT ROWS MATCHED 2) x
LIMIT ROWS MATCHED 1;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 2 | 2 | 2 |
+-------+------+------+
1 row IN SET (0.01 sec)
MariaDB [test]> SELECT * FROM (SELECT * FROM test WHERE fd2 BETWEEN 2 AND 4 LIMIT ROWS MATCHED 2) x LIMIT ROWS MATCHED 2;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+-------+------+------+
2 rows IN SET (0.00 sec)
MariaDB [test]&gt; SELECT * FROM (
SELECT * FROM test WHERE fd2 BETWEEN 2 AND 4 LIMIT ROWS MATCHED 2) x
LIMIT ROWS MATCHED 3;
+-------+------+------+
| fd_pk | fd1 | fd2 |
+-------+------+------+
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+-------+------+------+
2 rows IN SET (0.01 sec)
In above example, derived table(subquery of FROM clause) will cut before third matched row, so outer LIMIT ROWS MATCHED 2 and LIMIT ROWS MATCHED 3 don't change the final result.
But first query of above example, outer LIMIT ROWS MATCHED is 1 (less than inner LIMIT ROWS MATCHED 2) so the result set has just 1 row.
And LIMIT ROWS MATCHED syntax could be used in join also.
MariaDB [test]> SELECT * FROM test t1, test t2, test t3 WHERE t1.fd_pk=t2.fd_pk AND t2.fd_pk=t3.fd_pk LIMIT ROWS MATCHED 3;
+-------+------+------+-------+------+------+-------+------+------+
| fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 |
+-------+------+------+-------+------+------+-------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
+-------+------+------+-------+------+------+-------+------+------+
3 rows IN SET (0.02 sec)
MariaDB [test]> SELECT * FROM test t1, test t2, test t3 WHERE t1.fd_pk=t2.fd_pk AND t2.fd_pk=t3.fd_pk AND t3.fd1<>1 LIMIT ROWS MATCHED 3;
+-------+------+------+-------+------+------+-------+------+------+
| fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 |
+-------+------+------+-------+------+------+-------+------+------+
| 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
+-------+------+------+-------+------+------+-------+------+------+
3 rows IN SET (0.01 sec)
Result set are little weird or different from what you expected.
-- // duplicate test table's row for ORDER BY .. LIMIT ROWS MATCHED n query
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 40 rows affected (0.01 sec)
Records: 40 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 80 rows affected (0.03 sec)
Records: 80 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 160 rows affected (0.05 sec)
Records: 160 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 320 rows affected (0.12 sec)
Records: 320 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 640 rows affected (0.20 sec)
Records: 640 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;Query OK, 1280 rows affected (0.38 sec)
Records: 1280 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;Query OK, 2560 rows affected (0.74 sec)
Records: 2560 Duplicates: 0 Warnings: 0
MariaDB [test]> INSERT INTO test SELECT NULL, fd1, fd2 FROM test;
Query OK, 5120 rows affected (1.47 sec)
Records: 5120 Duplicates: 0 Warnings: 0
MariaDB [test]> SELECT *
FROM test t1, test t2, test t3
WHERE t1.fd_pk=t2.fd_pk AND t2.fd_pk=t3.fd_pk
ORDER BY t3.fd2 DESC, t2.fd2 DESC, t1.fd2 DESC
LIMIT 3;
+-------+------+------+-------+------+------+-------+------+------+
| fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 |
+-------+------+------+-------+------+------+-------+------+------+
| 578 | 5 | 5 | 578 | 5 | 5 | 578 | 5 | 5 |
| 2792 | 5 | 5 | 2792 | 5 | 5 | 2792 | 5 | 5 |
| 8694 | 5 | 5 | 8694 | 5 | 5 | 8694 | 5 | 5 |
+-------+------+------+-------+------+------+-------+------+------+
3 rows IN SET (2.27 sec)
MariaDB [test]> SELECT *
FROM test t1, test t2, test t3
WHERE t1.fd_pk=t2.fd_pk AND t2.fd_pk=t3.fd_pk
ORDER BY t3.fd2 DESC, t2.fd2 DESC, t1.fd2 DESC
LIMIT ROWS MATCHED 3;
+-------+------+------+-------+------+------+-------+------+------+
| fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 |
+-------+------+------+-------+------+------+-------+------+------+
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+-------+------+------+-------+------+------+-------+------+------+
3 rows IN SET (0.01 sec)
Both query of above example have "ORDER BY t3.fd2 DESC, t2.fd2 DESC, t1.fd2" clause and same WHERE conditions.
First query use "LIMIT ROWS MATCHED 3" on the other hand second query use just "LIMIT 3".
"LIMIT 3" is processed after sort but "LIMIT ROWS MATCHED 3" is processed before sort operation. So first query need to sort all joined rows, but second query only sort 3 rows.
(Of course, the result set of second query is not what you want, so be cautious)
And LIMIT ROWS MATCHED syntax can be used on outer join query.
MariaDB [test]> SELECT *
FROM test t1
INNER JOIN test t2 ON t2.fd_pk=t1.fd_pk
LEFT JOIN test t3 ON t3.fd_pk=t1.fd_pk-1
ORDER BY t3.fd2, t2.fd2, t1.fd2
LIMIT ROWS MATCHED 3;
+-------+------+------+-------+------+------+-------+------+------+
| fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 | fd_pk | fd1 | fd2 |
+-------+------+------+-------+------+------+-------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 | NULL | NULL | NULL |
| 2 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 1 |
| 3 | 3 | 3 | 3 | 3 | 3 | 2 | 2 | 2 |
+-------+------+------+-------+------+------+-------+------+------+
3 rows IN SET (0.02 sec)
* Performance (Appendded)
I ran a simple row counting test.
LIMIT ROWS MATCHED faster about 20~30% than SELECT COUNT(*) .. FROM (SELECT .. LIMIT).
-- // -----------------------------------------------------------------
-- // Counting 10000 rows
-- // -----------------------------------------------------------------
MariaDB [test]> SELECT COUNT(*) FROM test limit rows matched 10000;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row IN SET (0.05 sec)
MariaDB [test]> SELECT COUNT(*) FROM (SELECT 1 FROM test limit 10000) x;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row IN SET (0.06 sec)
-- // -----------------------------------------------------------------
-- // Counting 30000 rows
-- // -----------------------------------------------------------------
MariaDB [test]> SELECT COUNT(*) FROM test limit rows matched 30000;
+----------+
| COUNT(*) |
+----------+
| 30000 |
+----------+
1 row IN SET (0.14 sec)
MariaDB [test]> SELECT COUNT(*) FROM (SELECT 1 FROM test limit 30000) x;
+----------+
| COUNT(*) |
+----------+
| 30000 |
+----------+
1 row IN SET (0.19 sec)
-- // -----------------------------------------------------------------
-- // Counting 80000 rows
-- // -----------------------------------------------------------------
MariaDB [test]> SELECT COUNT(*) FROM test limit rows matched 80000;
+----------+
| COUNT(*) |
+----------+
| 80000 |
+----------+
1 row IN SET (0.36 sec)
MariaDB [test]> SELECT COUNT(*) FROM (SELECT 1 FROM test limit 80000) x;
+----------+
| COUNT(*) |
+----------+
| 80000 |
+----------+
1 row IN SET (0.47 sec)
* Finally
written on top of this blog, the priamry purpose of LIMIT ROW MATCHED syntax is limiting rows during count.
In message service or some other services, usally we need to count rows matched where condition.
But almost case we don't need to count all matched rows. and counting all matched rows need more cpu cycle and disk reads.
Yes, we can limit counting rows with sub-query on FROM clause, but this solution need memory or disk internal temporary table.
Even I saw some people use "SELECT 1 FROM ... LIMIT n" and counting rows on client side. This solution also need additional network bandwidth.
But we can do limited counting job with minimal computing power. Because LIMIT ROWS MATCHED features never use additional network bandwidth and internal temporary table.
* Limitations
1. "LIMIT ROWS MATCHED" syntax can not be used with "LIMIT ROWS EXAMINED" together.
2. "LIMIT ROWS MATCHED n" will push "LIMIT n" automatically. So MariaDB can not employ some semi-join optimizations when query contains "LIMIT ROWS MATCHED n" (This limitation is applied "LIMIT n" query too).
* Download
https://github.com/SunguckLee/MariaDB
** This feature may have some bug, so please be cautious.
