Our host has reviewed our server issues and advised this:
I would like to know if:
1. This modification would indeed improve performance and is good advice.
2. Considering I plan to upgrade to the most current version of vB over the next few days, would the upgrade address what the modification would, doing away with the need for the modification?
The following query is poorly optimized.
SELECT COUNT(*) AS count FROM post WHERE dateline >= 1140325200;
Since there is no index on the 'dateline' column of 'posts' mysql must read through the entire +2GB table to get the count. This datestamp represents is a relatively short range of dates so an index would be quite benificial in identifing the required posts. Currently when the count is in progress the table will be unable to be modified by another query. If an update query has priority as next query following the "select count", the table will block all other selects from completing untill the update finishes. This causes a case where lots and lots of queries which need access to the 'posts' table are stuck in the processlist. Thus making the apache process wait. This then causes the number of apache processes to build up and reach MaxClients. I believe if we add an index to 'posts.dateline' then your server will perform much better and you will have fewer issues with overload.
However the size of this table means that the process to add an index could take quite some time to complete. During this time no queries may access to table. I would like to do this modification with the forum disabled. This could be scheduled to be done at a time where it will cause the least interruption to service. Please let us know what time we could perform this maintence.
SELECT COUNT(*) AS count FROM post WHERE dateline >= 1140325200;
Since there is no index on the 'dateline' column of 'posts' mysql must read through the entire +2GB table to get the count. This datestamp represents is a relatively short range of dates so an index would be quite benificial in identifing the required posts. Currently when the count is in progress the table will be unable to be modified by another query. If an update query has priority as next query following the "select count", the table will block all other selects from completing untill the update finishes. This causes a case where lots and lots of queries which need access to the 'posts' table are stuck in the processlist. Thus making the apache process wait. This then causes the number of apache processes to build up and reach MaxClients. I believe if we add an index to 'posts.dateline' then your server will perform much better and you will have fewer issues with overload.
However the size of this table means that the process to add an index could take quite some time to complete. During this time no queries may access to table. I would like to do this modification with the forum disabled. This could be scheduled to be done at a time where it will cause the least interruption to service. Please let us know what time we could perform this maintence.
1. This modification would indeed improve performance and is good advice.
2. Considering I plan to upgrade to the most current version of vB over the next few days, would the upgrade address what the modification would, doing away with the need for the modification?
Comment