vBulletin and mysqli persistent connection

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Oracle simsim
    Senior Member
    • Jul 2009
    • 117

    vBulletin and mysqli persistent connection

    Actually its quite easy to support mysqli support persistent connection in vBulletin: You only need to replace the following code in class_core.php (vB_Database_MySQLi class) :

    PHP Code:
    $connect $this->functions['connect']($link$servername$username$password''$port); 
    with the following code:

    PHP Code:
    if($usepconnect$connect $this->functions['connect']($link'p:' $servername$username$password''$port);
                else 
    $connect $this->functions['connect']($link$servername$username$password''$port); 
    After that you enable db persistent connection in your config.php


    Tested! In my case, huge difference in performance. MySQL CPU reduced from 23% to 16% (since I have really busy server)

    upon the end of script execution, mysqli do cleaning automatically so you don't have to worry about locked tables or whatever, just use it! (see this)

    Of course this requires that you run something like php-fpm (so the connection remain open during the lifespan of PHP child process). Moreover you should have MySQL max connection set to suitable value (usually number of different connection * max number of PHP children)

    Oh what I mean by different connections is the connections that differ in database, user or both.

    I hope I can see this change in 4.2.3


    thanks


    منتديات درر العراق
  • Mark.B
    vBulletin Support
    • Feb 2004
    • 24286
    • 6.0.X

    #2
    It is not going to change to be honest.
    MARK.B
    vBulletin Support
    ------------
    My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
    My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

    Comment

    • Zachery
      Former vBulletin Support
      • Jul 2002
      • 59097

      #3
      You could have just enabled persistent connections

      Code:
       [SIZE=2]// ****** MASTER DATABASE PERSISTENT CONNECTIONS ******
      // This option allows you to turn persistent connections to MySQL on or off.
      // The difference in performance is negligible for all but the largest boards.
      // If you are unsure what this should be, leave it off. (0 = off; 1 = on)
      $config['MasterServer']['use[U]pc[/U]onnect'] = 0;[/SIZE]

      Comment

      • Mark.B
        vBulletin Support
        • Feb 2004
        • 24286
        • 6.0.X

        #4
        Originally posted by Zachery
        You could have just enabled persistent connections

        Code:
         [SIZE=2]// ****** MASTER DATABASE PERSISTENT CONNECTIONS ******
        // This option allows you to turn persistent connections to MySQL on or off.
        // The difference in performance is negligible for all but the largest boards.
        // If you are unsure what this should be, leave it off. (0 = off; 1 = on)
        $config['MasterServer']['use[U]pc[/U]onnect'] = 0;[/SIZE]
        I put that originally, but then I noticed it was actually mentioned in the post itself:
        "After that you enable db persistent connection in your config.php"
        MARK.B
        vBulletin Support
        ------------
        My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
        My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

        Comment

        • Oracle simsim
          Senior Member
          • Jul 2009
          • 117

          #5
          @Mark.B
          Actually that's mean I have to edit the file each time I do upgrade.

          @Zachery
          This will affect only php mysql users, not mysqli (check the class code if you have time). In other words, if you set "mysqli" in config.php then enabling "usepconnect" will not have any effect

          Sorry to post this here. I just want to help other people besides getting your attention to this feature (I think it is already have been requested in Jira)
          منتديات درر العراق

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73981

            #6
            For most people persistent connections will cause additional problems due to resource limits related to connections. Often these are very low and while we could argue with hosts that it isn't the correct way to limit database resources, they aren't going to change. So when you use persistent connections, you end up limiting your site to 10 users at a time. When you're not using persistent connections, the connection is closed immediately after page load and you can accomodate more end users. As such, we do not recommend using persistent connections. If you're server works better with them then as an advanced user you have the option.

            For the actual issue in the first post, you should report it as a bug in JIRA. Otherwise it will never get reviewed by the developers. Use the form linked below. All you need to do is fill in the summary and description.
            http://tracker.vbulletin.com/secure/...91&issuetype=1


            On a side note using the mysql library in PHP has been deprecated. It is no longer supported in current versions of PHP. As such we do not recommend using it and all sites should use the mysqli library.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API

            Comment

            • Oracle simsim
              Senior Member
              • Jul 2009
              • 117

              #7
              Originally posted by Wayne Luke
              For most people persistent connections will cause additional problems due to resource limits related to connections. Often these are very low and while we could argue with hosts that it isn't the correct way to limit database resources, they aren't going to change. So when you use persistent connections, you end up limiting your site to 10 users at a time. When you're not using persistent connections, the connection is closed immediately after page load and you can accomodate more end users. As such, we do not recommend using persistent connections. If you're server works better with them then as an advanced user you have the option.

              For the actual issue in the first post, you should report it as a bug in JIRA. Otherwise it will never get reviewed by the developers. Use the form linked below. All you need to do is fill in the summary and description.
              http://tracker.vbulletin.com/secure/...91&issuetype=1


              On a side note using the mysql library in PHP has been deprecated. It is no longer supported in current versions of PHP. As such we do not recommend using it and all sites should use the mysqli library.

              Dear Wayne

              As you can see, I am not saying that you should force all people to use persistent connections. If you review the code above, you can see it depends on the admin to use p connect or not (through the normal setting in config.php). So It is true that you do not recommend, but you should offer it as an option.

              Secondly, There is a misunderstanding about p. connections and limited MySQL connections. You can control the maximum opened p. connection by setting the maximum PHP children that can be exist at a time. Let's say you set in PHP-FPM configuration max max children to be 25. Then you will not get more than 25 active persistent connections at one time. 25 PHP children means Yes, 25 active client, but 25 is very enough to handle a lot of clients. More than this can cause the server to hang (if you use p. connect or not).

              Additionally, if the client does not use persistent PHP children (such as PHP-FPM or mod_fcgid) , s/he will not make any benefit from using p. connect because each PHP process will die immediately after serving the requests, and its db connection will die also.

              so this is my contribution to this site. Of course I can always edit the file after each upgrade, But I think This just a straightforward change that should be done.

              request in Jira:
              منتديات درر العراق

              Comment

              • Wayne Luke
                vBulletin Technical Support Lead
                • Aug 2000
                • 73981

                #8
                Most customers use shared hosting with no access to server configuration whatsoever. Often times they are restricted to connections in the single digits. Persistent Connections cause more problems then they would ever solve in this case. If you have access to your server's configuration and wish to go though all of that configuration for an optimal setup, more power to you and your site will probably run better than those on shared hosting.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API

                Comment

                widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                Working...