(This post is intended to be read with a fixed font.) Actually my previous figures where wrong - I had forgotten that forced writes is OFF by default for newly created databases on Linux. However,that doesn't invalidate the main reason why I started looking at this issue. I was trying to understand the relationship between firebird page size and disc i/o. I first looked at a production database (with FW=ON) and the statistics just didn't make sense. I've now done more extensive tests with a dedicated test database and the figures still don't make sense.
All tests are run from a freshly restored backup containing 100,000 rows in the test table. Forced Writes are ON and hard drive disc caches are disabled. Firebird 2.1.4 is the server. My test rig has a stored procedure that randomly updates 100,000 rows in a simple table. The update typically increase database size by around 20% - ie, new pages are definitely created and all existing pages are modified. This proc is called by isql with SET STATS ON. The isql script then commits and exits. The exit executes a db disconnect trigger which captures the mon$ I/O stats for the attachment and writes it out to a table that collects persistent i/o stats. The isql script then reconnects and does a select from this table. There is only one row. So, from this simple script I am capturing the page writes for the statement using the perf.h api and the MON$IO_STATS for page writes at the attachment level. The attachment only executes a single statement so the difference between page writes derived from the perf.h api and the mon$io page writes should be statistically insignificant, but in all cases the mon$io stat should be slightly larger due to the extra book-keeping for closing the isql txns. The figures do not match up. Here are the results: PLATFORM PAGE_SIZE PERF PAGE_WRI MON$IO PAGE_WRI ========= ========= ============= =============== Linux x64 4096 70,139 33,495 W2K3 x64 4096 69,948 33,138 Linux x64 8192 40,710 41,820 W2K3 x64 8192 43,945 44,118 Linux x64 16384 35,912 36,456 W2K3 x64 16384 39,056 38,874 We can see that the figures for Windows and Linux are broadly similar. 1/ Why are the perf page writes double the mon$io page writes for the 4K page size? 2/ Why is the perf page writes value slightly greater than the mon$io page writes for the 16K page size under windows? 3/ Why are the mon$io page writes greater for 8K than 4K? After running these tests I then took a look at the MON$IO_STATS for the last database run (Windows 16K page size). One would expect to see the page write stats for stat_group 0 to be around 39,000. However, I'm only seeing 49 page_writes. Does anyone know why these inconsistencies exist? Paul -- Paul Reeves http://www.ibphoenix.com Specialists in Firebird support ------------------------------------------------------------------------------ All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2d-c2 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
