OVH Guides

Configuring your database server

Find out how to configure and optimise your database server

Last updated 24/08/2020

Objective

With the Private SQL and Cloud Databases database servers, you can influence your server’s global settings. You can also view your server's activity.

Find out how to configure and optimise your database server.

Requirements

Instructions

View general information on your database server

In the services bar on the left-hand side of your OVHcloud Control Panel, go to the Databases section, then select the SQL instance concerned. Click on the General information tab.

You can also view important information on your SQL instance. Please take a few moments to ensure that the information displayed is correct, and corresponds to the instructions below.

Information Details
Service status Mainly shows if the instance has been rebooted, is in the process of rebooting, or is suspended. Your instance must be rebooted if you need to carry out any actions.
Type Shows the database system used by the server. If you are unsure if the correct type is being used, please note that the most common version is “MySQL”, but other types also exist (PostgreSQL, MariaDB). For example, if you are using WordPress for your website, a MySQL system is perfect for it.
Version Shows the database system version used by the server. Check that your website is compatible with the version you have chosen.
RAM Shows the RAM available for your instance, also shows if you are close to exceeding the RAM limit. Your database server has dedicated, guaranteed resources: its RAM. If required, you can scale the RAM, and receive warnings if you are consuming all of your instance’s RAM resources.
Infrastructure Shows the infrastructure used by your instance. This information is inherent to the OVHcloud infrastructure.
Datacentre Shows the datacentre in which the instance has been created. Verify that your instance is hosted in is the same datacentre as the OVHcloud Web Hosting plan that your website is based (or will be based) on.
Host Shows the OVHcloud server your instance has been created in. This information is inherent to the OVHcloud infrastructure, and can be used in our communications on OVHcloud incidents.

General information

Authorising an IP address (only on Cloud Database solutions)

In order for your CloudDB instance to be accessible, you must enter the IP addresses or ranges that can connect to your database.

In the services bar on the left-hand side of your OVHcloud Control Panel, go to the Databases section, then select the SQL instance concerned. Click on the General information tab.

To do this, click on the Authorised IPs tab, then click Add an IP address/mask.

clouddb

In the window that pops up, enter the IP address or mask that you wish to authorise IP/mask, together with a description, if you wish. You can then decide if you want to grant access to the databases only, or to the SFTP as well. Finally, click Confirm.

clouddb

Authorise connections to OVHcloud Web Hosting plans

For an OVHcloud Web Hosting plan, you will need to authorise its gateway IP address.

To find the gateway IP address go to your OVHcloud Control Panel. Click the Web tab, then click on Hosting plans in the left column. Select your Web Hosting plan in the list, then click on the FTP - SSH tab.

Here, look for FTP server, which will show you the cluster number you are on, as shown below.

clouddb

Once you have retrieved the number of the cluster on which your Web Hosting plan is located, go to the IP address list for Web Hosting clusters. It provides the gateway IP address for each cluster.

The cluster IP address will not work to authorise the connection to the Cloud DB server. Please add the gateway IP address.

Modifying the database server solution

To modify the solution for your database server, go to your OVHcloud Control Panel. Click the Web tab, and then click Database in the left pane. Select the name of your database server.

In the General information tab, displayed by default, click on ... to the right of “RAM”, then on Change the amount of RAM to access the order for this modification.

private-sql

Choose the amount of RAM you want, then click Next. You can then choose the duration you want.

The remaining term until expiration will be prorated. This pro rata calculation will be based on the expiration date of the Private SQL server, not on the date of the purchase order.

Once you have confirmed your contracts, you will be redirected to the purchase order to pay for this change. It will then be effective within a few hours.

If you currently have a free Private SQL server available as part of a Performance hosting, it will no longer be free after changing your offer.

Modifying my database server’s configuration

In your OVHcloud Control Panel, go to the Web section, and then click Databases in the left pane. Select the name of your Private SQL server.

MySQL and MariaDB instances

  • Click on the Configuration tab.

In the General configuration of MySQL box, you will see the configuration currently set for your database. You can modify it directly, then click Apply.

private-sql

  • Tmpdir: Directory of temporary files. "/dev/shm" is the instance’s RAM. "/tmp" is the instance’s hard drive.
  • MaxAllowedPacket: The maximum packet size.
  • Max_user_connections: The number of concurrent connections authorised per user.
  • AutoCommit: Sets whether requests are automatically committed or not.
  • Interactive_timeout: Time (in seconds) for which the server will wait for activity on an interactive connection before closing the connection.
  • InnodbBufferPoolSize: The selected buffer memory size.
  • MaxConnections: The number of concurrent connections authorised on Private SQL.
  • Wait_timeout: Time (in seconds) for which the server will wait for activity on a non-interactive connection before closing the connection.
  • Event_scheduler: Is used to trigger the execution of requests programmed directly on the MySQL server.

