High swapping (si/so) with perfommance impact at 5.6.26

MySQL & MariaDB Percona Server for MySQL 5.6
1

Greetings,

We use a 5.6.26 community edition MySQL at RedHat 6.2 64-bit box with 6 cpu and 24G RAM.

We experience high swap usage (11g from allocated 24g of swap) and high si/so values as from vmstat.

Some of main settings from my.cnf:
innodb_buffer_pool_size=20G
innodb_log_buffer_size=8388608
key_buffer_size=8388608
binlog_cache_size=32768
sort_buffer_size=262144
join_buffer_size=262144
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_ft_total_cache_size=640000000
innodb_ft_min_token_size=2
max_connections=700

Application uses a lot of stored procedures with “create temporary table … engine-memory”, and full text search is in use as well.

The regular number of connected threads is usualy 300-400, but there sometimes unclear peaks till 500.

Some questions:
1, How can i clear out which mysql activity causes the high swapping ?
2. Is the innodb_buffer_pool_size to high as per descripted environment ?
3. How much memory shall i take into accunt for each thread when doing the requied memory calculations ?

Best regards and looking forward your assistance,
Avi Vainshtein

2

Hi,

You can trace memory issues with Valgrind Massif for more info check here

[url]https://www.percona.com/blog/2014/01/24/mysql-server-memory-usage-2/[/url]

  1. Is the innodb_buffer_pool_size to high as per descripted environment ?
    Please provide output from
    [url]https://github.com/major/MySQLTuner-perl[/url]

  2. How much memory shall i take into accunt for each thread when doing the requied memory calculations ?
    Each thread connecting to MySQL server will needs its own buffers. About 256K is allocated at once even if thread is idle – they are used by default thread stack, net buffer etc.

more info here
[url]https://www.percona.com/blog/2006/05/17/mysql-server-memory-usage/[/url]