MySQL queries causing load and filling up

This forum is for general questions about extensions for Joomla! 3.x.

Moderators: pe7er, General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Post Reply
angelo_klarity
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Wed Jul 10, 2019 12:45 pm

MySQL queries causing load and filling up

Post by angelo_klarity » Wed Jul 10, 2019 1:04 pm

Hi All,

First time poster, but need urgent assistance.

We currently running Joomla! 3.6.4. Our server admins just came back on multiple times advising us that there is a MySQL query that is running multiple times, which is causing the /tmp directory on the server to fill up

Looks like the Orders table could be causing the issue. its currently @ 1390429 rows

Query:
EXPLAIN SELECT DISTINCT a.*,uc.name AS editor FROM `xxso2_thrifty_orders` AS a LEFT JOIN xxso2_users AS uc ON uc.id=a.checked_out LEFT JOIN xxso2_users AS created_by ON created_by.id = a.created_by ORDER BY ordering asc LIMIT 50:

*** row 1 ***
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 795949
Extra: Using temporary; Using filesort
*** row 2 ***
table: uc
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: thriftydb.a.checked_out
rows: 1
Extra: NUL

I attempted to fix this by changing the /tmp directory path in the configuration.php
from
$tmp_path = '/usr/www/users/thrifyvqvb/tmp/';
to
$tmp_path = '.tmp';

But looks like it still didn't work

The developers advised that the query runs through 795949 rows and there is no indexing or optimisation on the table.

Which columns should be indexed or optimised??

Could anyone assist please, much appreciated
Last edited by toivo on Wed Jul 10, 2019 2:41 pm, edited 1 time in total.
Reason: mod note: moved to 3.x Extensions

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 22194
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, The Netherlands
Contact:

Re: MySQL queries causing load and filling up

Post by pe7er » Wed Jul 10, 2019 3:36 pm

angelo_klarity wrote:
Wed Jul 10, 2019 1:04 pm
We currently running Joomla! 3.6.4. Our server admins just came back on multiple times advising us that there is a MySQL query that is running multiple times, which is causing the /tmp directory on the server to fill up

[..]

The developers advised that the query runs through 795949 rows and there is no indexing or optimisation on the table.

Which columns should be indexed or optimised??
Before trying to solve an issue in an outdated Joomla version,
you'd better upgrade to the most recent Joomla version,
which is of today Joomla 3.9.10:
  • 3.6.4 has been released almost 2 years ago. In the meantime a lot of improvements have been made. Maybe your issue has been solved in one of the versions between 3.6.4 to 3.9.10. Trying to fix an issue in your outdated version is a waste of your time.
  • Since 3.6.4 there have been released some security patches. It's important to keep your site up-to-date to keep it secure.
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
Co-developer of d2 Content https://data2site.com/joomla-extensions/d2-content


Post Reply

Return to “Extensions for Joomla! 3.x”