/ Register

Join the community today
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:
Previous Thread Next Thread
Loading...
Page 1 of 2
  1. Oct 28, 2015 #1
    jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    3:05 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. Oct 28, 2015 #2
    pamamolf

    pamamolf Premium Member Premium Member

    4,086
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    12:05 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    I was thinking the same as it is a huge value !
     
  3. Oct 29, 2015 #3
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Oct 30, 2015 #4
    jeffwidman

    jeffwidman Active Member

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

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Nov 16, 2015 #6
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    6:05 PM
    1.15.x
    MariaDB 10.3.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.
     
  7. Nov 16, 2015 #7
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    database host string in web app's config.php file ? like xenforo library/config.php ?
     
  8. Nov 16, 2015 #8
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    6:05 PM
    1.15.x
    MariaDB 10.3.x
    Yup. For example:
    Code:
    $config['db']['host'] = 'p:127.0.0.1';
$config['db']['port'] = '3306';
     
  9. Nov 16, 2015 #9
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Oooh nice.. learnt something new :D
     
  10. Jan 25, 2016 #10
    jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    3:05 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. Jan 25, 2016 #11
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Jan 25, 2016 #12
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
  13. Jan 25, 2016 #13
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    and #31018 (Persistent database connections with mysqli) – WordPress Trac
     
  14. Jan 25, 2016 #14
    jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    3:05 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. Jan 25, 2016 #15
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    6:05 PM
    1.15.x
    MariaDB 10.3.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
  16. Jan 26, 2016 #16
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    indeed MySQL :: MySQL PHP API :: 6.9.3 MySQL Native Driver Plugin Architecture
     
  17. Mar 1, 2016 #17
    abs0lut3

    abs0lut3 New Member

    7
    2
    3
    Jan 10, 2016
    Kiev, Ukraine
    Ratings:
    +3
    Local Time:
    12:05 PM
    1.9.12
    MariaDB 10.1
    Me too :)
     
  18. Mar 1, 2016 #18
    rdan

    rdan Well-Known Member

    5,447
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    6:05 PM
    Mainline
    10.2
    So you guys already using this on your Live XF Forum? :)
     
  19. Mar 1, 2016 #19
    eva2000

    eva2000 Administrator Staff Member

    54,894
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    8:05 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    not using it myself
     
  20. Mar 3, 2016 #20
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    6:05 PM
    1.15.x
    MariaDB 10.3.x
    Yes, used on SpaceBattles and Sufficient Velocity without an issue.

    Persistent Redis connections however are buggy as fuck.
     
Previous Thread Next Thread
Loading...
Page 1 of 2

.