I believe I have a design problem with how I am implementing a points system for my Twitch chat bot.
It is developed in Golang, and uses a SQLite3 DB for storage, this includes commands, quotes, and points.
However, the performance of said SQLite3 for a points system is horrendously slow. I’m not sure if it’s a problem with the driver, or if it’s me, but all I know is it isn’t working. I can store the points for maybe 50 users every few seconds.
How do you all implement a points system and store them? I could use some design feedback.
Since you’re doing a whole bunch at once in a loop, you probably will want to wrap the entire loop in a transaction. Otherwise sqlite will issues a transaction per insert, meaning it’ll have to flush to disk each time. Not sure if your library supports transactions directly. If not, prepare a statement of “BEGIN TRANSACTION” and “COMMIT” to issue before and after respectively.
As a side-note, try using user IDs instead of usernames. This way you won’t have people complaining about lost points when they change their name, which happens quite a bit.
Thanks for the tip, using a transaction makes a lot of sense.
Because it has a CLI component, it is entirely possible for a user to force quit the program by hitting ctrl+c. I can imagine the transaction not occuring properly in this case. Any thoughts on handling that, or should I just insist that users exit safely?
I can think of two things. Install a ctrl-c handler so that you make sure the transaction is finished correctly. Or two, batch the transaction into smaller groups to minimize loss.
This is fine, but once a stream approaches a high viewer count (5,000 or more) the system resources it takes gets pretty heavy. Basically, it takes my bot which runs at 4.0 MB of RAM without the API calls to well over 100 MB after a half hour or so with a very large stream.
Also, each API call can cause a spike in CPU usage.
Obviously this is a lot of data to gather, especially so quickly, so if anyone had a better approach I’d love to know. Thanks.
I don’t use Golang or SQLite so I’m not familiar with optimisation for them, but from my experience doing analytics and working with large channels it’s just one of those things that you’re going to have to accept that working with large arrays of users, and doing big inserts/updates for a points system, can use a lot of resources.
It’s worth doing some monitoring to see where the issues are for you and track the impact changes make. For example in my setup I found when my channels start hitting ~40k viewers MongoDB was having issue with such a big update, so I set it up to split it up into multiple updates and that improved performance somewhat.
On top of that you can also trade off real-time accuracy with your points system with reduced DB updates by buffering multiple updates and doing it at once, eg if you give out points at a 5 minute interval, buffer the points and update the db every 10, 15, or 30, minutes. Or if DB operations are not as much of an issue as the API request itself and handling that data, it’s not always possible to reduce that if the channel happens to have a lot of chatters, but you can reduce the frequency of requests and just increments points less often.
Also, I’m not sure what you’re making a request to https://api.twitch.tv/helix/users?login=channelid, is this just to get a chatters ID? If so, I hope you’re caching results so that you’re only requesting that on a cache miss, as if you already have that data cached there’s no point calling it again, at least not in the context of a points system.
My bad on the URL, I grabbed that from the part of my code that allows users to change titles and games. For actually getting viewers of a stream, I’m using: https://tmi.twitch.tv/group/user/channel/chatters.
Obviously, as you say, that involves dealing with a lot of JSON data in some cases, so it makes sense the system resources is high. I suppose I’ve never noticed that on other bots.
Since you’re opening an IRC connection and requesting the “twitch.tv/membership” capability, is there any reason you’re making an undocumented API call (I don’t recall seeing it documented, anyway) to get the list of users in chat instead of tracking JOIN/PART events?
Granted, those events don’t give you Twitch user IDs, but what I do is build a list of login names as users join and do a batch /helix/users query every 5 seconds with login names that have joined since the last time that query was run. You also get user IDs in the message tags for PRIVMSG events, which you’ll sometimes see before you see the JOIN event for a user.
As for the database backend, I’m currently using MySQL. Last I checked, SQLite has to lock the entire database file when committing changes (one reason why transactions make such a big difference), while locking in a client/server database engine can be more granular.
Just as well, then, that I stopped giving channel currency to lurkers a while ago. The only reason I process JOINs at all is to synchronize usernames and IDs in my database when they come in. As for currency, I make note of anyone talking in a 20 minute interval, and give those users currency for that period of time (in my case, 25 for followers, 20 for everyone else).
