Improving Performance With MySQLi And APC

By floris · Feb 11, 2010 ·
  1. floris
    This article is not tailored at just vBulletin, other forum and blog softwares out there can also improve their performance by considering using caching modules and MySQLi. And, applies to all linux distributions, paths and package manages might vary.

    Written for Linux CentOS 5, vBulletin 3, MySQL5.

    This article is written by me, and originally published on my blog here: MySQLi | MrFloris and Installing APC on CentOS 5 Server | MrFloris

    MySQLi

    With MySQL v3 out of the way, and MySQL 4 and 5 being used everywhere now (and if you have not upgraded to the latest stable .. shame on you) I think it is time to point out that you can use MySQLi for your code, software and sites.

    This article is to inform you about MySQLi and give you information so you can decide to upgrade and if you have already to perhaps use it on your vBulletin installation.

    More information about MySQLi can be found here: PHP: Mysqli - Manual and MySQL :: MySQL 5.0 Reference Manual :: 20.9.7 Enabling Both mysql and mysqli in PHP

    Using MySQLi with vBulletin might not give you huge benefits, but I have it turned on for my setups.

    Some of the benefits of using MySQLi (improved) are:
    - support for transactions for the storage engine InnoDB (autocommit, commit and rollback)
    - prepared statements
    - secure mysql connections
    - multi query
    - object oriented interface

    More details about these benefits can be found here: http://www.charlesrowe.com/2007/06/15/the-four-major-benefits-of-mysqli/

    And to save time heres a great article for experienced developers: ext/mysqli: Part I - Overview and Prepared Statements

    In regards to vBulletin, once you check phpinfo(); and see you can use MySQLi you can go to your config.php file and set $config['Database']['dbtype'] to mysqli, default is mysql.

    $config['Database']['dbtype'] = 'mysqli';

    And if your tables and HTML charset are set to UTF-8 you can also set $config['Mysqli']['charset'] to utf8, default is uncommented. And $config['Mysqli']['ini_file'] to , default is .

    $config['Mysqli']['charset'] = 'utf8';
    $config['Mysqli']['ini_file'] = '';

    Again, I now have my forums use MySQLi and I have no problems, but do not notice a great improvement. I am just happy to think that vBulletin can make use of it.

    APC

    Bigger PHP applications, such as vBulletin can gain a lot of increased performance from running a PHP op-code cache/accelerator such as APC.

    Here are some instructions on how install APC on various systems, my personal experience is with what appears the most common: 32bit CentOS 5 Server.

    APC is maintained by core PHP developers, APC does not utilze a disk cache, unlike both eAccelerator and Xcache (although it is probably configurable). URL: PECL :: Package :: APC

    A drawback to consider is that due to known segfaults you could get an increase in error 500 or blank pages.

    vBulletin 3.7 RC4 came with this in the announcement: We recommend that vBulletin 3.7 is run on PHP 5.2.5 with APC (or a similar opcode cache) and MySQL 5.0.51 for best performance and stability.

    Here are some instructions on how install APC on various systems, my personal experience is with what appears the most common: 32bit CentOS 5 Server
    Please note, due to installed control panels installation instructions might differ, this is for a dedicated box with no control panel.

    Lets first install the pecl command so we can later download and install APC from the repositories.

    yum install php-pear

    and we will need the following package

    yum install php-devel

    and we probably need the apxs command

    yum install httpd-devel

    Now we have all the software we need, so we install apc via the pecl command:

    pecl install apc

    To enable apc in Apaches configuration. The following command should do this for us.

    echo "extension=apc.so" > /etc/php.d/apc.ini

    Then we restart Apache:

    /etc/init.d/httpd start
    (though: service httpd restart should work too)

    You can find out if APC is now running by loading a phpinfo() page (vBulletin AdminCP > Maintenance > phpinfo) and search for APC block.

    Now that it works, go into the includes/config.php file and enable the datastore class to use APC:

    $config['Datastore']['class'] = 'vB_Datastore_APC';

    And youre done. (Note, if you run multiple forums, use the datastore prefix)

    Good luck vBulletin owners, with improving your forum's performance.

    Share This Article

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.