We are experimenting some performance problems with our xibo database.
The stats are active, and we have seen that very often, the database is not available because it reaches the max number of connections due to all the INSERTs with new stats.
I have increased the max number of connections to 500 but we have a lot of displays (like 500) so from time to time, the database is unavailable during some time.
There is a any special tunning in the database so we can have stats and the database up and running all the time?
Do you advise to use a specific engine (myISAM or innoDB)?
Regards,
The table should be innodb on installation as that is our convention - it also allows you to separate the stats table into its own data file (which is handy because it can get rather large).
Having its own data file might improve write performance, if that file is on a separate physical medium. Have you measured the number of transactions? Sounds like it might be rather large.
As for the insert - it could most likely be made more efficient in code - we issue individual inserts at the moment, in a transaction. These could be batched outside of a transaction.