Hello!

This is a relatively long post so please be patient with my English.

I´ve been trying to figure out what the values of mon$page_reads and
mon$page_fetches represent in practical terms but I´m having a hard
time.
Hopefully with the following example I will be able to explain to you
why it does not seem to make sense.

The test was made using FB 2.1 RC2 on WinXP 32bit and isql.
Example follows.

Thanks in advance,
Douglas

-- Create a new database with a simple table and some data:

SQL> create database "c:\test.fdb";
SQL> commit;
SQL> create table test (i integer primary key);
SQL> commit;
SQL> insert into test(i) values (1);
SQL> insert into test(i) values (2);
SQL> insert into test(i) values (3);
SQL> insert into test(i) values (4);
SQL> insert into test(i) values (5);
SQL> insert into test(i) values (6);
SQL> insert into test(i) values (7);
SQL> insert into test(i) values (8);
SQL> insert into test(i) values (9);
SQL> insert into test(i) values (0);
SQL> commit;
SQL> quit;

-- Restart Firebird server to make sure it releases its page cache.
-- Reconnect to the database and check the results for the monitoring
tables:

SQL> connect "c:\test.fdb";
SQL> select mon$page_reads, mon$page_fetches from mon$io_stats where
mon$stat_group=0;
MON$PAGE_READS MON$PAGE_FETCHES
============== ================
48 634

-- This is the result just after attaching to the database.
-- It serves as reference.
-- The next select will read the entire table "test" from disk.
-- Then commit the transaction and take a new snapshot of the monitor
table.

SQL> select * from test;
SQL> commit;
SQL> select mon$page_reads, mon$page_fetches from mon$io_stats where
mon$stat_group=0;
MON$PAGE_READS MON$PAGE_FETCHES
============== ================
58 732

-- A total of 10 pages were read from disk and another 98 from memory.
-- Is that correct?
-- Now selecting all from table "test" again will read it from the
cache.

SQL> select * from test;
SQL> commit;
SQL> select mon$page_reads, mon$page_fetches from mon$io_stats where
mon$stat_group=0;
MON$PAGE_READS MON$PAGE_FETCHES
============== ================
58 761

-- Only 29 pages were read from memory.
-- Is that correct? Why so many memory reads on the previous select?
-- I tried the same test on a bigger database and the results are
really strange:

SQL> connect "e:\db.fdb";
SQL> select mon$page_reads, mon$page_fetches from mon$io_stats where
mon$stat_group=0;
MON$PAGE_READS MON$PAGE_FETCHES
============== ================
44 1140

SQL> select count(*) from bigtable;
COUNT
=======
825778

SQL> commit;
SQL> select mon$page_reads, mon$page_fetches from mon$io_stats where
mon$stat_group=0;
MON$PAGE_READS MON$PAGE_FETCHES
============== ================
13405 1679681

-- The page_reads value seems to be OK with the size of the table,
but 1.6 milion page fetches? Is that correct?
-- This DB is 1.8GB in total size. Just restored.
-- Bigtable is 156bytes wide. Page size is 8KB. Page buffers if 1024.