| Subject | Re: [firebird-support] Re: High write access on disk |
|---|---|
| Author | Alexey Kovyazin |
| Post date | 2019-11-13T09:16:03Z |
Hello Thomas,
My advice was only to fix the obvious mistake regarding the TempCacheLimit.
In general, I don't see a problem in high traffic to /tmp, and I don't understand why do you think sorting queries produce the problem - and even if they produce, the first attempt would be to identify such queries with TempSpaceLogThreshold (it works in HQbird only) and then fix them with index/disabling index, restructuring it, etc.
As another short advice:
1) I need to say that tuning of 2.5 SuperClassic and 3.0 SuperClassic is different, and, at least, I would recommend to a) put DefaultDBCachePages to databases.conf b) allocate 25% of RAM at the first step, then increase it by 10-20%.
2) Migration 2.5 - > 3.0 requires at least clean metadata (isql -x > metadata.sql on 2.5, isql -i metadata.sql on 3.0 should not give errors), I hope you tested it, and, also, 3.0.4 have different optimization, so some queries can start to work slower, and thorough testing is required.
Regards,
Alexey Kovyazin
IBSurgeon
On 13.11.2019 11:56, kragh.thomas@... [firebird-support] wrote:
My advice was only to fix the obvious mistake regarding the TempCacheLimit.
In general, I don't see a problem in high traffic to /tmp, and I don't understand why do you think sorting queries produce the problem - and even if they produce, the first attempt would be to identify such queries with TempSpaceLogThreshold (it works in HQbird only) and then fix them with index/disabling index, restructuring it, etc.
As another short advice:
1) I need to say that tuning of 2.5 SuperClassic and 3.0 SuperClassic is different, and, at least, I would recommend to a) put DefaultDBCachePages to databases.conf b) allocate 25% of RAM at the first step, then increase it by 10-20%.
2) Migration 2.5 - > 3.0 requires at least clean metadata (isql -x > metadata.sql on 2.5, isql -i metadata.sql on 3.0 should not give errors), I hope you tested it, and, also, 3.0.4 have different optimization, so some queries can start to work slower, and thorough testing is required.
Regards,
Alexey Kovyazin
IBSurgeon
On 13.11.2019 11:56, kragh.thomas@... [firebird-support] wrote:
Hey Alexey
Thanks for the advise, I changed TempCacheLimit to 2100000000, last night and it looks like it had a positive effect on the disk queue size on sda. I have read a lot about Firebird tuning however I have never come across that limit on fb 2.5
The green line is when I updated the config. The spikes around midnight is due to backup/restore. The queue size has gone from around 1 to 0,1-0,2 during office hours.
However the amount of wirte access to sda, did not change after the config change. Perhaps the sensor that we use monitors the writes Firebird makes when firebird sorts response?
To accommodate further growth in users I am planning an upgrade to Firebird 3.0.4(SuperServer) this weekend. The server will be the same, however with a ram upgrade to 192Gb. I am planning to use the following config, do you or anyone else see any problems?
Firebird configDefaultDbCachePages = 6000K #96Gb (page size 16Kb)
FileSystemCacheThreshold = 20000K
TempCacheLimit = 10G #10Gb
TempBlockSize = 2M
LockMemSize = 116117248
LockHashSlots = 40099
OS configVm.pagecache = 30
Vm.swappiness = 10
vm.min_free_kbytes = 1048576
vm.max_map_count=250000
fs.file-max=2097152
net.core.somaxconn = 4096
net.core.netdev_max_backlog = 65536
net.core.optmem_max = 25165824
Thomas Kragh