Before you start you need to understand that there is a reason why the maximum of 16 is allowed. Increasing the connections to your server will have impact on its performance, so stay attentive for any negative changes that you see after changing the configuration.
Also, you can make these changes either on a per user level or globally. The advantage of per-user changes it that you have better control over what is happening. The advantage of global changes is that you only need to make the change once.
To do this edit your global my.cnf file (in Ubuntu that would be: /etc/mysql/my.cnf) and look for the max_connections entry in the [mysqld] section. Uncomment the entry for max_connections and table cache, and add max_user_connections, for example with:
max_connections = 100
max_user_connections = 100
table_cache = 800
In the MySQL documentatation it is stated that the max_connections value is already set at 100. And in fact changing only the max_connections value will not allow you to make more connections from R, you have to include the max_user_connections.
The change to the table_cache has to do with the
Don’t forget to restart the mysql server after you have made changes to the global configuration (otherwise you will not have any differences):
sudo service mysql restart
The above will not have any effect until you relax the default settings of RMySQL, the MySQl interface in R. In the documentation for RMySQL you will see that you can change the maximum connections as follows:
MySQL(max.con=100, fetch.default.rec=1000)
This is just one single line of R, that you could put right before you are actually making the connections. If you are working on a shiny app that would be in the server.R file.
In the example above I changed not only the default maximum connections from 16 to 100 but also the default number of rows to be fetched from 500 to 1000. It depends on the systems and the database that you are accessing whether that has an impact either on your app or on your database. You can try out different settings. Just remember to restart your R session every time you make a change to the above.
If the above does not work, you may have local settings for the user accessing the database, and these have priority over global settings in MySQL. There is a detailed answer that specifies all the steps to check whether per-user values exist and how to set them to 0 (so that global values apply again).
By the same token, that same entry explains how to work with per-user settings instead of global values. This may be prefereable in your setup. The key there is to set the max_user_connections for your user, for example:
GRANT USAGE ON *.* TO db_user@localhost MAX_USER_CONNECTIONS 100;
Where db_user stands for the user that you have used to set up the connection in your shiny app or R script.
An alternative is to put a connection function in global.R. It dit not work for me when I expect many users to access the database. You could of course also make all data in-memory before working with it in your app, but then you will need to set up a mechanism to refresh the data often enough to reflect the current state of the database.
It is not so easy to find the right place to close database connections in an interactive application. And hopefully you can rely on the auto-disconnect feature of RMySQL where you will see lines like this:
Auto-disconnecting mysql connection (0, 49)
Auto-disconnecting mysql connection (0, 48)
Auto-disconnecting mysql connection (0, 45)
Auto-disconnecting mysql connection (0, 44)
in your console. That is connection number 49! Already a long way past 16.
Please let me know if you have a better approach to achieve the same, or if in your experience any of the settings above could be fine-tuned to ensure a more responsive mysql server.