Re: Waits useless on MySQL?

This is with INNODB.

On Thu, Jan 26, 2023 at 10:12 PM Jon Crisler <joncrisler_at_gmail.com> wrote:

> 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