Investigating Slow Performance and Long Execution Times of Queries

Even using cache, the search was still very slow. So, I conducted some tests to identify the issue.

Let’s consider that I have a NETSONS hosting, where on Netsons shared hosting services, the execution of the database utilizes additional resources provided by Netsons, which are not accounted for in the hosting service’s resource monitor.

In this case, we can verify that the code is exceeding its limits multiple times.

[Mon May 15 15:45:17 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 52/limit 50 loadavg(2.18 2.42 2.26 6/1019 886343) vmstat( 1  0 549080 6669960  34484 36766852    0    0   439   411    0    1 36 10 54  0  0)cpu = 0.513667, read = 0, write = 14784
[Mon May 15 15:45:18 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 51/limit 50 loadavg(2.09 2.40 2.25 4/1026 886363) vmstat( 4  0 549080 6610484  34484 36793232    0    0   439   411    0    1 36 10 54  0  0)cpu = 0.507372, read = 0, write = 14784
[Mon May 15 15:45:19 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 51/limit 50 loadavg(2.09 2.40 2.25 7/1026 886378) vmstat( 5  0 549080 6540852  34484 36797076    0    0   439   411    0    1 36 10 54  0  0)cpu = 0.501083, read = 0, write = 14784
[Mon May 15 15:46:19 2023] Restrict mode is over for user ler*****

I have also checked the presence of so-called slow queries that took more than 3 seconds to execute.

The execution of queries usually takes milliseconds, therefore queries that exceed 3 seconds are considered slow.

In my case, I see that there are queries that even exceed 45 seconds for execution.

# Time: 230515 15:38:25
# User@Host: ler**********[ler**********] @ localhost []
# Thread_id: 12676555  Schema: ler**********  QC_hit: No
# Query_time: 45.002961  Lock_time: 0.000580  Rows_sent: 0  Rows_examined: 25461902
# Rows_affected: 0  Bytes_sent: 0
SET timestamp=1684157905;
SELECT   wpan_posts.ID
                        FROM wpan_posts  LEFT JOIN wpan_postmeta ON ( wpan_posts.ID = wpan_postmeta.post_id )  LEFT JOIN wpan_postmeta AS mt1 ON ( wpan_posts.ID = mt1.post_id )  LEFT JOIN wpan_postmeta AS mt2 ON ( wpan_posts.ID = mt2.post_
id )  LEFT JOIN wpan_postmeta AS mt3 ON ( wpan_posts.ID = mt3.post_id )  LEFT JOIN wpan_postmeta AS mt4 ON ( wpan_posts.ID = mt4.post_id AND mt4.meta_key = 'hp_booking_min_length' )  LEFT JOIN wpan_postmeta AS mt5 ON ( wpan_posts.ID = mt5.
post_id )  LEFT JOIN wpan_postmeta AS mt6 ON ( wpan_posts.ID = mt6.post_id AND mt6.meta_key = 'hp_booking_offset' )  LEFT JOIN wpan_postmeta AS mt7 ON ( wpan_posts.ID = mt7.post_id )  LEFT JOIN wpan_postmeta AS mt8 ON ( wpan_posts.ID = mt8
.post_id AND mt8.meta_key = 'hp_booking_window' )
                        WHERE 1=1  AND ( 
  wpan_postmeta.meta_key = 'hp_featured' 
  AND 
  ( 
    ( mt1.meta_key = 'hp_latitude' AND CAST(mt1.meta_value AS DECIMAL(10,6)) BETWEEN '40.912355' AND '41.454979' ) 
    AND 
    ( mt2.meta_key = 'hp_longitude' AND CAST(mt2.meta_value AS DECIMAL(10,6)) BETWEEN '9.020995' AND '9.737159' ) 
    AND 
    ( 
      ( mt3.meta_key = 'hp_booking_min_length' AND CAST(mt3.meta_value AS SIGNED) <= '1' ) 
      OR 
      mt4.post_id IS NULL
    ) 
    AND 
    ( 
      ( mt5.meta_key = 'hp_booking_offset' AND CAST(mt5.meta_value AS SIGNED) <= '15' ) 
      OR 
      mt6.post_id IS NULL
    ) 
    AND 
    ( 
      ( mt7.meta_key = 'hp_booking_window' AND CAST(mt7.meta_value AS SIGNED) >= '15' ) 
      OR 
      mt8.post_id IS NULL
    )
  )
) AND wpan_posts.post_type = 'hp_listing' AND ((wpan_posts.post_status = 'publish')) AND ( SELECT COUNT(*) FROM wpan_posts AS bookings
                        INNER JOIN wpan_postmeta AS start_times ON ( bookings.ID = start_times.post_id )
                        INNER JOIN wpan_postmeta AS end_times ON ( bookings.ID = end_times.post_id )
                        WHERE bookings.post_status IN ( 'publish', 'private', 'draft', 'pending' ) AND bookings.post_type = 'hp_booking'
                        AND bookings.post_parent = wpan_posts.ID
                        AND start_times.meta_key = 'hp_start_time' AND end_times.meta_key = 'hp_end_time'
                        AND (
                                ( CAST( start_times.meta_value AS SIGNED ) BETWEEN 1685404800 and 1685491198 )
                                OR ( CAST( end_times.meta_value AS SIGNED ) BETWEEN 1685404801 and 1685491199 )
                                OR (

I am reproducing my discussion to say that according to a netsons technician:

Good morning,
Thank you for the feedback.
As mentioned, upgrading to a hosting service may not solve the issue as it is necessary to work on the database.
In this case, if you are unable to intervene, I suggest switching to a private service so that you can allocate the necessary resources to the database.
If you confirm, I will forward the request to the colleagues in the sales department.
I remain at your disposal.

Federico S.
Technical Support Operator

So if upgrading the hosting cannot solve the problem, what can be done?
I think what is needed here is an update for query optimization.

I am attaching the screenshot for the authenticity of the message.

Yes, the query may become slow depending on the number of listings and search criteria, the booking availability search is probably the most complex one. Some hosting providers set limitations on the query complexity or max RAM used for execution, switching to VPS hosting may resolve this issue. We’ll also try to optimize this by generating a “lookup” table for listings, we have this on the core roadmap.

Yes, the query may become slow depending on the number of listings and search criteria, the booking availability search is probably the most complex one. Some hosting providers set limitations on the query complexity or max RAM used for execution, switching to VPS hosting may resolve this issue. We’ll also try to optimize this by generating a “lookup” table for listings, we have this on the core roadmap.

VPS COMFORT

Hi,

Thanks for the hosting recommendation.

You can additionally check out the following hosting services:

​I hope this is helpful to you.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.