Discover Centmin Mod today
Register Now

MariaDB Does the performance bug for table_cache exist on the MariaDB that Centmimod uses?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Jul 10, 2014.

  1. pamamolf

    pamamolf Well-Known Member

    2,450
    220
    63
    May 31, 2014
    Ratings:
    +377
    Local Time:
    8:16 PM
    Nginx-1.11.x
    MariaDB 10
  2. eva2000

    eva2000 Administrator Staff Member

    27,784
    6,337
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,340
    Local Time:
    3:16 AM
    Nginx 1.13.x
    MariaDB 5.5
    Best to ask MariaDB folks about the internals but that bug as updated patch further down the page and explains it in detail and it's only MyISAM table specific and does NOT affect InnoDB tables. Best to test for yourself and see where your optimal table_open_cache number is at for your specific database table size and work load. How you test is also left up to you. Centmin Mod by default raises MySQL max open file limits to 65536 compared to default 1024 so to be able to handle more open tables as well.

    The percona blog comments by Rick James suggest MySQL 5.6.8 didn't have the patch so could be either later MySQL >5.6.8 added it or they had different work arounds in code to fix it or they haven't patched it yet.

    However, Percona 5.1,5.5 and 5.6 have committed the patch i think https://bugs.launchpad.net/percona-server/ bug/1038940 so there's a chance MariaDB which takes some code from Percona has it too. Diffs for Percona 5.5 https://i132250861.restricted.launc...22.txt?token=d537f0e29e608a429fb658539d3d2f67 and Percona 5.6 https://i132267832.restricted.launc...22.txt?token=fdc553ba35b0b621e8be1b0fed9cc215

    http://lists.mysql.com/commits/121507

     
    • Like Like x 2
  3. RoldanLT

    RoldanLT Well-Known Member

    3,788
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,252
    Local Time:
    1:16 AM
    1.11
    10.2
    Where is this settings declare?
     
  4. eva2000

    eva2000 Administrator Staff Member

    27,784
    6,337
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,340
    Local Time:
    3:16 AM
    Nginx 1.13.x
    MariaDB 5.5
    line 26 and 27 of https://github.com/centminmod/centminmod/blob/master/inc/nginx_install.inc#L27 sets it. Forgot I raised the limit in .07 from 65536 to 262144 :)

    The default in /etc/my.cnf is set artificially lower though at open_files_limit=8192 but can be raised to 262144 if need be due to my tweaks

    Max open files = 8192 set in /etc/my.cnf

    checking to see if that's the case

    Code:
    mysqladmin var | tr -s ' ' | grep open_files_limit
    | open_files_limit | 8192 |
    
    Code:
    cat /proc/$(cat /var/lib/mysql/*.pid)/limits
    
    Limit                     Soft Limit           Hard Limit           Units 
    Max cpu time              unlimited            unlimited            seconds
    Max file size             unlimited            unlimited            bytes 
    Max data size             unlimited            unlimited            bytes 
    Max stack size            524288               524288               bytes 
    Max core file size        0                    unlimited            bytes 
    Max resident set          unlimited            unlimited            bytes 
    Max processes             3094211              3094211              processes
    Max open files            8192                 8192                 files 
    Max locked memory         65536                65536                bytes 
    Max address space         unlimited            unlimited            bytes 
    Max file locks            unlimited            unlimited            locks 
    Max pending signals       3094211              3094211              signals
    Max msgqueue size         819200               819200               bytes 
    Max nice priority         0                    0                
    Max realtime priority     0                    0                
    Max realtime timeout      unlimited            unlimited            us    
     
    • Like Like x 1
  5. RoldanLT

    RoldanLT Well-Known Member

    3,788
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,252
    Local Time:
    1:16 AM
    1.11
    10.2
    Mine:
    Code:
    # mysqladmin var | tr -s ' ' | grep open_files_limit
    | open_files_limit | 163840 |
    
    Code:
    # cat /proc/$(cat /var/lib/mysql/*.pid)/limits
    Limit  Soft Limit  Hard Limit  Units
    Max cpu time  unlimited  unlimited  seconds
    Max file size  unlimited  unlimited  bytes
    Max data size  unlimited  unlimited  bytes
    Max stack size  10485760  unlimited  bytes
    Max core file size  0  unlimited  bytes
    Max resident set  unlimited  unlimited  bytes
    Max processes  4096  4096  processes
    Max open files  163840  163840  files
    Max locked memory  65536  65536  bytes
    Max address space  unlimited  unlimited  bytes
    Max file locks  unlimited  unlimited  locks
    Max pending signals  256351  256351  signals
    Max msgqueue size  819200  819200  bytes
    Max nice priority  0  0
    Max realtime priority  0  0
    Max realtime timeout  unlimited  unlimited  us
    
    
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,788
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,252
    Local Time:
    1:16 AM
    1.11
    10.2
    How can I increase Max processes and Max pending signals ?
    How does it help my system?
    Thanks!
     
  7. eva2000

    eva2000 Administrator Staff Member

    27,784
    6,337
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,340
    Local Time:
    3:16 AM
    Nginx 1.13.x
    MariaDB 5.5
    no need to increase leave as is
     
    • Like Like x 1