MySQL: Difference between revisions

From Cheaha
Jump to navigation Jump to search
(Initial commit, added information on single-user optimizations)
 
(Redirected to MariaDB)
 
Line 1: Line 1:
=Summary=
#REDIRECT [[MariaDB]]
 
=Optimization for Single User Access on Cheaha=
 
[https://dev.mysql.com/ MySQL] is an open-source database management system available on Cheaha through the <code>module</code> system. While a mature and useful system, the default configuration of MySQL can be inefficient for large, not-in-memory databases, leading to poor performance and longer runtimes. The primary intended use-case for MySQL is serving database information to many remote users, like with UAB Oracly AdminSys. It is assumed for this page that science-related SQL jobs on Cheaha serve only a single user. That assumption allows us to configure the system for higher performance when querying large databases. There are a few potential fixes for these issues that are available to Cheaha users. Options include changing the default buffer pool size to reduce paging, storing the buffer state on disk to reduce warmup time, leaving a mysql terminal open on a long-running job, and requesting access to the large memory nodes on Cheaha. The latter can be done by sending a ticket to [support@listserv.uab.edu].
 
The flags listed below can also be modified at runtime at the MySQL terminal. A flag formatted as <code>--flag-name=value</code> can be changed using <code>SET GLOBAL flag_name=value</code>. Note that the hyphens have changed to underscores.
 
==Modifying the Buffer Pool==
 
Having a small buffer pool means that flushing occurs more frequently. Flushing is when the machine moves data to and from the buffer, which involves moving data between memory and storage. Communication with storage is
orders of magnitude slower than communication between processor and memory, so more frequent flushing can reduce performance. MySQL uses a collection of
variables to control how much of a database is in memory at one time, in a buffer pool. Fine control of the [https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-buffer-pool.html InnoDB buffer pool] involves a  number of variables. Two are relevant for the typical scientific use-case of MySQL. The flags below are recommendations for use with the <code>mysqld</code> command.
 
<pre>
--innodb_buffer_pool_instances=1
--innodb_buffer_pool_size=[80% of total allocated memory]
--innodb_buffer_pool_chunk_size=[80% of total allocated memory]
</pre>
 
The number of instances is set to one using the [https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html innodb-buffer-pool-instances] flag because there is only one user. Multiple instances are useful when there is concurrent access by many users. The buffer pool size is set to 80% of total allocated memory using the [https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html innodb-buffer-pool-size] because MySQL has an additional 10% for internal buffers. It is important to stay below the memory allocation to avoid accidentally killing the job and destroying hard work.
 
The MySQL server must be run inside of a container on Cheaha. If the container can't be modified, then the three above flags can't be used when starting the server daemon <code>mysqld</code>. Instead they must be modified at runtime in the MySQL terminal. See the note in the summary for how to change the flags into SQL queries that modify the global variables.
 
==Storing the Buffer Pool State==
 
The buffer pool is stored in memory, so it is destroyed when the MySQL server is shut down. When it is restarted at a later time, the buffered indices stored in the pool must be rebuilt. The nature of shared computing resources means that the job, and the server, are shut down periodically. This means the cache must be rebuilt for each new job, even with the same database. For large databases, this can result in significant and repeated warmup time. It is possible to mitigate this by writing the buffer pool state to disk on shutdown and reading it on startup. The fraction of buffer pool to store may also be controlled, with a default of 40%. The following flags enable loading and saving of the buffer pool. Loading and saving are enabled by default.
 
<pre>
--innodb_buffer_pool_load_at_startup=ON
--innodb_buffer_pool_dump_at_shutdown=ON
--innodb_buffer_pool_dump_pct=100
</pre>
 
The buffer pool file may be a significant fraction of the size of the original database, which could increase storage requirements. Depending on the use-case and sizes involved, it may not be a good choice to store the entire buffer pool. An alternative is to simply request a VNC job on a partition with a long time, and then leave the MySQL terminal open.

Latest revision as of 15:58, 10 January 2020

Redirect to:

Retrieved from "https://docs.uabgrid.uab.edu/w/index.php?title=MySQL&oldid=6004"