Bug MySQL Possible Load Bug

[Mick]

Hi Fillip H.,

I am not sure if this is a DBSEO issue or not to be fair...

But I can only make this happen with SAVING on the general DBSEO control panel page.

I have attachments aswell that should explain it better, But let me explain...

Over time my MySQL CPU usage goes quite high in the 20-40 range and it stays over 20 too rarely going in the 10's, however if I click the save button in the General settings page the load will drop drastically.

THIS PAGE DragonByte Tech: SEO - General Settings
IF you look at these attachments I have left the SAVE button in view, and also I have left the time in view on my desktop...


Here is attachment 1 before I click the save button.


Attachment 2 before I click the save button


OK I have now clicked the save button then done a screen GRAB


Another a little later after the save button.


Thanks for your time.

 

[Fillip H.]

I can't say I can think of anything that might be causing that, unless it's the URL lookup history. Is your forum very large, and do users browse a very large selection of pages?

 

[Mick]

Yeah we have over 2 million posts, and around 230,000 members, 15000 visits a day usually and around 40,000-60,000 page views,

To be honest I am configuring my php server so it is temporarily on my MySQL server until I can get it sorted as we have 3 servers usually, php, MySQL, and backup servers plus cdn for images.

Should I turn that option off, but I have recently moved all my pages, so should I wait a month then turn it off?

Cheers Fillip H.

Mick

 

[Fillip H.]

It's not a system that can be turned off, it's automatic in the background. I'll run some tests whether I need to apply an index to one or more of the columns over the course of the week

 

[Mick]

It's not a system that can be turned off, it's automatic in the background. I'll run some tests whether I need to apply an index to one or more of the columns over the course of the week

Hi Fillip H.,

Sorry to dig this up again, but I have had a few out of web emergencies and not been about to do anything with my site, today the load was incredibly high due to this feature?

Mysql load was over 200% at times, and as soon as I clicked the save button it would be less than 10%.

Here is a picture before I pressed the save button.



Thanks again, I really do appreciate your efforts...

 

[neounix]

FWIW, I noticed a similar problem the other day when I was editing (vi'ing) one of the DBSEO include() files, and I noticed PHP errors because, I thought, the DBSEO PHP files were being edited, and for some reason, this "seemed" to be causing DBSEO problem to include those files (which were include() files), during editing.

Again, I did not look into it; nor did I try to repeat it; so the issue I experienced could be completed unrelated, or not even an issue at all !

 

[Fillip H.]

The next time this happens, please inspect the size of the dbtech_dbseo_resolvedurl database table.

We may also attempt to add some indexes to that table, if the culprit is indeed that table. Please do not clear the cache by clicking save until we have attempted to resolve this

 

[Mick]

np problem Fillip H., I will leave it until tomorrow evening it will be the same by then...

I will report back with the findings of that table or any other table you want me to check via phpmyadmin

Thanks again.

Mick

 

[Mick]

Fillip H.,

I just thought I would update this post now so you see a snapshot of now and also tomorrow.

Roughly the last time I clicked the save button (cleared the cache) was around an hour ago!

 

[neounix]

Haha.. maybe that's why we don't see this problem.. ROTFL

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0008 sec)
SQL query:
SELECT *
FROM `dbtech_dbseo_resolvedurl`
LIMIT 0 , 30

Our dbtech_dbseo_resolvedurl table is completely empty and our site runs great!

ROTFL

 

[Mick]

Here you go Fillip H.,

Almost 24 hours, hopefully this is helpful.



Thanks again!

Cheers
Mick

 

[Fillip H.]

Can you run the following query:

ALTER TABLE `theforumprefix__dbtech_dbseo_resolvedurl` ADD INDEX ( `seourl` ( 255 ) ) ;

Then observe the load around 10-15 minutes after the index has been added successfully.

If the index takes too much time to load, you can flush the cache, then add the index, then wait for it to populate again

 

[Mick]

that took less than a second

here is the output...

MariaDB> ALTER TABLE `theforumprefix__dbtech_dbseo_resolvedurl` ADD INDEX ( `seourl` ( 255 ) ) ;
Query OK, 269540 rows affected (0.89 sec)
Records: 269540 Duplicates: 0 Warnings: 0

Regards
Mick

 

[Mick]

I would personally say that was a big success!!

Also I never pressed the save button either



Fillip H., do you mind if I asked what that INDEX did and how that would have affected the load in such a dramatic way?

Thanks again, that has really taken the sting out of the LOAD.

Cheers

 

[Fillip H.]

An index is basically a more efficient way for queries to execute. It speeds up lookup, but on the flip side it slows down inserts, so it's not a good idea to INDEX ALL THE THINGS as you'll do way more harm than good

 

[Nirjonadda]

Does This issue Effect on my site ? I have not checked this Test.

 

[Fillip H.]

If you haven't noticed it, then it probably doesn't.

It's not recommended to manually add the index in order to avoid double indexing when v1.0.1 is released.

 

[Mick]

It's not recommended to manually add the index in order to avoid double indexing when v1.0.1 is released.

Could you post the removal sql, so that I can remove it before I upgrade to the 1.0.1 version.

Cheers.
Mick

 

[Fillip H.]

ALTER TABLE `theforumprefix__dbtech_dbseo_resolvedurl` DROP INDEX `seourl`  ;

 

[RedWS600]

Thanks for this thread. I was having the same issue. Trying to track things down, and disabling all vbulletin plugins the server load would go. I found this thread and made the change (keeping note to change back before update) as it too reduced the server load for me.

This was my load test the day i installed DBSEO:- https://a.blazemeter.com/report/NDI1MDI0
This was my load test from today, no other changed:- https://a.blazemeter.com/report/r-ec52df9292a3945
So with the same simulated 50 uses and the same pages, I've gone from 900ms load times to well over 20 seconds. My processor load during the resent test was very high indeed, like over 40. usually it sits under 0.3.

Thanks again for posting the workaround.