Planet MySQL

Planet MySQL -
  1. MyISAM is one of the most popular storage engines in MySQL. MyISAM frequently is the second choice after InnoDB - in this blog post we will try to find out how best to work with this engine. What is MyISAM? MyISAM is one of MySQL’s storage engines. MyISAM is based on ISAM (Indexed Sequential Access Method), an indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way. However, it does not do very well when simultaneously reading from and writing to one table, due to its table locking. It also does not support transactions. For some MySQL engineers, this engine is the most popular choice after InnoDB: the MyISAM storage engine was the only storage engine provided by MySQL in 2005 and was available to use for more than 20 years. MyISAM was the default storage engine for MySQL up to version 5.5. MyISAM from the Inside An illustration of how MyISAM works from the inside is not within the scope of this blog, but we can provide you with the settings that help you optimize performance of the engine: Myisam_sort_buffer_size defines the buffer that is allocated when the index is sorted by running REPAIR, CREATE INDEX or ALTER TABLE queries. Key_buffer_size defines the size of the buffer used for index blocks across MyISAM tables. Increasing this parameter can lead to better index handling. Sort_buffer_size describes the size of a buffer that is allocated for threads that need to do sort operations. Read_buffer_size describes the size of a buffer that is allocated for threads that perform sequential scan operations. Write_buffer_size describes the size of the write buffer. These four parameters are important, but while they are important, you should also keep an eye on the key_buffer_size variable. The key_buffer_size variable determines the size of the index buffers held in memory - think of it as the innodb_buffer_pool_size counterpart, but for MyISAM. If your servers consist primarily of MyISAM tables, you could allocate about 25% or more of the available RAM on the server to the key_buffer_size variable. There is also another way to determine what the value of the key_buffer_size parameter should be - simply compare the key_read_requests value (total value of requests to read an index) and the key_reads value (the value of key_reads is the number of requests that had to be read from disk). The values for those parameters can be retrieved by looking at the server status variables (simply issue a SHOW GLOBAL STATUS query on your MySQL server). It is also beneficial to keep in mind that if key_reads returns a large value, the value of key_buffer_size is probably too small. MyISAM and MySQL 8.0 If you ask some MySQL engineers, they will say that MyISAM should no longer be used. Why? Well, some people say that because of the fact that because when MySQL advanced, they added the majority of the features that previously could only be seen in MyISAM into InnoDB effectively rendering MyISAM obsolete: Full-text indexes have been available in InnoDB since version 5.6. Portable tablespaces became available in InnoDB since version 5.6. Spatial indexes became available in InnoDB since version 5.7. Last update for table became available in InnoDB since version 5.7. So, should you still use MyISAM? Probably not. There is one caveat though - keep in mind that simple COUNT(*) queries will probably perform faster on MyISAM than they will on InnoDB - MyISAM stores the number in the table metadata, InnoDB does not. I am using MyISAM and want to switch to InnoDB, What Do I Do? If you still use MyISAM and want to switch to InnoDB, simply convert all of your tables to InnoDB. Obviously that’s easier said than done, but here’s a simple guide: Figure out which tables in your MySQL instance are using MyISAM: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘database_name’ AND ENGINE = ‘MyISAM’; Convert all of your MyISAM tables to InnoDB: ALTER TABLE `table_name` ENGINE = InnoDB; If you do not want to run multiple ALTER TABLE statements one after another, consider putting the ALTER TABLE statements into a loop. That’s it - you’re done! Summary MyISAM is one of the most popular MySQL engines. The engine was the default for MySQL versions up to 5.5. The engine is one of the most popular choices after InnoDB, but it can be called obsolete as of MySQL 8.0 - MySQL has already made sure that everything that can be done with MyISAM also can be done when InnoDB is in use, so at this point MyISAM is pretty much only useful if you want simple COUNT(*) queries to be faster. Such queries will be faster because MyISAM stores the number in table metadata - other MySQL engines do not. Tags:  MySQL myisam storage engine
  2. The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂 Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command. In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA. “SHOW PROCESSLIST” Using Thread Manager (default) This is the default method. The default “show processlist” implementation iterates across active threads from within the thread manager while holding a global mutex. Negatively impacts performance. Particularly impacts the busy systems quite badly. The INFORMATION_SCHEMA.PROCESSLIST is one of the sources of process information. This will also use the thread manager to collect the metrics. By default, “mysqladmin processlist” also uses the thread manager to get the details. The following statements are equivalent: SHOW FULL PROCESSLIST; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; Mysqladmin processlist --verbose “SHOW PROCESSLIST” Using Performance Schema Available from MySQL 8.0.22. It collects the thread details from the PERFORMANCE_SCHEMA>PROCESSLIST table. Global mutex is not needed. Helps to avoid the performance impact during querying the “show processlist”, particularly in busy systems. The implementation also applies to “mysqladmin processlist” The following statements are equivalent: SHOW FULL PROCESSLIST; SELECT * FROM PERFORMANCE_SCHEMA.PROCESSLIST; Mysqladmin processlist --verbose “PERFORMANCE_SCHEMA.PROCESSLIST” table has similar columns as “INFORMATION_SCHEMA.PROCESSLIST” mysql> desc performance_schema.processlist; +---------+-----------------+------+-----+---------+-------+ | Field   | Type            | Null | Key | Default | Extra | +---------+-----------------+------+-----+---------+-------+ | ID      | bigint unsigned | NO   | PRI | NULL    |       | | USER    | varchar(32)     | YES  |     | NULL    |       | | HOST    | varchar(255)    | YES  |     | NULL    |       | | DB      | varchar(64)     | YES  |     | NULL    |       | | COMMAND | varchar(16)     | YES  |     | NULL    |       | | TIME    | bigint          | YES  |     | NULL    |       | | STATE   | varchar(64)     | YES  |     | NULL    |       | | INFO    | longtext        | YES  |     | NULL    |       | +---------+-----------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> desc information_schema.processlist; +---------+-----------------+------+-----+---------+-------+ | Field   | Type            | Null | Key | Default | Extra | +---------+-----------------+------+-----+---------+-------+ | ID      | bigint unsigned | NO   |     |         |       | | USER    | varchar(32)     | NO   |     |         |       | | HOST    | varchar(261)    | NO   |     |         |       | | DB      | varchar(64)     | YES  |     |         |       | | COMMAND | varchar(16)     | NO   |     |         |       | | TIME    | int             | NO   |     |         |       | | STATE   | varchar(64)     | YES  |     |         |       | | INFO    | varchar(65535)  | YES  |     |         |       | +---------+-----------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) Implementation Make sure the PERFORMANCE_SCHEMA is enabled at the server startup. Make sure MySQL was configured and built with the thread instrumentations enabled. MySQL provides a variable “performance_schema_show_processlist” to enable this feature. Once we enable the variable, the “SHOW PROCESSLIST” command will start to show the details from the “PERFORMANCE_SCHEMA.PROCESSLIST” table instead of the thread manager. The variable has a global scope, no need to restart the MySQL server. mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name                       | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | OFF   | +-------------------------------------+-------+ 1 row in set (0.08 sec) mysql> set global performance_schema_show_processlist='ON'; Query OK, 0 rows affected (0.00 sec) mysql> \r Connection id:    23 Current database: *** NONE *** mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name                       | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | ON    | +-------------------------------------+-------+ 1 row in set (0.00 sec) Yes, the PERFORMANCE_SCHEMA.PROCESSLIST table is activated for the “SHOW PROCESSLIST”. “SHOW PROCESSLIST” output from “PERFORMANCE_SCHEMA”: mysql> show processlist\G *************************** 1. row ***************************      Id: 5    User: event_scheduler    Host: localhost      db: NULL Command: Daemon    Time: 2461   State: Waiting on empty queue    Info: NULL *************************** 2. row ***************************      Id: 23    User: root    Host: localhost      db: NULL Command: Query    Time: 0   State: executing    Info: show processlist 2 rows in set (0.00 sec) You can also query the “performance_schema.processlist” table to get the thread information. mysql> select * from performance_schema.processlist\G *************************** 1. row ***************************      ID: 5    USER: event_scheduler    HOST: localhost      DB: NULL COMMAND: Daemon    TIME: 2448   STATE: Waiting on empty queue    INFO: NULL *************************** 2. row ***************************      ID: 23    USER: root    HOST: localhost      DB: NULL COMMAND: Query    TIME: 0   STATE: executing    INFO: select * from performance_schema.processlist 2 rows in set (0.00 sec) “mysqladmin processlist” output from “performance_schema”: [root@mysql8 vagrant]# mysqladmin processlist +----+-----------------+-----------+----+---------+------+------------------------+------------------+ | Id | User            | Host      | db | Command | Time | State                  | Info             | +----+-----------------+-----------+----+---------+------+------------------------+------------------+ | 5  | event_scheduler | localhost |    | Daemon  | 2631 | Waiting on empty queue |                  | | 24 | root            | localhost |    | Query   | 0    | executing              | show processlist | +----+-----------------+-----------+----+---------+------+------------------------+------------------+ Recommendations To avoid having some threads ignored, leave the “performance_schema_max_thread_instances” and “performance_schema_max_thread_classes” system variables set to their default value (default = -1, meaning the parameter will be autosized during the server startup). To avoid having some STATE column values be empty, leave the “performance_schema_max_stage_classes” system variable set to its default (default = -1, meaning the parameter will be autosized during the server startup).
  3. MySQL Select and Sort Status Variables This page is a reboot of the original written 15 years ago in 2005. Back then, I must have been using MySQL 4.1 or 5.0. Today, I am using MySQL 8.0.22. A lot changed in MySQL from 4.1 to 8.0, but it still has the same Select_% and Sort_% status variables which are equally important today in 2021. Let’s examine them in greater detail and with better examples.
  4. Database monitoring is a critical part of any application’s maintenance. Finding database issues on time can help the application remain healthy and accessible. Without monitoring in place, database outages can be prolonged, resulting in increased downtime for users. In this blog we are going to explain what you need to monitor in a Moodle Database, and how you can do it using ClusterControl. ClusterControl Monitoring  Using ClusterControl you can monitor the databases efficiently, it can do so by deploying agents on the database hosts or in an agentless fashion, by simply connecting to the databases via SSH . Data is presented in a GUI, in the form of a number of dashboards and charts.  How Does ClusterControl Monitoring Work ? ClusterControl automatically collects database metrics as well as underlying server performance metrics. Since a high availability database stack would most probably consist of a load balancer (for Galera Cluster, one would have either HAProxy, ProxySQL or MaxScale), it is also included in the instances being monitored. It alerts on performance or availability problems for both database and server components. It also generates comprehensive reports to show database utilization and capacity issues. Real Time Monitoring  ClusterControl allows you to monitor your database servers in real-time. It has a predefined set of dashboards for you, to analyze some of the most common metrics mentioned above and even more.  It also allows you to customize the graphs and you can enable agent-based monitoring to generate more detailed dashboards for both system level and DB level.  Query Monitoring  Also, you have the query monitor and the performance sections, where you can find all your database information. With these features, you can understand database activity. Slow queries and things like deadlocks can be devastating to the stability of a database, and can affect all users of the application. So it is important to keep track of them. Moodle itself has its own schema, but at the time of writing, there’s over 1700 third party Moodle plugins that come with their own database tables. So how do you keep track of how the queries in these plugins are performing, and identify poorly written queries that can drag down the performance of the entire Moodle database. The query outliers show queries that take more time to execute than normally, longer being defined as a latency deviation of 2 sigmas + average_query_time. This can be an interesting feature as it would help identify queries that suddenly start to perform poorly, which might be because of a change in configuration, an upgrade, or for instance adding a new plugin. DB Growth  The ClusterControl frequently collects db, table, index stats. We can easily find the largest tables in your db. This can help understand when you might need to add more storage to the database, or for instance, if you need to do some cleanup on some tables. Alerting You can create alerts, which inform you of events in your cluster, or integrate it with different services such as PagerDuty, VictorOps or Slack. Summary  In this blog, we mentioned some important metrics to monitor in your Moodle Database. Monitoring your moodle database is necessary but also a time-consuming task if you don’t have any tools to help with this. We hope those couple of tips help to monitor your Moodle database backend much easier and to a greater extent. Tags:  MySQL moodle lms database monitoring
  5. We are pleased to announce that MySQL Database Service with HeatWave is now available in 22 regions worldwide.  With the introduction of 10 new regions, MySQL Database Service is now present in all Oracle Cloud Infrastructure commercial regions: North America  US East (Ashburn)  US West (Phoenix)  US West (San Jose)  Canada Southeast (Toronto)  Canada Southeast (Montreal) Latin America  Brazil East (Sao Paulo)  Chile Central (Santiago) EMEA  UK South (London)  UK West (Newport)  Germany Central (Frankfurt)  Switzerland North (Zurich)  Netherlands Northwest (Amsterdam)  Saudi Arabia West (Jeddah)  UAE East (Dubai) APAC  Japan East (Tokyo)  Japan Central (Osaka)  South Korea Central (Seoul)  South Korea North (Chuncheon)  Australia East (Sydney)  Australia Southeast (Melbourne)  India West (Mumbai)  India South (Hyderabad)     You are welcome to try MySQL Database Service and many other services for free. Go to and create your free account in Oracle Cloud Infrastructure. Check the documentation for the quick steps to create your MySQL databases.  Visit to learn more about MySQL Database Service and HeatWave. The MySQL team is tirelessly working to continue to evolve MySQL Database Service and HeatWave. More features are coming soon. Stay tuned!