Configuring your database server

Objective

With the Web 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

• A Web Cloud Databases instance (included in a Performance web hosting plan).
• Access to the OVHcloud Control Panel

Instructions

View general information on your database server

In your OVHcloud Control Panel, go to the Web Cloud 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.

Manage your access

You can access your Web Cloud Databases from your OVHcloud web hosting plans or from the public network.

Authorise an IP address

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

In your OVHcloud Control Panel, go to the Web Cloud Databases section, then select the Web Cloud Databases instance concerned. Click on the General information tab.

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

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.

Authorise connections to OVHcloud Web Hosting plans

For an OVHcloud web hosting plan, you can simply tick Authorise OVHcloud web hosting plans to access the database.

Modify your Web Cloud Databases offer

To modify the solution for your Web Cloud Databases instance, go to your OVHcloud Control Panel. Click the Web Cloud tab, and then click Web Cloud Databases. 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.

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

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.

Modifying my database server's configuration

In your OVHcloud Control Panel, go to the Web Cloud section, and then click Web Cloud Databases. Select the name of your Web Cloud Databases 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.

• 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 Web Cloud Databases.
• 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.
• sql_mode: The sql_mode option affects the supported SQL syntax, and the data validation performed by MySQL/MariaDB.

NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Make the necessary changes, then click Confirm.

PostgreSQL instances

• Click on the Configuration tab.

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

• log_min_messages: Controls the levels of messages to be logged in the server logs. The levels available for a Web Cloud Databases solution are:
  • WARNING: Provides warning messages about potential problems.
  • ERROR: Sends the error that caused an ongoing order to be cancelled.
  • LOG: Stores information for server administrators.
  • FATAL: Sends the error that caused the current session to end.
  • PANIC: Sends the error that caused all sessions to end.

Each level includes all levels that follow it. The higher the level, the fewer messages are recorded in the server logs.

By default, the value set is WARNING because it includes the values ERROR, LOG, FATAL, and PANIC.

You can also 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.

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.

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";

Logs and metrics

Log access

To access the logs for your Web Cloud Databases solution, please refer to our guide "Web Cloud Databases - How to retrieve logs".

Monitoring the RAM used

In your OVHcloud Control Panel, go to the Web Cloud section, and then click Web Cloud Databases. Select the name of your Web Cloud Databases server.

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

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 Cloud section, and then click Web Cloud Databases. Select the name of your Web Cloud Databases server.

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

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

For specialised services (SEO, development, etc.), contact OVHcloud partners.

If you would like assistance using and configuring your OVHcloud solutions, please refer to our support offers.

Join our community of users.