Please help: Nginx config for Drupal + APC + Php + FPM

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
johntang's picture
Posted by johntang on August 21, 2014 at 3:38pm

Hello there,

I use OpenVZ with 4 CPUs and 4GB RAM on Redis 6 (Final). I running Drupal 7 using perusio config and seem the site is load slowly.

My server is using Nginx PHP-FPM, APC, Redis.

These are my config for PHP-FPM:

[www]
listen = /var/run/php-fpm.sock

listen.allowed_clients = 127.0.0.1

user = apache
group = apache

pm = dynamic
pm.max_children = 20
pm.start_servers = 7
pm.min_spare_servers = 5
pm.max_spare_servers = 10
pm.max_requests = 200

slowlog = /var/log/php-fpm/www-slow.log

php_flag[display_errors] = off
php_admin_value[error_log] = /var/log/php-fpm/www-error.log
php_admin_flag[log_errors] = on
php_admin_value[error_reporting] = E_ERROR | E_WARNING

; Set session path to a directory owned by process user
php_value[session.save_handler] = files
php_value[session.save_path] = /var/lib/php/session

My Nginx Config:

user  apache;
worker_processes  4;

error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;


events {
  worker_connections  1024;
  multi_accept on;
}


http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;
  
  
   ## limit request frequency to 2 requests per second
    # Chan. D-Dos - Co van de - It's cause for very slow server
    #limit_req_zone  $binary_remote_addr  zone=one:10m   rate=2r/s;
    #limit_req   zone=one  burst=7;
  
   ## FastCGI.
    include /etc/nginx/apps/fastcgi.conf;
 
   server_names_hash_bucket_size 64;
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    #access_log  /var/log/nginx/access.log  main;

    sendfile        on;
    #tcp_nopush     on;
   
    include /etc/nginx/conf.d/*.conf;

   ## Define a zone for limiting the number of simultaneous
    ## connections nginx accepts. 1m means 32000 simultaneous
    ## sessions. We need to define for each server the limit_conn
    ## value refering to this or other zones.
    ## ** This syntax requires nginx version >=
    ## ** 1.1.8. Cf. http://nginx.org/en/CHANGES. If using an older
    ## ** version then use the limit_zone directive below
    ## ** instead. Comment out this
    ## ** one if not using nginx version >= 1.1.8.
    limit_conn_zone $binary_remote_addr zone=arbeit:10m;

   ## Timeouts.
    client_body_timeout 60;
    client_header_timeout 60;
    keepalive_timeout 2 5;
    send_timeout 60;

  
   ## Reset lingering timed out connections. Deflect DDoS.
    reset_timedout_connection on;
 

    ## Body size.
    client_max_body_size 20m;
  client_body_buffer_size 128k;
    ## TCP options.
    tcp_nodelay on;
    ## Optimization of socket handling when using sendfile.
    tcp_nopush on;

    ## Compression.
    gzip on;
    gzip_buffers 16 8k;
    gzip_comp_level 1;
    gzip_http_version 1.1;
    gzip_min_length 10;
    gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript image/x-icon application/vnd.ms-fontobject font/opentype application/x-font-ttf;
    gzip_vary on;
    gzip_proxied any; # Compression for all requests.
    ## No need for regexps. See
    ## http://wiki.nginx.org/NginxHttpGzipModule#gzip_disable
    gzip_disable "msie6";

    #gzip_static on;

    ## Hide the Nginx version number.
    server_tokens off;
 
   ## Block MIME type sniffing on IE.
    add_header X-Content-Options nosniff;
  
   ## Handling of IPs in proxied and load balancing situations.
    set_real_ip_from 0.0.0.0/32; # all addresses get a real IP.
    real_ip_header X-Forwarded-For; # the ip is forwarded from the load balancer/proxy
   
   include apps/reverse_proxy.conf;   
  
   ## Include the map to block HTTP methods.
    include apps/map_block_http_methods.conf;


    ## Include the php-fpm status allowed hosts configuration block.
    ## Uncomment to enable if you're running php-fpm.
    include apps/php_fpm_status_allowed_hosts.conf;

    ## Include the Nginx stub status allowed hosts configuration block.
    include apps/nginx_status_allowed_hosts.conf;

    ## Include blacklist for bad bot and referer blocking.
    include apps/blacklist.conf;
 
   include apps/map_cache.conf; ## Important John Tang
   
   ## Microcache zone definition for FastCGI.
    include fastcgi_microcache_zone.conf;
  
   upstream phpcgi {
      ## If your version of Nginx doesn't have the fair load balancer:
      ## https://github.com/gnosek/nginx-upstream-fair comment out the
       ## following line.
     ##fair;
        server unix:/var/run/php-fpm.sock;
     #keepalive 5;
  }
}

APC CONFIG:

apc.enabled = 1
; A comma-separated list of POSIX extended regular expressions.
apc.filters
; The mktemp-style file_mask to pass to the mmap module
apc.mmap_file_mask= /var/www/html/tmp/apc.XXXXXX
; This file_update_protection setting puts a delay on caching brand new files.
apc.file_update_protection=2
; Setting this enables APC for the CLI version of PHP (Mostly for testing and debugging).
apc.enable_cli=0
; Prevents large files from being cached
apc.max_file_size=5M
; Whether to stat the main script file and the fullpath includes.
apc.stat=0
; Vertification with ctime will avoid problems caused by programs such as svn or rsync by making
; sure inodes have not changed since the last stat. APC will normally only check mtime.
apc.stat_ctime=0
; Whether to canonicalize paths in stat=0 mode or fall back to stat behaviour
apc.canonicalize=0
; With write_lock enabled, only one process at a time will try to compile an
; uncached script while the other processes will run uncached
apc.write_lock=1
; Logs any scripts that were automatically excluded from being cached due to early/late binding issues.
apc.report_autofilter=0
; RFC1867 File Upload Progress hook handler
apc.rfc1867=1
apc.rfc1867_prefix =upload_
apc.rfc1867_name=APC_UPLOAD_PROGRESS
apc.rfc1867_freq=0
apc.rfc1867_ttl=3600
; Optimize include_once and require_once calls and avoid the expensive system calls used.
apc.include_once_override=1
apc.lazy_classes=0
apc.lazy_functions=0
; Enables APC handling of signals, such as SIGSEGV, that write core files when signaled.
; APC will attempt to unmap the shared memory segment in order to exclude it from the core file
apc.coredump_unmap=0
; Records a md5 hash of files.
apc.file_md5=0
; not documented
apc.preload_path
apc.shm_size= 120M

apc.shm_segments=1
apc.cache_by_default=0

; PHP file cache 1 hour ## 0 for drupal permancing
apc.ttl=0

; User cache 2 hour ##
apc.user_ttl=3600

; Garbage collection 1 hour ##
apc.gc_ttl=3600


extension=apc.so
extension=redis.so

Mysqld Config:

[client]
port        = 3306
socket        = /var/lib/mysql/mysql.sock

[mysqld]
user      = mysql
pid-file    = /var/run/mysqld/mysqld.pid
# generic configuration options
port        = 3306
socket        = /var/lib/mysql/mysql.sock
tmpdir        = /tmp
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
#log-bin=mysql-bin
#binlog_format=mixed

#
# * lower_case for vanilla forum, upcase issues
#
lower_case_table_names = 1

server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G

default-storage-engine=InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = /var/lib/mysql/
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_group_home_dir = /var/lib/mysql/

[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

#[myisamchk]
#key_buffer_size = 512M
#sort_buffer_size = 512M
#read_buffer = 8M
#write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192

So please help me have a look, is there wrong with this?

Thanks,
John

Comments

It will be tough to give you

Posted by bhosmer on August 21, 2014 at 4:53pm
bhosmer's picture

It will be tough to give you an answer based on just your configuration.

Have you tried determining what the bottleneck is?

Is it the database? Is it the webserver? Do you have a large number of logged in users or is most of your traffic anonymous? How many modules do you have enabled? Are there certain pages that load faster than others?

Do you have lots of views? Have you enabled caching?

Bhosmer, Thanks for your

Posted by johntang on August 21, 2014 at 5:24pm
johntang's picture

Bhosmer, Thanks for your reply.

My traffic is about 100 per day, I user mysql 5.1, PHP 5.3. Our site almost using Views. The main modules used is:

Search API
Views
Views PHP
Views Slideshow
Microdata
Microdata
Redis
Entity cache
Metatag
Omega Tools
XML sitemap
Views Content Cache

Through debugging, I see the bottleneck on Views module.
Beside Drupal, I have use Vanilla forum, but it very fast than Drupal.

Thanks
John

How did you determine it was

Posted by bhosmer on August 21, 2014 at 8:48pm
bhosmer's picture

How did you determine it was the views module that was your bottleneck? You might check out the High Performance group for some good information.

I'd start by analyzing your database performance using mysqltuner.pl and some other tools.

Check the slow query log

Posted by perusio on August 21, 2014 at 9:25pm
perusio's picture

activate it in your MySQL/MariaDB setup. Just a side comment. It makes no sense in your php-fom config to use UNIX sockets and then constrain the clients. By definition UNIX sockets are local things that are tied to a particular file system. So just comment it out.

Check for something like:

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

The above logs all queries tqking more than 2 seconds and not using indexes.

100 visits per day is a small site. You might tune your MySQL server setup a little bit. Starting by the InnoDB pool size. Make it be at least 75% of your server RAM. In your case 3G.

I used Devel module for

Posted by johntang on August 22, 2014 at 4:31am
johntang's picture

I used Devel module for determine the bottleneck, and I'm sure Views is caused.

My current innodb buffer pool size is 1GB, I think it normally. And log_slow_queries I'm also tried some time before But it not log anything :(.

@perusio: Your suggestion is using TCP instead of UNIX php-fpm.sock?

Other ideas?

Tracking twos day for slow

Posted by johntang on August 24, 2014 at 4:46am
johntang's picture

Tracking twos day for slow log 2 second, I get a lot of query:

/usr/libexec/mysqld, Version: 5.1.71-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 140823  1:33:55
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.412065  Lock_time: 0.298936 Rows_sent: 1  Rows_examined: 62249
use porwin;
SET timestamp=1408772035;
SELECT country FROM ip2country
             WHERE ('2015751061' >= ip_range_first AND '2015751061' <= ip_range_last) LIMIT 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.411049  Lock_time: 0.297579 Rows_sent: 1  Rows_examined: 62249
SET timestamp=1408772035;
SELECT country FROM ip2country
             WHERE ('2015751087' >= ip_range_first AND '2015751087' <= ip_range_last) LIMIT 1;
# Time: 140823  1:33:56
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.017395  Lock_time: 0.017344 Rows_sent: 3  Rows_examined: 3
SET timestamp=1408772036;
SELECT module, delta, rid FROM block_role;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.017401  Lock_time: 0.017332 Rows_sent: 3  Rows_examined: 3
SET timestamp=1408772036;
SELECT module, delta, rid FROM block_role;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.022344  Lock_time: 0.022295 Rows_sent: 13  Rows_examined: 13
SET timestamp=1408772036;
SELECT module, delta, type FROM block_node_type;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.022391  Lock_time: 0.022313 Rows_sent: 13  Rows_examined: 13
SET timestamp=1408772036;
SELECT module, delta, type FROM block_node_type;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.035628  Lock_time: 0.035570 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408772036;
SELECT 1 FROM views_view LIMIT 0, 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.035358  Lock_time: 0.035329 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408772036;
SELECT 1 FROM views_view LIMIT 0, 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.023378  Lock_time: 0.023293 Rows_sent: 2  Rows_examined: 2
SET timestamp=1408772036;
SELECT base.id AS id, base.name AS name, base.machine_name AS machine_name, base.description AS description, base.server AS server, base.item_type AS item_type, base.options AS options, base.enabled AS enabled, base.read_only AS read_only, base.status AS status, base.module AS module
FROM
search_api_index base;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.024027  Lock_time: 0.023913 Rows_sent: 2  Rows_examined: 2
SET timestamp=1408772036;
SELECT base.id AS id, base.name AS name, base.machine_name AS machine_name, base.description AS description, base.server AS server, base.item_type AS item_type, base.options AS options, base.enabled AS enabled, base.read_only AS read_only, base.status AS status, base.module AS module
FROM
search_api_index base;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.026540  Lock_time: 0.026006 Rows_sent: 250  Rows_examined: 250
SET timestamp=1408772036;
SELECT base.cid AS cid, base.iso2 AS iso2, base.iso3 AS iso3, base.name AS name, base.official_name AS official_name, base.numcode AS numcode, base.continent AS continent, base.enabled AS enabled, base.language AS language
FROM
countries_country base;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.029396  Lock_time: 0.028841 Rows_sent: 250  Rows_examined: 250
SET timestamp=1408772036;
SELECT base.cid AS cid, base.iso2 AS iso2, base.iso3 AS iso3, base.name AS name, base.official_name AS official_name, base.numcode AS numcode, base.continent AS continent, base.enabled AS enabled, base.language AS language
FROM
countries_country base;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.001272  Lock_time: 0.001228 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408772036;
SELECT 1 FROM facetapi LIMIT 0, 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000578  Lock_time: 0.000033 Rows_sent: 31  Rows_examined: 53
SET timestamp=1408772036;
SELECT t__0.
FROM
facetapi t__0
WHERE  (searcher = 'search_api@games');
# Time: 140823  1:33:58
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.065497  Lock_time: 0.065329 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408772038;
select *
from GDN_Permission Permission
limit 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.064282  Lock_time: 0.064165 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408772038;
select *
from GDN_Permission Permission
limit 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.021936  Lock_time: 0.021631 Rows_sent: 16  Rows_examined: 29
SET timestamp=1408772038;
select *
from GDN_User User
where UserID in ('31', '30', '29', '28', '27', '1', '26', '25', '24', '23', '22', '21', '20', '19', '18', '17');
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.022043  Lock_time: 0.021715 Rows_sent: 16  Rows_examined: 29
SET timestamp=1408772038;
select *
from GDN_User User
where UserID in ('31', '30', '29', '28', '27', '1', '26', '25', '24', '23', '22', '21', '20', '19', '18', '17');
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000242  Lock_time: 0.000046 Rows_sent: 15  Rows_examined: 29
SET timestamp=1408772038;
select *
from GDN_User User
where UserID in ('31', '30', '29', '28', '27', '1', '26', '24', '23', '22', '21', '20', '19', '18', '17');
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000108  Lock_time: 0.000025 Rows_sent: 32  Rows_examined: 32
SET timestamp=1408772038;
select *
from GDN_ActivityType ActivityType;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.034132  Lock_time: 0.033895 Rows_sent: 9  Rows_examined: 18
SET timestamp=1408772038;
select c.
from GDN_ActivityComment c
where c.ActivityID in ('218', '216', '214', '212', '210', '209', '206', '204', '203', '201', '195', '198', '196', '193', '191', '188', '185', '183', '181', '180', '177', '174', '172', '171', '170', '169', '168', '167', '166', '164')
order by c.ActivityID, c.DateInserted asc;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.034161  Lock_time: 0.033868 Rows_sent: 9  Rows_examined: 18
SET timestamp=1408772038;
select c.*
from GDN_ActivityComment c
where c.ActivityID in ('218', '216', '214', '212', '210', '209', '206', '204', '203', '201', '195', '198', '196', '193', '191', '188', '185', '183', '181', '180', '177', '174', '172', '171', '170', '169', '168', '167', '166', '164')
order by c.ActivityID, c.DateInserted asc;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000560  Lock_time: 0.000505 Rows_sent: 1  Rows_examined: 2
SET timestamp=1408772038;
select *
from GDN_Message Message
order by Sort asc;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000599  Lock_time: 0.000522 Rows_sent: 1  Rows_examined: 2
SET timestamp=1408772038;
select *
from GDN_Message Message
order by Sort asc;
.
.
.

.
.
.
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000445  Lock_time: 0.000039 Rows_sent: 1  Rows_examined: 1957
SET timestamp=1408854651;
SELECT
                updated_to, operation_interval, start_run, end_run
            FROM
                rv_log_maintenance_priority
                WHERE (run_type = 1)
            ORDER BY start_run DESC
            LIMIT 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000060  Lock_time: 0.000025 Rows_sent: 0  Rows_examined: 110
SET timestamp=1408854651;
DELETE FROM
            rv_ad_zone_assoc
            WHERE
                link_type = 2;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000451  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 1958
SET timestamp=1408854651;
SELECT
                updated_to, operation_interval, start_run, end_run
            FROM
                rv_log_maintenance_priority
                WHERE (run_type = 2)
            ORDER BY start_run DESC
            LIMIT 1;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000375  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 1959
SET timestamp=1408854651;
SELECT
                updated_to, operation_interval, start_run, end_run
            FROM
                rv_log_maintenance_priority
                WHERE (run_type = 2)
            ORDER BY start_run DESC
            LIMIT 1;
# Time: 140824  0:34:01
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.001233  Lock_time: 0.000040 Rows_sent: 889  Rows_examined: 889
SET timestamp=1408854841;
SELECT name, value FROM variable;
# Time: 140824  0:34:16
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000943  Lock_time: 0.000035 Rows_sent: 889  Rows_examined: 889
SET timestamp=1408854856;
SELECT name, value FROM variable;
# Time: 140824  0:35:38
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.001414  Lock_time: 0.000042 Rows_sent: 889  Rows_examined: 889
SET timestamp=1408854938;
SELECT name, value FROM variable;
# Time: 140824  0:35:52
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.025956  Lock_time: 0.000057 Rows_sent: 1  Rows_examined: 92682
SET timestamp=1408854952;
SELECT country FROM ip2country
             WHERE ('1318980578' >= ip_range_first AND '1318980578' <= ip_range_last) LIMIT 1;
# Time: 140824  0:35:57
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000099  Lock_time: 0.000039 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408854957;
SELECT c.cid AS cid, c.category AS category
FROM
contact c
ORDER BY weight ASC, category ASC;
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000098  Lock_time: 0.000050 Rows_sent: 1  Rows_examined: 1
SET timestamp=1408854957;
SELECT cid FROM contact WHERE selected = 1;
# Time: 140824  0:37:17
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.000226  Lock_time: 0.000069 Rows_sent: 0  Rows_examined: 1
SET timestamp=1408855037;
SELECT f.name AS name, fc.count AS count
FROM
flag f
LEFT OUTER JOIN flag_counts fc ON f.fid = fc.fid
WHERE  (fc.entity_type = 'node') AND (fc.entity_id = '47465');
# Time: 140824  0:37:31
# User@Host: porwin[porwin] @ localhost []
# Query_time: 0.009116  Lock_time: 0.000051 Rows_sent: 1  Rows_examined: 36103
SET timestamp=1408855051;
SELECT country FROM ip2country
             WHERE ('-952477997' >= ip_range_first AND '-952477997' <= ip_range_last) LIMIT 1;

Any suggestion for slow logs?

Start With the Slowest

Posted by mesch on August 24, 2014 at 9:40am
mesch's picture

It's tough to remotely diagnose this without all the details, but here are a few thoughts:

  1. If you're quite sure MySQL queries are the cause, then start with the slowest and/or most common recurring entries in the slow log.

  2. You say that you believe the problem is with Views, and further up you note Views PHP is enabled. Make sure you aren't doing anything with Views PHP that would slow things to the extent you're seeing.

  3. I see in the log a lot of entries pertaining to various "GDN" table operations. Revisit any custom module table schemas to make sure they're indexed/optimized.

Nginx

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: