MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINs in SQL
The majority of work needed when adjusting the MySQL server is editing the applicable directives within a MySQL configuration file. There are multiple, optional configuration files that MySQL looks for when starting up. They are read in the following order:
Any of these files can contain MySQL server directives. For the sake of simplicity, this article assumes the default file /etc/my.cnf is being used for all examples. This is the most widely known file path in the majority of cases. However, some Linux distributions favor others than the ones listed from above. Though the file location is changed in these other distributions, the syntax and recommendations in this article series apply equally to all Linux based systems.
MySQL Configuration File Syntax
Each entry inside the configuration file applies to the most recent section header. Section headers are made up of a single line with the name of the header encapsulated within square brackets (e.g. [name]). When optimizing the MySQL service daemon, changes will need to be within in the [mysqld] section header. The example below illustrates how this should look.
Example: Configuration File Syntax
[mysqld] innodb_buffer_pool_size=2G innodb_buffer_pool_instances=2
Applying MySQL Config Changes
The vast majority of directive changes merely require a restart of the MySQL service. There are a handful of other directives which may require an additional task to be undertaken while the MySQL service is offline. These tasks will be outlined in their specific section later in the article.
Restart MySQL Service:
service mysql restart
Note that with Mariadb 10.1+, servers using systemd (CentOS 7) --- `service mysql start/stop/restart` can cause issues as it calls the init script which differs from the systemd file for the service. This can cause MySQLsql services to start up resulting in dataloss. For these servers with systemd and MariaDB 10.1+ `systemctl stop/start/restart mysql` should be used.
In the follow-up articles to this part of the MySQL Performance series, we will be outlining several directives and some suggested techniques to use for configuring those directives. It is imperative to understand that MySQL Optimization is an ongoing, ever-evolving configuration. As sites grow, so do data sets and workload behavior. The settings you configure today, will eventually become obsolete and probably sooner than you would like. Because of this eventuality, it is vitally important that routine maintenance is conducted on your configuration.
- Reevaluate all buffers and directives that have been modified previously. This includes the changes discussed and recommended in the entirety of this article series.
- Reassess MySQL Query Statistics to determine workload behaviors.
- Reassess tablespace data, rate of growth or other trends in data consumption.
- Archive old data from large or heavy trafficked tables to ease the burden of read/write requests to those tables.
- Reevaluate indexes and their performance. Create new, better indexes and remove old, unused indexes.
Although, the changes recommended in this article series aim to squeeze the best performance out of MySQL, the performance increase you may see is entirely subjective. Following these recommendations should help smooth the edges on any servers hitting the bottlenecks they are designed to target. However, there is no guarantee that these changes will have a positive or noticeable impact on the application level.
The biggest issues facing application performance are often on the coding level and not the server level. The underlying MySQL server configuration only carries application performance so far. Identifying problematic queries and inefficient coding practices that serialize your workload are problems that even a finely tuned MySQL server configuration just cannot correct. If you are in doubt, or your application performance seems to suffer despite all the server level optimization in the world, then you’re probably hitting code level performance problems, and we recommend contacting a qualified Database Administrator (DBA) to evaluate your application performance properly.
- How to Use Disk Quotas in Dedicated Linux Servers With cPanel
- How to Use Disk Quotas in Dedicated Linux Servers with Plesk
- Remove a MySQL User on Linux via Command Line
- Remove Permissions for a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line
- Using MySQL Command Line to Create a User
About the Author: Jason Potter
A veteran of the IT Support field, I have more than a decade of experience in systems administration, web hosting, and cPanel servers. I enjoy writing and providing complicated technical concepts in layman terms. On my free time, I enjoy playing several types video games, automation scripting and just living life with my wife and two kids.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
What Is WebP and What Makes it Different from Other Image Formats?Read Article
Top 10 Password Security StandardsRead Article
Top 10 Password Security StandardsRead Article
How to Install MongoDB on AlmaLinuxRead Article
How to Use the WP Toolkit to Secure and Update WordPressRead Article