Low performance on IPB forum

[Nechifor Stefan]

Hello,

Although we have a decent dedicated server Intel Xeon X3330 @ 2.66 GHz, 4GB of RAM, 2 sata hdd(one for the webserver and for the sql server) and it's moving really slowly.

In the sql error log(acp -> stats & logs -> sql error logs) many "MySQL server has gone away appear" and "Connection refused" messages. I opened a ticket on invisionpower.com and they said that my sql server is really slow, timing out and refusing connections.

We use nginx and percona mysql server. Here are the configs

my.cnf

[mysqld]

#datadir=/var/lib/mysql

datadir=/backup/mysql

socket= /var/run/mysqld/mysqld.sock

#/var/lib/mysql/mysql.sock

#user=mysql

skip-external-locking

query_cache_size = 16M

query_cache_type = 1

query_cache_limit = 16M

max_connections=50

interactive_timeout=160

wait_timeout=10

connect_timeout=200

key_buffer_size=256M

join_buffer=2M

max_allowed_packet = 1M

table_cache=3086

tmp_table_size=10M

max_heap_table_size=512M

symbolic-links=0

thread_cache_size=8

join_buffer_size=64M

sort_buffer_size=4M

read_rnd_buffer_size=3536K

long_query_time=5

log-slow-queries=/var/log/mysql/log-slow-queries.log

log-queries-not-using-indexes

# Others options for Semisynchronous Replication

#;rpl_semi_sync_master_enabled=1

#;rpl_semi_sync_master_timeout=10

#;rpl_semi_sync_slave_enabled=1

#chestii noi adaugate

table_open_cache = 256

read_buffer_size = 1M

#skip-networking

skip-federated

local-infile=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

open_files_limit=8192

[isamchk]

key_buffer_size = 256M

sort_buffer_size = 512M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer_size = 16M

sort_buffer_size = 512M

read_buffer = 2M

write_buffer = 2M

nginx.conf

php5-fpm.conf

mysqltuner.pl results

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.32-31.0-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 2G (Tables: 228)

[--] Data in InnoDB tables: 16K (Tables: 1)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 25

-------- Security Recommendations -------------------------------------------

[!!] User '@forum' has no password set.

[!!] User '@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------

[--] Up for: 3s (916 q [305.333 qps], 276 conn, TX: 19M, RX: 79K)

[--] Reads / Writes: 97% / 3%

[--] Total buffers: 426.0M global + 72.7M per thread (50 max threads)

[!!] Maximum possible memory usage: 4.0G (102% of installed RAM)

[OK] Slow queries: 1% (13/916)

[OK] Highest usage of available connections: 8% (4/50)

[!!] Key buffer size / total MyISAM indexes: 256.0M/1.3G

[!!] Key buffer hit rate: 89.1% (5K cached / 650 reads)

[!!] Query cache efficiency: 3.4% (21 cached / 616 selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)

[OK] Temporary tables created on disk: 13% (54 on disk / 393 total)

[OK] Thread cache hit rate: 98% (4 created / 276 connections)

[!!] Table cache hit rate: 19% (256 open / 1K opened)

[OK] Open file limit used: 6% (506/8K)

[OK] Table locks acquired immediately: 99% (449 immediate / 450 locks)

[OK] InnoDB data size / buffer pool: 16.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Reduce your overall MySQL memory footprint for system stability

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

key_buffer_size (> 1.3G)

query_cache_limit (> 16M, or use smaller result sets)

table_cache (> 256)

The board has 40k members, 2.6kk posts and about 1000-1500 users active in the last 60 minutes. Our users are making about 20 views each.

I'm a beginner in tweaking a server. Should I upgrade the server or do I have to optimize it?

;;;;;;;;;;;;;;;;;;;;;

; FPM Configuration ;

;;;;;;;;;;;;;;;;;;;;;

; All relative paths in this configuration file are relative to PHP's install

; prefix (/usr). This prefix can be dynamicaly changed by using the

; '-p' argument from the command line.

