Join the community today
Register Now

Xenforo What's the size of your xenforo forum's database and do you have a database server?

Discussion in 'Forum software usage' started by Jon Snow, Nov 13, 2020.

  1. Jon Snow

    Jon Snow Active Member

    589
    88
    28
    Jun 30, 2017
    Ratings:
    +141
    Local Time:
    1:31 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    Curious how many of you guys use a server dedicated to MySQL and your database and how large the database is. If you remember, around when did you decide to get a MySQL server for it?

    My 1 GB database works fine on a single Linode $10 server with its xenForo files.
     
  2. rdan

    rdan Well-Known Member

    5,008
    1,208
    113
    May 25, 2014
    Ratings:
    +1,834
    Local Time:
    1:31 AM
    Mainline
    10.2
    The largest XF database I got is 70GB.
    Some are 10-30GB, all are running on the same machine and not on separate dedicated server which will add latency.
     
  3. eva2000

    eva2000 Administrator Staff Member

    45,661
    10,360
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,079
    Local Time:
    3:31 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    On disk around 2.6GB for Xenforo and from MySQL stats by engine breakdown

    Code (Text):
    +--------+--------+---------+-----------+---------+------------+---------+
    | engine | tables | rows    | data      | idx     | total_size | idxfrac |
    +--------+--------+---------+-----------+---------+------------+---------+
    | InnoDB |    280 | 1091816 | 1086.36MB | 84.28MB | 1170.64MB  |    0.08 |
    | MyISAM |      3 | 1860    | 59.53MB   | 9.47MB  | 69.00MB    |    0.16 |
    | MEMORY |      6 | 789     | 1.97MB    | 0.76MB  | 2.73MB     |    0.39 |
    +--------+--------+---------+-----------+---------+------------+---------+
    


    Only need to get a dedicated database server if
    1. memory usage for MySQL outgrows current single server
    2. MySQL concurrent connection loads outgrow a current single server <-- generally it's this one that decides
    3. disk space usage for MySQL outgrows a current single server
    And if you size your single server correctly, you won't outgrow it for a while so no need for a dedicated MySQL server.

    One of my client's Xenforo forums runs on a single VPS server with 50GB on disk database with
    Code (Text):
    +--------+--------+----------+------------+-----------+------------+---------+
    | engine | tables | rows     | data       | idx       | total_size | idxfrac |
    +--------+--------+----------+------------+-----------+------------+---------+
    | InnoDB |    209 | 31703843 | 12950.70MB | 2807.64MB | 15758.34MB |    0.22 |
    | MyISAM |      5 | 53823    | 15.68MB    | 4.14MB    | 19.83MB    |    0.26 |
    | MEMORY |      5 | 6954     | 5.99MB     | 4.25MB    | 10.24MB    |    0.71 |
    +--------+--------+----------+------------+-----------+------------+---------+
    


    Another client of mine with Xenforo on single dedicated server for web + MySQL with 53GB on disk database size with
    Code (Text):
    +--------+--------+----------+------------+-----------+------------+---------+
    | engine | tables | rows     | data       | idx       | total_size | idxfrac |
    +--------+--------+----------+------------+-----------+------------+---------+
    | InnoDB |    273 | 86404054 | 28644.61MB | 7052.80MB | 35697.41MB |    0.25 |
    | MyISAM |      7 | 18577211 | 617.29MB   | 541.22MB  | 1158.51MB  |    0.88 |
    | MEMORY |      4 | 17465    | 19.97MB    | 6.85MB    | 26.82MB    |    0.34 |
    +--------+--------+----------+------------+-----------+------------+---------+
    
     
    Last edited: Nov 14, 2020
  4. Colin

    Colin Premium Member Premium Member

    160
    44
    28
    Oct 7, 2015
    Sheffield UK
    Ratings:
    +121
    Local Time:
    5:31 PM
    1.19.#
    MariaDB 10.1.#
    Not XF, but IPB, same idea right ;)

    I have around 14Gb, in data. With the suppressed traffic levels I've kept php and mysql on the same box, but just within a 32Gb limit, a bit over my 75% soft cap.

    I think if I had to pick between a 32Gb vps and a 64Gb VPS, I'd break mysql out, but purely on costs. My host is pretty reliable, more so as the vps's have got bigger; fewer neighbours.
    A hosted cloud datastore, will cost more, a lot more, but does remove one headache, and add a few more.

    One thing to bear in mind though. Assume one box down for an hour, once a month and that's ok. But splitting, you now have the possibilty of two hours down + two instances to maintain. albeit with centminmod thats like one coffee break a day. Splitting stuff is only the begining of the Single Point Of Failure, SPOF headache. The cost to elimiate that second hour of downtime is rarely worth it... scale up till you can't.

    Many years ago I had an extended period of downtime, couple of days intermittent ups and downs. I eventually bit the bullet, pulled an all night transfer to my current host; 8+years I think. Traffic wise, it took around 6 months to get back to the levels. People simply found other things to do and the habit of the forum was lost. So downtime is important, but the odd server reboot/scaling event here and there is ok; for a forum.
     
  5. eva2000

    eva2000 Administrator Staff Member

    45,661
    10,360
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,079
    Local Time:
    3:31 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    +1 yeah you can only strive for eventual high availability with each step towards it costing more $$$ and more effort to accomplish. Though it's been a long standing strategy for scaling for performance, that the next step is to split web and MySQL database duties to separate servers.

    If it's uptime, then cheapest way is to have some form of maintenance mode in place which can be done on server i.e. if MySQL is down serve a site unavailable message via Nginx i.e. Beta Branch - sitestatus maintenance mode or you can do same from upstream proxy like Cloudflare. For instance with my Cloudflare Worker based advanced full HTML page caching at Cloudflare CDN level, I also bake in a 503 maintenance mode so I can switch a Cloudflare proxied/cached site to a static HTML maintenance page served originless at Cloudflare Edge server if the origin server(s) are unavailable.

    Much cheaper than scaling out high availability on multiple servers for the small downtime events - especially for a site like Centmin Mod which is on a lower budget. Only time scaling out and extra $$$ is justified is if cost of downtime outweighs the cost of scaling out i.e. adding/splitting servers.
     
    Last edited: Nov 15, 2020
  6. deltahf

    deltahf Premium Member Premium Member

    381
    176
    43
    Jun 8, 2014
    Ratings:
    +294
    Local Time:
    12:31 PM
    Good thread!

    My database sizes are 3.1 GB (WordPress, 7.7k articles, 195k comments) and 29.8 GB (XenForo, 13m posts). I have everything on one dedicated server.

    Naively, I did try to spin off my database server in 2012, only to realize that it was my application server that was the bottleneck and maintaining the two systems was way more of a headache than I needed. Since then, I've always just "kept it simple". I would rather maintain one powerful dedicated server that can handle all of my needs instead of managing several smaller VPSs. I might be able to save a bit of money going the multi-server route, though when I've checked this, it would end up costing me about the same by the time you add everything up.
     
  7. eva2000

    eva2000 Administrator Staff Member

    45,661
    10,360
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,079
    Local Time:
    3:31 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    That is also an important point for performance at least is to figure out where your bottleneck is first. If it's not MySQL, then splitting out to a dedicated MySQL server won't change anything.