Welcome to Centmin Mod Community
Become a Member

PHP-FPM Does PHP-FPM have connection pooling for MySQL?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by jeffwidman, Oct 28, 2015.

Tags:
  1. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    6:35 AM
    Saw this commit: update mysql my.cnf templates · centminmod/centminmod@1c10458 · GitHub

    I was surprised @eva2000 bumped it so high... does PHP-FPM have MySQL connection pooling where you don't want connections to be closed after every pageview?

    Most googling showed that PHP apps should have wait_timeout between 60-90 seconds max, but those were assuming PHP wasn't being served through an app server that could manage a connection pool.
     
  2. pamamolf

    pamamolf Well-Known Member

    2,682
    240
    63
    May 31, 2014
    Ratings:
    +427
    Local Time:
    4:35 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    I was thinking the same as it is a huge value !
     
  3. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    you're confusing php timeout and mysql wait_timeout which can occur without any php involvement i.e. classic 'MySQL server has gone away' issues MariaDB - DB problems, daily freezes
     
  4. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    6:35 AM
    No, I was thinking about MySQL wait_timeout in production systems that are serving PHP apps.
     
  5. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    well mysql wait_timeout applies to all connections not just PHP ones so not a good idea to restrict it's value specifically for PHP usage hence why i raised it's value and well PHP has it's own timeouts to restrict PHP specific usage anyway :)
     
  6. Xon

    Xon Active Member

    127
    57
    28
    Nov 16, 2015
    Ratings:
    +166
    Local Time:
    9:35 PM
    1.11.x
    MariaDB 10.1.x
    You can use persistent connections with php-fpm and MySQL. Assuming you are using mysqli connection connector, and php 5.3+

    For the database host string, enter 'p:127.0.0.1' instead of '127.0.0.1'. Make sure to test that your implementation works as expected.
     
    • Informative Informative x 3
  7. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    database host string in web app's config.php file ? like xenforo library/config.php ?
     
  8. Xon

    Xon Active Member

    127
    57
    28
    Nov 16, 2015
    Ratings:
    +166
    Local Time:
    9:35 PM
    1.11.x
    MariaDB 10.1.x
    Yup. For example:
    Code:
    $config['db']['host'] = 'p:127.0.0.1';
    $config['db']['port'] = '3306';
    
     
    • Informative Informative x 4
  9. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    Oooh nice.. learnt something new :D
     
    • Like Like x 2
  10. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    6:35 AM
    Hmm... I just tried this on a wordpress wp-config.php using PHP 5.6.17 and MariaDB 10 and '127.0.0.1' works, but 'p:127.0.0.1' fails to establish db connection, even after restarting php with 'nprestart' (centminmod shortcut).

    @eva2000 were you able to get this to work? Can you confirm centminmod by default using mysqli? I'm not sure how to check which db driver it's using (or is this specified by the app itself?)
     
  11. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    i believe that is something specific to Xenforo/Zend Framework ?
    see PHP-FPM - CentminMod.com LEMP Nginx web stack for CentOS
    PHP: Introduction - Manual
    Code:
    php --ri mysql
    
    mysql
    
    MySQL Support => enabled
    Active Persistent Links => 0
    Active Links => 0
    Client API version => mysqlnd 5.0.11-dev - 20120503 - $Id: 15d5c781cfcad91193dceae1d2cdd127674ddb3e $
    
    Directive => Local Value => Master Value
    mysql.allow_persistent => On => On
    mysql.max_persistent => Unlimited => Unlimited
    mysql.max_links => Unlimited => Unlimited
    mysql.default_host => no value => no value
    mysql.default_user => no value => no value
    mysql.default_password => no value => no value
    mysql.default_port => no value => no value
    mysql.default_socket => /var/lib/mysql/mysql.sock => /var/lib/mysql/mysql.sock
    mysql.connect_timeout => 60 => 60
    mysql.trace_mode => Off => Off
    mysql.allow_local_infile => On => On
    
    Code:
    php --ri mysqli
    
    mysqli
    
    MysqlI Support => enabled
    Client API library version => mysqlnd 5.0.11-dev - 20120503 - $Id: 15d5c781cfcad91193dceae1d2cdd127674ddb3e $
    Active Persistent Links => 0
    Inactive Persistent Links => 0
    Active Links => 0
    
    Directive => Local Value => Master Value
    mysqli.max_links => Unlimited => Unlimited
    mysqli.max_persistent => Unlimited => Unlimited
    mysqli.allow_persistent => On => On
    mysqli.default_host => no value => no value
    mysqli.default_user => no value => no value
    mysqli.default_pw => no value => no value
    mysqli.default_port => 3306 => 3306
    mysqli.default_socket => /var/lib/mysql/mysql.sock => /var/lib/mysql/mysql.sock
    mysqli.reconnect => Off => Off
    mysqli.allow_local_infile => On => On
    But yes whatever the web php app is configured to use as Centmin Mod supports them all out of the box :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
  13. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    and #31018 (Persistent database connections with mysqli) – WordPress Trac
     
  14. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    6:35 AM
    Thanks for the links... looks like I'll need to read up a bit to understand what all is happening behind the scenes here. I'm much more comfortable with connection pooling in the Python web framework/SQLAlchemy world.

    @Xon - have you tried this with WP at all? Or just with Xenforo?

    Does the locks not being released issue that @eva2000 mentioned in the post right above this one a potential issue with XF/Zend?
     
  15. Xon

    Xon Active Member

    127
    57
    28
    Nov 16, 2015
    Ratings:
    +166
    Local Time:
    9:35 PM
    1.11.x
    MariaDB 10.1.x
    Looks like Wordpress explicitly blocks persistent connections.

    Just with XenForo.

    If the php-fpm process seg-faults and dies, the connection should die and any locks be released. Otherwise php-fpm should still call the mysqlnd extension's cleanup function when the request ends. Regardless of the php script throws/dies/exits.

    I suspect Wordpress is guarding against old and shitty php versions where mysqlnd didn't not have that cleanup function so it could hit the case they talk about. And just haven't read or studied the latest behaviour.
     
    Last edited: Jan 25, 2016
    • Informative Informative x 1
  16. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    indeed MySQL :: MySQL PHP API :: 6.9.3 MySQL Native Driver Plugin Architecture
     
    • Like Like x 2
  17. abs0lut3

    abs0lut3 New Member

    7
    2
    3
    Jan 10, 2016
    Kiev, Ukraine
    Ratings:
    +3
    Local Time:
    4:35 PM
    1.9.12
    MariaDB 10.1
    Me too :)
     
  18. RoldanLT

    RoldanLT Well-Known Member

    3,883
    947
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,291
    Local Time:
    9:35 PM
    1.11
    10.2
    So you guys already using this on your Live XF Forum? :)
     
  19. eva2000

    eva2000 Administrator Staff Member

    29,763
    6,721
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,035
    Local Time:
    11:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    not using it myself
     
    • Like Like x 1
  20. Xon

    Xon Active Member

    127
    57
    28
    Nov 16, 2015
    Ratings:
    +166
    Local Time:
    9:35 PM
    1.11.x
    MariaDB 10.1.x
    Yes, used on SpaceBattles and Sufficient Velocity without an issue.

    Persistent Redis connections however are buggy as fuck.
     
    • Informative Informative x 2