Tuning your database is an important part of database management and is essential to getting the best performance out of your resources. The following section covers some of the basic information around tuning, what auto-tuning we have for default databases, and jumping off points for more information on the tuning process.
To start, by default our databases have some auto-tuned parameters that are set up when you first create your instance. This auto-tuning makes use of the database resources in a way which suits a variety of general use cases. This means that there will still be times when specific configuration parameters can be changed to optimize your resources for high performance workloads; but we will discuss how to do this later on.
The auto-tuned parameters are:
key_buffer_size = {{ (50 * flavor['ram']/512)|int }}M
max_allowed_packet = {{ (1024 * flavor['ram']/512)|int }}K
thread_cache_size = {{ (4 * flavor['ram']/512)|int }}
query_cache_size = {{ (8 * flavor['ram']/512)|int }}M
innodb_buffer_pool_size = {{ (150 * flavor['ram']/512)|int }}M
tmp_table_size = {{ (16 * flavor['ram']/512)|int }}M
max_heap_table_size = {{ (16 * flavor['ram']/512)|int }}M
table_open_cache = {{ (256 * flavor['ram']/512)|int }}
table_definition_cache = {{ (256 * flavor['ram']/512)|int }}
open_files_limit = {{ (512 * flavor['ram']/512)|int }}
max_user_connections = {{ (100 * flavor['ram']/512)|int }}
max_connections = {{ (100 * flavor['ram']/512)|int }}
The above calculations are supposed to take the amount of RAM you receive from your flavor and return a value, in MB, for each parameter. For example, if we were to use the c1.c2r2 flavor which has 2048 MB of RAM and plug it into one of the equations, we would find that:
key_buffer_size = 50 * [2048] / 512 = 200MB
Paired with the right flavor, these parameters will suit most of your needs. However, if you have particularly heavy read or write workloads, you are able to change these parameters to achieve a better performance.
Note
Not all of the parameters that you are able to change are present in our list of auto-defined parameters.
The previous list is only what we auto-tune for a basic database instance. You are able to tune your database to have specifications that fit your needs. In the following section we will discuss some of the common parameters to change when wanting to improve performance with read/write operations. These are only some of the common parameters and a more comprehensive list is available from the MySQL docs.
The MySQL documents have a much more in depth explanation of how tuning works and how to go about tuning your database. If you are considering tuning your database heavily to suite your needs at a very specific level, then reading through those documents will be a necessity.
Note
Another good resource to read when looking for more information on tuning and how it works can be found on this percona blog about tuning.
For write heavy workloads, the parameters to look at changing would be:
innodb_log_file_size 512MB -2GB
innodb_log_buffer_size 32MB
For read heavy workloads, you could take a look at:
sort_buffer_size (a few MB is typical for complex SELECT queries)
# Be careful as this can be allocated in each connection. You will run out of memory if you make it too big)!
Now that we know what we are looking at changing, next we will cover the process of implementing these changes. We go about this, by creating a configuration format, attaching it to our instance, and restarting the database. To begin, we need to create our new config file with our new parameters. In this example, we are going to be increasing the innodb_buffer_pool_size:
$ openstack database configuration create conf1 '{"innodb_buffer_pool_size" : 1073741824}' --datastore mysql --datastore_version 5.7.29
+------------------------+-----------------------------------------+
| Field | Value |
+------------------------+-----------------------------------------+
| created | 2020-08-13T00:55:08 |
| datastore_name | mysql |
| datastore_version_name | 5.7.29 |
| description | None |
| id | acef615c-81a1-4f60-85e9-xxxxxxxxxxxx |
| instance_count | 0 |
| name | conf1 |
| updated | 2020-08-13T00:55:08 |
| values | {"innodb_buffer_pool_size": 1073741824} |
+------------------------+-----------------------------------------+
Once this is done, we then have to attach the configuration to our database and restart the instance:
$ openstack database configuration attach db-instance-1 conf1
$ openstack database instance restart db-instance-1
Now we can test that our instance has the parameter we wanted to update:
$ mysql -h db-instance-1 -uusr -p db -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
Note
Before committing to changing any of these parameters on your main database, you can test the behaviour of your new configuration by using a replica.
While tuning is an important part of database performance and management, there are some other actions you can take to improve the general performance of your database:
If you want a volume that is performant in terms of it’s IO access then ensure that the database instance is created using one of the NVMe storage tiers. There are three options available depending on the level of IOPS cap you require.
The volume type can be selected using the --volume_type parameter when
creating your database. For example, if we wished to have a volume with an
IOPS limit of 2000 we would do the following.
$ openstack database instance create db-instance-1\
e3feb785-af2e-41f7-899b-xxxxxxxxxxxx \ # this is the flavor ID for your instance
--size 5 \
--datastore mysql \
--datastore_version 5.7.29 \
--databases myDB \
--users dbusr:dbpassword \
--volume_type b1.sr-r3-nvme-2000 \
--nic net-id=908816f1-933c-4ff2-8595-xxxxxxxxxxxx
In the event that you do manage to run out of memory, you can increase the flavor (RAM in particular) of your instance to meet the new demand. You can do this using the
openstack database instance resize flavor <instance> <flavor>