Database systems often need tuning for best performance. Lukas Vileikis explains to how to optimize my.cnf for MySQL performance. Show
Since the dawn of software engineering, developers turned to databases to store their data – as time went on, some developers were able to choose their path more clearly: some started using PostgreSQL, some chose MySQL or its flavors (MariaDB or Percona Server), some went for NoSQL-based solutions such as MongoDB. Those developers that elected to use MySQL, however, quickly noticed that the database management system is not that powerful by default and started searching for ways to improve its performance, security, and high availability beyond its default capabilities; as they found themselves working towards this goal, they found that the answers to the majority of their questions lie in one file – that file was called my.cnf. What is my.cnf and what does it do?In order to answer the question how should you optimize your my.cnf file for performance, I’ll start with the basics. The my.cnf file is the main configuration file available within MySQL and all of its flavors. The file contains various parameters that can be configured according to requirements. These parameters will then be loaded onto MySQL as the database management system starts up. There are multiple locations where the my.cnf file could be located (the locations differ depending on what flavor of Linux is used). MySQL usually loads the settings from the file after finding it in one of the following locations:
The path to the directory containing my.cnf can also be defined by changing the value of the Once opening the my.cnf file, you should see something similar to the following:
Image 1 – some of the settings available within my.cnf As you can see, the file houses a whole lot of parameters – some of those are more crucial for performance, others less. The Windows version of my.cnf (my.ini) is also widely known for the fact that the file includes a bunch of comments (see image above). The Windows version explains pretty much everything, while Linux isn‘t so generous; in many cases, Linux only provides the ‘skeleton’ settings that are most frequently changed and that are related to InnoDB (one of MySQL‘s primary storage engines). One statement is true regardless: to optimize the performance of MySQL, optimize the settings within my.cnf. Some of you might remember the earlier article about the nuances of indexes in MySQL. That article mentioned that one of the primary storage engines available within MySQL, InnoDB. There are a couple of parameters in the my.cnf file that are crucial for InnoDB’s performance and that need to be optimized to get the best out of MySQL, and the statement rings true for all storage engines available within the database management system. Engineers working with MySQL usually choose the storage engine they want to use according to their needs, modify cnf.ini file to get the best out of the engine, and restart MySQL. Once MySQL is restarted, engineers can enjoy better performance, availability, and, in some cases, security benefits. How to optimize my.cnf for performance?When looking at everything from a broader perspective, optimizing my.cnf for performance isn’t that hard. Everything comes down to two things:
That’s it, really – nothing that complex! Begin by choosing a storage engine with this table to simplify your choices: Storage Engine When to Use? InnoDB / Percona XtraDB InnoDB should be chosen when the database supports a general use case software product or if you’re not sure what to use to get the best out of MySQL. MyISAM Obsolete storage engine, should only be used if you need the exact row count in a specific table. If used as the main storage engine, MyISAM can become the cause of crashes and lost data. MEMORY Such a storage engine stores all data in memory. CSV Such a storage engine stores data as CSV files. MERGE (formerly MRG_MyISAM) A collection of two or more identical MyISAM-based tables that are supposed to be used as one table. ARCHIVE The storage engine is supposed to let us archive data. FEDERATED The storage engine lets us access data from a database without using replication or clustering. BLACKHOLE The storage engine should be used for testing purposes – all data contained inside of it will be gone in an instant. EXAMPLE Should be used as an example of how to build new storage engines. Despite the plethora of storage engines available for use, most developers and MySQL engineers use InnoDB as it’s widely considered the best for most use cases. Earlier, MyISAM was also a pretty reliable option, but as the storage engine became obsolete due to stability issues and other things, it’s now effectively only suitable for one purpose – knowing the exact count of rows in a table (InnoDB doesn’t provide that information, but MyISAM stores the row count inside of itself.) Optimizing InnoDB for performanceThe InnoDB storage engine has these options, among others, available within my.cnf: Image 2 – InnoDB parameters within my.cnf Here’s what the parameters do, one by one: Parameter Function
This parameter depicts the location of the data file (ibdata1) relevant to InnoDB – ibdata1 holds all of the data required for InnoDB to function correctly.
This parameter outlines the buffer pool size to be used within InnoDB. The buffer pool is used to cache data and indexes of the tables within InnoDB.
Sets the size of InnoDB log files. The bigger this value is, the less recovery time is needed in case of a crash.
Used to write to the log files.
Ways to flush the log when a transaction commits. MySQL offers three values – the default (1) makes InnoDB ACID compliant, while 0 or 2 exchanges ACID for faster write speed.
The length of time in seconds a transaction waits for a row lock.
Defines the method used to flush data. The flush method is very important because it can affect I/O throughput. I won’t get into all of your choices here, but keep in mind that MySQL offers a couple of flush methods for you to choose from including, but not limited to:
And here’s how to optimize each of them to get the max out of MySQL when using InnoDB:
These are most of the parameters you need to optimize when using InnoDB. Now, restart MySQL, and the settings will take effect. However, if you elect to use MyISAM instead (perhaps you’re trying out some of its features or using it on an older, not so important server), there are other parameters you should review. Optimizing MyISAM for performanceIf you’re using Windows, scroll down from InnoDB parameters, and you should see four parameters relevant to MyISAM. If you’re a Linux user and you want to use MyISAM for any reason, though, bear in mind that you would probably need to add these parameters in manually: Parameter What Does It Do?
The equivalent of the InnoDB buffer pool size – it’s recommended to set this value to approximately 40% of the available memory on the server.
Defines the size of the buffer that is allocated when sorting operations are performed. Raising this parameter from its default value might help with improving the performance of ORDER BY and GROUP BY operations – other than that, this parameter is important when creating indexes or repairing tables.
Defines the size of the buffer that is allocated for each thread that does a full table scan (when a database is finding a value by reading the table in sequential order.)
This parameter is used for some sorting operations in order to read rows after sorting is accomplished. The parameter can be used by all tables, not only MyISAM – for more information. Other parameters within my.cnfOnce you have optimized either (or both) InnoDB and MyISAM for performance, you can also look a little into other parameters available to be configured within my.cnf. Parameter Importance
Leaving this parameter inside of my.cnf without any value (or including this parameter inside of the file) disables the FEDERATED storage engine within MySQL.
This parameter defines the directory from where files can be loaded into MySQL when using the LOAD DATA INFILE command – if the wrong directory is specified, MySQL will respond with an error.
This parameter can be used to set the SQL mode to use when MySQL is running. For example, the “ANSI” parameter changes MySQL’s behavior to behave like standard SQL, including “ALLOW_INVALID_DATES” will disable the checking of dates while specifying no value will set the SQL mode to strict. Refer to the documentation for further explanation.
The “skip-ssl” option enables MySQL to use an unencrypted connection. The “ssl” option, on the other hand, will specify the fact that MySQL will attempt to connect via SSL and fail if a secure connection cannot be established.
Defines the location of the data directory of MySQL. Can be redefined – redefining this parameter can be useful for security reasons, though not much apart from that.
Defines the location of the error log file within MySQL. The location of the error log file can be changed using this parameter, though doing so could be considered “security through obscurity.”
Defines the default storage engine used by MySQL.
Some of the more interesting parameters dealing with error messages. The former defines the location of the directory containing language files to be used within MySQL when errors are encountered, while the latter defines the language of the error messages. Redefining these parameters might be very useful for MySQL installation is used by those who cannot speak English or for other reasons. The given table should give you a rough idea of just how powerful my.cnf really is. Not only can it be used to improve performance for various storage engines, including InnoDB and MyISAM, but the parameters contained within the file are also very useful to complete various kinds of operations. One thing shouldn’t be forgotten, though – whenever you change the values of any parameter contained inside of my.cnf, you should restart the server to be sure that your changes have taken effect. Optimizing my.cnf for MySQL performanceI hope this article has given you an idea of what a powerful beast my.cnf can be when used properly. The parameters within my.cnf are frequently used to improve the performance of various storage engines as well as queries, they can be used to set up replication and make MySQL capable of doing various kinds of other things. I hope that this article has helped you learn how you should use the parameters within my.cnf to adjust MySQL and make it perform better than before. Make sure to take the advice in this article into account and test the waters before performing any actions on a production server, and I’ll see you in the next one! Robert Sheldon 07 October 2022 Robert Sheldon 07 October 2022Introducing the MySQL INSERT statement 3 3 Robert Sheldon 05 January 2023 Robert Sheldon 05 January 2023Introducing the MySQL common table expression 2 Langkah kerja MySQL?Cara kerja MySQL. MySQL membuat database yang dapat memodifikasi, menyimpan data, dan menentukan keterkaitan tabel-tabel yang ada di dalam software.. Kemudian, perangkat pengguna membuat request dengan perintah spesifik menggunakan bahasa SQL.. Terakhir, server akan menerima dan menjalankan perintah.. MySQL pakai bahasa apa?MySQL atau dibaca My Sequel merupakan sebuah Database Management System atau sering disingkat DBMS yang dijalankan menggunakan perintah SQL (Structured Query Language) yang populer digunakan untuk pembuatan aplikasi berbasis website.
Apa itu MySQL query?MySQL adalah Sebuah program database server yang mampu menerima dan mengirimkan datanya sangat cepat, multi user serta menggunakan peintah dasar SQL ( Structured Query Language ). MySQL merupakan dua bentuk lisensi, yaitu FreeSoftware dan Shareware.
Bagaimana cara membuat database di MySQL?Untuk membuat database MySQL baru, masuk cPanel dan klik menu MySQL Databases:. Pilih nama database. Prefix defaultnya adalah "yoururser_". ... . Scroll ke bawah dan cari Add New User. Masukkan username dan password, lalu klik Create User:. Cari opsi Add User To Database untuk menghubungkan User ke Database. ... . Selesai!. |