First I want to thank everyone who attended my November 16, 2021 webinar “MySQL Performance for DevOps“. Recording and slides are available on the webinar page.
Here are answers to the questions from participants which I was not able to provide during the webinar.
Q: Hi! We have troubles with DELETE queries. We have to remove some data periodically (like, hourly, daily) and we have short-term server stalls during these DELETEs. Server is running on modern NVMe’s so we wonder why do we have this situation. Those DELETE’s are not so large, like 10 000 – 15 000 records, but tables on which DELETE’s are performed update frequently.
A: I would test if a similar DELETE statement is slow when you run it on the development server in an isolated environment while no other session is connected to the MySQL server instance. If it is slow in this case too, check if MySQL uses indexes to resolve the condition WHERE for the DELETE statement. You can use EXPLAIN statement for DELETE or convert DELETE into a similar SELECT query and experiment.
If the DELETE statement is running fast when called in the isolated environment, check how parallel sessions affect its performance. If the tables you are deleting from are updated frequently, DELETE statements could cause and be affected by locking conflicts. To resolve this situation study how MySQL works with locks. Great presentation about InnoDB locks “InnoDB Locking Explained with Stick Figures” could be found at https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures Then you need to optimize DELETE and UPDATE statements, so they finish faster. Alternatively, you can separate them in time, so they have less effect on each other. You may also split DELETE statements, so they update fewer records at a time.
Q: Question 2. We have innodb_buffer_size set around 260Gb on the dedicated server with about 320Gb of total RAM. Still, we have 99.9% memory full and there are no other large memory consumers, only MySQL (Percona 8.0.23). The server starts and around 3 hours it takes all available memory regardless of the innodb_buffer_size setting. We never had something like this with 5.7. Do you have any ideas?
A: MySQL uses memory not only for the InnoDB buffer pool but for other data, such as session-based and operation-based buffers. For example, if you have 100 connections that use underlying temporary tables to resolve queries and set the size of the internal temporary table to 100MB you will use around 10G additional memory for these tables. Query memory digest tables in Performance Schema and views on these tables in the sys schema to find the operations that allocate memory in your MySQL server.
Q: Can we get a copy of this presentation?
A: You should have received a copy of the slides. If you did not, they are attached to this blog post: DevOps_Perf_202111
Q: buffer_pool_size should be what percentage of the host RAM?
A: The percentage of the host RAM is a very rough estimation of the ideal amount of memory you need to allocate for the InnoDB buffer pool. For example, the MySQL user manual in past had recommendations for having InnoDB buffer pool size up to 80% of the available RAM. But 80% of RAM is very different if the host has, say, 8G, or 1024G. In the former case, 80% is 6.4G and the host will have 1.6G for other MySQL buffers and the operating system that could be not enough. In the latter case, 80% is 819.2G and the host will have 204.8G for other needs. Depending on your workload it could be a huge waste of resources. I recommend you to read this blog post: https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/ and follow the links in the end, then choose the size, appropriate for your data set and workload.
Q: How we can fitting RAM size vs data size?
Example: if I have 1G of data, how many RAM I need for get 100 QPS, and if I have 100G of data how many RAM I need for get 100 QPS?
A: RAM size, dataset size, and the number of queries per second that your server can handle are not directly related. You need to test your queries and follow how they are executed. For example, if you select everything from the InnoDB table and your table holds either 1G or 100G of data, and you do not access any other table on the server, the very first run will be slower than following because InnoDB will read data into the buffer pool. Then performance and the number of queries per second will be limited only by network speed and bandwidth between your client and server having you can allocate about 100G for your buffer pool. But cached size will stay almost the same as the table size no matter how many connections you have. Your MySQL server will only use a small amount of memory for new connections buffers.
In another case, however, you may have a comparatively small table that you will access by a quite complicated query. For example, if you try to repeat the test case for still valid https://bugs.mysql.com/bug.php?id=29423, a single query on the 184M table would run for a much longer time than you expect. In this case number of queries per second will be also very low.
Q: Do you have a recommendation parameter list for MySQL RDS on AWS?
A: It is the same as for the dedicated MySQL server but you may have not been able to change some of the options.
Q: If you know you have SSD’s, but ROTA = 1, what has to be configured to make use of the SSDs?
A: For SSD ROTA should be 0. If you are sure you have SSDs but they are shown as rotational disks this means that your storage is configured incorrectly. Depending on the configuration you may still have the same performance as if the disks were recognized properly. If this is not the case, check your storage, RAID controller, and system configuration.
MySQL just sends system commands for reading, writing, and syncing data. It does not care if the disk is rotational or not. For MySQL performance value of ROTA does not really matter.
Q: If you believed you tuned both master and slave for the best performance, but seconds behind master continues to increase, you decide to split the shard, but xtrabackup fails with log wrap. But even if you were to get a good backup, once it is online, the slave will never catch up. The Kobayashi Maru, a no win situation – have you been there? What did you do?
A: First make sure if you configured a multi-threaded replica. If you use parallel type LOGICAL_CLOCK , study option binlog_transaction_dependency_tracking. Practically how it works when set to WRITESET or to WRITESET_SESSION . For avoiding log wrap during backup increase redo log file size. If you can stop the source server, stop it and set up a replica by copying datadir: it is faster than using XtraBackup, because you would not need to copy changes in the redo log files while the backup is running.
Q: In MySQL 5.7, the tmp tablespace is now InnoDB, how can you tune tmp to take advantage of RAM and not use disk?
A: The tablespace file on disk is used only when the in-memory table is converted into a disk-based table. Otherwise, temporary tables continue using memory.
Q: What are the top 6 variables to get the best performance, how can you verify how effective their setting are, looking at the global status, when can you know when those variables can be increased to get the best utilization from CPUs/RAM/Disk/Network.
A: While I showed variables that can improve performance in most cases on my “Conclusion” slides I recommend you to start from the issue you are trying to solve and start adjusting variables only when you understand what you are doing.
Some of such variables could be measured for effectiveness. For example, if the number of free buffers in the output of SHOW ENGINE INNODB STATUS is small and the buffer pool hit rate shows that a number of disk access is consistently greater than the number of the buffer pool hits, it indicates that the buffer pool size may be too small for you your workload and data.
Regarding CPU, if the number of active threads is high, and you see performance drop when concurrency increases while the operating system shows low CPU usage, it may be a symptom that either:
– you limited the upper limit of the number of active engine threads
– disk does not support so many parallel operations and active threads are waiting for IO
Another issue with CPU performance could happen if the upper limit of the number of active engine threads is not set or too high and threads are spending time doing nothing while waiting in the priority queue.
The only option that directly limits IO activity is innod_io_capacity that limits the speed of background InnoDB operations. If set too low InnoDB may underuse your fast disk and if set too high InnoDB could start writing too fast, so each write request will waste time waiting in its queue.
Q: What was the last InnoDB setting, the one which should up to no of CPU cores?
A: This is innodb_thread_concurrency that limits the number of InnoDB threads that could run in parallel. You should set it either to 0 or to the number of CPU cores.
Q: Which is more secure and faster community MySQL or Percona MySQL or aws rds?
A: Percona MySQL has performance, diagnostic improvements, as well as Enterprise-level features, available as open source. AWS RDS supports hardware scaling on demand and physical replication that uses InnoDB redo log files instead of binary logs. However, it does not allow you to have the same control on the server as for your own physical instance. Community MySQL works on a higher number of platforms, thus uses function calls that work on all of them where Percona MySQL or AWS RDS may use optimized variants. So each of them has its own advantages and disadvantages.
Q: In case with open tables >>> open_files (and cannot change open_files) how to set table_open_cache? “as big as possible”?
A: Status variable Open_files is “the number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.” (https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Open_files) The status variable Open_tables is “the number of tables that are open”. They are not related to each other. You need to watch that value of Opened_tables (“the number of tables that have been opened”) is not greater than Open_tables .
There is an operating system option “open files” that is visible if you run the command ulimit -n . This option should be greater than the maximum number of files that your MySQL instance can simultaneously open. Speaking about Open_tables : you cannot have this value set to a number that is larger than the operating system option “open files” unless your tables are stored in the shared or general tablespace.
Q: How to tell if we should tune join_buffer_size? wait events anywhere?
A: If you use JOIN queries that do not use indexes and they perform slowly because of this. Start from regular query tuning using slow query log, Performance Schema, and Query Analyzer in PMM to find queries that require optimization. In Query Analyzer add a column “Full Join” to your query list. In the Performance Schema search for statements where the value of SELECT_FULL_JOIN is greater than 0 in the events_statements_* tables.
Check also my “Introduction to MySQL Query Tuning for Dev[Op]s” webinar.
Q: How to measure memory consumption of table_open_cache? 15K/table? FRM-related? some way to estimate?
A: This is event “ memory/sql/TABLE_SHARE::mem_root ” Check also this blog post.
Q: Hello guys!
Do we need to prepare different optimization depends on MySQL engine e.g. XtraDB, InnoDB? If yes, could you please explain differences?
Best regards,
Oleg Stelmach
A: XtraDB is an enhanced version of InnoDB in the Percona Server: https://www.percona.com/doc/percona-server/8.0/percona_xtradb.html. So differences are added features in the Percona Server. Namely, the options that exist in the Percona server and do not exist in the upstream Community MySQL.
Q: Regarding threads. Do better to use hyperthreading\multithreading for MySQL instance or we need to turn off this function?
Best regards,
Oleg Stelmach
A: You do not have to turn this option off but you may see that MySQL performance is not linearly predictable in high concurrent workloads. I recommend you to check this blog post with hyperthreading benchmarks on MySQL and comments on it for a better understanding of how hyperthreading can affect MySQL performance.
Q: Besides from setting os swap-pines correctly. would also recommend to enable memlock in my.cnf?
A: Normally you do not need it.