When you encounter an error on your website stating "Too many connections", this is due to the number of simultaneous connections on your database server being exceeded. You can then increase the MaxConnections variable if it is not at its maximum.

Tmpdir:
- /dev/shm: The database server will allocate half of its RAM to this directory for higher performance.

- /tmp: The server will allocate unlimited space on its hard disk for this directory, but this will be much less efficient. We recommend using this directory only for occasional heavy operations.

Make the necessary changes, then click Confirm.

Any changes require a restart of the database server.

PostgreSQL instance

You cannot modify the configuration of a PostgreSQL instance.

However, you can enable extensions for your databases. To do this, go to the Databases tab, and click on the table icon for your database in the Extensions column.

private-sql

Change the MySQL, PostgreSQL or MariaDB version of the database server

To find out the version of MySQL, PostgreSQL or MariaDB of your database server, you must go to the General information tab after choosing your database server.

The current version appears in the Version row.

To edit this version, click Update version.

private-sql

How do I know the exact version of PostgreSQL I am using?

Enter this command in phpPgAdmin by clicking on Your database in the SQL section, then click Launch:

select version();

How do I know the exact version of mySQL or MariaDB that I am using?

To do this, enter this command in phpMyAdmin, in the SQL section, then click Run:

show variables like "version";
  • Before migrating to a higher version, ensure that your database is compatible with the version you have chosen.
  • The modification will be effective in a few minutes.

It is not possible to switch from an old version to the latest version directly. It is mandatory to use all intermediate versions.

Logs and metrics

Query runtime statistics

This allows you to view the query execution time on your database server in the last 24 hours.

In your OVHcloud Control Panel, go to the Web section, and then click Databases in the left pane. Select the name of your Private SQL server.

Go to the Metrics tab for your database server. You will find the graph Query Execution Time Statistics.

private-sql

Access to Slow Query logs

Definition of slow query log

These are the queries that take longer to run. The value is set to 1 second on our database servers in the variable “long_query_time”.

These logs, labelled "slow-query.log", can be retrieved from the root of the SFTP space of your database server.

In your OVHcloud Control Panel, go to the Web section, and then click Databases in the left pane. Select the name of your Private SQL server.

In the General information tab, you will find the SFTP section in the Connection information box.

private-sql

To log in via SFTP, you can do so via the FileZilla software, using this guide.

If this file is empty, you do not have any slow queries.

Monitoring the RAM used

In your OVHcloud Control Panel, go to the Web section, and then click Databases in the left pane. Select the name of your Private SQL server.

Go to the Metrics tab in the OVHcloud Control Panel. You will find the graph RAM usage statistics.

private-sql

Number of connections per minute

This graph allows you to track, over the last 24 hours, the load of connections per minute on your database server.

In your OVHcloud Control Panel, go to the Web section, and then click Databases in the left pane. Select the name of your Private SQL server.

Go to the Metrics tab in the OVHcloud Control Panel. You will find the graph Statistics for total connections per minute.

private-sql

Managing your databases

It is recommended that you maintain your database to ensure high performance. Performance refers to the fact that the information contained in the database is most quickly returned to the script that requests it. This requires a structured and optimised database.

Indexing a database

To increase the speed of searches during a query, you must index the fields that are used in WHERE clauses.

Example: you do a regular search for people in relation to the city. Index the “city” field with the following query:

ALTER TABLE 'test' ADD INDEX ('city')

Purging a database

Some of your data is no longer being accessed? By archiving it, your tables will be less crowded and searches will go faster.

Display limit

Limit the display of records to a fixed number (for example: 10 per page) with the "LIMIT" portion of your SQL query.

Query grouping

Group your queries at the beginning of the script this way:

open_connection
request1
request2
...
close_connection

Display...
Treat data
Loop through data...
Display...
...

Retrieve only useful data

In your SQL queries, make sure you select only what you need, and especially that you have not forgotten the links between the tables.

Example:

(where table1.champs = table2.champs2)

Avoid options that consume too many resources

Avoid using "HAVING" for example. It increases your requests. Similarly, avoid using “GROUP BY”, unless it is strictly necessary.

Go further

IP address list for Web Hosting clusters

Join our community of users on https://community.ovh.com.


These guides might also interest you...