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

Slavik

XenForo moderator
Staff member
#3
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.
 
#4
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
#5
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
#6
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
#8
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
#9
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
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
#11
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
#12
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
#13
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
#14
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
#17
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)".