; Include one or more files. If glob(3) exists, it is used to include a bunch of

; files from a glob(3) pattern. This directive can be used everywhere in the

; file.

; Relative path can also be used. They will be prefixed by:

; - the global prefix if it's been set (-p arguement)

; - /usr otherwise

;include=/etc/php5/fpm/*.conf

;;;;;;;;;;;;;;;;;;

; Global Options ;

;;;;;;;;;;;;;;;;;;

[global]

; Pid file

; Note: the default prefix is /var

; Default Value: none

pid = /var/run/php5-fpm.pid

; Error log file

; If it's set to "syslog", log is sent to syslogd instead of being written

; in a local file.

; Note: the default prefix is /var

; Default Value: log/php-fpm.log

error_log = /var/log/php5-fpm.log

; syslog_facility is used to specify what type of program is logging the

; message. This lets syslogd specify that messages from different facilities

; will be handled differently.

; See syslog(3) for possible values (ex daemon equiv LOG_DAEMON)

; Default Value: daemon

;syslog.facility = daemon

; syslog_ident is prepended to every message. If you have multiple FPM

; instances running on the same server, you can change the default value

; which must suit common needs.

; Default Value: php-fpm

;syslog.ident = php-fpm

; Log level

; Possible Values: alert, error, warning, notice, debug

; Default Value: notice

;log_level = notice

; If this number of child processes exit with SIGSEGV or SIGBUS within the time

; interval set by emergency_restart_interval then FPM will restart. A value

; of '0' means 'Off'.

; Default Value: 0

emergency_restart_threshold = 3

; Interval of time used by emergency_restart_interval to determine when

; a graceful restart will be initiated. This can be useful to work around

; accidental corruptions in an accelerator's shared memory.

; Available Units: s(econds), m(inutes), h(ours), or d(ays)

; Default Unit: seconds

; Default Value: 0

emergency_restart_interval = 1m

; Time limit for child processes to wait for a reaction on signals from master.

; Available units: s(econds), m(inutes), h(ours), or d(ays)

; Default Unit: seconds

; Default Value: 0

process_control_timeout = 200s

; The maximum number of processes FPM will fork. This has been design to control

; the global number of processes when using dynamic PM within a lot of pools.

; Use it with caution.

; Note: A value of 0 indicates no limit

; Default Value: 0

; process.max = 0

; Specify the nice(2) priority to apply to the master process (only if set)

; The value can vary from -19 (highest priority) to 20 (lower priority)

; Note: - It will only work if the FPM master process is launched as root

; - The pool process will inherit the master process priority

; unless it specified otherwise

; Default Value: no set

; process.priority = -19

; Send FPM to background. Set to 'no' to keep FPM in foreground for debugging.

; Default Value: yes

;daemonize = yes

; Set open file descriptor rlimit for the master process.

; Default Value: system defined value

;rlimit_files = 1024

; Set max core size rlimit for the master process.

; Possible Values: 'unlimited' or an integer greater or equal to 0

; Default Value: system defined value

;rlimit_core = 0

; Specify the event mechanism FPM will use. The following is available:

; - select (any POSIX os)

; - poll (any POSIX os)

; - epoll (linux >= 2.5.44)

; - kqueue (FreeBSD >= 4.1, OpenBSD >= 2.9, NetBSD >= 2.0)

; - /dev/poll (Solaris >= 7)

; - port (Solaris >= 10)

; Default Value: not set (auto detection)

; events.mechanism = epoll

;;;;;;;;;;;;;;;;;;;;

; Pool Definitions ;

;;;;;;;;;;;;;;;;;;;;

; Multiple pools of child processes may be started with different listening

; ports and different management options. The name of the pool will be

; used in logs and stats. There is no limitation on the number of pools which

; FPM can handle. Your system will tell you anyway :)

; To configure the pools it is recommended to have one .conf file per

; pool in the following directory:

include=/etc/php5/fpm/pool.d/*.conf

user www-data;

worker_processes 4;

pid /run/nginx.pid;

events {

worker_connections 1024;

# multi_accept on;

}

http {

#limit_req_zone $binary_remote_addr zone=fp:10m rate=3r/s;

##

# Basic Settings

##

sendfile on;

tcp_nopush off;

tcp_nodelay on;

keepalive_timeout 0;

keepalive_requests 50;

types_hash_max_size 2048;

server_tokens off;

# server_names_hash_bucket_size 64;

# server_name_in_redirect off;

include /etc/nginx/mime.types;

default_type application/octet-stream;

##

# Logging Settings

##

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

error_log /var/log/nginx/error.log;

##

# Gzip Settings

##

gzip on;

gzip_http_version 1.1;

gzip_comp_level 2;

gzip_types text/plain text/html text/css application/x-javascript text/xml application/xml application/xml+rss text/javascript;

# gzip_vary on;

# gzip_proxied any;

# gzip_comp_level 6;

# gzip_buffers 16 8k;

# gzip_http_version 1.1;

# gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript;

##

# nginx-naxsi config

##

# Uncomment it if you installed nginx-naxsi

##

#include /etc/nginx/naxsi_core.rules;

##

# nginx-passenger config

##

# Uncomment it if you installed nginx-passenger

##

#passenger_root /usr;

#passenger_ruby /usr/bin/ruby;

##

# Virtual Host Configs

##

include /etc/nginx/conf.d/*.conf;

include /etc/nginx/sites-enabled/*;

client_max_body_size 20M;

}

#mail {

## See sample authentication script at:

##

http://wiki.nginx.org/ImapAuthenticateWithApachePhpScript

#

## auth_http localhost/auth.php;

## pop3_capabilities "TOP" "USER";

## imap_capabilities "IMAP4rev1" "UIDPLUS";

#

#server {

#listen localhost:110;

#protocol pop3;

#proxy on;

#}

#

#server {

#listen localhost:143;

#protocol imap;

#proxy on;

#}

#}

[Rick L]

Sounds as if your simply running out of memory at first glance.

[CheersnGears]

you are absolutely running out of memory and mysqltuner.pl is even telling you that it will happen.

Edit: I also run on a box with 4gb of memory

Here are some key settings from my my.cnf - this will make it fit in your memory footprint, but you may need more RAM for your size board.

key_buffer_size = 435M

max_allowed_packet = 16M

thread_stack = 256K

thread_cache_size = 16k

tmp_table_size = 64M

max_heap_table_size = 64M

join_buffer_size = 256k

read_buffer_size = 256k

sort_buffer_size = 256k

open_files_limit = 4096

table_cache = 2048

table_definition_cache = 2048

wait_timeout = 60

interactive_time = 60

max_connections = 50

query_cache_limit = 128M

query_cache_size = 128M

[isamchk]

key_buffer=32M

sort_buffer=32M

read_buffer=32M

write_buffer=32M

[IveLeft...]

If you have shell access then log in and see what the memory is doing

Some useful diagnosis commands here

http://community.invisionpower.com/topic/362126-tools-commands-to-diagnose-problems/

[Makoto]

query_cache_limit = 128M

query_cache_size = 128M

I would personally recommend disabling query_cache entirely.

My experience shows it's mostly useless with complex applications like IP.Board, and it's probably not worth dedicating 128MB of memory to it, especially when constrained.

Keep in mind that you need memory to run other applications on your server as well, such as Apache and the OS.

Running out of memory and forcing your server to swap will almost always lead to poor overall performance. On an active server, it can even cause your server to become almost completely unresponsive, making a hard reset required just to regain access to the machine again. I learned this lesson the hard way when I was first starting out.

It can be tempting to just throw large values into your configuration when "tuning" to try and improve performance, but this is foolish and dangerous. Be careful with these things.

[CheersnGears]

IP board isn't the only site software running on my server. My configuration run's well and leaves enough memory for Apache and such. I haven't had a reboot due to crash since September (before this my.cnf was even set up).

[CheersnGears]

If the OP has the ability to swap out the SQL hard drive for an SSD, that would improve response times. That's what i'm running on now.

[Makoto]

Yes, there's no overall perfect configuration though.

Running out of memory is one of the worst things that can happen on a server system.

My server has 16GB of memory installed, and I use around 8-9GB of that overall on a forum that peaks at around ~325 active users and has around 800,000 posts/30,000 members.

But I also have various other programs running on my server box. Roughly ~4.5GB of that is dedicated to MySQL however, mostly from the InnoDB buffer pool.

I don't know how large your forum is, and I'm hesitant to say "just throw more memory at the problem", but 4GB isn't really a lot and, if you have the option, upgrading to 8GB or so wouldn't be a bad idea.

Even if you won't need all of that 8GB, it's good to have a little breathing room. Plus, free memory is used for a wonderful thing called disk caching, so it's certainly not wasted.

[CheersnGears]

My forum fits in my ram nicely. I have SSDs for all storage, so the benefits of disk caching would be minimal.

The OP would probably need more ram for the size and load of his forum, but the my.cnf I posted would allow his SQL server to not use up 102% of physical ram.

Under this configuration, my MySQL maximum ram usage is just 22% of installed ram... again, this might not be enough allocation for OP, but it would still fit into his RAM footprint and give him breathing room if he wanted to expand it more. I do have a performance issue on my site with TTFB, but it isn't MySQL related because it happens on plain HTML pages also. In short, MySQL "just runs" with no issues under this config.

>> MySQLTuner 1.2.0 - Major Hayden 
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 635)
[--] Data in InnoDB tables: 118M (Tables: 606)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 2)
[!!] Total fragmented tables: 52

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 8h 4m 17s (21M q [20.264 qps], 784K conn, TX: 1859B, RX: 5B)
[--] Reads / Writes: 51% / 49%
[--] Total buffers: 771.0M global + 1.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 833.5M (22% of installed RAM)
[OK] Slow queries: 1% (392K/21M)
[OK] Highest usage of available connections: 56% (28/50)
[OK] Key buffer size / total MyISAM indexes: 435.0M/436.2M
[OK] Key buffer hit rate: 99.9% (427M cached / 600K reads)
[OK] Query cache efficiency: 45.6% (5M cached / 12M selects)
[!!] Query cache prunes per day: 22305
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 429K sorts)
[OK] Temporary tables created on disk: 10% (85K on disk / 826K total)
[OK] Thread cache hit rate: 99% (28 created / 784K connections)
[OK] Table cache hit rate: 58% (1K open / 2K opened)
[OK] Open file limit used: 33% (1K/4K)
[OK] Table locks acquired immediately: 99% (19M immediate / 19M locks)
[OK] InnoDB data size / buffer pool: 118.9M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_size (> 128M)

@mhtx.net>

[Makoto]

Okay. Now that I've actually skimmed your entire post, I'll point out a few things I noticed.

Your buffer sizes are rather huge. Your join_buffer_size is 64MB as an example, I have this set to 4MB. Setting this too high can actually have negative implications on performance, so there's no reason to do so. I don't use MyISAM for much anything, so I'm not going to comment on those configurations, but your buffer settings overall seem to be a bit on the high side.

This thread may be of interest to you Nechifor,

http://community.invisionpower.com/topic/395569-optimizing-mysql-whats-in-your-myini/

[Nechifor Stefan]

CheersnGears, with your config it runs much better even using less resource. :) I'll consider getting some SSDs.

Thanks Kirito for the link.

[CheersnGears]

Glad to help.

In 48 hours, run your mysqltuner.pl again. For a board your size, you're going to want to watch the Max Connections percentage. If that number gets anywhere near 80%, bump it up by 25 connections, wait another 48 hours, and test again. Adjust anything else mysqltuner tells you to except, as Kirito says, the query cache sizes.

especially look at your security issues... looks like you have some accounts without passwords there and that could lead to a compromised site.