Re: Waits useless on MySQL?

Does the wait event reporting differ based on the MySQL db engine in use ? I have not used MySQL very much recently, but we always used the Innodb engine as we needed ACID compliance and a few other features.

On Mon, Jan 23, 2023 at 11:07 PM Jeremiah Wilton <jcwilton93_at_earlham.edu> wrote:

> 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:
>
>
> 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)
>
> 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.
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

--
http://www.freelists.org/webpage/oracle-l