1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

nginx & mysql tuning

Discussion in 'Server Configuration and Hosting' started by JacobD, Jun 26, 2015.

  1. JacobD

    JacobD Member

    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?
     
  2. Mouth

    Mouth Well-Known Member

    https://tools.percona.com/wizard

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

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

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

    That will help more. Recommend keycdn

    Code:
           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;
           }
    
     
    Last edited: Jun 27, 2015
  3. Solidus

    Solidus Well-Known Member

    I'd set innodb_buffer_pool to 3G on your server. Ensure these are also in my.cnf,
    Code:
    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).
     
  4. Moscato

    Moscato Active Member

    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.
     
  5. JacobD

    JacobD Member

    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.
     
  6. Solidus

    Solidus Well-Known Member

    I take this back.
     
  7. Moscato

    Moscato Active Member

    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
     
  8. Mouth

    Mouth Well-Known Member

    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.

    Screen Shot 2015-06-30 at 19.07.15.png
     
    Last edited: Jun 30, 2015
    HWS likes this.
  9. Nuno

    Nuno Active Member

    Hello,

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

    Mouth Well-Known Member

    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.
     
  11. Nuno

    Nuno Active Member

    What about APCu vs Memcached?
     
  12. Moscato

    Moscato Active Member

    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
     
    RoldanLT likes this.
  13. Nuno

    Nuno Active Member

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

    Moscato Active Member

    Xenforo redis requires an addon, btw

    Zend Redis Cache | XenForo Community

    I am currently using it on my site.
     
    Nuno likes this.

Share This Page