Scaling Database

[TechnoKatz]

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?

thanks!

 

[Ghan_04]

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.

 

[Xon]

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.

 

[BentDreams]

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.

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?

Thanks

 

[Xon]

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?

Thanks

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.

 

[BentDreams]

Thanks for the thorough response @Xon appreciate it.

 

[TechnoKatz]

@Ghan_04 thanks for the tip, we will start to use memcached this week. @Xon yes we are running both database and FE with SSD, in separated layers inside AWS (EC2 & RDS)

 

[jeffwidman]

SSDs also allow you to dramatically reduce how much memory Elastic Search uses,

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.

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

Are you running Row_format=Dynamic, or Row_format=Compressed?

 

[Xon]

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.

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.

Are you running Row_format=Dynamic, or Row_format=Compressed?

Compressed on the Posts/Profile posts/Conversations/Edit History tables

 

[jeffwidman]

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.

@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

 

[rdn]

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

/etc/sysconfig/elasticsearch

 

[jeffwidman]

Many thanks @RoldanLT

 

[eva2000]

/etc/sysconfig/elasticsearch

yeah that's where i have it set too