Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Nechifor Stefan Posted February 2, 2014 Posted February 2, 2014 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/mysqldatadir=/backup/mysqlsocket= /var/run/mysqld/mysqld.sock#/var/lib/mysql/mysql.sock#user=mysqlskip-external-lockingquery_cache_size = 16Mquery_cache_type = 1query_cache_limit = 16Mmax_connections=50interactive_timeout=160wait_timeout=10connect_timeout=200key_buffer_size=256Mjoin_buffer=2Mmax_allowed_packet = 1Mtable_cache=3086tmp_table_size=10Mmax_heap_table_size=512Msymbolic-links=0thread_cache_size=8join_buffer_size=64Msort_buffer_size=4Mread_rnd_buffer_size=3536Klong_query_time=5log-slow-queries=/var/log/mysql/log-slow-queries.loglog-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 adaugatetable_open_cache = 256read_buffer_size = 1M#skip-networkingskip-federatedlocal-infile=0 [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidopen_files_limit=8192 [isamchk]key_buffer_size = 256Msort_buffer_size = 512Mread_buffer = 2Mwrite_buffer = 2M [myisamchk]key_buffer_size = 16Msort_buffer_size = 512Mread_buffer = 2Mwrite_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 limitsVariables 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: nonepid = /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.logerror_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: 0emergency_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: 0emergency_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: 0process_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 Posted February 2, 2014 Posted February 2, 2014 Sounds as if your simply running out of memory at first glance.
CheersnGears Posted February 2, 2014 Posted February 2, 2014 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... Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Posted February 2, 2014 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 Posted February 2, 2014 Author Posted February 2, 2014 CheersnGears, with your config it runs much better even using less resource. :) I'll consider getting some SSDs. Thanks Kirito for the link.
CheersnGears Posted February 2, 2014 Posted February 2, 2014 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.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.