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.tho...@yahoo.com [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
https://pasteboard.co/IGsU650A.png
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?
https://pasteboard.co/IGt93ec.png
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 config
DefaultDbCachePages = 6000K #96Gb (page size 16Kb)
FileSystemCacheThreshold = 20000K
TempCacheLimit = 10G #10Gb
TempBlockSize = 2M
LockMemSize = 116117248
LockHashSlots = 40099
OS config
Vm.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
