Planet MySQL

1. Are some MySQL wait events actually CPU time ?

   Sure, wait event times in any database will include some CPU time, though ideally it should be minimal. Waits should be instrumented as close to the off CPU wait event as possible, i.e. if I’m doing an IO, then I want the wait to cover just the read call: start_wait('my_read_wait'); pread(... ); end_wait('my_read_wait'); So my function that sets up the read will do some CPU that could get caught under the cover of the wait itself and the call to pread will use some CPU but most of the time should be waiting for the actual I/O if the I/O is coming from storage. start_wait('my_lock'); semtimedop(...); end_wait('my_lock'); On MySQL I’ve noticed that average active sessions on CPU are often well below what the host reports as CPU utilization. For example if I have one connection on CPU a 100% of the time on a 2 vCPU host then CPU Utilization should be around 50%.  There might be some other CPU activity by other processes but if the database is the only thing on the host and I only have one connection active then this is a pretty good approximation. One connection on CPU 100% of the time is the same a 1 average active session  (AAS) on CPU or 1 AAS on CPU. On MySQL systems I’d often see AAS on CPU say at an average of 10% where as the CPU Utilization of the host is 50% or even 100%. Finally I ran a test today where I had 2 sessions on a 2 vCPU host running a join using the same table 3 times and no index and no predicate filter. That basically makes the SQL have to do N * N * N lookups where N is the number of rows in the the table. In this case the table fit into memory so this should be a pure CPU operation and yes the host OS reported 100% CPU.  but low and behold in my case it came out ZERO AAS on CPU and 2 AAS on the wait ‘wait/io/table/sql/handler’. On MySQL I am constantly seeing ‘wait/io/table/sql/handler’ in the top waits and happening when the SQL running should just be  reading buffers in memory. Here is an example: CREATE TABLE seed ( id INT AUTO_INCREMENT PRIMARY KEY, val INT); insert into seed(val) values (1); insert into seed(val) select val from seed; /* 2 */ insert into seed(val) select val from seed; /* 4 */ insert into seed(val) select val from seed; /* 8 */ insert into seed(val) select val from seed; /* 16 */ insert into seed(val) select val from seed; /* 32 */ insert into seed(val) select val from seed; /* 64 */ insert into seed(val) select val from seed; /* 128 */ insert into seed(val) select val from seed; /* 256 */ insert into seed(val) select val from seed; /* 512 */ insert into seed(val) select val from seed; /* 1024 */ insert into seed(val) select val from seed; /* 2048 */ insert into seed(val) select val from seed; /* 4096 */ insert into seed(val) select val from seed; /* 8192 */ insert into seed(val) select val from seed; /* 16384 */ create table seed1 as select * from seed; create table seed2 as select * from seed; Then run the following query by a couple of sessions. select count(*) from seed a, seed1 b, seed2 c where a.id=b.id and a.id=c.id;   Here is the Performance Insights dashboard in Amazon RDS. CPU Utilizaiton goes from about 5% to 100% and DB Load goes from 0 to 2 with almost all load on ‘wait/io/table/sql/handler’ Now if we track down the IO waits. we can see it’s wrapped around fetch row without actually seeing if the fetch is doing an I/O or reading from cache: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.cc#L3047 MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_read_map(buf, key, keypart_map, find_flag); }) if (!result && m_update_generated_read_fields) { result= update_generated_read_fields(buf, table, active_index); m_update_generated_read_fields= false; } DBUG_RETURN(result); } index_read_map: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.h#L2819 virtual int index_read_map(uchar * buf, const uchar * key, key_part_map keypart_map, enum ha_rkey_function find_flag) { uint key_len= calculate_key_len(table, active_index, keypart_map); return index_read(buf, key, key_len, find_flag); } calculate_key_len: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.cc#L3047 uint calculate_key_len(TABLE *table, uint key, key_part_map keypart_map) { /* works only with key prefixes */ assert(((keypart_map + 1) & keypart_map) == 0); KEY *key_info= table->key_info + key; KEY_PART_INFO *key_part= key_info->key_part; KEY_PART_INFO *end_key_part= key_part + actual_key_parts(key_info); uint length= 0; while (key_part < end_key_part && keypart_map) { length+= key_part->store_length; keypart_map >>= 1; key_part++; } return length; } IO Waits also are instrumented at IO waits MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, MAX_KEY, result, { result= rnd_pos(buf, pos); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_read_map(buf, key, keypart_map, find_flag); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, index, result, { result= index_read_idx_map(buf, index, key, keypart_map, find_flag); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_next(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_prev(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_first(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_last(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_next_same(buf, key, keylen); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_WRITE_ROW, MAX_KEY, error, { error= write_row(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_UPDATE_ROW, active_index, error, { error= update_row(old_data, new_data);}) MYSQL_TABLE_IO_WAIT(PSI_TABLE_DELETE_ROW, active_index, error, { error= delete_row(buf);}) MYSQL_TABLE_IO_WAIT https://github.com/mysql/mysql-server/blob/8d8c986e5716e38cb776b627a8eee9e92241b4ce/sql/handler.cc #define MYSQL_TABLE_IO_WAIT(OP, INDEX, RESULT, PAYLOAD) ... case PSI_BATCH_MODE_STARTING: { m_psi_locker = PSI_TABLE_CALL(start_table_io_wait) PSI_TABLE_CALL include/mysql/psi/mysql_table.h #define PSI_TABLE_CALL(M) psi_table_service->M psi_table_service https://github.com/mysql/mysql-server/blob/8d8c986e5716e38cb776b627a8eee9e92241b4ce/mysys/psi_noop.cc#L536 static struct PSI_table_locker *start_table_lock_wait_noop( struct PSI_table_locker_state *, struct PSI_table *, enum PSI_table_lock_operation, ulong, const char *, uint) { return nullptr; } ... static PSI_table_service_t psi_table_noop = { get_table_share_noop, release_table_share_noop, drop_table_share_noop, open_table_noop, unbind_table_noop, rebind_table_noop, close_table_noop, start_table_io_wait_noop, end_table_io_wait_noop, start_table_lock_wait_noop, end_table_lock_wait_noop, unlock_table_noop}; PSI_table_service_t *psi_table_service = &psi_table_noop; Reference https://fritshoogland.wordpress.com/2012/04/26/getting-to-know-oracle-wait-events-in-linux/

