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

Scaling Database

Discussion in 'Server Configuration and Hosting' started by TechnoKatz, May 14, 2015.

  1. TechnoKatz

    TechnoKatz Member

    Hi guys,

    From time to time we are receiving a large number of concurrent connections (1000+ concurrent users) and we are looking for solutions on how we can scale the database more efficiently without the need to put the site on maintenance mode while scaling up hardware.

    We tried to load Xenforo over multiple master databases using percona cluster, but that required converting all MyISAM and memory tables to InnoDB. However, with InnoDB we get lots of deadlocks.

    We would like to check if there are any proven solutions on how we can scale up the database on demmand?

  2. Ghan_04

    Ghan_04 Active Member

    I'm running a board that gets upwards of 700 users online at time on a single VM with SSDs and I have no issues with the database. Do you know what kind of queries per second you are getting? Depending on what kind of addons you have, it shouldn't be much more than 500 per second and in that case, you should not even need to cluster the database yet. RAM might be a factor as well - make sure you are using it well. I have memcache doing session caching which definitely cuts down on database load.
    D.O.A., jeffwidman, Xon and 1 other person like this.
  3. Xon

    Xon Well-Known Member

    Have you converted to using SSDs for the Database server?

    It provides massive amounts of performance, to the point where I run a site with ~2000 users online (1000 members + 1000 guests) with the database being a single 2gb Linode VM, and there is still plenty of headroom.

    Elastic Search moves a decent amount of load off the database, and so too does a front-end application cache (ie Memcache, tho I use Redis).

    :edit: It does take about 4-6 vCPU cores from other Linode VMs to run all the php/elastic search. Really looking forward to php7 becoming production ready.
    Last edited: May 19, 2015
  4. BentDreams

    BentDreams Active Member

    Do you running everything on that one 2gb VM or just the database?

    If you have the XF web tier separated from the database tier what do you use for the web tier?

    I'm really having difficulty finding information that is clear regarding any sorts of guidelines on memory needs for an XF installation, so any details on your config would be greatly appreciated.

    Oh and are you running with MySQL or MariaDB or this Percona I've been seeing recently during my research?

  5. Xon

    Xon Well-Known Member

    The database is MariaDB 10, and with posts, edit history, and conversations being 9gb compressed it would require about ~32gb of ram to fit everything in memory. So the tactic is to use SSD as cheap (but slow) memory and tune MariaDB/MySQL to permit massive parallel IO usage.

    As long as the working-set fits in memory, only having 1gb of memory for the InnoDB buffer isn't even noticeable.

    The front-end uses between 4 to 6 vCPU and about 4-6gb of ram, with Elastic Search taking the load off the DB and making use of the excess disks space on the front-end nodes. SSDs also allow you to dramatically reduce how much memory Elastic Search uses, and clustering allows it to trivially scale out.

    One thing to keep in mind, is you probably don't want more than 4 php-fpm workers per cpu core. From what I've seen; on average XenForo only uses about 64mb -120mb per worker for a request (heavily depending on addons!) and can spike upto 256mb or even 512mb for some requests. Most of those high memory usage tasks tend to be related with admincp or deferred tasks; they are special so they can run under a separate pool with different resource limits.

    IMO, the single biggest memory consumer common request is when people upload avatars and they need to be resized.

    And rendering long posts with lots of bbcode is the biggest CPU consumer.
    jeffwidman and RoldanLT like this.
  6. BentDreams

    BentDreams Active Member

    Thanks for the thorough response @Xon appreciate it.
    Xon likes this.
  7. TechnoKatz

    TechnoKatz Member

  8. jeffwidman

    jeffwidman Active Member

    What's the current estimate on how much RAM per 1M posts? I've got a 3m post forum that I've thought about moving to ES, but last I checked the recommendation required more Ram than I wanted to pay for as search isn't used that often. That VPS has plenty of disk space + CPU cycles, just tight on Ram.

    Are you running Row_format=Dynamic, or Row_format=Compressed?
  9. Xon

    Xon Well-Known Member

    SpaceBattles perfectly fine at ~90mb ram per 1m posts (1.5gb ram total, and ~17.3 million posts). I wouldn't go lower than about 256mb ram per instance of Elastic Search 1.6. (newer versions of Elastic Search are vastly better at ram usage).

    Try 256mb ram total, and see how searches perform after indexing is done.

    Compressed on the Posts/Profile posts/Conversations/Edit History tables
    jeffwidman and Mouth like this.
  10. jeffwidman

    jeffwidman Active Member

    @Xon quick followup question. I see 'ES_HEAP_SIZE' is an environment variable, not set in the standard /etc/elasticsearch/elasticsearch.yml file.

    Where is the best place to set this environment variable so it persists through server reboots? .bash_profile or .bashrc?
    Do I set it for the entire server under '/etc/' somewhere? Or a particular user? ES runs under user 'elasticsearch', but that user has no home folder. Other options are the root user, my personal user, or the php webserver user (I think shouldn't matter since they're talking over TCP not unix sockets).

    Or is there some other config file I can change this variable in?

    Running CentOS 7 with Centminmod stack. cc @eva2000
  11. RoldanLT

    RoldanLT Well-Known Member

    eva2000, Xon and jeffwidman like this.
  12. jeffwidman

    jeffwidman Active Member

  13. eva2000

    eva2000 Well-Known Member

    yeah that's where i have it set too :)

Share This Page