search

tannn / TriviaTime

A feature-packed trivia plugin that includes multiple channel support, multiplayer, and robust editing tools. Play in #trivialand on Freenode
http://toagac.github.io
Eclipse Public License 1.0
14 stars 8 forks source link

Database growth is a problem over time #324

Open DecafMegalodon opened 8 years ago

DecafMegalodon commented 8 years ago

So glime has been chugging along great for the last two and a half years, generally without any problem. Recently though, performance has become unacceptable. Through a bit of digging, I found out that the triviagameslog (the database that keeps track of the round number + the question asked) has been growing nonstop since the beginning. It reached more that 1,250,000 entries and that has been pulling performance to a crawl when grabbing a new question. Sometimes taking more than 20 seconds to grab a new question on top of the waiting period on a high powered machine.

I went ahead and confirmed this was the problem by only dropping the triviagameslog database and starting the bot back up on the same machine. Wait times are now in the 2 seconds range.

For reference, glime's database is 163MB. After dropping triviagameslog it is only about 15MB.

Do we have a plan to address this? Chat suggested that the use of SQLite might be contributing to the problem but I think reusing round numbers in the database would be an easier solution instead switching to a new DB just so we can add a new entry for every question asked since round number do repeat after a while

tannn commented 8 years ago

Can you send me the database so I can take a look and run some tests? Preferably the one before you dropped triviagameslog. (btw, use my gmail email since we don't have the trivialand domain anymore. let me know if you don't have it.)

See also: #289

DecafMegalodon commented 8 years ago

I do not seem to have it. Can you send me am email at my username on gmail and I'll send you a link to the last snapshot