2. MySQL SELECT and WHERE Clause Column Existence

   I’m sharing another tip for anyone who wants to learn more about MySQL and how to use it. This post covers an example of the differences in columns specified in the WHERE clause but not the SELECT clause and vice-versa. Image by Drizzt_Do_Urden from Pixabay  The Newsletter for PHP and MySQL Developers Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter. Columns named in the WHERE clause but not the SELECT clause Any column used as part of a WHERE clause conditional filter does not have to be listed in the SELECT column list. However, it should be present in the FROM clause named table. See the following example queries for clarity. SELECT category_id, last_updateFROM categoryWHERE name = 'Action'; You can see that the WHERE clause uses the conditional filter, name = ‘Action’, which is perfectly valid, although the SELECT statement list does not include the ‘name’ column. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support! Non-existent Column in the WHERE Clause Yet, as shown in the next example query, you cannot specify a non-existent column name in the WHERE clause conditional filter or you will get an ‘Unknown column’ error: SELECT name, category_id, last_updateFROM categoryWHERE first_name = 'Action';Error Code: 1054. Unknown column 'first_name' in 'where clause' On the same token, a column listed in the SELECT list that does not exist in the named FROM clause table also returns an error: SELECT first_name, category_id, last_updateFROM categoryWHERE  name = 'Action';Error Code: 1054. Unknown column 'first_name' in 'field list' Stay tuned for more MySQL and PHP tips and content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Free MySQL Query Syntax Mantra PDF cheat sheet. Remember the query syntax order with this mantra.Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter.Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.5 Truths I’ve Come To Realize As a Self-taught DeveloperDiscover premium MySQL learning material over in my Kofi Shop today!Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The Newsletter for PHP and MySQL Developers Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support! The post MySQL SELECT and WHERE Clause Column Existence appeared first on Digital Owl's Prose.

