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/
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.
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.