WordPress.org

Make WordPress Core

Opened 2 months ago

Closed 4 weeks ago

#53845 closed enhancement (fixed)

Add some more MySQL information to the Debug section in Site Health

Reported by: zodiac1978 Owned by: SergeyBiryukov
Milestone: 5.9 Priority: normal
Severity: normal Version:
Component: Site Health Keywords: has-patch
Focuses: Cc:

Description

After having some trouble on a big multisite installation I needed to find the bottle neck. Unfortunately the Site Health debug info wasn't helpful in my case.

At the end it was some bad default configuration from the hoster. A limited number of mysql connections was causing the issue.

Therefore I would like to propose adding these three values to the debug info in the database section:

MYSQL Maximum Packet Size
MYSQL Maximum No. Connection
MYSQL Query Cache Size

I found this information via https://github.com/lesterchan/wp-serverinfo (https://wordpress.org/plugins/wp-serverinfo/).

I don't think we need the complete list ("Display MYSQL Information" in the plugin mentioned), but those three values from the overview page seem to be very helpful.

Attachments (2)

53845.diff (1.6 KB) - added by SergeyBiryukov 2 months ago.
53845.2.diff (592 bytes) - added by zodiac1978 4 weeks ago.
Removing the Query cache size part because of deprecation issues

Download all attachments as: .zip

Change History (17)

This ticket was mentioned in PR #1533 on WordPress/wordpress-develop by donmhico.


2 months ago

  • Keywords has-patch added; needs-patch removed

This PR adds additional DB info in Site Debug.

https://i0.wp.com/user-images.githubusercontent.com/5747475/127732436-48916ffe-860c-4396-ac06-013f3d5882f7.jpg

Trac ticket: https://core.trac.wordpress.org/ticket/53845

#2 @donmhico
2 months ago

Hello @zodiac1978,

Thanks for the request, I actually had a recent experience where I had trouble finding the cause of an issue where an option is not being updated and after diving deeper, I found out that it's because of a Max Packet size issue. This info would definitely help.

I've created a PR above, feel free to test it out and let me know if you have any feedback.

#3 @SergeyBiryukov
2 months ago

  • Milestone changed from Awaiting Review to 5.9

#4 @mukesh27
2 months ago

  • Keywords needs-refresh added

@donmhico The PR fails in PHP coding standards. Can you please correct it so others can test the PR/Patch?

#5 @zodiac1978
2 months ago

  • Keywords needs-refresh removed

@donmhico has added some fixes and now all checks pass.

Thanks for the PR/patch!

#6 @SergeyBiryukov
2 months ago

  • Keywords dev-feedback removed

Thanks for the PR! It looks good to me.

Some minor tweaks in 53845.diff:

  • I think we should not use size_format() here. As previously noted in comment:1:ticket:52471, the purpose of the Site Health Info screen is to provide raw data as reported by PHP or MySQL for debugging purposes, so we'd want to avoid any modifications of the displayed data. This can be reconsidered later in #52471 if necessary.
  • Pretty much like database server version or client version, I don't think these new values should be marked as "private". That would prevent them from being copied when the "Copy site info to clipboard" button is clicked.
  • The labels should use sentence case rather than title case, for consistency with other labels.
  • The ::get_mysql_var() method @return value should mention the expected result first.

#7 @SergeyBiryukov
2 months ago

  • Owner set to SergeyBiryukov
  • Resolution set to fixed
  • Status changed from new to closed

In 51522:

Site Health: Add some more MySQL information to the Site Health Info screen.

This adds three values to the debug info in the Database section:

  • Max allowed packet size
  • Max connections number
  • Query cache size

Props zodiac1978, donmhico, mukesh27, SergeyBiryukov.
Fixes #53845.

#8 @SergeyBiryukov
2 months ago

In 51523:

Docs: Document the $wpdb global in WP_Debug_Data::get_mysql_var().

Follow-up to [51522].

See #53845.

#9 follow-up: @johnbillion
8 weeks ago

  • Keywords needs-patch added; has-patch removed
  • Resolution fixed deleted
  • Status changed from closed to reopened

Any query_cache_* variable lookups need to be excluded when the MySQL version is 5.7.20 or higher as the query cache is deprecated. Details: https://github.com/johnbillion/query-monitor/pull/313.

This ticket was mentioned in Slack in #hosting-community by javier. View the logs.


8 weeks ago

#12 in reply to: ↑ 9 @zodiac1978
7 weeks ago

Replying to johnbillion:

Any query_cache_* variable lookups need to be excluded when the MySQL version is 5.7.20 or higher as the query cache is deprecated. Details: https://github.com/johnbillion/query-monitor/pull/313.

Deprecation does not mean it is not used, or not? This setting is not removed until MySQL 8, so we could add a check for MySQL 8 to show it or not.

The docs (https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html) are also mentioning a second value query_cache_type. If this is set to 0 or OFF the cache is not used.

So we could just show it for MySQL < 8 and query_cache_type set to ON/1 or 2/DEMAND.

Or we remove this third value completely and just go with max. connections and max. allowed packet size.

@zodiac1978
4 weeks ago

Removing the Query cache size part because of deprecation issues

#13 @zodiac1978
4 weeks ago

To keep the data in Site Health small and useful I made a patch for removing the "Query cache size" info. If anyone thinks it should be added, we just need a further patch with a test for MySQL < 8 (or 5.7.20 if we go with the deprecation to prevent warnings in errorlogs) and query_cache_type set to ON/1 or 2/DEMAND.

#14 @SergeyBiryukov
4 weeks ago

  • Keywords has-patch added; needs-patch removed

#15 @SergeyBiryukov
4 weeks ago

  • Resolution set to fixed
  • Status changed from reopened to closed

In 51715:

Site Health: Remove MySQL query cache size from the Site Health Info screen.

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Reference: MySQL 5.7 Reference Manual: Query Cache Configuration.

Follow-up to [51522], [51523].

Props johnbillion, zodiac1978.
Fixes #53845.

Note: See TracTickets for help on using tickets.