- 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 System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINS in SQL
MySQL Configuration Files:
MySQL Configuration Files:
1./etc/my.cnf
2./etc/mysql/my.cnf
3.SYSCONFDIR/my.cnf
4.$MYSQL_HOME/my.cnf
5.~/.my.cnf
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
Note
The trailing d character after mysql is required to apply settings to the MySQL service daemon. The [mysql] header without a trailing d character is incorrect and applies only to MySQL client.
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
Important:
Refrain from making several optimization changes to your existing MySQL config at one time. This is especially true when adjusting production level servers. Making small incremental changes will make it easier to identify problems with individual changes and isolate settings that may not work well for your unique setup.
Routine Maintenance
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.What are some tasks to perform routinely:
- 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.
Performance Expectations
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.