[rt-users] apache sessions in database : GET_LOCK performance penalty

[Ruben]

Hi List,

We are suffering from some performance issues on our production rt4.2.12
platform. Since we use several mod_perl apache frontends to handle the
incoming http(s) requests we use the default database session-backend.

While storing sessions in the db provides us with great flexibility
during maintenance windows, storing sessions in the database really
seems to impact performance.

In order to analyze this, we've enabled slow-query logging on our mysql
5.6 daemon and below are some findings / "facts" :

1 A lot of time seems to be spent waiting for GET_LOCK type of queries
that show up like this in the slowquery log:

# Time: 160530 16:09:54
# User at Host: obfuscated[obfuscated] @  [obfuscated]  Id: 31972
# Query_time: 2.044124  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617394;
SELECT GET_LOCK('Apache-Session-d989b9252edf62e1434e5e7484fb529f', 3600);

They are very much alike:

/data/databases]# grep -B 2 'SELECT GET_LOCK' slowqueries.log | grep -v
'\--' | tail -n 25

SELECT GET_LOCK('Apache-Session-ed5ffd0b90b04c0cbe6ec236434dfaef', 3600);
# Query_time: 2.088112  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464616947;
SELECT GET_LOCK('Apache-Session-ed5ffd0b90b04c0cbe6ec236434dfaef', 3600);
# Query_time: 2.074731  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617025;
SELECT GET_LOCK('Apache-Session-d989b9252edf62e1434e5e7484fb529f', 3600);
# Query_time: 2.432063  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617148;
SELECT GET_LOCK('Apache-Session-d989b9252edf62e1434e5e7484fb529f', 3600);
# Query_time: 2.542805  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617148;
SELECT GET_LOCK('Apache-Session-d989b9252edf62e1434e5e7484fb529f', 3600);
# Query_time: 7.287267  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617206;
SELECT GET_LOCK('Apache-Session-c1c8a4b8d622d93054be6f2d4d3b8d2d', 3600);
# Query_time: 7.393664  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617206;
SELECT GET_LOCK('Apache-Session-c1c8a4b8d622d93054be6f2d4d3b8d2d', 3600);
# Query_time: 2.331990  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617218;
SELECT GET_LOCK('Apache-Session-cf6df05dbe2e0d2159564a8068abc5dd', 3600);
# Query_time: 2.443245  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1464617218;
SELECT GET_LOCK('Apache-Session-cf6df05dbe2e0d2159564a8068abc5dd', 3600);

and there are a lot of them:

/data/databases]# grep -B 2 'SELECT GET_LOCK' slowqueries.log | grep
Query_time | wc -l

   78225

(no rotation of the file is currently in place and the file has been in
use for months but there are thousands of entries for every working-day)

We think this is certainly accounting for most of the performance
problems as experienced by the users of the rt4.2 instance.

2 The SELECT GET_LOCK queries that are logged by the mysql daemon
(long_query_time is set to 2 seconds ) take between 2 and 15 seconds to
finish


3 The experienced performance degradation by users is greatly reduced by
switching to on-disk session storage (which kind of kills the
flexibility of a redundant setup during maintenance etc)


4 The mysql daemon resides on a dedicated machine, has been tuned, has a
lot of resources and is optimized for innodb.

5 There are - on average - not that many active sessions :

mysql> select count(id) from sessions;
+-----------+
| count(id) |
+-----------+
|      1271 |
+-----------+
1 row in set (0.00 sec)

mysql>

6   The mysqltuner script points out that many joins are performed
without indexes :

55864 in just 10 days of running the mysql daemon.

This might be related but if it's a real problem they ought to take a
long time to run and turn op in the slowquery log, right?


I've had a look on the mailing-list / google but did not find any prior
threads that seem to match my predicament.

If anybody has any suggestions that might point me into the right
direction eventually resolving this issue I'd be very happy to receive
them (i'm kind of out of ideas at this point). Please feel free to
request additional information regarding our setup: i'd be happy to supply.

Help!


Kind regards,


Ruben