1

I'm maintaining a heavy traffic web app providing RESTFul API using codeigniter and mysql. After turning mysql slow query log with 1 second long query time following query is login repeatedly with execution time of 100+ seconds.

SELECT GET_LOCK('fhn1enbb1u9hgu4m1tq32fbsds4c8ohj', 300) AS ci_session_lock;

It seems something with codeigniter configuration please help.

Sample slow query log:

SET timestamp=1554912062;
SELECT GET_LOCK('fhn1enbb1u9hgu4m1tq32fbsds4c8ohj', 300) AS ci_session_lock;
Time: 2019-04-10T16:01:02.640796Z
Query_time: 99.819745  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1554912062;
SELECT GET_LOCK('taff1quqs4gv2g33740t41cdplnpkof8', 300) AS ci_session_lock;
Time: 2019-04-10T16:01:03.082128Z
Query_time: 94.859307  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1554912061;
SELECT GET_LOCK('fhn1enbb1u9hgu4m1tq32fbsds4c8ohj', 300) AS ci_session_lock;
Time: 2019-04-10T16:01:01.979967Z
Query_time: 106.766586  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
Community
  • 1
  • 1
user1743247
  • 29
  • 1
  • 4

1 Answers1

1

By looking at this archive from the old CodeIgniter forum, it looks like normal behavior.

You can just ignore this.

It's caused by the same user attempting to simultaneously load multiple pages (or one page with asynchronous AJAX calls) on your website. And it's exactly what is supposed to happen - wait for the lock to be freed (session closed on one page) before being able to acquire a lock for the second page load.

Concurrent users and the database engine are irrelevant. You didn't see this on MyISAM just because you didn't have a user with the same behavior during that temporary switch.

This is basically because CodeIgniter uses persistent database connection by default. This is set by the pconnect option:

$db['default'] = array(
    ...
    'pconnect' => TRUE,
    ...
);

Since the lock is not released by the connection, another page trying to load will wait for the lock to be freed...

You could try to disable the persistent connection. This SO answer gives a pretty good explanation on when and why enabling persistent connection.

Community
  • 1
  • 1
Indigo
  • 745
  • 5
  • 16
  • I wonder... Is it CogeIgniter's fault for not clearing `GETLOCKs`? Or is CI using some connection polling mechanism, and _that_ is at fault? – Rick James Apr 22 '19 at 22:16
  • Even if CodeIgniter is clearing its locks (and it seems it does), if a user is loading 2 pages simultaneously, there are still chances one page will hit a lock... However, looking at OP's query log, I wonder what could really cause a 100 seconds delay.. – Indigo Apr 23 '19 at 12:34
  • 'pconnect' => false, But still some queries of set_lock goes in sleep mode?? can anyone tell me why??? Thanks – w.Daya Oct 07 '21 at 12:39
  • Yeah this is happening several "fetch" requests (like the AJAX calls what @Indigo mentioned. – Gajen Dissanayake Jan 27 '22 at 14:36