nginx & mysql tuning

[JacobD]

I'm an admin migrating away from vb4 (yay! I took over from someone else).

New server stats running on Linode.com:
1. 1.5MM posts
2. 4GB RAM
3. 100GB SSD
4. CentOS7
5. VPS with the kernel using a Xen container

So I updated Xenforo to use front-end caching and back-end caching. I setup memcached to use 256MB of RAM and it will cache sessions as well. The site averages 30 users active, but can get up to 300 for brief periods of times.

So there's a couple of questions that we'd like to do. We've done a preliminary migration using MariaDB and plan to migrate by the end of next month.

Questions:
1. We are using MariaDB 5.5.41. According to some searches it uses InnoDB by default. Any suggested memory sizes to use for pooling? I couldn't seem to find a good up-to-date resource, but I found this https://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics/

2. Should I change MariaSQL's following variables:
> # Activate query cache
> query_cache_limit=2M
> query_cache_size=64M
> query_cache_type=1

> # Max number of connections
> max_connections=400

> # Reduce timeouts
> interactive_timeout=30
> wait_timeout=30
> connect_timeout=10

3. Should we increase or decrease the memory footprint of memcached based on #1 and #2?

4. I'm thinking of serving non-php files by having nginx use memcached (see https://www.igvita.com/2008/02/11/nginx-and-memcached-a-400-boost/). I want to move to a CDN in the near future, but this should be good enough for now. Is anyone else doing this?

5. Along with #2 and #5, I'm thinking of adding the following to nginx.conf

> location ~* .(gif|jpg|jpeg|png|ico|js|css)$ {
> root /path/to/your/yourwebsite.com;
> expires max;
> add_header Pragma public;
> add_header Cache-Control "public, must-revalidate, proxy-revalidate";
> memcached_pass 127.0.0.1:11211;
> error_page 404 = @dynamic_request;
> }

Am I going down the wrong path here?

 

[Mouth]

Any suggested memory sizes to use for pooling?

https://tools.percona.com/wizard

2. Should I change MariaSQL's following variables:

query_cache is quite irrelevant when using InnoDB
Best to use query_cache_size = 0;

3. Should we increase or decrease the memory footprint of memcached

256M is a good number for a 1.5M post site

I'm thinking of serving non-php files by having nginx use memcached

That won't help much, if at all. Nginx is already good at caching static files.

want to move to a CDN in the near future

That will help more. Recommend keycdn

I'm thinking of adding the following to nginx.conf

       location ~*  \.(jpg|jpeg|png|gif|css|js|ico|swf|flv|xml|pdf)$ {
                expires 30d;
                add_header Pragma public;
                add_header Cache-Control "public, must-revalidate, proxy-revalidate";
                access_log off;
                log_not_found off;
                gzip on;
                gzip_static on;
       }

 

[m1ne]

I'd set innodb_buffer_pool to 3G on your server. Ensure these are also in my.cnf,

innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 4
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_log_buffer_size=8M

Although most will recommend disabling query cache, I still see a performance gain when using it.
As for CDN, I'd recommend Cloudflare Pro (free is also good).

 

[Moscato]

As to query cache, whether it helps depends on a wide number of variables.
Every time you have a write, it hurts you. Every time you have a read (select) operation, it helps you. Anything above 128MB will significantly harm you, regardless how much memory you have. It's not uncommon to run query cache sizes of 20MB or less.

Thing is, Xenforo cache reduces database reads in general so it tilts you away from it being useful. I just turn it off. I'd rather find my performance gains by reducing database select queries instead of trying to make them faster.

For innodb_buffer_pool, it's best to go to 1.2x your database in size, with no more than 60% of your total ram used for this variable. If you had 8GB+ of ram, I'd say no more than 75%.

256MB isn't bad for memcached, but you may need to play with it depending on your specific database size, and workload.

 

[JacobD]

Thanks a lot guys. I tuned up nginx based on @Mouth's recommendation per the bottom of his post.

I used that site along with @Solidus' recommendations to tune mariaDB. I ended up disabling query_cache completely.

Finally, based on @Moscato's recommendation I bumped up memcached to 384MB.

 

[m1ne]

Although most will recommend disabling query cache, I still see a performance gain when using it.

I take this back.

 

[Moscato]

I take this back.

You need about a 90% or higher select rate, and if you're using any decent form of caching infront of it, that just won't happen

 

[Mouth]

256MB isn't bad for memcached, but you may need to play with it depending on your specific database size, and workload.

I think 128Mb for memcache, even with a busy and/or large XF site is more than enough. I have 128Mb, and still only see approx 60% memory utilisation. What are you seeing with your 256Mb? Anything more is just a waste of potential php/mysql/nginx memory allocation I believe.

 

[Nuno]

Hello,

Just for my informatiom, what are the advantages of using memcache in a single server setup instead of apc?

 

[Mouth]

what are the advantages of using memcache in a single server setup instead of apc?

You should do both, not instead.
APC memory caches chunks of PHP code, so repetitive browser requests for the same PHP page can come from the servers fast memory cache, instead of reading the PHP from disk and interpreting it each time. It reduces the load on php-fpm
Memcache memory caches chunks of DB data, so that when needed again it comes from the servers fast memory cache, instead of requesting/reading from the DB each time. It reduces the load on your MySQL server.

 

[Nuno]

What about APCu vs Memcached?

 

[Moscato]

What about APCu vs Memcached?

APCu is a flat data store with no real management besides kicking the oldest thing out

Memcached is a least recently used cache system, which kicks the least recently used thing out

APCu has a significantly higher chance of hash collisions, which can cause weird thrashing

I'd suggest memcached (or even better, redis) over APCu

I'd actually suggest not using APC for opcache at all either, as it's depreciated, and has formally been replaced with Zend Opcache.

My personal recommendation for best performance is Zend Opcache + Redis

 

[Nuno]

I use zend opcache with APCu. I'll try zend opcache with redis.

 

[Moscato]

I use zend opcache with APCu. I'll try zend opcache with redis.

Xenforo redis requires an addon, btw

Zend Redis Cache | XenForo Community

I am currently using it on my site.