mysqld recommended configuration for XF ?

MattW

Well-known member
Can anyone point out which is the best configuration for mysqld running big XF boards?
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.
 

Slavik

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


Code:
# 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.
 

Rodrigo Farcas

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


Code:
# 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.
Thanks... this is for a big big board, running on AWS
 

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"?
 

Floren

Well-known member
innodb_buffer_pool_size = 8G
Obviously alter the buffer pool to whatever you want to give it.
Not really. :) I use this formula to determine the proper pool size in GB:
Code:
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:
Code:
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:

p4guru

Well-known member
Is this "proper" calcuation documented anywhere as being "proper" or just your opinion/reccomendation?
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.
 

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.
 

rogerl

Member
Not really. :) I use this formula to determine the proper pool size in GB:
/snip/
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:

Welcome to the crazy world of MySQL optimizations. :)
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.
 

Floren

Well-known member
Is this "proper" calculation documented anywhere as being "proper" or just your opinion/recommendation?
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.
 

Slavik

XenForo moderator
Staff 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.

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.
 

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
 

Slavik

XenForo moderator
Staff 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

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

AndyB

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


Code:
# 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.
According to this:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_method

innodb-flush-method = O_DIRECT

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