We could write books about MySQL and every book could have a different focus: setup, optimization, backup, etc.
So, this review will be about sharing some of the experiences that I have had.
So, MySQL is probably the most used RDBMS for web applications.
The server is really easy to install, literally a straightforward installation.
It is very fast and stable and at the same time not resource hungry compared to some other RDBMS products.
For smaller projects, you will not even need to do any "after install" tweaks or configurations, just install and you are ready to go.
Nevertheless, for some more demanding projects, you will need to investigate and optimize your setup to fit the requirements.
What is the best configuration for a MySQL server?
There is no single best configuration. There are many of them, depending what is MySQL used for. E.g., it is not the same as having it installed in a shared hosting environment or as a dedicated instance to just one application. Having 1000 of users using a few hundred different databases or having 20 users using one database.
Is it gonna be an OLTP or OLAP, do you focus more on writing or reading? I will not dive into this, as there are many other articles covering this in a much better way than this article.
But, starting by selecting the adequate storage engine MyISAM or InnoDB, defining the maximal number of connections, all the buffer and caching settings, limit on open files, table caching, etc. is always a good approach.
You will need to sit down and write down all the requirements known to you and then aligned to those, start configuring your server.
A very handy tool to investigate your server's setup and performance is the MySQLTuner.pl script. It can be used as a starting point to investigate your server setup and increase performance.
Sometimes, although your server is configured properly, you might experience a slow performance, especially on a shared hosting server. During my career as a system administrator in a hosting company, we have monitored our MySQL servers and tracked the performance. Often, in such an environment, it might happen that you have a "running away" query, eating up your resources or causing performance issues. In such a case, database optimization is required, rather than server a config. A good example for this is having a well-visited web application with many concurrent users. Often on such an application, there are queries which run more frequently than others.E.g. presenting top 25 products in a web shop by using a query similar to this:
SELECT p.name, c.name
FROM product p
LEFT JOIN category c ON ( c.id = p.category_id )
GROUP BY c.name, p.name
ORDER BY p.name ASC
LIMIT 25 ;
Such queries will run very fast if the database itself is optimized. But otherwise, they can run really slow and cause high CPU utilization or in worse case a bottleneck and slow down the whole server. You don't need to have millions of records, this can happen even if you have only a few hundreds/thousand records in your database. In such case, you need to identify these "slow queries" (you can log slow queries) and then investigate the database they are referencing. Often it's about missing indexes on columns involved in GROUP BY, ORDER BY or WHERE clause.
Backup? Of course, you already have the backup in place and it's running, at the end we are talking about the database. There is always a backup policy when a database is involved. Many use MySqlBackup command to schedule a "hot backup" of a server. Although it works in 99% of cases, please consider having a mysqldump running in parallel. Why? It might happen that you have a corrupted InnoDB tables and you are backing them up. By the first server reboot, it might lead that you lose data.
When databases are involved, it is always good to have the data backed up in a flat structure if possible, if we are not talking about huge databases and the dump would consume too much time. This gives you the ability to import the data in a new instance, if you face any scenario where you have issues with corrupted storage engine and cannot recover from that.