Jump to content
TNG Community
MadRanger

High CPU use by cp_ queries (Census Plus)

Recommended Posts

MadRanger

My site was shut down because of exceedingly high CPU usage. When I asked the provider for help I was sent this an example:

1. Executed 9h 35m 8s ago for 1300.55894 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2022-06-12 14:28:53 Query_time: 1300.558940 Rows_examined: 542445: Rows_sent 1 Lock_time: 0.000270 Query_chars: 1316
(SELECT  `cp_data`.*, `cp_link`.*, `cp_summary`.*, `cp_veteran`.*, coalesce(cp_data.surname, cp_summary.surname, cp_veteran.surname, 'No name') as surname, coalesce(cp_data.givenname, cp_summary.givenname, cp_veteran.givenname, 'No givenname') as giv
 ...... 
*/'/**/OR/**/UPDATEXML(5947,CONCAT(0x44754b41,(SELECT/**/(ELT(2836=2836,1))),0x315a7051),5431)#/**/oovV' WHERE	cp_data.cpbaseID = '9144' OR cp_data.transcriptID = 'dbid=6224&iid=4638832_00658' GROUP BY `cp_data`.cpdataID ORDER BY `cp_data`.lineno ); 

Edit:

Some of the queries ran for over 200 seconds, with some even reaching over 1000 seconds. Those are extremely slow MySQL queries and need to be addressed.

Such queries are rather odd, considering that your database sizes range just above 160MB :
carrfami_genealogy 172.5 MB

  carrfami_piwik              160.3 MB

Which suggests that there could be some issues with the scripts themselves. Those queries appear to have mostly originated from the following script : 8704 http://carrfamilytree.com/genealogy/cpdisplay.php /home/u5-8ubdvmht0r3c/www/carrfamilytree.com/public_html/genealogy/cpdisplay.php

Can anyone point me in the right direction? Ken? :)

I hit the road tomorrow and will be gone for 5 days with no access to my xampp - will try and drag the wife's laptop with me.

Thanks,

Regis

www.CarrFamilyTree.com

TNG 13.0

Census Plus v13.0.0.11

Edit: Siteground will allow me access when I know what to do...

 

 

Share this post


Link to post
Ken Roy

Regis,

If you are still using v13.0.0.11, you should download and install the latest Census Plus International v13.0.0.13_b1version.  The b1 designation is only to signify that it is not the final.  That version is required for PHP 8 and 8.1

 

Share this post


Link to post
MadRanger

Ken,

Thank you. I will get to that upgrade after I sort out the problem which the provider thinks is a sql query. I thought it could have been a corrupted database so I prepared a new mysqldumper version to ftp to the site but I have not restored it to the production site.

Having been afforded limited access to the site from my home IP, I Optimized the tables and then checked to see it Census Plus was working - it was on at least one page. I looked back at provider's ticket and found this url -http://carrfamilytree.com/genealogy/cpdisplay.php

It displays an empty "Census Transcript." I checked the census transcript in a random 1900 Census on a person's page - it renders properly.

I also ran the query I copied into my OP above -- and ran it on my phpmyadmin in the cp_data table and got a syntax error. I don't know if a syntax error would generate a CPU usage issue. I have minimal sql expertise, so I have no idea what the syntax error is.

So I am at the point where I can't replicate the problem.

Share this post


Link to post
Ken Roy

Well if your host has upgraded PHP versions that might be the root cause of the problem.  The mod version you are using does not support the current PHP versions.  The current Census Plus International mod fixes a lot of PHP issues and may fix your issue. 

Can you provide the exact message you got from phpMyAdmin as to the syntax error, and the query you entered.

 

Share this post


Link to post
MadRanger

I have been shut down indefinitely. Here is Undergrounds explanation:

Thank you for the update.

I believe the best approach in this case in order to address the problem for good and optimize your database, is to consult with a professional database developer or your developer who can assist you further on the matter. I am afraid that at this point we cannot remove the limitation until the database has been optimized as such action would affect the rest of the users on the shared hosting environment. The database is still generating slow SQL queries and I am providing you with a couple of examples:

