{"action":"created","issue":{"url":"https://api.github.com/repos/rails/rails/issues/14645","repository_url":"https://api.github.com/repos/rails/rails","labels_url":"https://api.github.com/repos/rails/rails/issues/14645/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/14645/comments","events_url":"https://api.github.com/repos/rails/rails/issues/14645/events","html_url":"https://github.com/rails/rails/issues/14645","id":31083805,"number":14645,"title":"Prepared statement eat lots of memories","user":{"login":"ajoulie","id":588433,"avatar_url":"https://avatars.githubusercontent.com/u/588433?v=3","gravatar_id":"","url":"https://api.github.com/users/ajoulie","html_url":"https://github.com/ajoulie","followers_url":"https://api.github.com/users/ajoulie/followers","following_url":"https://api.github.com/users/ajoulie/following{/other_user}","gists_url":"https://api.github.com/users/ajoulie/gists{/gist_id}","starred_url":"https://api.github.com/users/ajoulie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ajoulie/subscriptions","organizations_url":"https://api.github.com/users/ajoulie/orgs","repos_url":"https://api.github.com/users/ajoulie/repos","events_url":"https://api.github.com/users/ajoulie/events{/privacy}","received_events_url":"https://api.github.com/users/ajoulie/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/activerecord","name":"activerecord","color":"0b02e1"},{"url":"https://api.github.com/repos/rails/rails/labels/stale","name":"stale","color":"444444"}],"state":"closed","locked":false,"assignee":{"login":"tenderlove","id":3124,"avatar_url":"https://avatars.githubusercontent.com/u/3124?v=3","gravatar_id":"","url":"https://api.github.com/users/tenderlove","html_url":"https://github.com/tenderlove","followers_url":"https://api.github.com/users/tenderlove/followers","following_url":"https://api.github.com/users/tenderlove/following{/other_user}","gists_url":"https://api.github.com/users/tenderlove/gists{/gist_id}","starred_url":"https://api.github.com/users/tenderlove/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tenderlove/subscriptions","organizations_url":"https://api.github.com/users/tenderlove/orgs","repos_url":"https://api.github.com/users/tenderlove/repos","events_url":"https://api.github.com/users/tenderlove/events{/privacy}","received_events_url":"https://api.github.com/users/tenderlove/received_events","type":"User","site_admin":true},"assignees":[{"login":"tenderlove","id":3124,"avatar_url":"https://avatars.githubusercontent.com/u/3124?v=3","gravatar_id":"","url":"https://api.github.com/users/tenderlove","html_url":"https://github.com/tenderlove","followers_url":"https://api.github.com/users/tenderlove/followers","following_url":"https://api.github.com/users/tenderlove/following{/other_user}","gists_url":"https://api.github.com/users/tenderlove/gists{/gist_id}","starred_url":"https://api.github.com/users/tenderlove/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tenderlove/subscriptions","organizations_url":"https://api.github.com/users/tenderlove/orgs","repos_url":"https://api.github.com/users/tenderlove/repos","events_url":"https://api.github.com/users/tenderlove/events{/privacy}","received_events_url":"https://api.github.com/users/tenderlove/received_events","type":"User","site_admin":true}],"milestone":null,"comments":21,"created_at":"2014-04-08T15:58:01Z","updated_at":"2016-08-01T22:12:38Z","closed_at":"2014-11-19T19:52:49Z","body":"**The problem**\r\n\r\nSummary :\r\nIn certain cases, Rails4 generates prepared statements where certain variables are not bound but hardcoded in the query instead. \r\n1. This seems to defeat the purpose of prepared statements, by generating an infinite number of \"single usage\" prepared statements \r\n2. The immediate consequence is that this bloats the database server prepared statements cache very fast, with thousands of unique prepared statements, ending up with an OUT OF MEMORY exception. \r\n\r\nSolution : \r\nEither bind all variables (preferred), or do not use prepared statements for queries where certain variables cannot be bound.\r\n\r\n\r\n\r\nExample :\r\n\r\nThe model :\r\n\r\n```ruby\r\nClass User\r\n  has_many :addresses, -> where(current:true)\r\nend\r\na_user.addresses\r\n```\r\ngenerates the following query : \r\n\r\n```sql\r\nSELECT \"addresses\".* FROM \"addresses\" WHERE \"addresses\".\"user_id\" = $1 AND \"addresses\".\"current\" = 't'  [[\"user_id\", 1]]\r\n```\r\n\r\nThis statement is executed as a prepared statement, but column \"current\" is not treated as a bound variable. \r\nThis is ok when current is a boolean, a most two prepared statements will be used. But when it is a timestamp, a different prepared statement will be used (and saved into postgresql) for each request. \r\n\r\nMore generally, only a few columns can be bound variables. This leads to lots of useless prepared statement into database, especially, if you have conditions on timestamp or subquery.  \r\n\r\nFuthermore, the limit size of StatementPool is 1000. With postgresql this is very high due to the fact that prepared statements are not shared between connections : the prepared statement cache is per connection / backend. This leads quite fast to a very high memory usage on the postgresql side. \r\n\r\n\r\n\r\nHow did we find this :\r\nOur application used to work well  with Rails 3.1 and 3.2. After upgrading to Rails 4, we experienced a memory leak on the db side : after about 1 hour of running, where we could observe the db memory grow and grow, the database ended up by returning OUT OF MEMORY errors. We finally figured out it was the number of prepared statements that exploded and was bloating the backend prepared statements cache. It seems to come from the fact that Rails 4 makes a wider use of prepared statements. While this is a great idea (thanks @tenderlove to bring that is Rails), it can very be problematic if there is no way to bind variables all variables properly.\r\n\r\nIf you have timestamp into some queries and if by no chance those queries are big, memory consumption of postgresql server increases drastically as well. In our case, we have 30 postgresql connections, with Rails 4, due to prepared statements, the memory used by the database server moves from 500 KB to more than 3 GB in about 1 hour, leading to out of memory errors.\r\nHere is a stackoverflow post where we explain the symptoms http://bit.ly/1hcG1ba\r\n\r\nThanks for reading, hope it helps.\r\n"},"comment":{"url":"https://api.github.com/repos/rails/rails/issues/comments/236723957","html_url":"https://github.com/rails/rails/issues/14645#issuecomment-236723957","issue_url":"https://api.github.com/repos/rails/rails/issues/14645","id":236723957,"user":{"login":"ghiculescu","id":509837,"avatar_url":"https://avatars.githubusercontent.com/u/509837?v=3","gravatar_id":"","url":"https://api.github.com/users/ghiculescu","html_url":"https://github.com/ghiculescu","followers_url":"https://api.github.com/users/ghiculescu/followers","following_url":"https://api.github.com/users/ghiculescu/following{/other_user}","gists_url":"https://api.github.com/users/ghiculescu/gists{/gist_id}","starred_url":"https://api.github.com/users/ghiculescu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ghiculescu/subscriptions","organizations_url":"https://api.github.com/users/ghiculescu/orgs","repos_url":"https://api.github.com/users/ghiculescu/repos","events_url":"https://api.github.com/users/ghiculescu/events{/privacy}","received_events_url":"https://api.github.com/users/ghiculescu/received_events","type":"User","site_admin":false},"created_at":"2016-08-01T22:12:38Z","updated_at":"2016-08-01T22:12:38Z","body":"Not yet :( need to migrate off protected attributes so that might take a while."}}
{"actor":{"display_login":"ghiculescu"}}