API SQL Commands Drive High Server Resouces Utilization

[jmcafee]

My web host throttled my server resources, crippling the web site performance, because MT was consuming excessive server resources.

I made an edit to the api.php file that dramatically reduced utilization, made my host happy, and restored full server resources and web site performance. Thus far I have not seen any unintended consequences.

I need to know if there are any long term issues that could arise. And if not, Jachym would you consider modifying the api.php as I describe below?

My web site hosts 2 different MT sites. The databases have about 1.9M and 1.4M records. The api.php frequently issues the "ALTER TABLE alldata ORDER BY DateTime" sql query, and it would take from 2-18 seconds to complete. This gobbled lots of resources and caused server faults. I checked/optimized the tables, but this didn't change the performance. The graphs of resource use were quite impressive.

So I commented out the line after research indicated the command was an unnecessary task since the table is already indexed. The resource utilization dropped next to nothing except for a few brief spikes.

Is there any reason why I should not also comment out the "ALTER TABLE alldataExtra ORDER BY DateTime" query?

[Jachym]

Hi,
there is a reason for it because it is necessary for the data to be in correct order (by date). This can be a problem when importing history data etc.
However in the latest version of MT, this command was changed so that it is only run once after midnight every day

[jmcafee]

Hi,
I am using v17.1 of MT, api.php v3.1.

Attached is the snapshot of the DB I/O for the last 30 days. The dramatic drop in I/O is where I edited the api.php file.

Your thoughts?

Screenshot_2019-05-27 cPanel - Resource Usage Overview.png (61.57 KiB) Viewed 9742 times

[jmcafee]

One other observation. My Notepad++ editor indicates that the original api.php has Windows style EOL (CRLF). When I saved my version with the ALTER TABLE command commented out, it was saved as a Unix EOL (LF) and about 1600 bytes smaller. It it possible that the EOL makes a difference?

[jmcafee]

Hey Jachym,

Here is the evidence that there is a problem with line 1482 ("ALTER TABLES...") in api.php that isn't executing as you expect.

The snapshot of the last 7 days of the db I/O is instructive:
Section 1 is where the 2 api.php files (for both websites) is unmodified from the 17.1 update.
Section 2 is when the files are commented out at line 1482
Section 3 is when 1 of the 2 api.php files is commented, the other is uncommented. Both are saved with Unix EOL.
Section 4 is where both have line 1482 commented. Identical to Section 2.

So it looks like the ALTER TABLES sql command is still executed every 5 minutes.

Jim

Screenshot_2019-05-28 cPanel - Resource Usage Overview MOD.jpg (77.91 KiB) Viewed 9718 times

[staze]

What format is your database? InnoDB or MyISAM?

[Jachym]

Hi,
this is something I fixed (or at least thought I did) in v 17.0. However, I just realized this command is executed at two places in the code and I only fixed one. This will be changed in v17.3.

I do not recommend deleting the line completely, there is a very important reason for having it, however it does not have to be executed every db update, but only at longer intervals.