Inefficient MySQL Session Queries

[juanrossi]

Hello, I've doing some load testing to the last version of CTF (ed0a225) and saw that some queries and the DB in general could be optimised.

The session table has the column cookie with type text. This is inefficient because we have a fixed size value that could fit inside a VARCHAR(200) without any issue. Also, the text type doesn't save values to memory, which generates lot of unnecessary CPU usage.

I know that on a normal usage, all this information would be also saved to Memcached, but I don't see why we can't also optimise this.

This graph represents the CPU usage (With 100+ concurrent requests) after changing the type to varchar and adding an INDEX to cookie.

Also, I haven't seen any index besides the primary key ones or a UNIQUE key.

Would you be interested in a PR with improvements on the database schema?

[stevcoll]

@juanrossi Thanks for the research! We would certainly appreciate any optimizations you could suggest for the database schema if possible. There are some other priorities right now so we haven't had a lot of time to look into these areas.

[wisco24]

Linking Issue #456 as I think they are related.

[stevcoll]

Closing as there is a PR #564 for this that should be merged soon.