Tags Posts tagged with "DB"

DB

0

How to optimize WordPress and clean database? The easiest way, download WordPress WP-Optimize Plugin, activate and use it. The end… Nope, that’s not the end. Yes, WP-Optimize is a database cleanup and optimization tool. It doesn’t require PhpMyAdmin to optimize your database tables. And it allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks. But by using it is not enough, thanks to AllGuru.NET guide, it explains the detail to further delete orphan records in wp_term_relationships table.

DELETE FROM wp_term_relationships
WHERE NOT EXISTS (
SELECT * FROM wp_posts
WHERE wp_term_relationships.object_id = wp_posts.ID);

Note – do make a backup of your database before start executes any of WP-Optimize or manual delete tasks.

Above scripts are useful for those disabled the post revision function and want to clean up the orphan records that was not deleted by WP-Optimize. By the way, you can continue to clean wp_options table with WordPress Clean Options Plugin. But be warned, this plugin over cleaned my wp_options table. So use it carefully.

6

HostMonster and Bluehost list out all the slow SQL queries that use more than 1 second query_time in $HOME/tmp/mysql_slow_queries folder. I guess they have poor performance DB server because the other 2 web hosting that I use ~ HostGator and Just Host don’t have it. Anyway, if you see lot of log files there, this mean that you are using quite some CPU resources and getting sooner to get suspended like I did. When you look into these logs, you will find most of them are WordPress standard SQL queries and you can’t do any optimization on the queries themselves. But there are other ways to reduce access to DB server like wp-config.php tweaks.

How to speed up WordPress blog with wp-config.php tweaks?

By adding WP_HOME and WP_SITERUL, WordPress does not need to access to DB server to grab these 2 values every time when header or footer call these for RSS, CSS, JavaScript location on your theme.

define(‘WP_HOME’, ‘http://spblogger.com’);
define(‘WP_SITEURL’, ‘http://spblogger.com’);

WordPress Security Keys. Have it pre-defined in wp-config.php to eliminate WordPress to generate there salts for you if none are provided. Get yours here.

define(‘AUTH_KEY’, ‘xxx’);
define(‘SECURE_AUTH_KEY’, ‘xxx’);
define(‘LOGGED_IN_KEY’, ‘xxx’);
define(‘NONCE_KEY’, ‘xxx’);

Disable post revision function. This will reduce number of rows in WordPress post table == faster page finding and loading. I don’t use it anyway, so I disabled it.

define(‘WP_POST_REVISIONS’, false);

Be sure to check out AllGure.NET post for more info.

8

As I said, HostMonster and Bluehost are famous with CPU Throttling. Therefore, a WordPress cache plugin is a must installed on their hosting. My account got suspended even I am using WP Super Cache for quite some time. After do a comparison between WP Super Cache and W3 Total Cache, I decided to use W3 Total Cache as it is better than WP Super Cache.


HostMonster CPU Limiting Factors (Throttling)

I am using W3 Total Cache v0.9.1.3 on WordPress v3.0.4. W3 Total Cache is working out of the box with default settings. However, I discovered HTTP (gzip) compression is not working on HostMonster and Just Host most of the time. Therefore, CSS, JavaScript and HTML are not compressed with gzip. Only HostGator works perfectly. Must be web hosting issue.

On the other hand, W3 Total Cache failed to cache other object like images with expires header, cache control header and entity tag (eTag). None of the 3 web hosting that I use is working. So no “Instant” second page views (browser caching after first page view). Besides, its minify function might broke your other plugin’s CSS and JavaScript. I tried on PrettyGirl2u.com and it failed totally. So use it carefully.

By the way, I enabled database caching via disk. It does reduce some CPU resources on HostMonster. But it can’t help much if your visitors or yourself is ‘touching’ DB server directly by posting comments or editing posts. As you can see picture above on the last 1 hour, CPU usage spiked because I was replying few comments.

Overall, W3 Total Cache is a must installed plugin on WordPress blog. Not perfect but worth to install. And do tweak it until you get the best performance out of it.

Download W3 Total Cache WordPress Plugin here.

8

Value in price is one of the benefits of shared web hosting. However, there are downsides come along with it too. My blogs are hosted on HostMonster. And I face several DB server down issues lately. I don’t have problem if were down for few minutes but this took like 2 to 3 hours. And was happening 4 to 5 times in a month recently. Nothing is perfect, but this seem like a bit more to me. By the way, 99.9% Network Uptime Guarantee from hosting company does not mean that your website will be up for at least 99.9%. That’s their network connection to outside guarantee only.

We don’t have control on other website that hosts with us in the same server. In my case, host233.hostmonster.com is server that hosts my blogs. Not sure the exact name for the DB server. But whenever other people screw up their DB, and HostMonster Support need to do something to fix it. My blogs will be down.

Few questions that I have been thinking for a while ~ should I change to a better hosting company? Should I use virtual private server (VPS)?

Prefer to host it at US (due to Google IP address optimization). Let me know if you have any suggestion. Thanks. ;)

SOCIAL

115FansLike
198FollowersFollow
4FollowersFollow