MySQL tuning and the max_allowed_packet default value

[piningforthefnords]

I can understand if the GRR project doesn't want to start offering advice on tuning MySQL, but...

We've tried GRR with both SQLite and MySQL. Both work well, however when receiving large files, GRR will stall when using MySQL. This is due to a rather low default (16M) max_allowed_packet setting on Ubuntu. Should correcting this value be mentioned in the documentation?

[destijl]

Definitely. Send us a pull request for a MySQL tuning section in the admin guide. I'm sure sean will have some things he can add too. We've never run this datastore at scale, so we've never had to do it.

[destijl]

This is from sean on a users list thread, should be cleaned up and go in the documentation.

GRR Configs:
Mysql.conn_pool_max (default 10) - Since the default threadpool for GRR is 50 I try to set this to 50. This means that every thread in GRR can have immediate access to a database connection. Otherwise the GRR threads get stuck waiting for a free connection and everything gets really backed up. The reason it defaults to 10 is that mysql defaults to max_connections = 100 so it is really easy to run into that on even small installs. The thing to watch out for here is that there are still some inefficiencies in how GRR updates objects in the datastore so having a lot of workers/frontends with lots of threads can potentially lead to more locking problems in the database as transactions have to retry/wait for rows to be available. Basically you will want to keep an eye on performance and sometimes removing frontends/workers will actually improve things because the core bottleneck is going to be the datastore. Keep an eye on how often things deadlock in the database and slow query log to get a sense of where you are running into problems.

Mysql.conn_pool_min (default 5) - I try to set this to 10. This isn't as important, but it just keeps connections ready and makes GRR a little more responsive when load surges. The connector tries to prune the connection pool back down to this number when it isn't actively using lots of connections. This defaults to 5 because it doesn't make sense to default it to 10 when the max is 10.

Worker.queue_shards (default 5) - There is probably some CS math that can be done here to find the optimal value, but I generally try to use a number at least equal to the number of frontends or workers. Having more queues helps distribute the load more evenly across the workers and helps database performance as the writes are spread across more rows. I would probably try to set this to 10-20. Greg might be able to chime in if there is any risk to setting it higher, but I don't think setting it slightly higher is going to cause any issues.

MySQL Configs:
max_connections = 100 - Most mysql installs default this to 100 so 10x frontends, 10x workers, 1xUI with a default 10 connection pool max is already going to exceed that value and cause problems. You ultimately want to set this equal to your conn_pool_max X workers+frontends+ui. The main goal here is to make sure MySQL doesn't get in the way of things, but you will still need to make sure you balance performance rather than just spin up as many connections as you possibly can due to locking/deadlocks.