Moodle
  1. Moodle
  2. MDL-16641

Remove all attempts to cast sesskey to BINARY as it was causing MySQL to ignore the preferred index, resulting in slow response time

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.8, 1.8.1, 1.8.2, 1.8.3, 1.8.4, 1.8.5, 1.8.6, 1.8.7, 1.8.8, 1.9, 1.9.1, 1.9.2, 1.9.3, 1.9.4
    • Fix Version/s: None
    • Component/s: Libraries, Performance
    • Labels:
      None
    • Database:
      MySQL
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Rank:
      2187

      Description

      Our team (at SFSU) talked to the MySQL support (we have subscribed to their enterprise level support) and they suggested that we look into the codes and remove all attempts to cast sesskey to BINARY, as it will cause MySQL to ignore the preferred index and not use an index at all, resulting in slow response time. So we dig into the codes and did the following changes

      in /lib/adodb/session/adodb-session.php (and adodb-session2.php in the same directory)

      replace

      $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

      to

      $binary = $conn->dataProvider === 'mysql' ? '' : '';

      This has dramatically improved the performance on our production site. However, would like to hear people's opinion about this change (also emailed Martin D, Martin L, and the author of ADODB already, but haven't heard back from them yet). Maybe the core could consider this change too. Also see the discussion thread that our system administrator Taylor posted here for more details:

      http://moodle.org/mod/forum/discuss.php?d=106108

        Issue Links

          Activity

          Hide
          Petr Škoda added a comment -

          The database-session code will be fully rewritten in 2.0, unfortunately the current session code using adodb has several known problems

          Show
          Petr Škoda added a comment - The database-session code will be fully rewritten in 2.0, unfortunately the current session code using adodb has several known problems
          Hide
          Petr Škoda added a comment -

          the adodb session code is not used anymore in 2.0 - thanks for the report

          Show
          Petr Škoda added a comment - the adodb session code is not used anymore in 2.0 - thanks for the report
          Hide
          Martin Dougiamas added a comment -

          Petr, is there any actual problem with just implementing this fix in 1.9? (removing the binary cast)

          It seems to work for a lot of people using 1.9.

          Show
          Martin Dougiamas added a comment - Petr, is there any actual problem with just implementing this fix in 1.9? (removing the binary cast) It seems to work for a lot of people using 1.9.
          Hide
          Elvedin Trnjanin added a comment -

          Works in 1.8 with no issues as well. Can it be fixed for those that will need to use 1.8 for a bit longer?

          Show
          Elvedin Trnjanin added a comment - Works in 1.8 with no issues as well. Can it be fixed for those that will need to use 1.8 for a bit longer?
          Hide
          Wen Hao Chuang added a comment -

          I hope people don't mind that I extend the "affects version/s" to include 1.8 as well. Instead of just listing out 1.8, I listed 1.8.x ~ 1.9.x just so that when people use JIRA's filter they could still find this ticket - NOT that I'm trying to make this bug looks really important/scary across many versions! Martin or Petr if I should just list "affects version/s" as 1.8 and 1.9 in the future (for generic 1.8.x and 1.9.x bugs), please let me know. Thanks!

          Show
          Wen Hao Chuang added a comment - I hope people don't mind that I extend the "affects version/s" to include 1.8 as well. Instead of just listing out 1.8, I listed 1.8.x ~ 1.9.x just so that when people use JIRA's filter they could still find this ticket - NOT that I'm trying to make this bug looks really important/scary across many versions! Martin or Petr if I should just list "affects version/s" as 1.8 and 1.9 in the future (for generic 1.8.x and 1.9.x bugs), please let me know. Thanks!
          Hide
          Petr Škoda added a comment -

          the problem here is that it just "seems" to work, but in fact it does not work much
          When people use db sessions in 1.9 or earlier they sometimes get weird errors and various seemingly unrelated problems, I do think we should not recommend db sessions at all yet

          Show
          Petr Škoda added a comment - the problem here is that it just "seems" to work, but in fact it does not work much When people use db sessions in 1.9 or earlier they sometimes get weird errors and various seemingly unrelated problems, I do think we should not recommend db sessions at all yet
          Hide
          Wen Hao Chuang added a comment -

          Oh boy... Petr when you said that "it just seems to work but in fact it does not work much," are you implying that there are still a lot of potential problems with dbsessons? Could you please elaborate this a little bit more? So if moodle core team will not recommend db sessions at all yet, is there any work-around or alternatives for people who are running large/busy 1.9.x sites (or sites built on cluster of servers)? Thanks!

          By the way I did notice the MDL-17754 and have added myself to the watch list. I have also quickly updated the moodle doc sessions to reflect your comments. Please feel free to edit this page:

          http://docs.moodle.org/en/sessions

          Show
          Wen Hao Chuang added a comment - Oh boy... Petr when you said that "it just seems to work but in fact it does not work much," are you implying that there are still a lot of potential problems with dbsessons? Could you please elaborate this a little bit more? So if moodle core team will not recommend db sessions at all yet, is there any work-around or alternatives for people who are running large/busy 1.9.x sites (or sites built on cluster of servers)? Thanks! By the way I did notice the MDL-17754 and have added myself to the watch list. I have also quickly updated the moodle doc sessions to reflect your comments. Please feel free to edit this page: http://docs.moodle.org/en/sessions
          Hide
          Petr Škoda added a comment -

          Oh boy...
          It is not my fault, I just discovered the problem and tried to fix it in HEAD. Frankly I do not know how to solve it on 1.9.x sites.

          All our code expects sessions to be locked properly - see http://php.net/manual/en/function.session-write-close.php If it is not locked it can not work as expected because concurrent http requests start overriding session or undoing session changes.

          Long ago somebody implemented db session support in Moodle using incomplete session code from adodb, at that time we did not store that much critical data in session, but now we cache user access data, group access, role switching and many other critical data there. I can not tell you exactly what and when may go wrong, it may or may not work.

          I can not stop anybody using db sessions, all I say is that it should not be supported because it is not reliable and we can not fix it in 1.9.x. Even in 2.0 it might be a big problem to implement it somehow for Oracle and MSSQL and it may be slow/expensive for pgsql adn mysql.

          more info:
          http://www.mysqlperformanceblog.com/2007/03/27/php-sessions-files-vs-database-based/

          We could just remove the binary, but I would personally prefer if somebody found out why it was added there originally, maybe it has some purpose

          Show
          Petr Škoda added a comment - Oh boy... It is not my fault, I just discovered the problem and tried to fix it in HEAD. Frankly I do not know how to solve it on 1.9.x sites. All our code expects sessions to be locked properly - see http://php.net/manual/en/function.session-write-close.php If it is not locked it can not work as expected because concurrent http requests start overriding session or undoing session changes. Long ago somebody implemented db session support in Moodle using incomplete session code from adodb, at that time we did not store that much critical data in session, but now we cache user access data, group access, role switching and many other critical data there. I can not tell you exactly what and when may go wrong, it may or may not work. I can not stop anybody using db sessions, all I say is that it should not be supported because it is not reliable and we can not fix it in 1.9.x. Even in 2.0 it might be a big problem to implement it somehow for Oracle and MSSQL and it may be slow/expensive for pgsql adn mysql. more info: http://www.mysqlperformanceblog.com/2007/03/27/php-sessions-files-vs-database-based/ We could just remove the binary, but I would personally prefer if somebody found out why it was added there originally, maybe it has some purpose
          Hide
          Dakota Duff added a comment -

          We we experiencing outrageous CPU usage on our database server for one of our busier sites and I noticed that queries to the mdl_sessions2 table were frequently waiting locks to clear which led me here. It also led me to this page:

          – QUOTE –
          http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

          OK but the problem is that with binary, the index cannot be used :
          mysql> explain select prod_name, prod_id from products where binary prod_name = 'Hype Beau';

          The solution is to apply the binary operator to the constant, then the index is used :
          mysql> select prod_name, prod_id from products where prod_name = binary 'Hype Beau';
          – END QUOTE –

          This particular mdl_sessions2 table had about 10K rows, so not having indexes was killing us.

          I did a find & replace of '$binary sesskey = ' to 'sesskey = $binary ' in /lib/adodb/session/adodb_session.php and /lib/adodb/session/adodb_session2.php and our DB server's CPU usage dropped from an average of about 100-160% to about 20-30%.

          Show
          Dakota Duff added a comment - We we experiencing outrageous CPU usage on our database server for one of our busier sites and I noticed that queries to the mdl_sessions2 table were frequently waiting locks to clear which led me here. It also led me to this page: – QUOTE – http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html OK but the problem is that with binary, the index cannot be used : mysql> explain select prod_name, prod_id from products where binary prod_name = 'Hype Beau'; The solution is to apply the binary operator to the constant, then the index is used : mysql> select prod_name, prod_id from products where prod_name = binary 'Hype Beau'; – END QUOTE – This particular mdl_sessions2 table had about 10K rows, so not having indexes was killing us. I did a find & replace of '$binary sesskey = ' to 'sesskey = $binary ' in /lib/adodb/session/adodb_session.php and /lib/adodb/session/adodb_session2.php and our DB server's CPU usage dropped from an average of about 100-160% to about 20-30%.
          Hide
          Dakota Duff added a comment -

          For those trying to fix this for earlier versions of Moodle, I first made a backup copy of adodb_session.php and adodb_session2.php. Then the following command will change the instances in the file:

          sed -i 's/$binary sesskey = /sesskey = $binary /g' adodb-session.php adodb-session2.php

          Show
          Dakota Duff added a comment - For those trying to fix this for earlier versions of Moodle, I first made a backup copy of adodb_session.php and adodb_session2.php. Then the following command will change the instances in the file: sed -i 's/$binary sesskey = /sesskey = $binary /g' adodb-session.php adodb-session2.php
          Hide
          Greg Milsted added a comment -

          Hi people,

          We originally used file based sessions stored in our moodledata folder on our SAN. We found that when there were high periods of activity (8000+ connections within an hour say) the file system struggled to read and write to the disk, probably coupled with indexing the amount of files in the folder so we switched to database stored sessions.

          This just moved the problem to a different arena as we found the sessions2 table locking to process the amount of activity during periods of high load causing the CPU on our database server to go nuts.

          Following the information posted by SFSU (Taylor Judd), we've changed the files below and found a significant performance increase, just in case this helps anyone else supporting a large user base!

          in /lib/adodb/session/adodb-session.php (and adodb-session2.php in the same directory)

          replace

          $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

          to

          $binary = $conn->dataProvider === 'mysql' ? '' : '';

          Show
          Greg Milsted added a comment - Hi people, We originally used file based sessions stored in our moodledata folder on our SAN. We found that when there were high periods of activity (8000+ connections within an hour say) the file system struggled to read and write to the disk, probably coupled with indexing the amount of files in the folder so we switched to database stored sessions. This just moved the problem to a different arena as we found the sessions2 table locking to process the amount of activity during periods of high load causing the CPU on our database server to go nuts. Following the information posted by SFSU (Taylor Judd), we've changed the files below and found a significant performance increase, just in case this helps anyone else supporting a large user base! in /lib/adodb/session/adodb-session.php (and adodb-session2.php in the same directory) replace $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : ''; to $binary = $conn->dataProvider === 'mysql' ? '' : '';
          Hide
          Paulo Porto Maciel added a comment -

          Hello,

          I did like Greg Milsted suggested: Changed the source of /lib/adodb/session/adodb-session.php and dodb-session2.php

          from

          $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

          to

          $binary = $conn->dataProvider === 'mysql' ? '' : '';

          OW! performance is better now!
          i add 2 new index in mdl_sessions2 to improve performace too.
          thanks

          Show
          Paulo Porto Maciel added a comment - Hello, I did like Greg Milsted suggested: Changed the source of /lib/adodb/session/adodb-session.php and dodb-session2.php from $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : ''; to $binary = $conn->dataProvider === 'mysql' ? '' : ''; OW! performance is better now! i add 2 new index in mdl_sessions2 to improve performace too. thanks
          Hide
          Petr Škoda added a comment -

          I think we have just discovered some nasty duplicates most probably caused by the lack of locking in DB sessions in 1.9 during the 2.0 upgrade.
          I personally think that there is no reason to attempt to fix this because the db sessions should not be recommended for any production sites running Moodle < 2.0
          Sorry for the bad news...

          Show
          Petr Škoda added a comment - I think we have just discovered some nasty duplicates most probably caused by the lack of locking in DB sessions in 1.9 during the 2.0 upgrade. I personally think that there is no reason to attempt to fix this because the db sessions should not be recommended for any production sites running Moodle < 2.0 Sorry for the bad news...
          Hide
          Chris Fryer added a comment -

          My tests have not revealed this to be a problem when using MySQL 5.5 with the InnoDB storage engine. Is row-level locking likely to help here?

          Show
          Chris Fryer added a comment - My tests have not revealed this to be a problem when using MySQL 5.5 with the InnoDB storage engine. Is row-level locking likely to help here?
          Hide
          Dan Poltawski added a comment -

          Is this resolved in 2.x? Can it be closed?

          Show
          Dan Poltawski added a comment - Is this resolved in 2.x? Can it be closed?
          Hide
          Petr Škoda added a comment -

          Moodle 1.9 is not supported any more, closing. Thanks for the report anyway.

          Show
          Petr Škoda added a comment - Moodle 1.9 is not supported any more, closing. Thanks for the report anyway.

            People

            • Votes:
              11 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: