Support Forum
Support Forum
Offline
nothing screams out at me in the mysql conf file... but then again, I really dont change mine from the server defaults my host sets up...
what version of sp did you upgrade from?? 5.3.3, I think you said?
The only real change in 5.3.4 was the removal of a debug statement that was affecting user display names for recent members... one other line affecting php undefined notices made it in too, but not sure how that would affect your mysql (might show serious signs of mysql setup instability)... you could try putting that one back in...
around line 89 in simple-press/sp-api/sp-error.php, right before:
E_ERROR => 'Error',
E_WARNING => 'Warning',
E_PARSE => 'Parsing Error',
E_NOTICE => 'Notice',
E_CORE_ERROR => 'Core Error',
E_CORE_WARNING => 'Core Warning',
E_COMPILE_ERROR => 'Compile Error',
E_COMPILE_WARNING => 'Compile Warning',
E_USER_ERROR => 'User Error',
E_USER_WARNING => 'User Warning',
E_USER_NOTICE => 'User Notice',
E_STRICT => 'Runtime Notice',
E_RECOVERABLE_ERROR => 'Catchable Fatal Error'
);
add back in a removed line:
if($errno == E_NOTICE && substr($errstr, 0, 9) == 'Undefined') return false;
Visit Cruise Talk Central and Mr Papa's World
Offline
Thanks Steve. I'd like to try this but could you please confirm the precise placement for that line? Placing it directly above the E_ERROR line (within that array) throws a fatal error prohibiting plugin activation. And inserting it at the line shown in my screenshot below didn't help.
But I think we may be on to something. There are a lot of the errors in the log on sferrorlog queries and you did mention this update generated a lot of new notices. We're also seeing a lot of slow queries on "SHOW TABLES LIKE" especially for wp_1_sferrorlog.
And yes, I updated to 534 from 533.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
Yellow Swordfish said
Steve made some changes to ours about a year back when we saw some of the 'gone away' messages on our server so he may recognise something perhaps.
Steve, do you recall what changes you may have made to your MySQL config that Andy is referring to? Or could you possibly provide your current my.conf file so I can have my DBA compare notes? I presume you were able to squelch the "gone away" errors.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
the gone away errors were due to host incompetence... had multiple issues with them... everything resolved nicely with our new host - no issues... and running the stock mysql conf they set us up with... could it still be improved? maybe... but I dont know enough to risk the changes... nor do I remember the tweaks I had tried based on google search results (I am far from mysql expert)...
the screenshot is correct... I said around line 89 because I have dev version of sp which may or may not be the same location... but seems my first line (the array statement) didnt make the cut and paste... sorry...
I didnt expect it to help, but it was the only other change in 5.3.4 besides removing the accidental debug code (reason for the release)... I dont understand how 5.3.4 vs 5.3.3 has anything to do with it... 3.7 is a new variable, but 5.3.3 handled and 3.7 issues...
the code change I listed, would reduce the number of notices... you wont see any more php undefined notices... we are fixing any that have popped up during the 5.3.5 development, but of course not available to you now... may take a couple releases to catch them all...
Visit Cruise Talk Central and Mr Papa's World
Offline
Mr Papa said
I dont understand how 5.3.4 vs 5.3.3 has anything to do with it...
Nor I, but that's clearly when things went awry.
If anyone gets any bright ideas, I'm all ears.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
well you could start with upping the mysql timeout value as various websites suggest. Not actually sure where this is done though.
 |
YELLOW
SWORDFISH
|
Offline
OK guys, this is where it gets fun...
I've worked with our server manager to silence all "gone away" errors. We've adjusted MySQL settings, defragmented all tables and confirmed good health of the database. I gave it ample time to confirm there are no longer any communication problems with the db and there are no longer any errors.
I then activated SimplePress while we monitored the MySQL process list and we immediately watched loads spiral out of control with constant SHOW TABLES LIKE 'wp_1_sferrorlog' queries:
| 5384 | root | localhost | | Query | 0 | | show processlist | | 5521 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5539 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5554 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5558 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5561 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5565 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5573 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5578 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5580 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5582 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5586 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5588 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5589 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5590 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5591 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' | | 5592 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 1 | checking permissions | SHOW TABLES LIKE 'wp_1_sferrorlog' |
This is just a small sample of constant entries like this that I was able to capture. The server load spiked to 25+ within a minute. Immediately upon plugin deactivation, the load quickly dropped and stabilized.
I've confirmed that all permissions are correct with the db and db user. I also ran another repair and mysqlcheck on the wp_1_sferrorlog table and all appears OK.
Could you please review the table structure and index in the attached screenshot below and advise of anything out of order? If necessary, please provide steps on how I might rebuild this table and its index if that may help.
If you recalll, when we had serious performance issues last summer Andy recommended this hack to comment out a SHOW TABLES LIKE query on the sfoptions table. I wonder if something similar may be going on or you might be able to suggest a similar fix.
One DBA I consulted suggested that SHOW TABLES LIKE may be poor practice, especially when dealing with a db that has thousands of tables like ours. Admittedly this is way over my head, so I'm desperately hoping you may be able to offer further assistance here. Is there any way to just disable the SF error log function to see if the forums will load without spinning the server out of control? Any other suggestions?
Thanks again for any direction. Our forums have now been offline for days and Tripawds members are getting restless.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
that singular index on that table looks good - at least what I would expect... will let Andy comment if more is needed...
Andy did make a change today to the error logging to ping the mysql server to make sure its there before query the like tables since a slew or errors are created if its not there (nothing we can do about that)... but if you have ceased the gone errors, that code change wont help you either...
will have to let Andy (our mysql guru) comment about querying like tables for large number of tables... this is multisite, so how many network sites do you have?
If you want to disable error logging:
in file sp-site-support-functions.php, around line 160 find and comment out this code:
$wpdb->hide_errors();
set_error_handler('sp_construct_php_error');
comment out both lines…
still not really sure why that is killing your msyql server...
Visit Cruise Talk Central and Mr Papa's World
Offline
jim, sorry if covered earlier, but what version of mysql are you using?
Visit Cruise Talk Central and Mr Papa's World
Offline
Mr Papa said
...how many network sites do you have?
As of right now Tripawds currently hosts 1,122 blogs with 5,431 members, and more joining all the time. Any "SHOW LIKE" query is scanning 10,370+ database tables so you can see how MySQL processes quickly add up.
I recall you mentioning that this update had an increased number of notices so that may explain why the sudden impct on our site after this upgrade.
I noticed other queries in the SP code that were calling tables directly with some sort of WP prefix variable, like these snippets from sp-db-management...
$wpdb->query('DELETE FROM '.SF_PREFIX."options WHERE option_name='_transient_timeout_$transient[1]'");
$wpdb->query('DELETE FROM '.SF_PREFIX."options WHERE option_name='_transient_$transient[1]'");
I've noticed others, so I wonder if that might be better practice than SHOW TABLES LIKE for better performance on large databases. Of course, any such prefixes would have to account for wp_x_ in a multisite environment.
OK. I admit, I don't really know what I'm talking about here. I've been researching like crazy and have come across a few discussions about how the SHOW TABLES LIKE query can bog things down when dealing with a lot of tables. And I'm reaching for straws at this point.
Thanks for the input! I'll try turning of SF error logging and see what happens.
PS: We are running
MySQL version 5.5.32-cll
Apache version 2.2.24
PHP version 5.3.26
My server Admin tells me MySQL 5.5.3 is the latest version compatible with cPanel.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
All RSS1 Guest(s)