Support Forum
I've been working with our server manager to address recent performance issues, some of which he believes are related to the SimplePress plugin. Some of his questions/concerns follow below. I would greatly appreciate any feedback that I can convey so we can quickly address load spikes that are continuing to cripple our site... thank you in advance for your time and thoughts!
FYI: We are on our own dedicated Linux box with 8 Xeon processors and 8GB RAM, and SQL queries are constantly consuming resources.
SP-Specific Slow Queries
First, we're seeing a lot of slow queries like the following in our logs:
mysql> show processlist;
+-------+----------------+-----------+---------------------+----------------+------+----------------------+----------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------+-----------+---------------------+----------------+------+----------------------+----------------+
| 35996 | tripawds_xxxx | localhost | tripawds_xxxx | Query | 4 | checking permissions | SHOW TABLES LIKE 'wp_1_sfoptions' |
| 36001 | tripawds_xxxx | localhost | tripawds_xxxx | Query | 3 | checking permissions | SHOW TABLES LIKE 'wp_1_sfoptions' |
| 36003 | tripawds_xxxx | localhost | tripawds_xxxx | Query | 3 | checking permissions | SHOW TABLES LIKE 'wp_1_sfoptions' |
| 36018 | tripawds_xxxx | localhost | tripawds_xxxx | Query | 2 | checking permissions | SHOW TABLES LIKE 'wp_1_sfoptions' |
I'm trying to determine if these are legitimate queries or rogue db scans, and our server manager suggests they may be "poorly written queries".
Database Engine Optimization
We are currently running MySQL v. 5.1 and are considering upgrading to 5.5. The default db engine for for 5.5 is InnoDB and we are considering converting our database from MyISAM.
What ramifications are there, if any, on SimplePress by either upgrading to MySQL and/or converting db engine to InnoDB? Or, are there any recommended tweaks to the current MyISAM db?
MySQL and Apache Config Tweaks?
Our server manager has been "tweaking" the MySQL and Apache configurations with a tedious trial and error approach to try and address load spikes caused by queries and http requests. Any known recommendations for MySQL and/or Apache optimization are greatly appreciated.
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.
PS: Not sure if these are related, but below are the only errors I see in the SF Toolbox. There are, however, lots of them!
20 June 2013, 10:07 am
php file: /simple-press/sp-api/sp-api-primitives.php
line: 611
function: Unavailable
Notice | Uninitialized string offset: 020 June 2013, 10:07 am
php file: /simple-press/sp-api/sp-api-primitives.php
line: 611
function: sp_send_email
Notice | Uninitialized string offset: 020 June 2013, 10:07 am
php file: /simple-press/sp-api/sp-api-primitives.php
line: 611
function: sp_send_email
Warning | Illegal string offset 'sfmailuse'
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.
First up - the 4 queries are genuine although you should only ever see it run once per page load. I also see no reason why this should be slow in any way. The table itself is not even referenced - just the catalogue. It is probably a good time for us to review the use of this and some other SHOW queries in any case. They may not really be needed any more so I will open a ticket to see if we can remove them.
As to innoDB tables then the only negative I am aware of is that at one point they did not support FULL TEXT indexing which SP makes use of on the sfposts table. However, I believe at some stage support has been added but I am not sure with which version so just check that this is supported in your target version. I am interested to know the results if this change if you go through with it.
Personally I have never played around with MySQL settings but I do know Steve has so he may want to add something.
 |
YELLOW
SWORDFISH
|
It is probably a data issue with your database we are not covering.
Can you clarify what you mean by this please? I completely understand if you mean you do not offer general wp/db support. That's fine, I'm just looking for guidance since the issues seem to be SF related and I don't know what you mean by, "we're not covering." Thanks!
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.
Just my sloppy turn of phrase. I meant simply we are probably trying to load an option setting (from the database) that you have never set and we have not checked for a null or not set condition. That is the usual cause of these annoying notices and/or warnings.
|
YELLOW
SWORDFISH
|
I meant simply we are probably trying to load an option setting (from the database) that you have never set and we have not checked for a null or not set condition.
Thank you for the clarification! Any steps I can take to resolve these errors? Or is this in regard to the sp-api-primitives.php notices/warnings and not the query issues I first addressed?
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.
Jim Said:
| 35996 | tripawds_xxxx | localhost | tripawds_xxxx | Query | 4 | checking permissions | SHOW TABLES LIKE 'wp_1_sfoptions' |
Yellow Swordfish said:
It is probably a good time for us to review the use of this and some other SHOW queries in any case. They may not really be needed any more so I will open a ticket to see if we can remove them.
Thank you! I'm happy to help with this any way I can.
I've been troubleshooting with our server manager all day. When I deactivate SP, we see pretty consistent stability. Upon activation, we experience random load spikes caused by MySQL eating up system resources with numerous SHOW TABLES LIKE queries on wp_1_sfoptions. We're logging any queries taking over two seconds, and this one is getting logged a lot, often taking over 5 seconds.
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.
not sure how sfoptions would be that long loading... its one of the quickest for me... how often are you seeing it per page load?
Visit Cruise Talk Central and Mr Papa's World
1 Guest(s)