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:
Category:MySQL Server: Memcached Severity:S5 (Performance)
Version:5.7.22 OS:Debian (9)
Assigned to: CPU Architecture:x86 (amd64)

[6 Jun 2018 6:52] Shubhra Prakash Nandi

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


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 character set configuration
default_character_set = utf8mb4

# 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 client character set configuration
default_character_set = utf8mb4
[11 Jun 2018 13:52] MySQL Verification Team
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.

[11 Jun 2018 14:33] Shubhra Prakash Nandi
File to test memcached protocol

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

[11 Jun 2018 14:36] Shubhra Prakash Nandi
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 &
[11 Jun 2018 16:30] Shubhra Prakash Nandi
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.

    [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
[13 Jun 2018 10:47] MySQL Verification Team
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.

[13 Jun 2018 10:49] MySQL Verification Team
test results

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

[13 Jun 2018 13:05] Shubhra Prakash Nandi
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.