A few years ago, Stephane Combaudon wrote a blog post on Ten MySQL performance tuning settings after installation that covers the (now) older versions of MySQL: 5.1, 5.5 and 5.6. In this post, I will look into what to tune in MySQL 5.7 (with a focus on InnoDB).
Those are a good starting point for any new installation. There are a number of other variables that can increase MySQL performance for some workloads. Usually, I would set up a MySQL monitoring/graphing tool (for example, the Percona Monitoring and Management platform) and then check the MySQL dashboard to perform further tuning.
MySQL 5.7 Performance Tuning Immediately After Installation
There are of course other settings that can make a difference depending on your workload or your hardware: low memory and fast disks, high concurrency, write-intensive workloads, for instance, are cases when you will need specific tuning. However, the goal here is to give you a few MySQL performance tuning to allow you to quickly get a sane MySQL configuration without spending too much time on changing non-essential MySQL settings or on reading documentation to understand which settings do matter to you.
This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.
Before continuing, please look at the following MySQL performance tuning articles: MySQL Database Performance: Avoid this common mistake and note that due to the limitations of MySQL query cache, it has been deprecated as of MySQL 5.7.20 and is removed in MySQL 8.0.
MySQL performance tuning in terms of software involves configuring MySQL server options, increasing the performance of MySQL queries, tuning MySQL indexes, switching to the MySQL InnoDB storage engine, etc. Let's consider all these in detail.
One of the first tuning tips for those having a heavy load on their database would be to try switching to InnoDB from the MyISAM storage engine. Having a clustered index, with data in pages and consecutive physical blocks, InnoDB has better performance for large volumes of data as compared to MyISAM.
InnoDB also boasts a rich set of variables and advanced settings that can be configured to improve MySQL performance even further. InnoDB performance settings are more extensive and thus there are more ways to tune InnoDB for higher performance as against tuning MyISAM.
With dbForge MySQL performance tuning tool, you can: Optimize queries with the EXPLAIN plan
Monitor session statistics
Compare query profiling results
Identify the most expensive queries
Running MySQL at optimal settings for specific resources helps handle larger server loads and prevents server slowdown. Generally, after tuning Apache to handle larger loads, it is beneficial to tune MySQL to additional connections.
Your goal in MySQL performance tuning is to drive the query with the table that returns the least data. In other words, you compare the number of rows in the final result set to the number of rows the query examined.
Your new settings are now active and you can run the script from above again and see the difference in your results. After some experimenting I've found that it is useful to look at the script results right after making a change just to see if your modifications were recognized by the system and get the early returns from whether things were improved or not -- but, to get a truly accurate reading from the script you should check back in 24-48 hours after rebooting mysql (this is actually noted at the top of the script itself, but it doesn't really explain why) depending on your site traffic. Also, I've found that the way I've got Drupal set up it is particularly demanding in the tmp_table_size and table_cache areas (e.g., you may want to bump up the number for both of these areas in the settings above)
The tables of the sys schema distill the information of the performance schema into a more user-friendly, readable form. Its ease of use makes the sys schema ideal for ad hoc investigations or performance tuning, as opposed to programmatic access.
My conclusion was, that during the (development) usage of the mysql docker the performance might have become worse and worse. Maybe the index files are getting corrupted by stopping a stack with ctrl-c rather than docker-compose down.
Debian and Ubuntu installation does not automatically create a special debian-sys-maint user which can be used by the control scripts to control the Percona Server for MySQL mysqld and mysqld_safe services like it was the case with previous Percona Server for MySQL versions. If you still require this user you must create the user manually.
By default, cached scripts are revalidated on access to ensure that changes on disk take effect after at most 2 seconds. Since Nextcloud handles cache revalidation internally when required, the revalidation frequency can be reduced or completely disabled to enhance performance. Note, however, that it affects manual changes to scripts, including config.php. To check for changes at most every 60 seconds, use the following setting:
Image optimization can take place both inside and outside of WordPress and is essential for any WordPress performance tuning strategy. To help reduce the strain on your server, ensure that images are sized no larger than the maximum size allowed on the site. This will keep unnecessarily large media files from overwhelming the server.
Have you considered that the hardware and software powering your web hosting plan might be the culprit? If all the data shows your site performing reasonably and as expected, your server might be in need of a reconfiguration and some serious WordPress performance tuning.
When setting up your initial caching configuration, did you stick to the standard browser and server caching settings? If so, revisit your options now as a part of your WordPress performance tuning efforts, as there is a much broader expanse of website caching that can be used to optimize your server and databases.
That said, establishing multiple servers for your website might not be the most economical or reasonable choice right now. One suggestion the WordPress Codex makes that you can include in your WordPress performance tuning plan, in that case, is to distribute your files across various hostnames. That way, each hostname will handle different types of content; JavaScript and CSS in one, image files in another, and so on.
HTTP/2 is the next generation of the hypertext transfer protocol. A couple of years ago, this was something you had to take care of switching to in order to reap the benefits of the WordPress performance tuning that came from it. However, most sites should be on it now (especially if you have an SSL certificate configured).
The configured tungsten user must be able to read the MySQL configuration file (for installation) and the binary logs. Either the tungsten user should be a member of the appropriate group (i.e. mysql), or the permissions altered accordingly.
Parsing of mysqld_multi configuration files is not currently supported. To use a mysqld_multi installation, copy the relevant portion of the configuration file to a separate file to be used during installation.
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
To improve performance issues of short queries on the thread pool, you can enable batch execution. Instead of returning back to the thread pool immediately after running a query, threads will keep active for a short time to wait for the next query through this connection. The thread then runs the query rapidly and, when this is complete, the thread waits for the next one. This process continues until the overall time spent exceeds a threshold.
MySQL tuning refers to the process of improving the performance of SQL statements in the database for better, more accurate, and faster results. MySQLTuner can be used to improve the query performances as well as verify the installation.
TiFlash v6.1.0 introduces the ALTER TABLE ... COMPACT statement, which provides a manual way to compact physical data based on the existing background compaction mechanism. With this statement, you can update data in earlier formats and improve read/write performance any time as appropriate. It is recommended that you execute this statement to compact data after upgrading your cluster to v6.1.0. This statement is an extension of the standard SQL syntax and therefore is compatible with MySQL clients. For scenarios other than TiFlash upgrade, usually there is no need to use this statement.
To enhance performance in OLAP scenarios, dynamic pruning mode is supported for partitioned tables. If your TiDB is upgraded from versions earlier than v6.0.0, it is recommended that you manually update statistics of existing partitioned tables, so as to maximize the performance (not required for new installations or new partitions created after upgrade to v6.1.0).
You can use the SHOW ANALYZE STATUS statement to show cluster-level statistics collection tasks. Before TiDB v6.1.0, the SHOW ANALYZE STATUS statement shows instance-level tasks only, and history task records are cleared after a TiDB restart. Therefore, you cannot view history statistics collection time and details. Starting from TiDB v6.1.0, history records of statistics collection tasks are persisted and can be queried after a cluster restart, which provides a reference for troubleshooting query performance issues caused by statistics anomalies. 2ff7e9595c
Comments