Page 2: How to Maintain a WordPress Database
Lester Chan comes through yet again with the “WP-DBManager WordPress plugin to manage your WordPress database. Once you install and enable the plugin you’ll find that in your dashboard you now have Write, Manage, Design, Comments, and “Database”. The first thing I look for is excessive “overhead”. Just past your database information is a section labeled “Tables Infomation”. You’re looking for any tables that (in the rightmost column) have “overhead”.
What is database overhead? First I’ll give you the technical answer, then I’ll explain: “OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.”
Basically a table in a database is kind of like a spreadsheet that stores information. That infomation is stored in “rows”, and over time rows are edited and deleted. When that happens “overhead” occurs, and the table isn’t as efficient. You know how you have to defragment a hard drive? Well, “optimizing” a database table is like that. So if you have some db table “overhead” it’s time to optimize the table. Even if you have no overhead, you should do this once or twice per month.
How do you “optimize” a wordpress database? Just click on the “Optimize” tab at top of the “Database” screen in your WordPress dashboard (that you have since installing the WP-DBManager plugin), and then at the bottom of the page click “optimize” again. You should get a page that looks something like this after optimizing with a success message at the top:
If for some reason when you optimize your database there is an error of any kind, then you can click on “Repair database” to see if it can be fixed. It works the exact same way, and when complete will tell you whether it was successful or not. Again – you should optimize your wordpress database once per month at minimum, and more often if your blog is very busy. In the next step, you’ll learn how to automatically do this.
Very useful post JT.
Q
Is DB-manager compatible with 2.5? Shows only 2.3 on the site, are you using it now with your 2.5?
The diagnosis part is great! Noobs like me to WorPress don’t think about stuff like that. Very important! And a good plugin.
Do you really see an advantage over backing up the database vs just exporting the information out of WordPress?
I just zip my site every so often and export from WordPress. I also keep a DB backup because I’m paranoid =)] but it does seem a little redundant. So far I’ve only used the export/import of wordpress to set things back up. When I’ve toasted the DB. . . yes I have done it already. . . gotta learn some way. Anyway, I’ve just imported again and I’m right back to where I was.
Just an idea for us noobs =
Thanks for the DB education!
C
Acupunctures last blog post..The Science of Getting Rich by Wallace Wattles
I really enjoy this wordpress hacks series. I’m no programmer and even I can understand and use this.
On a related note (kind of =[] I Made My First BANS Sale!!!
Just wanted everyone to know I made my first BANS sale today!
I did the following to make it happen.
1. Used Rochelle’s “The Niche Store Checklist has been Revised” to create the BANS site. http://www.nichestorestrategies.com
2. Used Marks’s “More Free Build a Niche Store templates – Come and Get Em!” to build the site. http://www.thenichestorebuilder.com
3. Used JTPratt’s “Watch Me Build a BANS Niche Site from Scratch!” to fine tune the site. http://www.jtpratt.com
4 Used JTPratt’s “Best Practices for Setting Up a New WordPress blog in 60 Minutes or less” to set up a blog correctly. http://www.jtpratt.com
Thanks to all the forum posts at BANS and all the people who commented on the sites listed above.
Come take a look http://www.toolboxhero.com and make relevant comments at http://www.toolboxhero.com/blog. The blog is dofollow so go ahead and build some backlinks.
Next, more backlinks, more BANS stores, and implement more ideas from Rochelle. Mark, and JTPratt!!
Thanks again, Paul
Pauls last blog post..Sears Normal Tool Warranty
I have to agree that backing up your database is a pain, but very important. I read a post on a forum, that I frequent, today. From someone who got their blog hacked. It’s just much easier to recover from that if things are backed up.
Sid Softwares last blog post..Best Windows backup software
first off i want to thank you for the marvelous article…it is very clear and even i could “get it”….however, i have done everything you suggested and i am still loading slow….
i have also taken all the text off of you widgets, deleted all on active plug ins…
i have done everything i have read about other than the cashe and i am so unfamiliar with that it sort of scares me…
could you please please help me….i have bugged my hosting company many times….and they said there is nothing else they can do….
so any imput would be very appreciated.
thanks
darlene
@Darlene –
Looks like you have multiple issues. I have had this exact same problem and fixed it. I think you have a plugin conflict and also you have massive code issues.
This is what I would do…
1. Disable all plugins
2. Set the theme to default
3. Take your July 15th post and edit it. Copy the text into Windows notepad. Use Ctrl-A to “select all” and copy, then go back to the post in your WordPress dashboard. Delete everything. Paste the text you copied from notepad. The problem is you’ve been writing your entries in Microsoft Word, copying the text and pasting it into your posts using the WordPress visual editor. You have to stop doing that – it’s bloating the bejeezus out of your code.
4. Save the post, reload your home page
At this point everything should be better. If it’s not, download all your plugins to your desktop in folder, then in FTP on your web server delete all the plugins in /wp-content/plugins. WordPress still looks in your plugin directory – even if they aren’t enabled.
So now, what happens? Your blog should be light years faster. If it’s STILL not you may have a web host problem. There may be too many people on your web server or the MySQL (database server) might be overused. I mean, if you have the default them with no plugins your blog still takes 30 seconds to load, unless you have serious database corruption your web host is to blame. You already optimized your database – so it should be ok.
At this point, I would dump your web host for someone like Hostgator. I use them for about everything, they work great, and you can have unlimited sites and databases for $15/mo or less. The servers are fast, tech support is great, and I’ve never had any issues with anything – EVER.
Post again on your success (or failure) once you’ve tried these steps…
hi there…
thanks for your comments…i have done the plugin thingy…i am afraid to do the default as i didn’t build that part of my website….and don’t know if i could get it back without a lot of tears….and i have really spent them all on this issue….
i do want you to tell me more in detail about my posting behavior….and you are right in what i am doing….and i know for you you probably felt it was in detail…but i need it sort of step by step….how do i write my articles….in notebook and then copy them into wordpress??? how do i get the various styles i like in my writing….do i have to give that up???…
also i finally got a senior service rep and he went to the top and found out that one of data servers (the second one or something) was/is the problem and my issue is suppose to resolved by morning…i made 6 calls to them and asked about the server thingy each time and each time they just repeated that the server load was checked and it was ok….evidiently there are two servers and they didin’t take it that far….
so will lst you know what happens in the morning…and i really, really appreciate your answer and thoroughness in helping me…you are great!!!!!
namaste,
darlene
Darlene,
I checked your site also. I don’t think it has anything to do with the plugins or themes.
If you watch your browser working you will see that it is waiting to connect to the domain and once it does it loads the page rather fast. Therefore, the problem. . . in my opinion is your host.
Hostgator is a good host I also like Hawk Host – very good and low cost.
One of my sites responds like yours at times. . . thus it isn’t my plugins or theme. It’s whatever is going on with your host at that time.
Good luck
@Darlene: What you need to do is never write posts in anything other than WordPress from now on. Make sure you are using the “visual” editor (the one with bold, italic, font size, bullets, etc). If you aren’t – edit your profile under “Users” and enable it.
If there are things in Word you want to do that WordPress visual editor won’t – you should be doing those in web pages anyway. Microsoft Word adds in 50X more code (most of it useless) for every page you write – making the load times slower. Not a ton slower, but slower.
To fix the existing posts you just open up Windows Notepad by going to start->Accessories ->Notepad. Just copy your posts there. Select all, and then copy again, and paste back to your post and save.
Hi, maybe long time after post created but i have High Resource Usage for my site. and my site moved to Cloud Server…
Browsing phpmyadmin and seeing DB for my main site, though i use wordpress i have encountered that there are also joomla fileds in the same db. is this normal ?
may this cause High Resource Usage ?
Does anything happen if i delete all joomla related fileds ?
will be happy if you could guide me
eddais last blog post..Monday Links Roundup #1