SET timestamp=1655113898;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `laz_postmeta`;
# Time: 2022-06-13T10:20:19.797990Z
# User@Host: carrfami_RJCarr[carrfami_RJCarr] @ localhost []  Id: 92219
# Schema: carrfami_genealogy  Last_errno: 0  Killed: 0
# Query_time: 2.422812  Lock_time: 0.001311  Rows_sent: 50  Rows_examined: 495829  Rows_affected: 0
# Bytes_sent: 33968
use carrfami_genealogy;
SET timestamp=1655115619;
(SELECT  `cp_data`.*, `cp_link`.*, `cp_summary`.*, `cp_veteran`.*,
coalesce(cp_data.surname, cp_summary.surname, cp_veteran.surname, 'No name') as surname,
coalesce(cp_data.givenname, cp_summary.givenname, cp_veteran.givenname, 'No givenname') as givenname,
--
ORDER BY `cp_data`.lineno
);
# Time: 2022-06-13T10:21:18.062960Z
# User@Host: carrfami_RJCarr[carrfami_RJCarr] @ localhost []  Id: 92447
# Schema: carrfami_genealogy  Last_errno: 0  Killed: 0
# Query_time: 2.405875  Lock_time: 0.000610  Rows_sent: 50  Rows_examined: 495829  Rows_affected: 0
# Bytes_sent: 33968
SET timestamp=1655115678;
(SELECT  `cp_data`.*, `cp_link`.*, `cp_summary`.*, `cp_veteran`.*,

Once ready, please update the ticket so we can review the case.

Siteground

Share this post


Link to post
Ken Roy

Regis,

When you get back send me the information offlist.  You have my email address or use my Contact Us page.

Thanks

Share this post


Link to post
Ken Roy

Regis,

I think I figured out the issue but am having to re-write day one code written by Janice Nation.

When you get back send me an email to see if I have completed the re-write of the code

Share this post


Link to post
MadRanger

Ken,

I tried to reply via iPhone to this post while away and lost the effort while working with another window.

I replied to the service provider that the SQL queries had been in use for years without a CPU issue, so I posited that the problem might be the Google Analytics code I had placed in the meta.php in May in the manner theKiwi posted in January this year. When the provider gave me temp access to my site, I deleted the code. The provider then gave me a 24-hour window of universal access. When the CPU usage then fell into normal rage, they closed my ticket. I am just back from my trip and the site is still has public access.

I have no clue why the cod impacted CPU usage. None.

My file notes read:

Removed the code below on 13 June, 2022 after Siteground shut down the website due to CPU consumption issues.
<!-- Global site tag (gtag.js) - Google Analytics -->
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-xxxxxxxx-1"></script>
<script>
window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

 

 

  gtag('config', 'UA-33391705-1');

</script>

With respect to the queries - when I told the provider I had no expertise in queries they suggested I hire someone to get the stuff right. I replied that the queries were in use worldwide. That may have mollified them. I did try to run some of the above cited queries in my cPanel - but did not know if I was getting the proper returns.

I'll send the backup file posthaste via email.

Thank you,

Regis

Share this post


Link to post
MadRanger

If anyone can explain why the Google Analytics code issued by Google and pasted into Meta.php per theKiwi's guidance in January would impact CPU usage, this blind (deaf mostly) squirrel could use the insight.

Thanks,

Regis

Share this post


Link to post
Ken Roy
On 6/20/2022 at 3:39 PM, MadRanger said:

I'll send the backup file posthaste via email.

Thank you,

Regis

Regis,

Thanks for sending me a copy of your database.  I am posting my reply to you via email for the benefit of others

I am not seeing anything wrong that would cause the high CPU usage you encountered, but would offer the following suggestions:

  • upgrade your site to TNG 13.1.2, especially if you host is running current PHP versions
  • upgrade to use the Census Plus International v13.0.0.13 b1

In addition (not in the email), I would suggest deleting unused rows for the census transcript by using the Admin > Census Plus International > Edit tab to reduce the size of your cp_data table.  This is only relate to not being able to restore you cp_data table from the TNG backups.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×