mysqld recommended configuration for XF ?

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.
 
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
 
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"?
 
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:
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.
 
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.
 
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.
 
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.
 
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.
 
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
 
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.
 
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 Bottom