|Tìm kiếm các bài viết theo từ khoá
||Liệt kê theo danh mục
|vB4mance Part 1 : Helping communities grow, performance data model changes in vB 4.0
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 (20 Bình chọn)
Cảm ơn bạn đã đánh giá câu trả lời này.
The new vBulletin 4.0 boasts a lot of new changes such as
CSS-friendly styles, multi-content search and other various new
features. The biggest improvement in vBulletin 4.0, however – is well
hidden under the hood and it is the actual changes to the database
storage mechanism. While there are exceptions, this change improves the
way vBulletin sites scale upwards to serve more visitors and more
content without sacrificing performance. As a preface to my blog post,
allow me to please explain its purpose. I'd like to very simply describe
the database processes and the problems that the average forum owner is
facing. I'd also like to explain the basic solutions to these problems.
Furthermore, I'd like to discuss this in the context of exciting
changes coming in vB 4.0. I also further understand the problem, and I
realize that this change is not a complete solution to the problems that
large forums are facing, but it is a really good and exciting step in
the right direction towards helping vBulletin perform better.
Let's talk Simple Database Server Mechanics:
In an average installation, vBulletin typically stores data in only one
database, while it uses many different tables inside that database to
store different sets of data. For example, user information is stored in
a table called “user”, and post text is accordingly stored in a
separate table called “post”. When a new thread is added, or a reply to a
post is made, a new “row” is created in these database tables to store
the additional information. When a visitor wants to read a thread, they
click on a link sending the database server a command to query the
various database tables and show the specified results. An average
active discussion forum could be seeing hundreds of these queries per
Defining the General Database Problem and Errors:
As your discussion forum grows, you will notice that your hosting
requirements may change drastically. As the user activity on your forum
increases, so is the amount of data collected in your database. In turn,
as the physical size of the database increases the server uses more and
more hardware resources to service each individual request, such as
displaying a thread or adding a new reply. When the server becomes
overwhelmed with requests it starts taking a longer time to service each
query, this starts a chain reaction where even the simplest of queries
aren't being serviced; this condition is called “Locked”. The best way
to visualize the “Locking” issue is to imagine a road traffic jam. This
problematic condition manifests itself via MySQL error messages and
general site slowness. It's believed that upgrading to a better web host
(ie. Dedicated server) would resolve the performance issues; and while
it's generally good advice – the reality is that upgrading to a better
host only increases the safety buffer before the errors will occur
again. In our example of a traffic jam, a server upgrade will increase
the amount of travel lanes – but the traffic jam is still possible, even
though more cars are able to travel. This traffic jam condition is not
exclusive to vBulletin, but rather to a default implementation of MySQL;
let's further examine the problem by talking about the Search function.
Taking a Closer Look at “Search”
Let's drill down and examine the search functionality in vBulletin 3.
Earlier, we talked about the “thread” and “post” tables in the forum
database. When a forum user executes a search, a query is issued that
checks the “post” and “thread” tables for availability of the specified
keywords. This process is very intensive and it uses an internal MySQL
function called “fulltext” database search. Both the “thread” and “post”
tables have an index that speeds up this process, something that
resembles a table of contents in a book and it's called a “fulltext
index”. While this search process is happening, the database server has
much less resources dedicated to serving other queries (reading and
especially writing). Depending on server capacity, activity level and
the size of your post table, a single simple search can cause the
aforementioned traffic jam condition and bring your forum down.
Examining the Search Issue and Solutions
Currently, the most common approach to scaling a vBulletin forum to
handle the load is to avoid the traffic jam condition entirely. Lets
evaluate the root cause of the problem.
Issues can be found in the mechanism storing and retrieving the data inside the database tables; this storage engine is called “MyISAM”. When a database request is made on a specific table (specifically the “write” query), the MyISAM
engine temporarily “locks” the specified table briefly making it
unavailable to all other requests. This means that given the typical
conditions while it's searching through the larger tables such as “post”
all other requests are quickly queuing as well, creating the traffic
jam condition, in turn spewing database errors and slowing down the
The solution to “table level locking” in MyISAM is as simple as converting the database table engine to type “InnoDB”. At a first glance, InnoDB is
very similar to MyISAM; the main difference being that unlike MyISAM
engine, InnoDB uses “row level” locking, whereas the specific table is
never locked completely and is always available to service all other
requests. This means that when an expensive search query is executed and
and/or writing occurs, the “post” table does not lock and delay all
other requests from successfully completing.
InnoDB - The important difference between vB 3.x and 4.0
The main problem is that InnoDB does not support “fulltext” searching;
this means that converting to InnoDB will break the default search
engine in vBulletin 3. This is due to InnoDB requiring that “fulltext
index” be dropped when the conversion occurs. No full text index, no
search; it's as simple as that. Currently, the solution is to use a
third-party search engine such as Sphinx. vBulletin 4 data model
tackles this problem head on by redesigning the search engine
functionality by no longer relying on a fulltext index in the “post” and
“thread” tables and giving the flexibility of optimization without
sacrificing the search.
Database performance changes in vB 4.0
As I've mentioned in the beginning of my blog post – what's really cool
in vBulletin 4.0 is not necessarily apparent to the untrained eye.
Previously, vBulletin utilized the fulltext index on both “thread” and
“post” tables making optimization and server problem resolution very
expensive and complicated. vBulletin 4.0 no longer needs the fulltext
index to be present in either of the “thread” or “post” tables. This
means that vBulletin 4.0 database can utilize the InnoDB table engine
and prevent a lot of common database issues associated with locked
database queries. This is great news for small to medium board owners
who will be able to resolve some performance overhead problems without
having unnecessary expenditures for expensive hosting or complicated third-party search engines.
What else is new?
Another cool update in vBulletin 4.0 are the new indicies that it builds
on tables such as thread and post that further optimize database
performance. This is especially beneficial to forums with “long” threads
where replies could reach well into the thousands.
Stay tuned for a detailed guide to InnoDB converting vBulletin 3.8 and 4.0.
|File đính kèm
Không có File đính kèm nào được tìm thấy.