3. MySQL InnoDB Redo Log Archiving

   When performing physical backup on system that are heavily used, it can happen that the backup speed cannot keep up with the redo log generation. This can happen when the backup storage is slower than the redo log storage media and this can lead in inconsistency in the generated backup. MySQL Enterprise Backup (aka MEB) and probably Percona Xtrabackup, benefit from the possibility to sequentially write redo log records to an archive file in addition to the redo log files. This feature was introduced in MySQL 8.0.17. How to enable it ? To enable this feature, two settings are necessary: set globally a directory where those archiving logs can be storedstart the archiving process in a session by calling a dedicated functionThe global variable is innodb_redo_log_archive_dirs. This variable musts contain labelled directories where the archiving redo logs can be stored. The format is a semi-colon separated string like this: innodb_redo_log_archive_dirs='label1:/backups1;label2:/backups2' The system user running mysqld must have access to those directories and should not be accessible to all users. The redo log archiving is started using the function innodb_redo_log_archive_start() and stopped using innodb_redo_log_archive_stop(). Only users with the INNODB_REDO_LOG_ARCHIVE privilege can call those functions. It’s important to notice that the MySQL session that activates redo log archiving must remain open for the duration of the archiving. You must deactivate redo log archiving in the same session. If the session is terminated before the redo log archiving is explicitly deactivated, the server deactivates redo log archiving implicitly and removes the redo log archive file. Let’s see how to enable it: $ sudo mkdir -p /var/lib/mysql-redo-archive/backup1 $ sudo chown mysql. -R /var/lib/mysql-redo-archive $ sudo chmod -R 700 /var/lib/mysql-redo-archive/ In fact , it’s ready to work but it’s not enabled, only when a session, usually the one initializing the backup, will invoke the innodb_redo_log_archive_start() it will really be enabled: Is it enabled ? How can we see that the redo log archiving is active ? We can check if MySQL is using a redo log archive file using the following query: select * from performance_schema.file_instances where event_name like '%::redo_log_archive_file'\G If there is an entry, this means that the redo log archive process is enabled or has been enabled and stopped successfully using the dedicated function: So this is not enough to be sure that the redo log archiving is active. But we have the possibility to also check if the thread is active using this query: select thread_id, name, type from threads where name like '%redo_log_archive%'; If a row is returned, it means that the redo log archiving is enabled and active: Error Messages Here are some common error messages related to Redo Log Archiving: ERROR: 3850 (HY000): Redo log archiving failed: Session terminated with active redo log archiving - stopped redo log archiving and deleted the file. This error happens when you try to stop the redo log archiving from another session and the session that started it was terminated. ERROR: 3851 (HY000): Redo log archiving has not been started by this session. This is when the session that started the process is still open and you try to stop the redo log archiving from another session. ERROR: 3848 (HY000): Redo log archiving has been started on '/var/lib/mysql-redo-archive/backup2/archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log' - Call innodb_redo_log_archive_stop() first: this happens when you try to start the archiving process and there is already one active. ERROR: 3842 (HY000): Label 'backup2' not found in server variable 'innodb_redo_log_archive_dirs': this is when you try to start the redo log archiving and you are using a label which is not defined in innodb_redo_log_archive_dirs. ERROR: 3846 (HY000): Redo log archive directory '/var/lib/mysql-redo-archive/backup2' is accessible to all OS users: this is when the directory is accessible by others users too. Only the user running mysqld should have access to it. ERROR: 3844 (HY000): Redo log archive directory '/var/lib/mysql-redo-archive/backup3' does not exist or is not a directory: this is a very common error, it happens when the subdir is not existing in the directory defined by the corresponding label in innodb_redo_log_archive_dirs. In this example, backup3 is not created in /var/lib/mysql-redo-archive. ERROR: 3847 (HY000): Cannot create redo log archive file '/var/lib/mysql-redo-archive/backup3/archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log' (OS errno: 13 - Permission denied): this is simple to understand, the directory and sub-directory exist but doesn’t belong to the user running mysqld (usually mysql). Callable Functions There are several functions that are related to Redo Log Archiving, we already used 2 of them to start and stop the process. Here is the list as MySQL 8.0.29: The last two functions are used by MEB and are not documented in MySQL Server’s manual and there is no reason to use them as normal user. innodb_redo_log_archive_flush is used to flush the redo log archive queue. innodb_redo_log_sharp_checkpoint makes a checkpoint calling log_make_latest_checkpoint(*log_sys) Conclusion Even if not popular yet, this feature is mandatory for heavy workload when the backup storage doesn’t have the same capabilities of the production storage and is not able to follow up the speed of the writes. When enabled by the DBA, MySQL Enterprise Backup will use it automatically. To know if a the redo log archiving process was started and is still active, the DBA can check the performance_schema.threads table.

4. How to Install Nginx with PHP and MySQL (LEMP Stack) on Ubuntu 22.04

   The LEMP software stack is a group of open-source software that enables a server to host dynamic websites and apps written in PHP. This guide will show you to install a LEMP Stack on an Ubuntu 22.04 server.

5. Announcing Vitess 14

   We are pleased to announce the general availability of Vitess 14. Major Themes # In this new release, major improvements have been made in several areas of Vitess, including usability and reliability. Online DDL is now GA. Gen4 planner is the new default planner. VTAdmin and VTOrc are officially in beta with Vitess 14. Usability # Command-Line Syntax Deprecation # This release marks the beginning of Vitess standardizing its command-line and flags syntax.