Prepared statements exceeding limits when upgrading from 0.5 to 0.6
lucacorti opened this issue · 14 comments
After upgrading to myxql 0.6 we saw prepared statemets constantly growing over time and exceeding the configured limit (16k)
while on 0.5 they are a small constant number
We are experiencing the exact same.
Thank you for the report. I am afraid this commit introduced the issue. /cc @greg-rychlewski
Can you please try running from commit: 1014f1c and see if the issue persists?
Hi @pierot @lucacorti ,
Do you know if you are using prepared: :named or :unnamed? Thank you
@greg-rychlewski we don't have it set, so I guess it is :named?
@josevalim We'll try using that version.
@greg-rychlewski No, we are not setting any prepared configuration.
@pierot @lucacorti Thanks and sorry a couple other questions:
are you using Ecto with MyXQL adapter or MyXQL directly?
- if Ecto are you issuing them with Repo.all/Repo.one, etc or with Ecto.Adapters.SQL.query?
- if MyXQL how are you calling the queries
@greg-rychlewski We are mostly using myxql via the Ecto adapter with Repo.all/one
@greg-rychlewski Only using Ecto with MyXQL adapter and Repo.all.
@josevalim looks like it's working with 1014f1c:
> SHOW GLOBAL STATUS LIKE '%prepared_stmt_count%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Prepared_stmt_count | 0 |
+---------------------+-------+
1 row in set
Time: 0.113s
No, sorry, I was on the wrong node, still having problems with prepared statements growing indefinitely even with that version.
@lucacorti @pierot can you please try master? I have reverted both commits that could have caused this.
@josevalim I have an idea what the issue could be. But it's difficult for me to set up the environment needed right now.
In multi-node setups prepared statements with the same name can be saved with different refs. If it was prepared on one node then executed on another it will be re-prepared and the next time it's executed on the original node, the refs won't match and it will be re-prepared again.
Before the cache change commit, executions would check for the name and not the ref. So each node would only be able to re-prepare an existing statement once. If it went back to checking for name instead of ref I believe this issue should disappear.
@josevalim I'm running latest master in production, seems to behave like 0.5.2.
Thank you! v0.6.1 is out!