What is a Database and how it works?
A database is a collection of data organized following methods defined by its developers. In the most commonly used architecture, a typical database will be storing data in rows which on the other hand are contained in a table. In simple words Database -> Table -> Row of data.
Further reading:
How to Create a MySQL Database, a User and Delete Database in Cpanel
Backup mysql database tutorial
How to Reset or Change to a New CPanel Password
How to Use the Hosting Features in cPanel control Panel
How to check your PHP version and configure your PHP settings
How to optimize your website to use less server resources
Every database can contain either one or multiple tables depending on the database design, which typically a database engineer is responsible for. Every table can contain as many columns as needed again depending on table design. The columns are like in an ordinary table representing certain criteria for storing data. For example, a commonly used table for storing user information will contain columns like:
- Id
- Username
- Password
- Name
- Phone
Whenever a new row for a brand new user is inserted in the database, every column will be populated with data for that specific user forming a row at the end. Whenever the application needs to retrieve the data from that row (for example on user login) the database will be queried by the web application and a result set will be returned to the application.
But where does all that data gets physically stored?
A typical Database Management System is storing all of its components directly on the file system, of course, separating the databases into different folders and the tables in different files. Logically if the databases are stored on the file system, then this means they use space.
MySQL databases and Shared Web Hosting
MySQL Databases are included by default in each and every Web Hosting Plan we offer. Although there are no limitations for MySQL Databases on our VPS and Dedicated Server Plans, our Shared Web Hosting packages are metered based on these 3 Database parameters:
1. Database Size
2. Database Table Size
3. Database Queries Execution times
In the next tutorials of these Series, we will cover everything you need to know in regards to the database of your Web Hosting Package and most importantly how to maintain those in a healthy condition. This will also help you speed up your website resulting in a better experience for your visitors.
Further reading:
How to Create a MySQL Database, a User and Delete Database in Cpanel
Backup mysql database tutorial
How to Reset or Change to a New CPanel Password
How to Use the Hosting Features in cPanel control Panel
How to check your PHP version and configure your PHP settings
How to check MySQL Database Size
Relation between Database Size and Hardware Resources
As we explained in the Introductory section, in order for a typical MySQL database to exist, it should be stored on the file system of the Web Hosting Server. This means that a portion of the allocated space for each Web Hosting Package will be used by the MySQL databases maintained by the Shared Web Hosting users.
Furthermore, whenever a database gets queried by a web application, it uses a different amount of hardware resources like CPU, RAM, and I/O operations. These are required so the MySQL database management service can execute the query against the database and provide the result to the web application that sends it.
If the query targets large collection of rows (for example it selects all the rows in a table), then the MySQL service will require more resources to find and prepare the rows for the web application.
Therefore, we can surely say that the bigger a database is, the more resources the queries sent to it will need.
How to check your database size?
To check the sizes of your databases, you will need to access the cPanel service for the Web Hosting package you have purchased with us. You can find more details on how to do that in the Welcome email, or you can directly access it via the Dashboard of your Client Area.
The cPanel service offers a vast amount of features, and you will need to locate the MySQL databases feature specifically. This can be done pretty easily by typing MySQL in the search bar of cPanel.
The MySQL Databases feature will offer few options separated in different sections. The section you are looking for here is called Current Databases where you will find a table populated with all the databases you currently have and more importantly their sizes.
Unfortunately, cPanel does not offer the option to order the databases by their actual size so please bear in mind that the databases in the table are by default sorted by name and not by size. Ideally you would want to show all the databases on a single page considering the pagination at the bottom of the Current Databases section.
Once you identify the big databases, you can go ahead and optimize their size which we will review in other tutorials of these Series. /
Observer Reports
As we have mentioned there are certain limitations in regards to the database sizes for our Shared Web Hosting users. Depending on the used Web Hosting package the limitations are growing exponentially, but most importantly our customers will always be notified when reaching or being close to reaching a certain limit.
The system that takes care of reporting and resolving Web Hosting Limits related issues is called Observer and you might have already seen it in the Client Area section of our website.
Lifecycle of Observer Reports for Database size
To better understand how the Observer Reporting service works we will explain all the reports you will receive in regards to the size of your databases.
- Firstly, if any database on your web hosting account reaches 75% of the allowed size per database of the used Web Hosting Package, the Observer service will open a Notice type of Report providing detailed information for the Database Size limit.
- Secondly, if 90% or more of the allowed size per database is being reached the Observer service will open an Alert type of Report reminding the customer of the ongoing issue and the potential risk. If already reported as Notice database reaches 90% of the allowed size per database the Notice report will be updated and changed to Alert.
- Finally, if 100% of the allowed size per database is being reached the Observer service will open an Incident type of Report informing the customer that the web hosting package has reached it's potential. 72 hours after that report has been filed and if the customer does not take action, the system will update it with information that the Web Hosting Account has been temporarily put on hold due to its complete incapability of handling visitors' requests due to high resource usage caused by the big database it has.
Of course, the customer is provided with the option of temporarily re-activate his/her web hosting account. The temporary re-activation will provide the customer with access to the web hosting account for a limited amount of time so the database size issue can be addressed.
Further reading:
How to Create a MySQL Database, a User and Delete Database in Cpanel
Backup mysql database tutorial
How to Reset or Change to a New CPanel Password
How to Use the Hosting Features in cPanel control Panel
How to check your PHP version and configure your PHP settings
Reasons for a high database size and how to reduce it
We outlined that the size of the database is relative to the number of rows its tables contain. However, the question of how these entries got there at first place still remains unanswered. In the next lines of this tutorial, we will cover some of the most common reasons why your database increase in size.
The most common reasons for a big database:
Spambots
If you have never heard of what a spambot is - it is basically a computer program that connects to websites for spam purposes.
But why those are causing the space to your database to grow?
All the modern websites are storing their data (Like blog posts, articles, comments, etc.) in a database. This means that any data added to a website no matter by an Admin or Regular User will also go to the database. Combine that with an insecure registration form allowing registrations without human verification (captcha challenge), and the equation gets completed.
It is pretty simple - the spambots are registering accounts on a website which grants them with regular user privileges like posting comments and sometimes even creating pages (mainly on social platform type of websites). From there they can inject as many comments and content as they are configured to. Since all the data is being stored in a database as we already mentioned then the database will get filled with thousands of meaningless rows causing for its size to grow needlessly.
How to protect your database from spambots?
As you want only legitimate users to be able to post content on your website, you will have to configure:
- Captcha Human verification on login/registration forms
- User Level Privileges for posting content/comments
- Content approval - comments/pages/articles
How to clear your database from spam content?
Unfortunately, there is no universal approach for clearing your database from any user-posted content. The reason for that is mainly because the added spam content is mixed with all the legitimate content on your website. The best option here would be to contact a developer who can review the entries one by one and remove those that are spam related. Of course, the cleanup should be performed only after you are sure that the door is closed for new content posting.
Cache
Another thing that some websites do is placing their cache in the databases they are using. Why? Well, the database is answering requests way faster than the storage, which means that it will deliver cached content faster. Although that is no longer a common practice, it is still a thing for some custom scripts.
How to clear your database from cache entries?
You can identify the cache table and "Empty" it out. Emptying that table should not on theory cause issues with your website since any missing cache will be regenerated. However, that should be done only after the database cache is stopped or at least changed to File System cache. Of course, the alternatives for caching are quite a lot like APC or even Memcached for example.
Logs and Analytics Data
We are combining these two under the same topic because they are generated basically the same way.
There are websites that are using their databases for storing logs for example for:
- Access - Login/Logout
- Registrations
- Errors
- Admin Actions
- Backups
Those are important indeed, however storing those in the same database as the one your website actually utilizes is pretty much destructive for your visitors. The logs will not only increase the size of your database but if multiple logs are constantly updated this can cause uncontrolled and exponential growth. Often such logs are the Access or Registration logs which are recording, for example, each login attempt or a registration. However, if the website Login/Registration forms are not protected, and a spambot hits those, the log tables will surely be filled with quite a number of rows.
The analytics data is the very same - it logs information for your users' activity on your website similarly to the logs. However, since it aims for that information to be graphically presented to the admin of the website it often relies on the database of the website when storing the data.
How to prevent logging in the database?
For that, you will need to either contact the developer of your application or check with the author of the Open Source script you are using. Often the logging is defined in some sort of configuration file which you will need to edit or even sometimes defined in configuration tables stored in the same database.
Sessions
Writing user sessions to the database is a good idea indeed, but the issue with that is if those are not cleared. Typically every user logging on your website is creating a session which is then used by the cookie added to the browser of the visitor. On theory every time a user logs out the cookie gets destroyed, and the session should be too, however in practice sometimes for the developers is enough to destroy only the cookie from the client's browser and leave the session on the server. If the sessions are preserved in the database, then those will fill it up eventually.
How to prevent sessions filling the database?
For that, you will have to change the way how sessions are being stored - from a Database to File and that will be sufficient. Usually, that configuration is kept either in a configuration file or directly in the database. This will allow for the sessions to be actually written directly on the file system allowing for manual deletion either by the server admin or by the website administrator.
Images
MySQL databases typically allow for storing even image files. However, there is a conceptual issue with that since the modern images are quite big. If your website uses the main database for storing let's say any user uploaded images like for example profile pictures or any custom uploaded images this will cause for the database size to grow. In that case, even if you identify the table is storing those images, you will be unable to clear it since you are clearing user uploaded data.
How to prevent images being added to the database?
Again here the only solution is to contact the developer of your website or the authors of the open source application you are using because only changing a column type in MySQL will only cause for certain functionalities on your website to break. Therefore, this needs to be addressed by a developer since all of the upload forms have to be rewritten.
Excessive Tables Quantity
Sometimes the size of your database is not caused by a few large tables but by lots of small ones. This is a typical result from too many plugins being installed on the website or if for example, and additional database import is being performed on a wrong database.
How to reduce the number of Tables?
First and foremost, you will have to uninstall any plugins/modules that are not used on your website. Then you will have to look if there is a patter since the tables can also be created by a regular script. This means that your website might be creating database tables with different purposes. Ideally, you would want to strip all the needless tables, and in case you need developer assistance you should definitely get some.
