recent index.html problem

[shadowood1]

I was working on a site tonight and the site was shutdown by the host for a massive 18% cpu spike, this site has been recently upgraded (last several weeks) to 2.3 version. Seems the support staff said this:
______________________________________________________________________

Here is a summary of your CPU and memory usage on your account over the past 24 hours:
CPU: 2.47 %
MEM: 0.71 %
We believe the processes shown below are responsible for this level of resource usage, please keep in mind if you run a dynamic site off WordPress or another script it might just show index.php which is the primary file that runs the entire site:
==========
artanv articleanvil.com 2.47 0.71 1.8
Top Process %CPU 18.0 /usr/bin/php /home/artanv/public_html/index.php
Top Process %CPU 14.0 /usr/bin/php /home/artanv/public_html/admin/index.php
Top Process %CPU 11.0 /usr/bin/php /home/artanv/public_html/index.php
==========

While I was doing some general edits, adding banner blocks etc, I did nothing that should have caused such a spike. The site has been intact since like 2005, and has 75k articles active with 123k more to go, and 50k users.

The resource usage has been steady at 3.4 gigs xfer and a few hundred hits a day without problem.

Further info from support:

Also we have found your mysql queries are causing major problem for the server. And queries are consuming too much cpu time.
====
USER: artanv_articlems
CPU_TIME: 801
595 rows in set (0.00 sec)
====
We have found too many logs in slow quereies for your database:
===
User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 2838
use artanv_articlems;
SELECT COUNT(*) AS found_rows FROM ams_articles WHERE article_state = 1 AND MATCH (article_title, article_text, article_keywords) AGAINST('I Still Remember You: The Future of Social Networking') LIMIT 300;
# Time: 100419 8:30:16
===

I am hoping to get this site back up and running, but am waiting for support to release / restore the files (they deleted them it seems as I cannot access them, trust me I am pissed but thats a seperate issue).

I know there was an issue with a sql injection attacks vulnerability in the past? have these been corrected?

The only change I could have made that might impact things was the "allowed tags" for posts, I removed the "ALL" and put in the tags for i,u,a, etc, but not sure if it needed commas seperating the tags, or if it needed closed tags ex: <i> </i>. so not sure if that screwed anything up

More from support:

Thank you for your attention to this, and I apologize for these issues. I re-enabled your account to see the usage, and the server's load immediately started to spike. It had started spawning many processes for your site's index.php, and I've attached a file that shows a snippet of these processes. Your account was also generating many long mysql queries that were causing tmp table writes, which cause performance issues as well. I've added this to the attached file also.
If you believe that this could be due to coding related issues with your site, then I'll be happy to create a backup for you to download so that you may investigate in a local environment.

Iwill try to check the attached files and post more.

Please I need help so bad.

Derek

[Dejavu]

This query in the slow log:

Code: [Select]

SELECT COUNT(*) AS found_rows FROM ams_articles WHERE article_state = 1 AND MATCH (article_title, article_text, article_keywords) AGAINST('I Still Remember You: The Future of Social Networking') LIMIT 300;
# Time: 100419 8:30:16 is a search on your site.

