Rodrigo Farcas
Active member
Can anyone point out which is the best configuration for mysqld running big XF boards?
mysqld recommended configuration for XF ?
- Thread starter Rodrigo Farcas
- Start date
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.
As a general rule of thumb, if your tables are innodb (which they should be).
These 4 lines are pretty much standard. Obviously alter the buffer pool to whatever you want to give it.
Code:
# INNODB #
innodb-flush-method = O_DIRECT
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 8GThese 4 lines are pretty much standard. Obviously alter the buffer pool to whatever you want to give it.
Rodrigo Farcas
Active member
Thanks... this is for a big big board, running on AWS
Floren
Well-known member
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
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)
Last edited:
Not really.
Is this "proper" calcuation documented anywhere as being "proper" or just your opinion/reccomendation?
p4guru
Well-known member
It's an informed opinion... InnoDB buffer pool < InnoDB data size != Good
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.
Not really.
/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
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%.
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%.
I just stick it at 8gb on my box
Why, just cos.
Mike Edge
Well-known member
I just stick it at 8gb on my box
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.
AndyB
Well-known member
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)".
Mouth
Well-known member
Similar threads
