forum.coppermine-gallery.net

Support => cpg1.4.x Support => Older/other versions => cpg1.4 miscellaneous => Topic started by: JohannM on September 04, 2008, 05:32:14 pm

Title: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 05:32:14 pm
Hi there

I have upgraded to the latest coppermine version, and ever since, my server's CPU are running tops, totally 99.6% on average. This caused by mysql.

Any idea on how this can be possible ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 05:39:27 pm
Do you know which query provoke the high CPU performance? If no you should look with "mtop".
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 05:49:03 pm
mtop can you finde here: http://mtop.sourceforge.net/

Are your sure that is an mysql problem? Otherwise you should check your logfilers/traffic ... .
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 07:35:51 pm
Hi

According to the sysadmins running my server (rackforce), the CPU load is caused by mysql.  I never use to have this problem, only after the upgrade.

I am looking at mtop now, but i'm not sure how to use / install it.  The readme on the link you gave is dead.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:00:32 pm
http://sourceforge.net/project/showfiles.php?group_id=43496 --> Download

http://mtop.sourceforge.net/mtop.html Readme
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:02:30 pm
It is hard to help you, because there can be different errors. Maybe a mod/admin can help you.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:06:42 pm
But there is no error in the mysql table or? http://felipecruz.com/repair-mysql-database.php
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 08:15:51 pm
Thanx

I downloaded mtop, and tried to install it with dependencies, but I'm very stupid if it comes to unix / linux things.

