Re: [firebird-support] Re: High write access on disk
14.11.2019 12:43, kragh.tho...@yahoo.com [firebird-support] wrote: > Just to be sure, /tmp should be mounted with tmpfs, like in this archicle? Yes and add some automatic swap management like here: https://unix.stackexchange.com/questions/134258/dynamically-growing-swap-file-on-debian -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: High write access on disk
13.11.2019 21:06, kragh.tho...@yahoo.com [firebird-support] wrote: > Just to clarify, one could make a RAM drive with tmpfs, eg /ramdrive, and > then specify > this path in firebird.config under "TempDirectories"? Not quite. > If correctly understood, does this provide any benefits over just allocation > more memory > for TempCacheLimit, under Firebird 3? It is not clear what exactly caused high I/O in your case. /tmp is used by default not only for sorting files and temporary tables but also for a lot of other things. Making it RAM drive is such a generic approach that can improve performance without further detailed investigation. You can change TempDirectories and see if high I/O spot will be moved accordingly or stay in /tmp. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: High write access on disk
Hello, For predictable load reads/fetches is very useful metric, but in case of spikes like Thomas has - 96 connections to 1300, it will be far less useful. Regards, Alexey On 13.11.2019 14:50, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 13.11.2019 12:41, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: >> we also started with Page Buffers = 25% RAM and increased it step by step >> (and still >> continue to work with fine tuning). > Isn't it better to use cache hit ratio to make the decision about its > growth? > > ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: High write access on disk
Hello, Yes, better use default - pagecache in terms of Linux is different from Page Buffers/Page cache of Firebird. Regards, Alexey On 13.11.2019 15:10, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Alexey Thanks for the answer, I only have one question left, do I need to adjust linux page cache(vm.pagecache) according to amount of RAM allocated for DefaultDBCachePages, so that memory is not over "subscribed"? Eg. if DefaultDBCachePages is increased should i then decrease vm.pagecache? Or should I use vm.pagecache default values and have Linux work it out?
Re: [firebird-support] Re: High write access on disk
13.11.2019 12:41, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: > we also started with Page Buffers = 25% RAM and increased it step by step > (and still > continue to work with fine tuning). Isn't it better to use cache hit ratio to make the decision about its growth? -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: High write access on disk
Hello Thomas, Ok, good to know that you are prepared for the migration! FB3 with proper migration and config certainly will increase the overall performance. In my presentation regarding Linux I spoke about specific case which is under everyday control, and in our long way, we also started with Page Buffers = 25% RAM and increased it step by step (and still continue to work with fine tuning). Also, there we have 320Gb and smooth predictable increase of connections during the day, and you have much more intensive spikes, so, better be more conservative - if you will see no problems with 25% of RAM, increase to 40-50-70, week by week. Regards, Alexey On 13.11.2019 13:59, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Alexey Its not that I see high traffic to /tmp as a problem, I was just worried that it was becoming a bottleneck in our system. Great to know that HQbird has a way of tracking these qureys, I have already been looking into HQbird especially because of prepared statements. Regarding the upgrade to Fb 3 SuperServer, a complete restore of production database to Fb 3 have already been made in our dev envioment last week without problems, and for almost a year dev, test and preprod have been running Fb 3, so i am quite comfortable in that regard. We have also uses FB TraceManager to weed out bad queries, and procedures and triggers recompile without errors. My only concern is that I somehow configures Fb 3 in a way that results in worse performance that our current 2.5 installation. The server is a dedicated Firebird server with a single database, does this change your recommendation to allocate 25% RAM for DefaultDBCachePages? My initial estimate was based on your presentation at this years Firebird conference(20_tuninglinux.pdf page 28) Thomas Kragh
Re: [firebird-support] Re: High write access on disk
13.11.2019 11:59, kragh.tho...@yahoo.com [firebird-support] wrote: > Its not that I see high traffic to /tmp as a problem, I was just worried that > it was > becoming a bottleneck in our system. In this case you can use for it tempfs backed up with auto-expanded swap files. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: High write access on disk
Hey Alexey Its not that I see high traffic to /tmp as a problem, I was just worried that it was becoming a bottleneck in our system. Great to know that HQbird has a way of tracking these qureys, I have already been looking into HQbird especially because of prepared statements. Regarding the upgrade to Fb 3 SuperServer, a complete restore of production database to Fb 3 have already been made in our dev envioment last week without problems, and for almost a year dev, test and preprod have been running Fb 3, so i am quite comfortable in that regard. We have also uses FB TraceManager to weed out bad queries, and procedures and triggers recompile without errors. My only concern is that I somehow configures Fb 3 in a way that results in worse performance that our current 2.5 installation. The server is a dedicated Firebird server with a single database, does this change your recommendation to allocate 25% RAM for DefaultDBCachePages? My initial estimate was based on your presentation at this years Firebird conference(20_tuninglinux.pdf page 28) Thomas Kragh
Re: [firebird-support] Re: High write access on disk
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 21, 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 = 2K 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=25 fs.file-max=2097152 net.core.somaxconn = 4096 net.core.netdev_max_backlog = 65536 net.core.optmem_max = 25165824 Thomas Kragh
