|Tìm kiếm các bài viết theo từ khoá
||Liệt kê theo danh mục
|vB4mance Part 2: vBulletin4 Optimization - Basic Guide to InnoDB Conversion
Project Difficulty: Advanced Users
Chi tiết bài viết
Lần cập nhật cuối
2nd of November, 2011
|Ý kiến người dùng (12 Bình chọn)
Cảm ơn bạn đã đánh giá câu trả lời này.
Requirements: Major database changes, database backup is a must!
Version: vBulletin4 only! Do not run on vB 3!
In an earlier blog post (here)
we've discussed the basic benefits of converting your vBulletin forum
database from the default MyISAM storage engine to InnoDB.
To recap, a typical vBulletin installation will use the MyISAM table
engine. MyISAM engine is a popular and efficient table storage engine.
Under most typical conditions, MyISAM tables are fast to serve both
"reading" and "writing" request commands. When the server is highly
utilized, for example your forum is on a highly shared server and/or has
a high number of posts and users online, the MyISAM tables will begin
queuing MySQL queries and "locking" tables. This is because MyISAM
engine uses "table level locking" instead of "row locking". This is
problematic for vBulletin as such condition causes several issues not
limited to general site slowness and database errors. Database slowness
due to table locking is best visualized as a road traffic jam where
database updates block the entire highway. It boils down to this:
During the update, all other sessions that want to access this
particular table must wait until the update is done, meaning that users
have to wait until the database processes queued requests.
Knowing the general issue - our focus becomes avoiding "table level
locking" in MyISAM. A free, easy to implement alternative to MyISAM
engine is called InnoDB. The advantage of InnoDB to MyISAM is that
during rush-hour conditions, InnoDB only closes one lane of traffic and
allows the rest of the traffic to go through. In the case of
vBulletin, InnoDB will still serve data from the database table even if
portion of that table is "locked". Because of its row-locking feature
InnoDB is said to thrive in high load environments. Its CPU efficiency
is probably not matched by any other disk-based relational database
So, what is different in vBulletin 4 and why should you consider using
InnoDB? Prior to vBulletin4, the main "post" table was impossible to
convert to InnoDB without breaking the default fulltext search engine.
The same problem also affected the "thread" table, making optimization
hard - and requiring a third party search engine. One of the great
benefits introduced in vBulletin4 is a search engine that is segmented
away from the "post" and "thread" tables in the database schema. This
alteration allows for an easy conversion to InnoDB without requiring any
third party products to re-enable searching. Everything is optimized -
and all of the default features work!
Getting down to business now.
IMPORTANT: Please make sure you have a
valid backup copy of your database prior to any database changes!
vBulletin, nor anyone else is responsible for your database, please
back it up!
Notes: MySQL queries can be executed
via many different applications, including command line access to the
mysql server directly. This guide will assume that you are executing
these queries via the command line interface while logged directly into
your mysql server.
Turn off your vBulletin forum in AdminCP options.
- Log into your mysql server using the credentials and host information from your vBulletin config.php file.
mysql -u'mysqlusername' -p'mysqlpassword'
mysql -h'serverhostname' -u'mysqlusername' -p'mysqlpassword'
- Go into the vBulletin database, this database name is also found in config.php file.
- Now is a great time to check if your database server configuration
supports InnoDB; if it does not - you should install the necessary
components or contact your hosting provider for support. After
executing the command below, look for the "YES" next to InnoDB engine
- After confirming that your database server supports InnoDB you can
begin the conversion process. Enter and execute only one conversion
command query at a time. Depending on your server and database size -
some tables could take a really long time to finish converting. You can
always verify the progress by opening a new database sesssion and
executing "SHOW PROCESSLIST;" command; this will show you the duration
of the conversion query and current action. POST table is usually the
alter table attachment engine=InnoDB;
alter table datastore engine=InnoDB;
alter table deletionlog engine=InnoDB;
alter table forum engine=InnoDB;
alter table pm engine=InnoDB;
alter table pmreceipt engine=InnoDB;
alter table pmtext engine=InnoDB;
alter table post engine=InnoDB;
alter table postparsed engine=InnoDB;
alter table searchcore engine=InnoDB;
alter table searchgroup engine=InnoDB;
alter table searchlog engine=InnoDB;
alter table sigparsed engine=InnoDB;
alter table subscribethread engine=InnoDB;
alter table thread engine=InnoDB;
alter table threadviews engine=InnoDB;
alter table user engine=InnoDB;
alter table usertextfield engine=InnoDB;
- Almost done. Now, to verify that the tables are indeed InnoDB,
execute this command and look for the InnoDB engine type in an
SHOW TABLE STATUS;
SHOW TABLE STATUS WHERE ENGINE='InnoDB';
EDIT 4/30/2010: Adding COMMAND LINE unix script that will tell you which
tables can be converted to innodb on your database server! Take the
guess work out of it. Script for advanced users; must fill out server
details and be able to execute from command line in shell.
|File đính kèm
Không có File đính kèm nào được tìm thấy.