Posts Tagged ‘database tables’

How to check and repair broken MySQL ISAM tables

Monday, July 11th, 2011

MySQL repair artistic picture

If you are stuffed with errors in /var/log/mysqld.log similar to:

110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_seo_cache.frm'
110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_sessions.frm'

This is a sure sign something terrible has happened with your mysql database tables that lead to corruption.
Having corrupt table in mysql installation can severely lead to data loss as well as significantly reduce the speed and performance of a MySQL server in this awful times mysqlcheck is the best friend of the administrator, here is how you can check and repair broken tables in MySQL server:

mysql-server:~# mysqlcheck --all-databases -u root -p
chillor_hjbgl.vn_users OK
chillor_lul.mybb_adminlog OK
chillor_lul.mybb_adminoptions OK
chillor_lul.mybb_adminsessions OK
chillor_lul.mybb_adminviews OK
chillor_lul.mybb_announcements OK
...

You will notice the corrupt sql tables will be reported as corrupt by the tool and mysqlcheck will try it’s best to recover the corrupt tables.

In most cases this should be enough to recover corrupt tables.

WordPress blog MySQL data maintainance valuable plugin WP-OPTIMIZE

Tuesday, April 12th, 2011

The more my blog is growing the slower it becomes, this is a tendency I’ve noticed since a couple of weeks.

Very fortunately while reading some random articles online I’ve came across a super valuable wordpress plugin called WP-OPTIMIZE

I think it’s best if I present instead of taking the time to explain what the WP-optimize does for a wordpress powered blog:

WP-Optimize is a WordPress 2.9++ database cleanup and optimization tool. It doesn’t require PhpMyAdmin to optimize your database tables. It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.

Additionally you can rename any username to another username too.

For example: If you want to rename default ‘admin’ username to ‘someothername’; just put ‘admin’ (without quotes) to the Old username field and the new username to the New username field, then click “PROCESS”)

Now in short to rephrase above text, during MySQL database requests a lot of database starts needing the so called MySQL optimization , this operation becomes necessery especially for databases which are very actively used and is related to the basic design of the mysql server.

Very often many tables starts having a lot of garbage (repetitive) data and therefore read and writes from the SQL server gets slower and slower day by day.

Thus the MySQL server has it’s famous OPTIMIZE TABLE command which does wipe out all the garbage unnecessery data stored in a tables/s and hence seriously impacts the later read/write table operations.

Now to go back to wordpress the same optimization issues, very often are a cause for a performance bottleneck and some smart guy has came with the great idea to create a specific wordpress plugin to fix such an optimize table issues

The WP-Optimize plugin has 5 major options which somehow could have a positive impact on blog’s performance, these are:
 

  • Remove all Post revisions
  • Remove all auto draft posts
  • Clean marked Spam comments
  • lean Unapproved comments
  • Optimize database tables

Apart from the nice performance boost applicaiton the wp-optimize plugin has one super valuable function, it could change the default wordpress blog administrator user admin to some other desired username, for example rename the default blog administrator username “admin” user to “john”.

From a security perspective this is another must have feature in every wordpress blog as it can kill many of the possible brute force attacks with some web brute force softwares like Hydra

Installing and using wp-optimize is a real piece of cake, here is a very quick few command lines to install it:

host:~# cd /var/www/blog/wp-content/plugins/
host:/var/www/blog/wp-content/plugins:# wget https://www.pc-freak.net/files/wp-optimize.0.9.2.zip
host:/var/www/blog/wp-content/plugins:# unzip wp-optimize.0.9.2.zip
...

To launch WP-OPTIMIZE and fix many speed bottlenecks caused by unoptimized tables, or just hanging around in database old spam comments, go and login with your adminsitrator to wordpress.

In the left pane menu starting up with Dashboard somewhere near the end of the vertical menu you will notice a button reading WP-Optimize .
Click over the Wp-Optimize button and a screen like the one in below’s screenshot will appear in your browser screen:

wp optimize plugin database optimization options screen

I have personally decided to use just 2 of all the 5 available primary plugin options, I decided to select only:
 

  • Clean marked Spam comments
  • Optimize database tables

Scrolling below the PROCEED button I could also notice a number of tables which were missing optimization and hence required an optimize table to be executed on each one of them.
Further on I pressed the PROCESS button and after a couple of minutes (2, 3 minutes) of waiting the wp-optimize plugin was done it’s selected tasks:

In the screenshot below, you see all my blog tables which were optimized by the plugin:
WP-Optimize optimized blog tables screenshot

It’s interesting to say few words about the Remove All Posts revisions plugin option, as many might be wondering what this plugin option really means.

When writting posts, wordpress has an option to restore to a certain point of the write out and makes a sort of different versions in time of each of it’s written posts.

Therefore later restoration if something gots deleted by mistake is facilitated, this is actually the all meaning of Remove All Posts revisions

With time and the increase wp posts the amount of Posts Revisions is growing on and on and just taking space and possibly making the access to the database entries slower, thus it might be a good idea to also remove them as, it’s absolutely rare to use the wp post restoration function.
However I’ve personally decided to not wipe out the old posts revisions as someday i might need it and I’m a bit scared that it might cause me some database issues.

I’ll be glad if somebody has tried the Posts Revisions wp-Optimize funciton and is happy with the results.