Flarum on VPS (Optimization)

neogeo

I'm using Flarum on a VPS (1vcore and 2 GB ram)

PROBLEM:
MYSQL/MARIADB is getting almost all the ram on the VPS.
(and I'm just testing in private way so I'm 4evahlone in this VPS )

QUESTION:
¿Is it normal? ¿Someone happens the same?

SOLUTION:
Before to upgrade the VPS for another one better... I would like to ask to Flarumminity which would be a nice httpd.conf and/or my.cnf configuration for optimize that kind of mini VPS

Maybe we can found why the flarum are eating all the ram on VPS, or maybe I discover that it's totally normal and just I need a better VPS.

TheyCallMeTojo

You can try nginx instead of Apache. It’s much lighter.

neogeo

TheyCallMeTojo thank you for the suggestion, but I need apache.

andreherberth You're right, these are my httpd.conf and my.cnf

HTTPD.CONF

Apache: 2.4.6
Server MPM:     prefork
Server API: 	FPM/FastCGI

KeepAlive On
MaxKeepAliveRequests 500
KeepAliveTimeout 3

<IfModule mpm_prefork_module>
    StartServers             5
    MinSpareServers          5
    MaxSpareServers         10
    MaxRequestWorkers	   250
    MaxConnectionsPerChild   0
</IfModule>
<IfModule mpm_worker_module>
    StartServers             3
    MinSpareThreads         75
    MaxSpareThreads        250
    ThreadsPerChild         25
    MaxRequestWorkers	   400
    MaxConnectionsPerChild   0
</IfModule>
<IfModule mpm_event_module>
    StartServers             3
    MinSpareThreads         75
    MaxSpareThreads        250
    ThreadsPerChild         25
    MaxRequestWorkers	   400
    MaxConnectionsPerChild   0
</IfModule>

MY.CNF

MariaDB: 10.1.37

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance_schema = on

symbolic-links=0
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
innodb_log_file_size = 16M
thread_concurrency = 8
query_cache_type =0
query_cache_limit =1M
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_open_cache = 512
max_allowed_packet = 1M
key_buffer_size = 384M

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

htop says:

mysqltuner says:

andreherberth

What are your current my.cnf/httpd.conf config? A tad easier to give you pointers if these where available 🙂

TheyCallMeTojo

You can try something like
‘ulimit -s 2048’ or whatever memory limit you’d like to set.

ahrasis

I think you can even run a server on that kind of vps. 😉

Try using latest stable mariadb 10.3 via adding its repository: https://downloads.mariadb.org/mariadb/repositories
Do you have swap? Is it enabled? I think this could be a good backup for your system if your ram is suddenly fully utilized.
I already forgot abot apache2 since I turned to use nginx. Somebody with good apache2 knowledge and experience may help you with its configuration.

Just a thought. 🙂

rob006

mysqltuner should show you summary about RAM usage - it is much more useful than screen of warning. Also according to htop MySQL takes only _14% of RAM, it is not that much. Do you have any real stability problems?

neogeo

rob006 Do you have any real stability problems?

That's the thing, I'm alone in this VPS in a private way testing Flarum a little... and I get alerts about the ram.

So, I'm wondering what will happens when I make it public and people come to use it. ƪ(‾.‾“)┐

andreherberth

I use this tool sometimes when starting fresh (Before mysqltuner has enough data to work with): https://tools.percona.com/wizard

It generates a my.cnf based on your input.
It's mainly aimed at the Percona fork of mysql, but it got options for MariaDB and mysql vanilla as well.

If you are only hosting flarum on this server, you'd like to optimize for innodb, not myisam. 🙂

rob006

neogeo and I get alerts about the ram.

You mean alerts from mysqltuner or something else?

neogeo

rob006 mysqltuner and others likeVirtualmin and tops

Right know I'm not doing nothing on the VPS... but ram is used and mostly by mysql

Real memory: 1.75 GB total / 726.90 MB free

Imagine what will happens when I make flarum public on this VPS, it will eat all the ram in minutes.

At the end, I just want to know if i need another VPS with more ram (now it have 2GB) or for other hand is something on my configuration that I have to change to optimize the performance.

rob006

neogeo Imagine what will happens when I make flarum public on this VPS

Probably nothing. You didn't share your memory usage report from mysqltuner, but I'm guessing that this warning (and current memory usage) is related to innodb_buffer_pool_size and high number of allowed connections. If you're afraid about memory usage, you may lower both values - if you're hosting only forum on this server, you can set innodb_buffer_pool_size a bit higher than size of your database (128M may be a good start if this is a fresh installation) . If you have only one CPU, you probably will not be able to serve more than 10 concurrent requests, so you may set max_connections to something like 20.

You should also switch Apache to use event MPM and set max_children in php-fpm to something like 10. With these settings it should work on server with half of that RAM. You may increase these values in future, but I'm guessing that for busy forum CPU will be main bottleneck, not RAM.

neogeo

Thanks for all the tips @rob006 ☜(ﾟヮﾟ☜)

rob006 You didn't share your memory usage report from mysqltuner

Currently running supported MySQL version 10.1.37-MariaDB
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 1.6M (Tables: 28)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 20h 53m 45s (26K q [0.161 qps], 2K conn, TX: 56M, RX: 5M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 1.7G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 0B
[--] Total buffers: 696.0M global + 12.5M per thread (151 max threads)
[--] P_S Max memory usage: 92M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 838.9M (46.86% of installed RAM)
[!!] Maximum possible memory usage: 2.6G (149.77% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/26K)
[OK] Highest usage of available connections: 2% (4/151)
[!!] Aborted connections: 7.69%  (202/2627)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 38.0% (12K cached / 32K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 370 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 7% (121 on disk / 1K total)
[OK] Thread cache hit rate: 99% (4 created / 2K connections)
[OK] Table cache hit rate: 95% (140 open / 146 opened)
[OK] Open file limit used: 0% (61/16K)
[OK] Table locks acquired immediately: 100% (24K immediate / 24K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 92.7M
[--] Sys schema isn't installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.1.37-MariaDB)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/124.0K
[!!] Read Key buffer hit rate: 93.3% (30 cached / 2 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/1.6M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 16.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.71% (229355 hits/ 230020 total)
[!!] InnoDB Write Log efficiency: 84.9% (9529 hits/ 11224 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1695 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.2% (14K cached / 121 reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)

rob006

Limiting number of concurrent requests at php-fpm level should be enough - as long you do not open over 20 DB connections, MySQL should not use more than 1 GB of memory. You may also lower key_buffer_size and other MyISAM related buffers.

Fell

I just set this on my VPS to fix the performance issues:

opcache.validate_timestamps = 0 # was 1
opcache.memory_consumption = 512 # was 128

Background: I have a very old 2 core VPS that still uses mechanical disks. It looks like validate_timestamps caused too many short file reads on all the php files that the I/O just blocked for 3-5 seconds on every request.