Server stuck at "query end" state

[Bernard Pavković]

Hi,

I have a problem and can't find appropriate help on the net. In last month I had 2 system downtimes due to too many connections on servers. Looking at "show processlist" I saw a bunch of queries stuck at "query end" state. Execution time of queries gets to ~50s.
This happened when system load was not high so I excluded that as a potential cause (it works ok on higher loads).  Some ppl suggested that this may be due to low disk space but there is enough of it. Other suggested that mysql query cache may be causing it - bit it's disabled (query_cache_size is 0). Looking at this it must be something with my config so can anyone please give me a suggestion what am I doing wrong.

Here is system info and config:

- MariaDB galera cluster with 3 nodes
- Dedicated DB server hardware with SSD drives

[root@db1 ~]# mysql --version
mysql  Ver 15.1 Distrib 10.0.10-MariaDB, for Linux (x86_64) using readline 5.1

[root@db1 ~]# cat /etc/my.cnf.d/my-common.cnf
[mysqld]
port=3306
datadir=/db/common
socket=/db/common/mysql.sock
user=mysql
log-bin=/db/common/mysql-bin.log

binlog_format=ROW

wsrep_sst_method=xtrabackup-v2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_slave_threads=32
wsrep_cluster_name=ReMoVeD
wsrep_cluster_address=gcomm://ReMoVeD
wsrep_node_name=ReMoVeD
wsrep_node_address=ReMoVeD
server_id=1
wsrep_sst_auth=ReMoVeD

log_slave_updates

innodb_locks_unsafe_for_binlog=0
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size=4G
innodb_log_file_size=128M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_file_format=Barracuda

thread_handling=pool-of-threads

bind-address=::

slow_query_log=1
slow_query_log_file=/db/common/slow.log
long_query_time=0.1

[mysqld_safe]
log-error=/db/common/mysqld.log
pid-file=/db/common/mysqld.pid

[alexey.y...@galeracluster.com]

Hi Bernard,

Sadly you have not provided the most vital pieces of information: error
log contents and output of
SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES\G
SHOW STATUS LIKE 'wsrep%';
SHOW ENGINE InnoDB STATUS\Gof
for the "downtime" period. Also you didn't describe how those
"downtimes" were resolved, by themselves or some interference was
required?

So no can do. Most likely you are hitting some bug.

Regards,
Alex

On 2014-10-06 16:19, Bernard Pavković wrote:
> Hi,
>
> I have a problem and can't find appropriate help on the net. In last
> month
> I had 2 system downtimes due to too many connections on servers.
> Looking at
> "show processlist" I saw a bunch of queries stuck at "query end" state.
> Execution time of queries gets to ~50s.
> This happened when system load was not high so I excluded that as a
> potential cause (it works ok on higher loads). Some ppl suggested that
> this may be due to low disk space but there is enough of it. Other
> suggested that mysql query cache may be causing it - bit it's disabled
> (query_cache_size is 0). Looking at this it must be something with my
> config so can anyone please give me a suggestion what am I doing wrong.
>
> Here is system info and config:
>
> - MariaDB galera cluster with 3 nodes
> - Dedicated DB server hardware with SSD drives
>
> [root@db1 ~]# mysql --version

> *mysql Ver 15.1 Distrib 10.0.10-MariaDB, for Linux (x86_64) using
> readline
> 5.1*

>
> [root@db1 ~]# cat /etc/my.cnf.d/my-common.cnf
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

> *[mysqld]port=3306datadir=/db/commonsocket=/db/common/mysql.sockuser=mysqllog-bin=/db/common/mysql-bin.logbinlog_format=ROWwsrep_sst_method=xtrabackup-v2wsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_slave_threads=32wsrep_cluster_name=ReMoVeDwsrep_cluster_address=gcomm://ReMoVeDwsrep_node_name=ReMoVeDwsrep_node_address=ReMoVeDserver_id=1wsrep_sst_auth=ReMoVeDlog_slave_updatesinnodb_locks_unsafe_for_binlog=0innodb_autoinc_lock_mode=2innodb_buffer_pool_size=4Ginnodb_log_file_size=128Minnodb_flush_log_at_trx_commit=2innodb_file_per_table=1innodb_file_format=Barracudathread_handling=pool-of-threadsbind-address=::slow_query_log=1slow_query_log_file=/db/common/slow.loglong_query_time=0.1[mysqld_safe]log-error=/db/common/mysqld.logpid-file=/db/common/mysqld.pid*

[Bernard Pavković]

Hi Alexey,

Thank you for reply. Unfortunately I don't have error log contents - didn't exported them when problem occurred (pressure from PM - "this must work now..."). If it happens again I will provide them.
Problem did not resolved itself, I had to "kill" all instances since "service restart" didn't work.