Results 1 to 21 of 21
  1. #1
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46

    * Looking for Apache, event MPM, PHP-FPM seasoned assistance.

    Hey gang. It's bee quite some time since I've been active here, but coming back to post as I need some help. This is cross-posted from cpanel (at their suggestion), so if you've seen this before, please accept my apologies, but I'm getting no traction there.


    Level of urgency: 8 (out of 10, being the highest)

    Quick History

    I have 173 /etc/localdomains, but at any given time only have about 10 or so producing any amount of traffic. For the small amount of traffic I get, the server can come to a crawl with high CPU and Memory usage. The configurations below are what I have now, that have combated higher CPU and Memory usage, but still I know are not optimized for my server.

    The end result is that I need a speedy responsive server that doesn't die every other day (early hours of the morning) for no reason, and can keep up with... maybe 10 concurrent connections without spiking to a 17 load.


    Here are my server specs:

    Linux 2.6.32-358.2.1.el6.i686 #1 SMP Tue Mar 12 21:42:46 UTC 2013 i686 i686 i386 GNU/Linux
    Version: Apache/2.4.10 (Unix) OpenSSL/1.0.1e-fips mod_bwlimited/1.4 mod_fcgid/2.3.9 Server MPM: event
    Cpanel::Easy::Apache v3.26.7 rev9999
    CENTOS 6.5 i686 vmware
    WHM 11.44.1 (build 17)
    PHP 5.5.16 (cli)
    Zend Engine v2.5.0
    with the ionCube PHP Loader v4.6.1
    MySQL version: 5.5.37-cll

    I used SysAlly's cpHstack for php-fpm for MPM Event

    Current meminfo (nice and low load of course...)

    Code:
    top - 09:17:37 up 65 days, 23:59,  8 users,  load average: 0.73, 1.22, 1.34
    Tasks: 170 total,   1 running, 168 sleeping,   0 stopped,   1 zombie
    Cpu(s):  1.4%us,  3.6%sy,  0.0%ni, 94.7%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
    Mem:   8181200k total,  7270404k used,   910796k free,   418896k buffers
    Swap:  4161528k total,    53780k used,  4107748k free,  3652880k cached
    # /etc/my.cnf

    Code:
    [mysql]
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    # GENERAL #
    user                           = mysql
    default_storage_engine         = MyISAM
    socket                         = /var/lib/mysql/mysql.sock
    pid_file					   = /var/lib/mysql/myserver.com.pid
    # MyISAM #
    key_buffer_size                = 2G
    myisam_recover                 = FORCE,BACKUP
    # SAFETY #
    max_allowed_packet             = 16M
    max_connect_errors             = 1000000
    sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    # CACHES AND LIMITS #
    tmp_table_size                 = 64M
    max_heap_table_size            = 64M
    query_cache_type               = 1
    query_cache_size               = 8M
    max_connections                = 500
    thread_cache_size              = 50
    table_definition_cache         = 4096
    table_open_cache               = 10540
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 32M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 1M
    # LOGGING #
    log_error					   = /var/lib/mysql/myserver.com.err 
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql-slow.log
    max_allowed_packet		       = 268435456
    Portions of php.ini

    Code:
    [PHP]
    max_execution_time = 30  
    max_input_time = 60
    max_input_vars = 5000
    memory_limit = 32M
    
    error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED
    
    extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20121212"
    zend_extension="/usr/local/IonCube/ioncube_loader_lin_5.5.so"
    extension = xcache.so
    [xcache]
    xcache.shm_scheme =        "mmap"
    xcache.size  =               512M
    xcache.count =                 2
    xcache.slots =                8K
    xcache.ttl   =                 0
    xcache.gc_interval =           0
    xcache.var_size  =            16M
    xcache.var_count =             2
    xcache.var_slots =            8K
    xcache.var_ttl   =             0
    xcache.var_maxttl   =          0
    xcache.var_gc_interval =     300
    xcache.var_namespace_mode =    0
    xcache.var_namespace =        ""
    xcache.readonly_protection = Off
    xcache.mmap_path =    "/dev/zero"
    xcache.coredump_directory =   "/tmp/xcache/"
    xcache.coredump_type =         0
    xcache.disable_on_crash =    Off
    xcache.experimental =        Off
    xcache.cacher =               On
    xcache.stat   =               On
    xcache.optimizer =            On
    xcache.coverager_autostart =  On
    xcache.coveragedump_directory = "/tmp/xcache/"

    # ulimit -a

    Code:
    core file size          (blocks, -c) 0
    data seg size           (kbytes, -d) unlimited
    scheduling priority             (-e) 0
    file size               (blocks, -f) unlimited
    pending signals                 (-i) 63796
    max locked memory       (kbytes, -l) 64
    max memory size         (kbytes, -m) unlimited
    open files                      (-n) 1024
    pipe size            (512 bytes, -p) 8
    POSIX message queues     (bytes, -q) 819200
    real-time priority              (-r) 0
    stack size              (kbytes, -s) 10240
    cpu time               (seconds, -t) unlimited
    max user processes              (-u) 63796
    virtual memory          (kbytes, -v) unlimited
    file locks                      (-x) unlimited
    Current php Compile config:

    './configure' '--disable-fileinfo' '--disable-posix' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-fastcgi' '--enable-fpm' '--enable-ftp' '--enable-gd-native-ttf' '--enable-intl' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-wddx' '--enable-zip' '--prefix=/usr/local' '--with-bz2' '--with-curl=/opt/curlssl/' '--with-enchant=/usr' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-icu-dir=/usr' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-png-dir=/usr' '--with-pspell' '--with-snmp' '--with-tidy=/opt/tidy/' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr'


    Apache Loaded Modules:

    core.c, event.c, http_core.c, mod_access_compat.c, mod_actions.c, mod_alias.c, mod_auth_basic.c, mod_authn_core.c, mod_authn_file.c, mod_authz_core.c, mod_authz_groupfile.c, mod_authz_host.c, mod_authz_user.c, mod_autoindex.c, mod_bw.c, mod_bwlimited.c, mod_cgi.c, mod_dbd.c, mod_deflate.c, mod_dir.c, mod_env.c, mod_expires.c, mod_fcgid.c, mod_file_cache.c, mod_filter.c, mod_headers.c, mod_include.c, mod_info.c, mod_log_config.c, mod_logio.c, mod_mime.c, mod_negotiation.c, mod_proxy.c, mod_proxy_connect.c, mod_proxy_fcgi.c, mod_proxy_http.c, mod_rewrite.c, mod_security2.c, mod_setenvif.c, mod_slotmem_shm.c, mod_so.c, mod_socache_dbm.c, mod_socache_shmcb.c, mod_ssl.c, mod_status.c, mod_suexec.c, mod_unique_id.c, mod_unixd.c, mod_userdir.c,


    Apache /whm-server-status

    Code:
    Current Time: Monday, 20-Oct-2014 08:59:51 EDT
    Restart Time: Sunday, 19-Oct-2014 16:12:34 EDT
    Parent Server Config. Generation: 136
    Parent Server MPM Generation: 135
    Server uptime: 16 hours 47 minutes 17 seconds
    Server load: 1.00 0.99 0.96
    Total accesses: 72507 - Total Traffic: 1.9 GB
    CPU Usage: u80.36 s39.52 cu.09 cs.53 - .199% CPU load
    1.2 requests/sec - 32.9 kB/second - 27.4 kB/request
    2 requests currently being processed, 190 idle workers
    PID	Connections	Threads	Async connections
    total	accepting	busy	idle	writing	keep-alive	closing
    3245	3	yes	0	64	0	0	3
    3246	5	yes	2	62	0	1	4
    3247	2	yes	0	64	0	1	1
    Sum	10	 	2	190	0	2	8

    Apache /server-info

    Code:
    Server Version: Apache/2.4.10 (Unix) OpenSSL/1.0.1e-fips mod_bwlimited/1.4 mod_fcgid/2.3.9
    Server Built: Sep 6 2014 15:26:15
    Server loaded APR Version: 1.5.1
    Compiled with APR Version: 1.5.1
    Server loaded APU Version: 1.5.3
    Compiled with APU Version: 1.5.3
    Timeouts: connection: 100    keep-alive: 4
    MPM Name: event
    MPM Information: Max Daemons: 10 Threaded: yes Forked: yes
    Server Architecture: 32-bit

    PHP-FPM information ###########################:

    #/usr/local/etc/php-fpm.conf

    Code:
    [global]
    pid = /var/run/php-fpm/php-fpm.pid
    error_log = /var/log/php-fpm.log
    process.max = 128
    [nobody]
    user = nobody
    group = nobody
    listen = 127.0.0.1:9000
    pm = ondemand
    pm.max_children = 2
    pm.process_idle_timeout = 20s
    pm.max_requests = 0
    pm.status_path = /fpm-status
    ping.path = /ping
    slowlog = /var/log/php-fpm/$pool.log.slow
    request_slowlog_timeout = 10s
    php_admin_value[memory_limit] = 16M
    include=/opt/xstack/cphstack/php-fpm.pool.d/*.conf
    #/opt/xstack/cphstack/php-fpm.pool.d/bob.conf [Example Vhost That Crawls with traffic]

    Code:
    [bob]
    user = bob
    group = bob
    listen = /var/run/php-fpm/bob.sock
    listen.mode = 0666
    pm = ondemand
    pm.max_children = 2
    pm.process_idle_timeout = 1s;
    pm.max_requests = 200
    Example vHost portion of httpd.conf

    Code:
    <VirtualHost 192.81.81.81:80>
        ServerName bob.com 
        ServerAlias www.bob.com
        DocumentRoot /home/bob/public_html
            <IfModule mod_proxy_fcgi.c>
                    ProxyPassMatch ^/(.*\.php(/.*)?)$ unix:/var/run/php-fpm/bob.sock|fcgi://localhost/home/bob/public_html/
            </IfModule> 
        ServerAdmin webmaster@bob.com
        UseCanonicalName Off
        CustomLog /usr/local/apache/domlogs/bob.com combined
        CustomLog /usr/local/apache/domlogs/bob.com-bytes_log "%{%s}t %I .\n%{%s}t %O ."
        UserDir enabled bob
        <IfModule mod_include.c>
            <Directory "/home/bob/public_html">
                SSILegacyExprParser On
            </Directory>
        </IfModule>
        <IfModule !mod_disable_suexec.c>
            <IfModule !mod_ruid2.c>
                SuexecUserGroup bob bob
            </IfModule>
        </IfModule>
        <IfModule mod_ruid2.c>
            RMode config
            RUidGid bob bob
        </IfModule>
        <IfModule itk.c>
            AssignUserID bob bob
        </IfModule>
        ScriptAlias /cgi-bin/ /home/bob/public_html/cgi-bin/
    </VirtualHost>


    A simple stress test, such as:

    Code:
    #ab -n 200 -c 50 bob.com
    Takes about 40 seconds to complete, and can raise the server load upwards of 10. Tests are performed on both wordpress and silverstripe sites. Some with additional caching, some not. Some with CloudFlare CDN, some not.

    Any assistance to this would be GREATLY appreciated.

    - - - Updated - - -

    I should also note, I've not been able to upgrade cpanel due to a conflict:

    Running `/usr/local/cpanel/scripts/updatenow --upcp --log=/var/cpanel/updatelogs/update.1413778561.log` failed, exited with code 1 (signal = 0)

    Results in tons of these:
    file /usr/include/mysql/mysql/XYZ.h from install of MySQL55-devel-5.5.37-2.cp1136.i386 conflicts with file from package MySQL56-devel-5.6.17-1.cp1142.i386

    Lastley, with php-fpm I'm unable to actually get the /status (or in my case /fpm-status) to work, making it more difficult to troubleshoot php-FPM as a whole.

    Thanks for any and all help...
    <<Please see rules for signature setup>>

  2. #2
    Join Date
    Mar 2003
    Location
    /root
    Posts
    23,990
    Moved > Hosting Security and Technology.

    Specially 4 U
    Reseller Hosting: Boost Your Websites | Fully Managed KVM VPS: 3.20 - 5.00 Ghz, Pure Dedicated Power
    JoneSolutions.Com is on the net 24/7 providing stable and reliable web hosting solutions, server management and services since 2001
    Debian|Ubuntu|cPanel|DirectAdmin|Enhance|Webuzo|Acronis|Estela|BitNinja|Nginx

  3. #3
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,681
    maybe 10 concurrent connections without spiking to a 17 load.
    If you struggle to do 10 concurrent connections then your issue more likely going to be at the application layer than webserver/stack configuration. The FPM slow log might help but some things to do are,

    When the load is occurring,

    - Get the whm-server-status
    - Grab the mysql process list
    - iostat -x 1 10
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: sales@admingeekz.com

  4. #4
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Hi Scott.

    Thanks for your time and interest.

    Ok, so... I've created the various outcomes you've requested.

    Please see files/links attached.
    Attached Thumbnails Attached Thumbnails server_status_before.png   server_status_during.png   apachebench.png  
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Scott, (or anybody for that matter)

    Any thoughts based on these screenshots/findings?

    Also, I've managed to be able to run some php-fpm stats from the console as needed. I'm not sure how to interpret them but if you (or anybody for that matter) have suggestions on settings to test, I can do so and provide feedback from the php-fpm stats.

    Thanks.

  6. #6
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    I'm guessing 24 hours & 340+ views is telling me that my post is either too long to garner interest, or not detailed enough to form an opinion.... or doesn't have the verbiage "willing to pay for assistance" somewhere in it....



    If I can get this server running how I know it should be able to run... I'm not afraid of paying for the performance boost.
    <<Please see rules for signature setup>>

  7. #7
    Join Date
    Oct 2010
    Posts
    58
    Probably unrelated: is there a good reason you are still using 32-bit versions of software?
    I would highly recommend changing OS and software to 64-bit.

  8. #8
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    This is probably the cause of your problems:

    Code:
    pm.max_children = 2
    The above limits php-fpm to a maximum of just 2 processes for the bob pool. If this is the sort of the config you have for your more popular websites you will have gateway timeout errors. So raise that number. Generally a php-fpm process takes between 30-50MB of RAM so keep that in mind.

    I also suggest using pm = dynamic for your more popular websites. ondemand means that no processes are left running for long. Dynamic means that a configurable minimum number of processes is always running. ondemand is good for sites that get very little traffic while dynamic is better for sites that get a lot of traffic because you aren't wasting cpu time killing and starting processes all the time.

    Note that for dynamic you will have to specify some other options too like :

    Code:
    pm.start_servers  
    pm.min_spare_servers 
    pm.max_spare_servers

  9. #9
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Quote Originally Posted by PimEffting View Post
    Probably unrelated: is there a good reason you are still using 32-bit versions of software?
    I would highly recommend changing OS and software to 64-bit.
    The server has grown from a smaller hardware configuration, and OS that suggested the use of 32-bit at the time... far less ram when I started. Since then it has grown, but the software has remained.
    <<Please see rules for signature setup>>

  10. #10
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    @Website themes,

    I'll give that a shot. Though it's not that any of the site area really popular... but when they get even a small bit of traffic, they create a great deal of CPU usage. I don't have one single popular site... but I'll try this setting out on a few of the most-popular (which is laughable by many standards as being popular... )

    I'll report back with some AB tests.

    Thanks.
    <<Please see rules for signature setup>>

  11. #11
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46

    *

    @Website themes,

    I've made some various changes, first running an AB test with current settings, then again with modified settings.

    Screenshots of apache status and ab stats with mods and notes are attached.

    Overview:

    Code:
    I went FROM:                         TO:
    ondemand                             dynamic
    pm.max_children = 2            pm.max_children = 15
    pm.max_requests = 200       pm.max_requests = 500
    pm.max_spare_servers = 3   pm.max_spare_servers = 8
    The results were basically 3 times HIGHER load usage at just over 2.5 seconds faster test completion at a 30% increase on CPU

    So, I'm not convinced that the bottlneck is php-fpm.

    Any other thoughts/suggestions for possibly isolating the real cause of the problem? Is it Apache's Event MPM? MySQL I/O ?

    I'm at such a loss...

    Thanks everyone.
    Attached Thumbnails Attached Thumbnails bob-ab-initial-test.jpg   bob-ab-post-test.jpg  
    <<Please see rules for signature setup>>

  12. #12
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    Usually high load means that you are running out of RAM. You have a mysql key_buffer_size of 2GB so yeah that could be a contributing factor . Also 512MB for xcache. It's all starting to add up to a lot of waste. Reduce key_buffer_size to something more reasonable like 128M. You can also run mysqltuner.pl to get some tailored numbers.

    You have 8GB RAM, right? Post the output of free -m when you're stressing the server and also post the apache configuration for mpm_event. You'll find it up top in httpd.conf between <IfModule mpm_event_module> and the closing tag </ifmodule>

  13. #13
    Join Date
    Aug 2014
    Location
    United States
    Posts
    767
    I use the same setup on my Shared Servers. PHP-FPM goes nuts without some sort of caching in place. If you look in my signature you can see the load test using the same exact software setup you have, but I am using 5.4 (for whmcs purposes)

    If you do a test on, say, a text file, or a jpg, or some static entity, the load shouldn't go up at all using event MPM. I'll help you any way I can if you need assistance. It's my username here at gmail.
    highavailability.io ██████ ███ ██ █ █
    High Availability WordPress Hosting

  14. #14
    Join Date
    Jun 2011
    Location
    Woodbridge, NJ
    Posts
    840
    Quote Originally Posted by Website themes View Post
    I also suggest using pm = dynamic for your more popular websites. ondemand means that no processes are left running for long. Dynamic means that a configurable minimum number of processes is always running. ondemand is good for sites that get very little traffic while dynamic is better for sites that get a lot of traffic because you aren't wasting cpu time killing and starting processes all the time.

    Note that for dynamic you will have to specify some other options too like :

    Code:
    pm.start_servers  
    pm.min_spare_servers 
    pm.max_spare_servers
    Ondemand can work well for high-traffic sites, because even if the process timeout is set fairly low, new requests will usually come in before processes are killed. Besides, he has max_requests set to 200, meaning processes will be killed and spawned all the time.

  15. #15
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46

    Question Maybe MySQL?

    I'm beginning to think that the root issue may be MySQL...

    See attached monitored results during a simple: ab -n200 -c50

    It took 50 seconds to complete. This is a PHP / MySQL site built on SilverStripe CMS

    Code:
    Concurrency Level:      50
    Time taken for tests:   53.302 seconds
    Complete requests:      200
    Failed requests:        0
    Total transferred:      3281200 bytes
    HTML transferred:       3153600 bytes
    Requests per second:    3.75 [#/sec] (mean)
    Time per request:       13325.603 [ms] (mean)
    Time per request:       266.512 [ms] (mean, across all concurrent requests)
    Transfer rate:          60.12 [Kbytes/sec] received
    
    Connection Times (ms)
                  min  mean[+/-sd] median   max
    Connect:        0   13  27.1      0     111
    Processing:  2174 11996 2723.4  12879   14787
    Waiting:     2046 11726 2718.7  12610   14541
    Total:       2183 12009 2718.4  12879   14887

    Current /etc/my.conf (I say current, as I'm playing with it now)

    Code:
    [mysql]
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    user                           = mysql
    default-storage-engine         = MyISAM
    socket                         = /var/lib/mysql/mysql.sock
    pid_file                        = /var/lib/mysql/server.com.pid
    
    local-infile=0
    max_connections = 500
    max_user_connections=1000
    key_buffer_size = 256M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 2000
    connect_timeout = 10
    tmp_table_size = 128M
    max_heap_table_size = 128M
    max_allowed_packet = 32M
    net_buffer_length = 16384
    max_connect_errors = 10
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 4M
    query_cache_limit = 3M
    query_cache_size = 64M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65535
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    external-locking=FALSE
    open_files_limit=50000
    
    # LOGGING #
    log_error                       = /var/lib/mysql/server.com.err
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1   
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    
    
    [mysqld_safe]
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 256M
    sort_buffer = 256M
    read_buffer = 128M
    write_buffer = 128M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer = 256M
    read_buffer = 128M
    write_buffer = 128M
    
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K

    I ran the same ab on a PHP-only site, and it flew... so much that I increased it to -n1000 -c100 and that finished in 3.5 seconds perfectly...

    Is my hunch good or bad?
    Attached Thumbnails Attached Thumbnails mysql-monitor.png  
    <<Please see rules for signature setup>>

  16. #16
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46

    * Feel like I'm flailing... no hope/end in site : Desperate Gang

    Though I appreciate everyone that's viewed this, and more so to the ones that have chimed in... I'm still no better off now than I was (with the exception of fixing my cpup issue with MySQL conflicting 5.5 to 5.6.

    I've:
    Reinstalled MySQL [5.6.17],
    Recompiled PHP [5.5.18],
    Re-easyapache'ed [Apache/2.4.10 (Unix) OpenSSL/1.0.1e-fips mod_bwlimited/1.4],
    Re-cphstack'ed (sysally) ,
    Defaulted httpd.conf for suggested values for MPM Event,
    Been trying to tweak the crap out of my.conf as I'm almost positive THAT is where my problem lies.

    Problem:
    Maxed CPU / High Load on slight traffic bump for MySQL back-end sites (SilverStripe, Wordpress, Etc)

    Even a simple Apache Bench of 100 connections with 20 concurrent takes upwards of 50 seconds to complete, and maxes the server during that time.
    Though the same Apache Bench test on a php page with no MySQL backend completes within a 1.2 seconds.

    No memory is swapping, but I am reaching within 1GB of physical memory limits.

    32-bit, 8GB Ram, 2-Cores (correct, not 64-bit as it's grown from smaller hardware specs)



    Barely an hour & a half on these stats, I know these should be 24 hours, but I'm hurting here.

    MySQLTuner
    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.17-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 1G (Tables: 7590)
    [--] Data in InnoDB tables: 90M (Tables: 1276)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 24)
    [!!] Total fragmented tables: 43
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 23m 27s (197K q [39.372 qps], 5K conn, TX: 1B, RX: 69M)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 944.0M global + 22.2M per thread (200 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 5.3G (67% of installed RAM)
    [OK] Slow queries: 1% (3K/197K)
    [OK] Highest usage of available connections: 14% (28/200)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/461.6M
    [OK] Key buffer hit rate: 98.6% (634K cached / 8K reads)
    [OK] Query cache efficiency: 61.4% (92K cached / 149K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 503
    [!!] Temporary tables created on disk: 28% (3K on disk / 13K total)
    [OK] Thread cache hit rate: 99% (35 created / 5K connections)
    [OK] Table cache hit rate: 97% (9K open / 9K opened)
    [OK] Open file limit used: 15% (15K/100K)
    [OK] Table locks acquired immediately: 99% (88K immediate / 88K locks)
    [!!] Connections aborted: 6%
    [OK] InnoDB buffer pool / data size: 128.0M/90.4M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        join_buffer_size (> 16.0M, or always use indexes with joins)
    # /etc/my.conf

    Code:
    [mysql]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    
    [mysqld]
    slow-query-log=1
    user=mysql
    default-storage-engine=MyISAM
    socket="/var/lib/mysql/mysql.sock"
    pid_file="/var/lib/mysql/mysql.com.pid"
    skip-external-locking
    max_allowed_packet=268435456
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=4M
    myisam_sort_buffer_size=64M
    thread_cache_size=8
    thread_concurrency=8
    log_error="/var/lib/mysql/mysql.com.err"
    log-queries-not-using-indexes=1
    max-connections=200
    open_files_limit=100000
    
    #MySQL Tuner Recomended Tweaks
    join_buffer_size = 16M
    query_cache_size= 32M
    innodb_buffer_pool_size = 64M
    query_cache_type = 1
    tmp_table_size = 256M
    max_heap_table_size=256M
    table_open_cache = 10000
    key_buffer_size = 512M
    
    #phpMyAdmin Advisor Suggestions
    long_query_time = 5
    
    # INNODB #
    innodb-flush-method=O_DIRECT
    innodb-log-files-in-group=2
    innodb-log-file-size=32M
    innodb-flush-log-at-trx-commit=1
    innodb-file-per-table=1
    innodb-buffer-pool-size=8MB  
    
    innodb_buffer_pool_size=134217728
    [mysqldump]
    quick
    max_allowed_packet=16M
    [mysql]
    no-auto-rehash
    [myisamchk]
    key_buffer_size=128M
    sort_buffer_size=128M
    read_buffer=2M
    write_buffer=2M
    <<Please see rules for signature setup>>

  17. #17
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Almost 3 days of MySQL being up. Any help?

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.17-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 1G (Tables: 7609)
    [--] Data in InnoDB tables: 91M (Tables: 1276)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 24)
    [!!] Total fragmented tables: 55
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 21h 59m 32s (10M q [41.116 qps], 254K conn, TX: 60B, RX: 3B)
    [--] Reads / Writes: 71% / 29%
    [--] Total buffers: 944.0M global + 22.2M per thread (200 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 5.3G (67% of installed RAM)
    [OK] Slow queries: 2% (211K/10M)
    [OK] Highest usage of available connections: 21% (43/200)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/471.9M
    [OK] Key buffer hit rate: 97.4% (46M cached / 1M reads)
    [OK] Query cache efficiency: 68.6% (5M cached / 8M selects)
    [!!] Query cache prunes per day: 132263
    [OK] Sorts requiring temporary tables: 0% (13 temp sorts / 189K sorts)
    [!!] Joins performed without indexes: 14384
    [OK] Temporary tables created on disk: 22% (110K on disk / 492K total)
    [OK] Thread cache hit rate: 99% (638 created / 254K connections)
    [!!] Table cache hit rate: 12% (9K open / 78K opened)
    [OK] Open file limit used: 15% (15K/100K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] Connections aborted: 7%
    [OK] InnoDB buffer pool / data size: 128.0M/91.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 32M)
        join_buffer_size (> 16.0M, or always use indexes with joins)
        table_open_cache (> 10000)
    <<Please see rules for signature setup>>

  18. #18
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    It's obvious really:

    Code:
    [--] Total buffers: 944.0M global + 22.2M per thread (200 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 5.3G (67% of installed RAM)
    You've allowed mysql to use too much ram.

    And this shows why it is using that much ram:

    Code:
        Your applications are not closing MySQL connections properly
    Investigate why your user's apps (wordpress, joomla etc) are not closing mysql connections.

    In the meantime make these changes:

    Reduce the number of connections allowed globally:

    Code:
    max_connections=100
    Note in your last post above you have it set to max-connections instead of max_connections i.e. hyphen instead of underscore.

    And per user:

    Code:
    max_user_connections=15
    Also reduce the amount of RAM you allow mysql to use. For example set key_buffer_size to 256M and lower tmp_table_size and max_heap_table_size.

    Finally the biggest problem you have is that you have 8GB RAM but you are using a 32bit OS. You really should be using a 64 bit OS on such a machine.

  19. #19
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Hi @Website themes

    Thanks for your suggestions. I implimented them all (except the 64bit upgrade, which I simply can not do at this particular moment)

    Here's a 17-hour run on those settings, of which I haven't felt an increase in performance and ApacheBench still cripples the site. Additionally, when using a Wordpress admin site, I'm now presented with a white-screen-of-death every 3 or 4 pages (saves/edits/etc).

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.17
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 1G (Tables: 7609)
    [--] Data in InnoDB tables: 91M (Tables: 1276)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 24)
    [!!] Total fragmented tables: 57
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17h 37m 40s (2M q [32.666 qps], 55K conn, TX: 10B, RX: 673M)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 528.0M global + 38.2M per thread (100 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 4.2G (54% of installed RAM)
    [OK] Slow queries: 1% (40K/2M)
    [OK] Highest usage of available connections: 32% (32/100)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/467.4M
    [OK] Key buffer hit rate: 98.9% (7M cached / 79K reads)
    [OK] Query cache efficiency: 69.0% (1M cached / 1M selects)
    [!!] Query cache prunes per day: 49311
    [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 31K sorts)
    [!!] Joins performed without indexes: 3098
    [OK] Temporary tables created on disk: 23% (25K on disk / 110K total)
    [OK] Thread cache hit rate: 99% (149 created / 55K connections)
    [OK] Table cache hit rate: 81% (13K open / 16K opened)
    [OK] Open file limit used: 18% (18K/100K)
    [OK] Table locks acquired immediately: 99% (796K immediate / 797K locks)
    [!!] Connections aborted: 8%
    [!!] InnoDB  buffer pool / data size: 64.0M/91.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 32.0M, or always use indexes with joins)
        innodb_buffer_pool_size (>= 91M)
    <<Please see rules for signature setup>>

  20. #20
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    @Mindlash : Did you look into why your apps are not closing mysql connections? As I said before that is the key problem. The changes I suggested were only designed to prevent your server from crashing. They were not going to improve performance.

    Perhaps it's best if you hired a sys admin to take a look. You can place an ad in the advertising forums.

  21. #21
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    46
    Hey, that was next, but getting side-tracked. I'll happily place an ad.
    <<Please see rules for signature setup>>

Similar Threads

  1. Apache to nginx with php-fpm?
    By magayo in forum Hosting Security and Technology
    Replies: 6
    Last Post: 02-05-2014, 01:07 PM
  2. Replies: 2
    Last Post: 05-05-2013, 04:33 PM
  3. Apache 2.4 and PHP-FPM
    By ubuntu2 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 07-22-2012, 03:33 PM
  4. Apache worker MPM & PHP
    By RandomThoughts in forum Hosting Security and Technology
    Replies: 3
    Last Post: 10-22-2009, 08:01 AM
  5. Apache vs Nginx (mod_php VS php-fpm)
    By bostjan in forum Hosting Security and Technology
    Replies: 7
    Last Post: 06-25-2009, 07:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •