#391 Can't create more than max_prepared_stmt_count statements

Reporter BioShock78
Owner MattJ
Created
Updated
Stars ★★★ (4)
Tags
  • Status-Fixed
  • Milestone-0.10
  • Type-Defect
  • Patch
  • Priority-High
  1. BioShock78 on

    Hi, I have a problem with Prosody 0.9.2 with mod_storage_sql and MySQL. I have some k users online and i get randomly this error in Prosody: http://wklej.org/id/1261314/ What is this? I have never seen this error before. Tell my how i can fix that. Increase max_prepared_stmt? default is ~16000 max is 1000000.

  2. BioShock78 on

    I increase stmt limit in mysql to extremely value - set global max_prepared_stmt_count=1000000; and i see strange phenomenon. Mysql increased memory usage from 250 mb to 380 mb and suddenly dropped memory to 248 mb. When its going on stmt the beign to close - this is output from mysql when memory usage is 370 and 248 MariaDB [(none)]> show global status like 'com_stmt%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Com_stmt_close | 2025517 | <---- 370 mb | Com_stmt_execute | 2048988 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 2047586 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | +-------------------------+---------+ 7 rows in set (0.00 sec) MariaDB [(none)]> show global status like 'com_stmt%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Com_stmt_close | 2040480 | < ---- 240 | Com_stmt_execute | 2055893 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 2054513 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | +-------------------------+---------+ 7 rows in set (0.00 sec) and it's repeat. Sorry for my english again :)

  3. Waqas on

    Apparently we are leaking prepared statements. We will investigate this for 0.9.3, thanks for reporting this.

    Changes
    • tags Milestone-0.9.3 Status-Accepted
    • owner Waqas
  4. Zash on

    Changes
    • tag Milestone-0.9
  5. Zash on

    #664 and #748 are duplicates of this.

  6. Zash on

    And this issue would in turn be part of, or related to #688

  7. Zash on

    Everyone who posted duplicates are now subscribed to this issue. If you have anything to add, do so here. I note that apparently sql2 (sql in 0.10/trunk) is also affected(?)

  8. ProsodyFan on

    I can confirm sql in 0.10 (specifically 0.10.r7656+.296543556065+) is affected. In addition to max_prepared_stmt_count statements here is my latest log entry where Prosody crashed 6 hours ago. Oct 28 15:26:52 sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:179: Error executing statement parameters: Deadlock found when trying to get lock; try restarting transaction stack traceback: /usr/lib/prosody/util/sql.lua:179: in function 'insert' /usr/lib/prosody/modules/mod_storage_sql.lua:86: in function </usr/lib/prosody/modules/mod_storage_sql.lua:78> (tail call): ? (tail call): ? [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:220: in function '_transaction' /usr/lib/prosody/util/sql.lua:234: in function </usr/lib/prosody/util/sql.lua:233> (tail call): ? (tail call): ? ... (tail call): ? (tail call): ? /usr/lib/prosody/modules/mod_iq.lua:54: in function '?' /usr/lib/prosody/util/events.lua:78: in function </usr/lib/prosody/util/events.lua:74> (tail call): ? (tail call): ? /usr/lib/prosody/core/stanza_router.lua:192: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:139: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:257: in function 'func' /usr/lib/prosody/util/async.lua:90: in function </usr/lib/prosody/util/async.lua:88>

  9. Zash on

    That's actually trunk, not 0.10. Arch has weird version numbers. And that looks like a completely different error, so you should file a new issue for that.

  10. MattJ on

    Patch to be tested at https://prosody.im/issues/issue/688#comment-5

    Changes
    • owner Waqas MattJ
    • tags Priority-High
  11. MattJ on

    Changes
    • tags Status-Started
  12. MattJ on

    Changes
    • tags Patch
  13. MattJ on

    Changes
    • tags Milestone-0.10
  14. MattJ on

    Fix pushed in becb593ed86d. Thanks Waqas!

    Changes
    • tags Status-Fixed

New comment

Not published. Used for spam prevention and optional update notifications.