With 75k+ articles it might be slow to search and it is possible that bots are automatically accessing the search page.  I would suggest you try switching off the search function for now (in the admin panel find the global settings -> url settings, and change the search url to something random (eg search323423)

If that doesnt fix it ask you host for more entries from the slow_query_log, without it it is difficult to know what the problem can be.

You should also enable the cron option (under settings), it will help reduce the number of times articles are recounted (which can add to the site load)

[shadowood1]

You asked, here it is:

From support

# PHP Processes#


27310 artanv    20   0  127m  20m 7180 S  5.6  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26367 artanv    20   0  127m  20m 7184 S  0.0  0.2   0:00.18 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26534 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26703 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26583 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.19 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26827 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26995 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26897 artanv    20   0  127m  20m 7180 S  0.0  0.2   0:00.17 /usr/bin/php /home/artanv/public_html/index.php                                                                                 
26504 artanv    20   0  125m  19m 7176 S  0.0  0.2   0:00.13 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26875 artanv    20   0  125m  19m 7176 S  0.0  0.2   0:00.12 /usr/bin/php /home/artanv/public_html/index.php                                                                           
26981 artanv    20   0  125m  19m 7176 S  0.0  0.2   0:00.13 /usr/bin/php /home/artanv/public_html/index.php                                                                           
27262 artanv    20   0  125m  19m 7176 S  0.0  0.2   0:00.13 /usr/bin/php /home/artanv/public_html/index.php 

####

#Mysql Queries #
-----------------+
| 50585 | artanv_articlems | localhost | artanv_articlems | Query   | 56   | Sorting result       | SELECT * FROM ams_s1_articles
                        INNER JOIN  ams_users ON user_id = article_author_id
        |
| 50665 | artanv_articlems | localhost | artanv_articlems | Query   | 39   | Copying to tmp table | SELECT * FROM ams_s1_articles
                        INNER JOIN  ams_users ON user_id = article_author_id
        |
| 50686 | artanv_articlems | localhost | artanv_articlems | Query   | 35   | Sorting result       | SELECT * FROM ams_s1_articles
                        INNER JOIN  ams_users ON user_id = article_author_id
        |
| 50721 | artanv_articlems | localhost | artanv_articlems | Query   | 27   | Sorting result       | SELECT * FROM ams_s1_articles
                        INNER JOIN  ams_users ON user_id = article_author_id
        |
| 50776 | artanv_articlems | localhost | artanv_articlems | Query   | 18   | Sorting result       | SELECT * FROM ams_s1_articles
                        INNER JOIN  ams_users ON user_id = article_author_id

####

I will likely have to disable the search function via db options as they will not reenable the site as is. claiming the traffic spikes. I do now have access to the site files and the db but they want me to move it to a vps, with little gaurantee it will help handle teh load.

The load spike was quite high and it has been running fine for the last several weeks with no problem. Not that I mind upgrading the server, but the total bw usage of 3.5 gigs should be nothing, I have a mail account that gets more transfer then that each month (damm spam),

[Dejavu]

That can not be the full query (and if it is something is a bit weird since there is no WHERE part which should really always be there).  Ask them for the entries from the slow_query_log

If you really want to get the load down you can do the following:
open your settings.php file (with a text editor) and change the line
$set['use_cache'] = 0;
to
$set['use_cache'] = 3;

Make sure you _cache directory is writable (chmod 777).  This will make ArticleMS cache all pages for guests

also
$set['log_stats'] = 1;
to
$set['log_stats'] = 0;
(this will disable view counting for articles)

[shadowood1]

My present settings.php file had neither of those entries so I added both.

I will ask the support for the information but they are fairly slow in getting back to me (so it might be a bit), even though I have made it clear I am focused on nothing else. ;-(

I do however have access back to the system, all being disabled. The only thing I remember was I was trying to fix an issue with pen names giving me a parse error, when all this started. (I made no changes to it, but noticed when I accessed the user panel it gave teh following error:

Parse error: syntax error, unexpected '>', expecting T_STRING or T_VARIABLE or '{' or '$' in /home/artanv/public_html/code/core.php(5728) : eval()'d code on line 1

so I had been trying to see if it was a skin issue and the site kept lagging so I could not get pages to load.

One thing I find curious, or not not sure is this table in teh db showing the reads for articles even this seems quite high

Code: [Select]

TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES
artanv_articlems ams_options 3508 200 600
artanv_articlems ams_media 6 0 0
artanv_articlems ams_s1_categories 1753 0 0
artanv_articlems ams_email_templates 90 0 0
artanv_articlems ams_sections 14 2 2
artanv_articlems ams_users 24809993 5 10
artanv_articlems ams_s1_articles 33134846 3 66
artanv_articlems ams_blocks 2225 1 23
artanv_articlems ams_amsskins 38 0 0
artanv_articlems ams_active 11841 732 1464
artanv_articlems ams_s1_ratings 6 0 0
artanv_articlems ams_block_cache 2 0 0
artanv_articlems ams_articleratings 20 0 0
artanv_articlems ams_amssettings 23 5 5
artanv_articlems ams_comments 67 0 0
artanv_articlems ams_s1_pages 11 0 0
artanv_articlems ams_amstemplates 246 0 0
artanv_articlems ams_email_messages 24 0 0
artanv_articlems ams_amsgroups 41 0 0
Even with 75k active messages I find it hard to believe I had 24 and 33 million reads on users and articles.

[Dejavu]

If those settings were missing you also need to make sure these are all present
//--Cache Settings--
//use_cache = 0 to disable caching
//use_cache = 1 to enable caching for search engine bots only (cached pages will only be displayed for bots)
//use_cache = 2 to enable caching for all guests/bots, articles are not cached (article stats will still update)
//use_cache = 3 to enable caching for all guests/bots, including the article pages (article stats will not update)
$set['use_cache'] = 3;
$set['use_cache_feeds'] = 0;  //(recommended) use cache for rss feeds
$set['cache_dir'] = '_cache/'; //you must set write permissions for this directory (chmod 777)
$set['cache_ext'] = 'htm'; //file extension for cached files (usually htm or cache)
$set['cache_timeout'] = 900; //cache time in seconds
$set['cache_timeout_feeds'] = 900; //cache time in seconds (for feeds)
$set['cache_use_gzip'] = 0; //use gzip cached output
$set['identify_cached_pages'] = 1; //add <!--cached-> comment after page if it is served from the cache (ignored on rss feeds)
$set['cache_exceptions'] = array('admin/','phpsessionid','PHPSESSID'); //list of url's (or parts of url's) to never cache
$set['cache_max_server_load'] = 0.8; //cache wont expire until server load goes below this number.  usefull for handling traffic spikes. (ignored on windows)

plus make sure you have _cache directory that is set to writable (chmod777)

Did you upgrade from an older version, maybe 2.0 or before?

[shadowood1]

Dejavu,

here is my original settings.php file as it sat in my folder (sensitive info x'd out)

Code: [Select]

<?php
$set = array();
$set['db_host'] = 'localhost';
$set['db_user'] = 'xxxxxxxxxxxxxx';
$set['db_name'] = 'xxxxxxxxxxxxxx';
$set['db_socket'] = '';
$set['db_pass'] = 'xxxxxxxxxxxxx';
$set['db_pre'] = 'ams_';
$set['installed'] = '1';
$set['cookie_path'] = '/';
?>
I will add in the lines you list. I was running a 1.3 install and did the entire upgrade thing several weeks ago. Had problems when I got to 2.0 but figured it out to be a permissions issue on the files, and fixed that and upgraded to 2.3 with the auto-update feature.

It has worked perfectly fine since then (until today).

I love the software and so want to get this site back up and running. :-) I appreciate all the help. let me know if you need access to the files or db if that would help. I could create a temp account for ya or zip the files if needed.

I dont have a local LAMP setup to do direct testing on. So if you want to test it locally please let me know. At this point I would pay for support to make sure it runs again. ;-)

[Dejavu]

Ok the update from 1.3 explains why that data was not in settings.php (not really an issue)

Disabling the search and then adding this caching options should be enough to get your site running fast again I would think.
With almost 100k articles/users you are pushing the limits for shared hosting though, I ran into the same issue originally (mysql seems to have a magic limit near 100k where things just become slow unless you upgrade to more cpu/ram)

[shadowood1]

how do I disable teh search without accessing teh site? I can use the phpmyadmin but cannot login as of yet. I am not opposed to moving the site to a vps or dedicated, as I was planning on it anyways ( actually emailed the company first before they disabled it to ask about the option to do so.

I am mostly broke, but not sure what level of server will handle the load. and the sql queries spiking would that mean I had to install a seperate sql server instance? I have never done my own server administration. and hate to learn on the fly

[Dejavu]

You have to login to disable the search.  Since your site is down anyway what you can do is to simply rename the index.php file (to say index2.php)
That way your site will not load anything but you can still login to the admin panel directly

[shadowood1]

well, I guess I will have to try tomorrow, cus I am sure they altered teh ownership of the file, as I cant change the name. And since they are still not responding to my tickets, there is little chance of me accomplishing this before I pass out from exhaustion.

[shadowood1]

You have to login to disable the search.  Since your site is down anyway what you can do is to simply rename the index.php file (to say index2.php)
That way your site will not load anything but you can still login to the admin panel directly

Dejavu,

I must be going blind, but where do I find teh button to disable the search??? I thought I knew but can't seem to locate it.

Ok, so I managed to get access to the site,unfortunately I changed the password (in case it was compromised) now I receive the error:

Code: [Select]

?>Your must allow cookies to login (Cookie path = '/')
EDITED: Fixed - the extra ?> was in the settings.php file you had me edit, and this also fixed cookie issue, can log back in. 

Please also note the extra "?>" at the beginning. that is showing up on the admin index page for some strange reason. I do not believe it was there yesterday.

Also, the admins reverted back the site files a couple days in case I screwed something up. When it was reenabled the traffic spiked again. Here is the queries they gave me.

Code: [Select]

# Time: 110916 19:06:58
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 444210  Schema: artanv_articlems
# Query_time: 38.366322  Lock_time: 0.000044  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
use artanv_articlems;
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;

# Time: 110916 19:06:58
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 444210  Schema: artanv_articlems
# Query_time: 38.366322  Lock_time: 0.000044  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
use artanv_articlems;
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;

 Time: 110916 19:11:13
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445213  Schema: artanv_articlems
# Query_time: 4.129807  Lock_time: 0.000109  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# Time: 110916 19:11:14
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445219  Schema: artanv_articlems
# Query_time: 4.315456  Lock_time: 0.000244  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# Time: 110916 19:11:20
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445213  Schema: artanv_articlems
# Query_time: 7.013376  Lock_time: 0.000080  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;

# Time: 110916 19:11:09
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445176  Schema: artanv_articlems
# Query_time: 7.230585  Lock_time: 0.000067  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
use artanv_articlems;
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445158  Schema: artanv_articlems
# Query_time: 11.804549  Lock_time: 0.000065  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# Time: 110916 19:11:13
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445213  Schema: artanv_articlems
# Query_time: 4.129807  Lock_time: 0.000109  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# Time: 110916 19:11:14
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445219  Schema: artanv_articlems
# Query_time: 4.315456  Lock_time: 0.000244  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;
# Time: 110916 19:11:20
# User@Host: artanv_articlems[artanv_articlems] @ localhost []
# Thread_id: 445213  Schema: artanv_articlems
# Query_time: 7.013376  Lock_time: 0.000080  Rows_sent: 5  Rows_examined: 225560  Rows_affected: 0  Rows_read: 5
SELECT * FROM ams_s1_articles^M
                                                                INNER JOIN  ams_users ON user_id = article_author_id^M
                                WHERE   article_state = 1 ^M
                                  ORDER BY RAND()  LIMIT 5;

not sure if this will help any.

On a side note, what kind of server should I consider to keep this running when it gets over 100k articles?

[Dejavu]

Interesting.. do you have any blocks/widgets that display 5 articles in random order?  You should disable those.
Also if there still is a feeds.php file in your installation you should delete it, it is not used since version 2.0

[shadowood1]

Dejavu,

Ok, I can disable the feeds. I do have a random block as I was trying to post random articles so as to alter the content from time to time?

Admittedly I did not think initially this would be such an issue. I wanted to fill up blocks of dead space in the template.

Still cannot find the search function

[Dejavu]

All the queries they posted are from the random block.  (order by RAND() )
my guess is if you disable those (and change the url of the search) things should start to go back to normal.  You should also enable the caching if you did not yet do that.