MySQL Bugs: #91153: Mysql memcached plugin stops responding on large no. of concurrent connections

Bug #91153	Mysql memcached plugin stops responding on large no. of concurrent connections
Submitted:	6 Jun 2018 6:52	Modified:	13 Jun 2018 13:05
Reporter:	Shubhra Prakash Nandi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Memcached	Severity:	S5 (Performance)
Version:	5.7.22	OS:	Debian (9)
Assigned to:		CPU Architecture:	x86 (amd64)

Description:
Hello,

I was testing Mysql memcached plugin and found that if a lot of concurrent requests are sent to the plugin with mix of get, set, delete requests the plugin stops responding and all requests get stuck.

How to repeat:
Install the plugin. Follow the steps given below. Run a script which sends 1000 set/delete requests to memcached port/socket and run 6-7 instances of the script in parallel so that set command tries to access the same record.

-- Run the below SQL queries

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE cache_items (item_key VARCHAR(512) NOT NULL, item_data MEDIUMBLOB, flags INT, cas BIGINT UNSIGNED, expiry INT,PRIMARY KEY(item_key));

USE innodb_memcache;

DELETE FROM containers;

INSERT INTO containers (name,db_schema,db_table,key_columns,value_columns,flags,cas_column,expire_time_column,unique_idx_name_on_key) VALUES ('default','test_db','cache_items','item_key','item_data','flags','cas','expiry','PRIMARY');

-- Add update below options to my.cnf

[client]
# Client character set configuration
default_character_set = utf8mb4

[mysqld]
# Setting default storage engine
default_storage_engine = InnoDB

innodb_file_per_table = ON
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_page_size = 16K
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_buffer_pool_instances = 1
innodb_flush_method = O_DIRECT
innodb_doublewrite = OFF
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 2

max_allowed_packet	= 32M
max_connections = 2048

large_pages = ON

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

daemon_memcached_w_batch_size = 100
daemon_memcached_r_batch_size = 100
daemon_memcached_option = "-v -m256 -I4M -s/tmp/memcached.sock -a770"

[mysql]
# Mysql client character set configuration
default_character_set = utf8mb4

Hello Shubhra,

Thank you for the report and feedback.
I tried to reproduce the issue at my end with provided config, schema. Observed sometimes "GET" is not returning the value but not seeing any "requests get stuck" situation. May I kindly request you to share your script which is triggering this issue? Please mark it as private if you wish after posting here.

Thanks,
Umesh

File to test memcached protocol

Attachment: memcached_benchmark.php (application/octet-stream, text), 3.40 KiB.

I have attached the file. You can place this file in a location and create a file stress.sh in the same location and put the below lines in it. Install php-cli and php-memcached packages and run stress.sh. To stress the memcached plugin more you can add more calls to the script in stress.sh

php memc_bm.php -s /tmp/memcached.sock &
php memc_bm.php -s /tmp/memcached.sock &
php memc_bm.php -s /tmp/memcached.sock &
php memc_bm.php -s /tmp/memcached.sock &
php memc_bm.php -s /tmp/memcached.sock &
php memc_bm.php -s /tmp/memcached.sock &

I will also like to add, this issue comes in light prominently when 6 or more of the php script is run in parallel. Below is a sample output when 8 php scripts are run in parallel. Times are in milliseconds. As you can see set command is taking about 2.5 minutes.

Array
(
    [touch] => 3,016.93
    [set] => 151,259.69
    [get] => 1,055.19
    [delete] => 635.50
    [setMulti] => 4,102.81
    [getMulti] => 951.93
    [deleteMulti] => 375.37
)

Thank you for the requested details.
I didn't observe any "stuck" situation but observed the latency as you have reported for "set" operations. Also, compared the latency with "memcached" server installed locally with the same script but didn't notice any latency as seen with InnoDB memcached.

Thanks,
Umesh

test results

Attachment: 91153.results (application/octet-stream, text), 23.21 KiB.

Thanks Umesh for verifying this. Yes, latency is very prominent when using the script over 8 times in parallel. I have even observed latencies of over 500,000 milliseconds for 2-3 instances of the script when executing in parallel. This actually causes web applications to timeout when using a backend like PHP and only a short term solution is to restart all the servers - web, php, mysql. 

As you reported latencies do increase for the stock memcached daemon using memory for storage but are manageable and I have not seen web applications to timeout due to this. So that was the reason I was calling it getting stuck since restart seemed to be the only way out.