The sysadmin can install this at $ 100 per hour. I simply can not afford this.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:28:06 pm
http://www.dbtuna.com/ Maybe this is easier?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:30:58 pm
Oh that cost also money  >:(

What is your url? Have you reboot your server?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 08:31:33 pm
Taking a look at the mysql.log file I believe I have tracked down the issue:
 
Note: ****** changed due to security reasons, YSF-PHOTOS2008 is the database name used on my site

# Time: 080904 18:06:52
# User@Host: *****[*****] @ localhost []
# Query_time: 1303  Lock_time: 0  Rows_sent: 50  Rows_examined: 152474977
use YSF-PHOTOS2008;
SELECT user_id as user_id, user_name as user_name, user_email as user_email, UNIX_TIMEST
AMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, use
r_active as user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as dis
k_usage, group_name, group_quota FROM `YSF-PHOTOS2008`.cpg_users AS u INNER JOIN cpg_use
rgroups AS g ON u.user_group = g.group_id LEFT JOIN cpg_pictures AS p ON p.owner_id = u.
user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 50;

According to the sysadmin, the number of rows (152474977) being queried is the source for the cpu to run in overhead / full capacity.

You know why this is ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 08:33:08 pm
Mc

Beleive me, with my limited knowledge, I have tried everything, rebooting, increasing memory, etc.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 08:35:09 pm
Thing is I am running a sports photo company here in South Africa, the photo's became to much, so I splitted the photo's into years, each on their own "year" system, each with their own directory and databse ...


www.youth-sport-fotos.com 

then to

www.youth-sport-fotos.com/2005

www.youth-sport-fotos.com/2006

www.youth-sport-fotos.com/2007

www.youth-sport-fotos.com/2008

Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 08:45:43 pm
No, I need a query overview. Do you have an mysal backup for CPG?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 08:57:21 pm
Just made one on the 2007 year system

Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 09:01:52 pm
USER:
------------------
Array
(
    [ID] => 03008bc3007081748c34145428dc4cf1
    [am] => 1
)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => Admin
    [groups] => Array
        (
           
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 0
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 0
    [can_upload_pictures] => 1
    [can_create_albums] => 0
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
)

==========================
Queries:
------------------
Array
(
   
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 09:15:56 pm
Here is the Backup Plugin, make an Backup: http://forum.coppermine-gallery.net/index.php/topic,40439.0.html ... . Maybe you must reinstall your gallery

This facts won't help me at the moment (it need time to check them),  I'm new @ CPG.

I hope Joachim can help you?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 09:20:36 pm
Here are my DEBUG, maybe it is helpful for you (7460 files in 5104 albums and 359 categories), but I don't think so:
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 09:42:43 pm
Let me think ...

When I redone 2007, I stopped with an image count of e.g. 25 847 for year 2007.  When I created the db tables for 2008, I set the auto increasement of pid to 25 848.

Do you think that can be a reason ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 10:07:00 pm
Yes if there is alot of downloading and the script is running contiuously. But not with 200 vistors/day.

Of course, if you have many visitors who watch many files you have a high CPU. If you have 10.000.000.000.000.000.000 Pics and 0 vistors you have 1-2% CPU only.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 10:18:51 pm
I do not have that much pictures on my e.g. 2008 system, as I said, I only started the auto increasement with a high number when I installed / upgraded.

But if I understand you correctly, this high "pid" number can cause the delay and cpu usage ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 10:44:22 pm
I checked your site on alexa.com in compare with autowp.ru and that is definitely no "too many" visitor problem. Maybe a bot/tramp problem but then you should have very high traffic. You told me that you have a mysql problem but I think that there was something wrong on updating (too big site?). You sould install the backup plugin make a backup, save the album folder (where your pics are inside) and download an all new cpg 1.4.19 and install it (no upgrade), install the plugin again and restore your database. But if there is someting wrong in the tabels you should look with a repair-tag (I posted the link).
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 10:47:43 pm
Check your databse first with http://felipecruz.com/repair-mysql-database.php (the problem must be in that database) or there is something wrong with your server or some grabber etc. don't like your site or want download all pics. http://www.spider-trap.de/en_index.html could help you but you should first check your logflies.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 04, 2008, 11:27:15 pm
I dumped my pictures table ... and it seems that the load on the CPU is normal now ... seems to me that there is in fact a problem somewhere.

I will have to redo this specific installation.

Thanx for your help so far.

I do not allow users to post their own pics on this, you think it is wize to delete some query strings to exclude the functions used for user pics ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 04, 2008, 11:48:33 pm
I posted the link for checking tables.  No, some cpg-webmaster forbids their user to upload pics, this could be a problem if the table is broken, but you can create a user account, enable user upload and check if something is broken, but I don't think so. But you should make from now on after every change a backup (with the plugin I posted on the first page), save all backups on your local disk and put them also on an external hard disk (for 3 month) so nothing should happen and download (copy) once a month your picture folder to your local disk, so that this can't happend anymore.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: McKenzie on September 05, 2008, 12:47:11 am
Ah your page is working  :) . Is everything fine now?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: Joachim Müller on September 05, 2008, 09:51:57 am
Maybe you must reinstall your gallery
Not a bright idea - don't!

I hope Joachim can help you?
This is not a hotline. There is no need to PM me (which is what JohannM did, although both board rules as well as my signature clearly say that you mustn't). I'm reluctant to be forced to look into, so I won't.

When I created the db tables for 2008, I set the auto increasement of pid to 25 848.
Huh? You mangled with the auto_increase option. Not a very bright move neither.

If you have 10.000.000.000.000.000.000 Pics and 0 vistors you have 1-2% CPU only.
Yeah, because you'd need the computer of the "U.S.S. Enterprise NCC-1701-E" to perform such a stunt. With computers currently available, you probably won't be able to host 10 billion billions of pics. Your example is a bit drastic ;)
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 08, 2008, 08:27:47 pm
Mc

Yes, it's seems to be ok now.  What I did is I backup my pictures table, dropped it, and re-uploaded the backup. Seems to work fine now.

As for Joachim, sorry to PM you, but I was not sure if you do monitor this kind of posts ... but by this time I know your bark is bigger than your bite ... so I won't take your remarks personally. Besides, your a fine person ...
Title: Re: CPU USAGE AFTER UPGRADING
Post by: Nibbler on September 08, 2008, 09:00:05 pm
Taking a look at the mysql.log file I believe I have tracked down the issue:
 
Note: ****** changed due to security reasons, YSF-PHOTOS2008 is the database name used on my site

# Time: 080904 18:06:52
# User@Host: *****[*****] @ localhost []
# Query_time: 1303  Lock_time: 0  Rows_sent: 50  Rows_examined: 152474977
use YSF-PHOTOS2008;
SELECT user_id as user_id, user_name as user_name, user_email as user_email, UNIX_TIMEST
AMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, use
r_active as user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as dis
k_usage, group_name, group_quota FROM `YSF-PHOTOS2008`.cpg_users AS u INNER JOIN cpg_use
rgroups AS g ON u.user_group = g.group_id LEFT JOIN cpg_pictures AS p ON p.owner_id = u.
user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 50;

According to the sysadmin, the number of rows (152474977) being queried is the source for the cpu to run in overhead / full capacity.

You know why this is ?

I do not allow users to post their own pics on this.

This issue has been posted before. MySQL doesn't like it when all the pictures have the same owner_id. If you search the board you should be able to find alternate code that helps.
Title: Re: CPU USAGE AFTER UPGRADING
Post by: JohannM on September 16, 2008, 07:43:04 pm
Nibler

I am the only one allowed to upload pics in the albums, no users are allowed.

What search string should I try to look for ?
Title: Re: CPU USAGE AFTER UPGRADING
Post by: Nibbler on September 16, 2008, 07:50:45 pm
http://forum.coppermine-gallery.net/index.php/topic,27540.msg129081.html#msg129081