{"action":"created","issue":{"url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/322","repository_url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map","labels_url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/322/labels{/name}","comments_url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/322/comments","events_url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/322/events","html_url":"https://github.com/PokemonGoMap/PokemonGo-Map/issues/322","id":170223677,"number":322,"title":"[Bug] mysqld: Deadlock found when trying to get lock; try restarting transaction","user":{"login":"billbo99","id":17733206,"avatar_url":"https://avatars.githubusercontent.com/u/17733206?v=3","gravatar_id":"","url":"https://api.github.com/users/billbo99","html_url":"https://github.com/billbo99","followers_url":"https://api.github.com/users/billbo99/followers","following_url":"https://api.github.com/users/billbo99/following{/other_user}","gists_url":"https://api.github.com/users/billbo99/gists{/gist_id}","starred_url":"https://api.github.com/users/billbo99/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/billbo99/subscriptions","organizations_url":"https://api.github.com/users/billbo99/orgs","repos_url":"https://api.github.com/users/billbo99/repos","events_url":"https://api.github.com/users/billbo99/events{/privacy}","received_events_url":"https://api.github.com/users/billbo99/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/labels/backend","name":"backend","color":"fbca04"},{"url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/labels/bugfix","name":"bugfix","color":"ee0701"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2016-08-09T17:25:32Z","updated_at":"2016-08-13T16:45:41Z","closed_at":null,"body":"## Current Behavior\r\nsyslog on database server is generating 100's of \r\n`mysqld: Deadlock found when trying to get lock; try restarting transaction`\r\n\r\nAnd there are several of these error messages popping up\r\n`Aborted connection 38 to db: 'pokemongomapdb' user: 'xxx' host: 'ubuntu' (Got an error reading communication packets)`\r\n\r\n## Possible Solution\r\n1) Change the workers to send their collected data via restapi to the db server process, which then performs batch inserts into the database.\r\n\r\n2) Have each thread insert there data into a queue on the worker, then have a dedicate thread insert the data into the database in batches of 100 as the queue fills up.\r\n\r\n## Steps to Reproduce (for bugs)\r\nRunning a beehive with\r\n   steps = 8\r\n   leaps = 3\r\n   accounts per worker = 8  (296 accounts total)\r\n\r\n## Your Environment\r\nVM ware guests all running  Ubuntu 16.04.1 server\r\n3g memory for main db server\r\n2g memory for 4x worker servers\r\n37 worker processes spread out over the worker servers.\r\n\r\n## Fixes I have tried\r\nPinned mysql into memory to speed things up.\r\nSet max connections to 5000 just to be sure.\r\nDropped and re-created the mysql database to force indexes to be recreated.\r\n"},"comment":{"url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/comments/239630201","html_url":"https://github.com/PokemonGoMap/PokemonGo-Map/issues/322#issuecomment-239630201","issue_url":"https://api.github.com/repos/PokemonGoMap/PokemonGo-Map/issues/322","id":239630201,"user":{"login":"rrbailey89","id":6540217,"avatar_url":"https://avatars.githubusercontent.com/u/6540217?v=3","gravatar_id":"","url":"https://api.github.com/users/rrbailey89","html_url":"https://github.com/rrbailey89","followers_url":"https://api.github.com/users/rrbailey89/followers","following_url":"https://api.github.com/users/rrbailey89/following{/other_user}","gists_url":"https://api.github.com/users/rrbailey89/gists{/gist_id}","starred_url":"https://api.github.com/users/rrbailey89/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rrbailey89/subscriptions","organizations_url":"https://api.github.com/users/rrbailey89/orgs","repos_url":"https://api.github.com/users/rrbailey89/repos","events_url":"https://api.github.com/users/rrbailey89/events{/privacy}","received_events_url":"https://api.github.com/users/rrbailey89/received_events","type":"User","site_admin":false},"created_at":"2016-08-13T16:45:41Z","updated_at":"2016-08-13T16:45:41Z","body":"I also get this error pretty often.\r\n\r\n```\r\n2016-08-13 09:44:27,062 [ search_worker_0][        models][ WARNING] (1213, u'Deadlock found when trying to get lock; try restarting transaction')... Retrying\r\n2016-08-13 09:44:27,249 [ search_worker_0][        models][    INFO] Upserted 2 pokemon, 16 pokestops, and 6 gyms\r\n2016-08-13 09:44:31,696 [ search_worker_1][        search][    INFO] Search step 7 beginning (queue size is 54)\r\n2016-08-13 09:44:32,170 [ search_worker_1][        models][    INFO] Upserted 0 pokemon, 15 pokestops, and 5 gyms\r\n2016-08-13 09:44:36,697 [ search_worker_0][        search][    INFO] Search step 8 beginning (queue size is 53)\r\n2016-08-13 09:44:37,345 [ search_worker_0][        models][    INFO] Upserted 1 pokemon, 14 pokestops, and 5 gyms\r\n2016-08-13 09:44:41,697 [ search_worker_1][        search][    INFO] Search step 9 beginning (queue size is 52)\r\n2016-08-13 09:44:42,154 [ search_worker_1][        models][ WARNING] (1213, u'Deadlock found when trying to get lock; try restarting transaction')... Retrying\r\n2016-08-13 09:44:42,338 [ search_worker_1][        models][ WARNING] (1213, u'Deadlock found when trying to get lock; try restarting transaction')... Retrying\r\n```"}}
{"actor":{"display_login":"rrbailey89"}}