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

mysqld recommended configuration for XF ?

Discussion in 'Server Configuration and Hosting' started by Rodrigo Farcas, Aug 26, 2013.

  1. Rodrigo Farcas

    Rodrigo Farcas Active Member

    Can anyone point out which is the best configuration for mysqld running big XF boards?
  2. MattW

    MattW Well-Known Member

    It's totally dependent on the size of your board, and the hardware you are running. What works for me, probably won't work for you unfortunately.
  3. Slavik

    Slavik XenForo Moderator Staff Member

    As a general rule of thumb, if your tables are innodb (which they should be).

    # INNODB #
    innodb-flush-method  = O_DIRECT
    innodb-flush-log-at-trx-commit = 2
    innodb-file-per-table  = 1
    innodb-buffer-pool-size  = 8G

    These 4 lines are pretty much standard. Obviously alter the buffer pool to whatever you want to give it.
  4. Rodrigo Farcas

    Rodrigo Farcas Active Member

    Thanks... this is for a big big board, running on AWS
  5. MattW

    MattW Well-Known Member

    What version of MySQL are you running? Have you switched to either of the performance branches (Percona or MariaDB)?

    What resources are available (RAM etc) and how large is a "big big board"?
  6. Floren

    Floren Well-Known Member

    Not really. :) I use this formula to determine the proper pool size in GB:
    mysql> SELECT CEILING(data * 1.5 / POWER(1024, 3)) AS pool_size
           FROM (
               SELECT SUM(data_length + index_length) AS data
               FROM information_schema.tables WHERE engine = 'InnoDB'
           ) AS result;
    | pool_size |
    |         4 |
    1 row in set (0.02 sec)
    innodb_buffer_pool_size = 4G
    Edit: The formula calculates the actual size and increases the result by 50%. Obviously you should run this formula at regular intervals, as your lengths increase constantly. You can verify the actual data stored, with this query:
    mysql> SELECT (data * size) / POWER(1024, 3) AS pool_size
           FROM (
                   SELECT variable_value AS data
                   FROM information_schema.global_status
                   WHERE variable_name = 'Innodb_buffer_pool_pages_data'
           ) AS pages_data,
                   SELECT variable_value AS size
                   FROM information_schema.global_status
                   WHERE variable_name = 'Innodb_page_size'
           ) AS page_size;
    | pool_size          |
    | 1.5499334716796875 |
    1 row in set (0.03 sec)
    Welcome to the crazy world of MySQL optimizations. :)
    Last edited: Aug 27, 2013
    Da Bookie Mon, p4guru and MattW like this.
  7. Slavik

    Slavik XenForo Moderator Staff Member

    Is this "proper" calcuation documented anywhere as being "proper" or just your opinion/reccomendation?
    Tracy Perry likes this.
  8. p4guru

    p4guru Well-Known Member

    It's an informed opinion... InnoDB buffer pool < InnoDB data size != Good :D

    InnoDB data usually has an additional 10-15% overhead to add to calculation for proper InnoDB buffer pool calculation. So that extra 35% is is just an opinion.

    Folks can recommend anywhere between 25-200% more than size of InnoDB data. so best to properly monitor InnoDB stats on a regular basis to understand your InnoDB buffer pool and data requirements.
  9. MattW

    MattW Well-Known Member

    I'm glad I just checked mine, as it's grown quite a bit over the last few months, and the pool was at 1.7GB, and I'd only got 2GB allocated from when I set the VPS up a few months back.
  10. rogerl

    rogerl Member

    That would be great but it tells me to allocate 34GB of RAM in my server with 16GB of RAM :(

    I do agree though, allocate as much as you can to the InnoDB buffer pool.
  11. Floren

    Floren Well-Known Member

    MySQL is pretty much like Nginx, there is no exact science that you set a value and let it die like that for decades. Tuning and maintaining MySQL is a constant ongoing and dynamic process, which explains why sites with large databases often require expensive consulting or a DBA position.
    p4guru likes this.
  12. Slavik

    Slavik XenForo Moderator Staff Member

    Well you can have fun with that :) To be honest such, "constant" adjustment is hard to justify for most sites. When setting a nice big buffer pool that will last them years is much easier.
  13. Floren

    Floren Well-Known Member

    It depends, it is not recommended to have a large buffer. In fact, Peter Zaitsev recommends to have the buffer 10% higher than actual data and monitor it constantly. Obviously, Percona people know what they are doing... but since I'm lazy, I set it to 50%. :D
  14. Slavik

    Slavik XenForo Moderator Staff Member

    I just stick it at 8gb on my box :) Why, just cos.
  15. Da Bookie Mon

    Da Bookie Mon Well-Known Member

    Too high over the needed buffer can effect performance though too.. I run that weekly too, though I use 20% higher rather then the 50% Floren does.
    Floren likes this.
  16. Slavik

    Slavik XenForo Moderator Staff Member

    When it becomes a problem, ill let you know ;)
  17. AndyB

    AndyB Well-Known Member

    According to this:


    innodb-flush-method = O_DIRECT

    is only "(available on some GNU/Linux versions, FreeBSD, and Solaris)".
  18. Mouth

    Mouth Well-Known Member

Share This Page