Re: Waits useless on MySQL?
From: Jeremiah Wilton <jcwilton93_at_earlham.edu>
Date: Mon, 23 Jan 2023 20:06:16 -0800
Message-Id: <45D72273-39BB-43B6-B992-8FDF0D4D4AE3_at_earlham.edu>
Postgres since v.10 (2017) has included pretty comprehensive and reliable wait instrumentation in the pg_stat_activity table. It’s no Oracle, but as Malden notes, Oracle is hard to top for instrumentation.
Date: Mon, 23 Jan 2023 20:06:16 -0800
Message-Id: <45D72273-39BB-43B6-B992-8FDF0D4D4AE3_at_earlham.edu>
Postgres since v.10 (2017) has included pretty comprehensive and reliable wait instrumentation in the pg_stat_activity table. It’s no Oracle, but as Malden notes, Oracle is hard to top for instrumentation.
My understanding as to the MySQL performance_schema.events_waits_current table is that there is a bug with the reporting of nested waits. In MySQL Perf Schema, waits can be nested. In other words, a short-lived event may also be listed during a longer event, and an even shorter event may be present during the short event. So in the table events_waits_current, you could see two or more rows with null timer_end (wait is currently active) for a single thread_id (session). The ubiquitous sql_handler IO wait often has shorter, more useful waits nested. But alas through MySQL 8, at least for the sql_handler event, this nested reporting is broken and just shows sql_handler. I think AWS have fixed this in Amazon Aurora MySQL so waits (like lock thread mutex or whatever MySQL’s lock wait is called) show up correctly in RDS Performance Insights. I’ll verify tomorrow.
Thanks
Jeremiah
Sent from my iPhone
On Jan 23, 2023, at 4:50 PM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:
On 1/23/23 18:18, kyle Hailey wrote:
None of the open source databases is instrumented as well as Oracle. Jeff Holt suggested something on the PostgreSQL list, but was put in his place in a rather rude response. Both MySQL and PostgreSQL are good for small databases, not exceeding 10 TB. If you have a 100TB monster, than Oracle, Snowflake or SQL Server are your only choices. There are also commercial PostgreSQL derivatives, like EDB, Yugabyte and CockroachDB which all look promising. Frits Hoogland, who has recently written a nice article on this list about Oracle to Postgres comparison, works for Yugabyte and that instills confidence in me, more than the actual name. The prefix "Yuga" is a part of the recent history in my country of origin and that does not instill confidence.
Anyone tried to use wait events on MySQL?Looking across a large fleet of MySQL, 70% of the wait time is on wait/io/table/sql/handler.The event wait/io/table/sql/handler can be CPU, IO and lock!Seems like waits are pretty useless.We captured them in RDS and are currently doing so at Datadog, but thinking might even be worth skipping their collections and just reporting MySQL state instead of waits.Seems incredible that row level locks are not instrumented as their own waits. (Looks like we can deduce lock in rather unsatisfying way TBD)
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 24 2023 - 05:06:16 CET