OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



MySQL database overhead

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

MySQL database overhead

Postby leossa » Tue Aug 21, 2007 9:18 am

Hi,

we are going through an issue of overhead at the mysql db server for quite some time.
Investigating a little further, we found that specific requests are been made continuously.
The queries been made are like bellow:
Code: Select all
...
SELECT * FROM comments WHERE parent_comment_id = '15250' ORDER BY date_posted
SELECT * FROM comments WHERE parent_comment_id = '14134' ORDER BY date_posted
...

And there goes on. These queries are surprinsingly consuming about 100% of the cpu load.
It seems like that request is defined in the file CommentDAO.inc.php. We have already moved the system to another more robust server but with no effect, and also upgraded the mysql server, still with no success.

Any ideas what could be the cause of the overload of the server?

leossa
leossa
 
Posts: 11
Joined: Thu Jun 28, 2007 4:25 pm

Re: MySQL database overhead

Postby asmecher » Tue Aug 21, 2007 9:22 am

Hi leossa,

Check the "comments" table to see what it contains -- if you've allowed anonymous commenting in your Journal Setup, it's likely that you're getting spammed. The next release will contain CAPTCHA testing, which will help avoid this; in the meantime, I'd suggest flushing the contents of that table and disabling anonymous commenting. We haven't heard of problems with spamming if you require registration.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8470
Joined: Wed Aug 10, 2005 12:56 pm

Re: MySQL database overhead

Postby leossa » Tue Aug 21, 2007 12:35 pm

asmecher wrote:Hi leossa,

Check the "comments" table to see what it contains -- if you've allowed anonymous commenting in your Journal Setup, it's likely that you're getting spammed. The next release will contain CAPTCHA testing, which will help avoid this; in the meantime, I'd suggest flushing the contents of that table and disabling anonymous commenting. We haven't heard of problems with spamming if you require registration.

Regards,
Alec Smecher
Public Knowledge Project Team


Hi asmecher,

I checked the `comments` table and I came to discover that there isn't no rows in the table with those `parent_comment_id` I queried, which is strange. Also, the anonymous comments are already disabled ("Registered users can post; anonymous posting is disabled.") at the journal setup. I have changed to disable any commentaries to see what I will get until tomorrow.
Although I've already seen one or another spam commentary there, shouldn't the query be something like an INSERT or UPDATE statement?

leossa
leossa
 
Posts: 11
Joined: Thu Jun 28, 2007 4:25 pm

Re: MySQL database overhead

Postby asmecher » Tue Aug 21, 2007 1:15 pm

Hi Leossa,

If you've previously had problems with spam, search engines will still try to update existing indexes for these postings, resulting in queries for comments that have been removed. These should gradually fade as the caches expire. If the overhead on the database is too high, you might edit pages/comment/CommentHandler.inc.php and comment out the contents of the view function until indexing traffic dies down.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8470
Joined: Wed Aug 10, 2005 12:56 pm


Return to OJS Technical Support

Who is online

Users browsing this forum: No registered users and 5 guests