Results 1 to 21 of 21
-
10-21-2014, 05:03 PM #1Junior Guru Wannabe
- 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
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
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
'./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
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
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
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>>
-
10-21-2014, 06:17 PM #2The Linux Specialist
- 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
-
10-21-2014, 07:26 PM #3Engineer
- Join Date
- Jan 2005
- Location
- Scotland, UK
- Posts
- 2,681
maybe 10 concurrent connections without spiking to a 17 load.
When the load is occurring,
- Get the whm-server-status
- Grab the mysql process list
- iostat -x 1 10Server 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
-
10-22-2014, 02:01 AM #4Junior Guru Wannabe
- 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.
-
10-22-2014, 05:30 PM #5Junior Guru Wannabe
- 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.
-
10-23-2014, 04:53 PM #6Junior Guru Wannabe
- 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>>
-
10-24-2014, 10:29 AM #7Junior Guru Wannabe
- 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.
-
10-24-2014, 02:08 PM #8Web Hosting Master
- Join Date
- Nov 2006
- Location
- Karachi, Pakistan
- Posts
- 1,359
This is probably the cause of your problems:
Code:pm.max_children = 2
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
-
10-24-2014, 04:43 PM #9Junior Guru Wannabe
- Join Date
- Apr 2003
- Location
- Columbia, SC
- Posts
- 46
-
10-24-2014, 04:45 PM #10Junior Guru Wannabe
- 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>>
-
10-26-2014, 11:57 AM #11Junior Guru Wannabe
- 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
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.<<Please see rules for signature setup>>
-
10-26-2014, 04:35 PM #12Web Hosting Master
- 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>
-
10-26-2014, 04:48 PM #13Web Hosting Master
- 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
-
10-26-2014, 05:33 PM #14(formerly WhichGunDotCom)
- Join Date
- Jun 2011
- Location
- Woodbridge, NJ
- Posts
- 840
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.
-
10-28-2014, 03:55 PM #15Junior Guru Wannabe
- Join Date
- Apr 2003
- Location
- Columbia, SC
- Posts
- 46
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?<<Please see rules for signature setup>>
-
10-29-2014, 11:45 AM #16Junior Guru Wannabe
- 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)
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>>
-
11-01-2014, 08:09 AM #17Junior Guru Wannabe
- 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>>
-
11-01-2014, 02:13 PM #18Web Hosting Master
- 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)
And this shows why it is using that much ram:
Code:Your applications are not closing MySQL connections properly
In the meantime make these changes:
Reduce the number of connections allowed globally:
Code:max_connections=100
And per user:
Code:max_user_connections=15
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.
-
11-02-2014, 10:32 AM #19Junior Guru Wannabe
- 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>>
-
11-02-2014, 12:23 PM #20Web Hosting Master
- 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.
-
11-02-2014, 12:26 PM #21Junior Guru Wannabe
- 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
-
Apache to nginx with php-fpm?
By magayo in forum Hosting Security and TechnologyReplies: 6Last Post: 02-05-2014, 01:07 PM -
apache+php-fpm crash on high loads (nginx+php-fpm = fine, apache+mod-php = fine)
By abrcam in forum VPS HostingReplies: 2Last Post: 05-05-2013, 04:33 PM -
Apache 2.4 and PHP-FPM
By ubuntu2 in forum Hosting Security and TechnologyReplies: 1Last Post: 07-22-2012, 03:33 PM -
Apache worker MPM & PHP
By RandomThoughts in forum Hosting Security and TechnologyReplies: 3Last Post: 10-22-2009, 08:01 AM -
Apache vs Nginx (mod_php VS php-fpm)
By bostjan in forum Hosting Security and TechnologyReplies: 7Last Post: 06-25-2009, 07:19 AM