Author: Robert Agar
Most websites that have any real utility are at least partially constructed with applications backed by databases. When you make a purchase on the web or look up information, you are interacting with a database application. The databases are used to store inventory, sales, and customer data and are essential components of the e-commerce world we live in.
Websites are built taking certain assumptions into account. One of these is the expected volume of traffic that the site will attract. Some amount of research should have been done regarding the number of visitors to expect so the server can be sized correctly and any necessary optimizations can be made to the database. This research cannot always predict the true demands that users will put on the databases or applications fronted by the website. Things can change suddenly and unexpectedly.
Recent events surrounding the spread of the COVID-19 or Coronavirus demonstrate this fact in all too concrete terms. While the world struggles to cope with a public health crisis, many websites that may have previously seen sporadic use are now being called upon to provide information or services to a tremendous influx of visitors. Sites that educate visitors on ways to keep safe, furnish health-related products, or allow families to shop from home to avoid unnecessary contact with strangers are all being put under the strain of accommodating massive increases in traffic.
MySQL is used to power many websites and is familiar to a large percentage of DBAs. They may be called upon to find methods with which to address the additional strain put on their databases by this type of change in traffic patterns.
MySQL Websites
MySQL is one of the most popular relational database solutions available and is widely used for making websites. Many reasons make MySQL an attractive database solution for creating websites. Some of them are:
- MySQL is a free solution;
- It can be run on many different operating systems;
- Basic SQL is used, making it easier to use without specialized knowledge;
- Straightforward setup and configuration;
- Query caching and full-text indexing and searching;
- MySQL is a mature and stable solution;
- The platform enjoys a large community of developers.
These factors contribute to the popularity of MySQL as the database platform for web-based applications.
Tuning MySQL Websites for High Traffic
When tasked with the need to implement optimizations to MySQL systems, database teams have some focus areas that may achieve some immediate effects. Configuration changes made to the /etc/my.cnf file can help the performance of databases that are experiencing a higher than anticipated number of users.
One strategy that can help is to keep the maximum amount of data and indexes cached in RAM. If InnoDB is your stage vehicle, the innodb_buffer_pool_size variable needs to be set appropriately. After determining how much data is being used by InnoDB, you have a guideline as to the size of your buffer pool. You should aim to accommodate the full size requirements of InnoDB without using more than 50% of available memory. If this cannot be accomplished, it may be time to consider more memory. Using too much memory for InnoDB will negatively affect the system’s performance in other ways.
Similar changes can be made if MyISAM is used as the storage vehicle for your database. In this case, key_buffer_size is the variable that needs to be addressed. It should be set to less than 30% of the total RAM available.
Some other optimization techniques include disabling comments which require extra database calls, minimizing database queries, and modifying how you cache MySQL queries. Increasing the value of the query_cache_size variable allows queries to be cached for a longer time and results in reduced database calls.
Monyog (SQL Diagnostic Manager for MySQL)
SQL Diagnostic Manager for MySQL can help you quickly find out what is slowing down database response time so the issues can be addressed. It provides real-time monitoring so you know what’s happening on your MySQL systems. You can use this knowledge to take corrective actions or proactively to identify potential problems before they impact your users. Find locked and long-running queries that might be dragging the whole system down and kill them if necessary.
This versatile monitoring application has over 600 monitors and advisors that check system health continuously and send alerts when defined thresholds are met. You can create customized dashboards and charts so you are advised how specific areas of concern are performing. SQL Diagnostic Manager for MySQL can be instrumental in enabling your database team to get the best performance out of your systems and deal with high-volume traffic demands.
