Large database query issue - [Solved]

[treat2day]

Query issue has no specific connection to my activity.

The opening of link to subcategories and files were timing out. In Joomla 3, cache was the key to fix the slow response to opening the link.


In Joomla 4 Plugins: System - jDownloads, I turned on Re-Enable Caching After Dispatch. I have no idea what it does but it seems to fix the timing out issue.

For the Database it seems Joomla4 is more space hungry so try allocating more memory

Will need to refer  this to Arno as he does all the database stuff - can I share the info you sent with him ?Also could you start another 'stream' in the Forum please as this is the second case with sites that have a large number of Downloads.

Also what were you searching for eg a specific Download or Category or ?

Colin

The query that is problem is attached.

Web host support team has been helping to find a solution.

Host Customer Support Team

You currently have 8GB of RAM and 1000 GB of disk space on your dedicated server.

At the moment you're using about 38% or 334G of your available disk space and about 4G of RAM with another 3.5G held in buff/cache ready to be deployed with the remaining 500M free.

A large portion of your CPU and RAM have been allocated to MySQL.

Here's a peek at how your MySQL server is performing:

Uptime:       2 hours 56 sec

Threads: 38  Questions: 17019157  Slow queries: 237473   Opens: 81363
Flush tables: 3  Open tables: 2048  Queries per second avg: 2345.528

It's pretty active with a large number of slow queries.

The server load can definitely contribute to the query time.

Obviously, inefficient queries can add to the server load.

The modifications made to the my.cnf appear to have kept the server load down.

I think adding indexes to your larger tables will speed up JOIN operations and help MySQL retrieve data faster, thereby improving your query response time.

Any suggestions would very helpful.

Four domains with large database 4K - 6k .

This one is the largest with 10K.

[Arno]

Hi,
just briefly on the quick:
A jDownloads installation with >= 10.000 downloads is a lot. But I know that there are quite a few users who have been running websites with that many downloads for many years.

Surely it is also crucial how many records the other tables of the database have. For example, the number of jDownloads categories or users.
Often log tables grow unnecessarily over the years and sometimes contain hundreds of thousands of records. Here is then also times a clean up announced. Naturally further (purposeful) indexes can help. But for this you have to take a closer look.

The attached file shows that 2.8 million records were searched during the query? How do they come about?

Please contact me via PM and give me more information about your DB.

[treat2day]

Surely it is also crucial how many records the other tables of the database have. For example, the number of jDownloads categories or users.
Often log tables grow unnecessarily over the years and sometimes contain hundreds of thousands of records. Here is then also times a clean up announced. Naturally further (purposeful) indexes can help. But for this you have to take a closer look.

Total Number of listed Logged Actions: 14 shown in Control Panel - Logs
Since the upgrade from Joomla 3 to Joomla 4.

I am the only user.

Do not understand logs clean up.

I limit the amount of logs and use the captcha for public downloads. I assumed logs were over written they reach the maximum set in the the plugin set to 93. If not self cleaned in database, logs has never been deleted from the initial setup 10 years ago.

Sent PM message.

[ColinM]

Hi
If you click on the jD Control Panel you should see a panel at top left Stats & Info
In that panel there are three tabs  Monitoring Logs, Restore Logs  and Installation Logs.

The Monitoring Log looks huge - the other two are OK.  Each Log has a button to to delete its contents so I suggest you do that for the Monitoring Log.

Colin

[treat2day]

Bravo.

These are the dates of the queries in question.

Looks like this ends another successful post with results.
I am good with the progress in JD 4.

Nore optimization in Joomla 4 starting to see results.

Thanks for all your help.

[Arno]

Hi,
you have an issue with your DB collations. See pic.

Should I try to correct this? In this case I would create first an Akeeba backup.
Or would you like to do it yourself?

[treat2day]

I have changed these collations many times to phpMyAdmin default utf8mb4_unicode_ci.

1. When I change phpMyAdmin server default to utf8mb4_unicode_520_ci it reverts back to phpMyAdmin default utf8mb4_unicode_ci

1. Changed the tables and columns utf8mb4_unicode_ci default.

Maybe Joomla update 4.3.4 changes it to utf8mb4_unicode_520_ci

I do not know how they are returning to utf8mb4_unicode_520_ci and a few times to _0900_ai_ci

[Arno]

Okay, I will look at this tomorrow.

[treat2day]

Yesterday changed all to utf8mb4_unicode_520_ci
Today they are all mixed again. see image.

[treat2day]

After many searches and a lot of "answers," I found only one that is specific to MySQL 8 collation.

It also explains why server keeps getting utf8mb4_0900_ai_ci

Unfortunately, the procedure is above my knowledge of simply changing collation types.

The technical explanations and examples not posted in reply.

See link for full explanation.
https://dev.mysql.com/doc/refman/8.0/en/charset-server.html

10.3.2 Server Character Set and Collation

Excerpts

MySQL Server has a server character set and a server collation. By default, these are utf8mb4 and utf8mb4_0900_ai_ci, but they can be set explicitly at server startup on the command line or in an option file and changed at runtime.

The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

[treat2day]

SOLVED Mixed collation issue.

Important: Change table and column collation, MyISAM, and InnoDB after you change the MySQL Server -  see image

"Joomla 4 CORE" uses InnoDB database type and Collation utf8mb4_unicode_ci

jDownloads installation respects these settings InnoDB and Collation utf8mb4_unicode_ci in the database.

This can be verified and tested by using Xampp to install "Joomla 4 CORE"

Problem: Mixed server database "type" and collation

1. utf8mb4_general_ci Web host database creation in panel is
2. utf8mb4_unicode_ci myPHPAdmin Server Database
3. MyISAM mixed with InnoDB database

Some extensions are installing with both utf8 and utf8mb4 collations
Some extensions use MyISAM mixed with InnoDB database

Examples "tables and columns" are not always the same
utf8mb4_general_ci
utf8mb4_unicode_ci
utf8mb4_unicode_520_ci

Solution:

Xampp and Web host MySQL server .ini

Change collation \xampp\mysql\bin\my.ini

Important: repeat on live site before uploading backup with Xampp MySQL server my.ini collation fix

1. Change
##The MySQL server
default-character-set=utf8mb4
2. Change
## UTF 8 Settings
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Solution: Setting the SQL Mode
Errors - #1067 - timestamp and/or server time out

1. Change
## UTF 8 Settings
sql_mode=ALLOW_INVALID_DATES

Documentation

-- MySQL 8 SQL Mode --
5.1.11 Server SQL Modes
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

-- MySQL 8 collation --
10.3.2 Server Character Set and Collation
See link for full explanation.
https://dev.mysql.com/doc/refman/8.0/en/charset-server.html

More info on Should I use InnoDB with Joomla Sites?
https://www.liquidweb.com/kb/mysql-performance-myisam-vs-innodb