[firebird-support] Generator Performance
[Hello, The firebird manual says that generators are thread-safe [1]. How does the firebird engine guarantee that two consecutive executions of a "SELECT NEXT VALUE FOR..." do not return the same value? Does it use a lock on the RDB$GENERATOR table? Does the call to "SELECT NEXT VALUE..." block, when another thread is reading/writing into it? Would that mean that having multiple clients connected and issuing these commands simultaneously degrade the performance of the database? My application case is the following: I want to append a column to some tables in my database representing an "update counter". Whenever an insert or update command is issued, this field will be incremented (globally for the whole database). [1] http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/generatorguide-basics.html -- Matheus Degiovani Gigatron Software e Treinamentos Ltda. (18) 3649-4045 MSN: math...@gigatron.com.br -- Críticas ou Sugestões? Ligue para Ouvidoria Gigatron: (18) 3649-4048 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Generator Performance
On Fri, Sep 23, 2011 at 2:18 PM, Matheus Degiovani wrote: > > The firebird manual says that generators are thread-safe [1]. How does the > firebird engine guarantee that two consecutive executions of a "SELECT > NEXT VALUE FOR..." do not return the same value? Generator values are stored on generator pages. In shared cache architectures (i.e. SuperServer) a connection that changes a generator value gets a transient write lock on the page, makes its change and releases the page. In a cache-per-connection architecture (e.g. super-classic, classic) a connection that changes a generator value reads the appropriate generator page, gets a transient write lock on the page, and makes its change. If another connection wants to read or change a generator value on that page, it requests a read lock on the page which causes the connection that holds the write lock to write the page and release its lock. The other connection reads the page from disk. In fact, the write is real, but the read will be satisfied from the operating system page cache. > Does it use a lock on the RDB$GENERATOR table? No. > Does the call to "SELECT NEXT VALUE..." block, when > another thread is reading/writing into it? Not exactly. See above. > Would that mean that having > multiple clients connected and issuing these commands simultaneously > degrade the performance of the database? > Yes, under classic, but not particularly SuperServer. This is true for any high contention page (e.g. header page, transaction inventory page, page inventory pages). > > My application case is the following: I want to append a column to some > tables in my database representing an "update counter". Whenever an insert > or update command is issued, this field will be incremented (globally for > the whole database). > Should work OK ... better or worse depending on the architecture. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Generator Performance
Hello Ann, > > Generator values are stored on generator pages. In shared cache > architectures (i.e. SuperServer) a connection that changes a generator > value > gets a transient write lock on the page, makes its change and releases > the > page. This "page" is the mapping of a set of table records into a memory/disk page, right? So, if I had a large number of generators, for workloads which accessed generators on different pages (say, generator ID 10 and generator ID 4000) there wouldn't be contention, right? Is there a way of finding out how many pages are used to store a given table? Or documentation about how the mapping from tables to memory and disk pages are implemented on firebird? Anyway, thanks for the info! Cya. -- Matheus Degiovani Gigatron Software e Treinamentos Ltda. (18) 3649-4045 MSN: math...@gigatron.com.br -- Críticas ou Sugestões? Ligue para Ouvidoria Gigatron: (18) 3649-4048 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Generator Performance
Hello Matheus, > > > Generator values are stored on generator pages. In shared cache > > architectures (i.e. SuperServer) a connection that changes a generator > > value > > gets a transient write lock on the page, makes its change and releases > > the > > page. > > This "page" is the mapping of a set of table records into a memory/disk > page, right? > Right. Everything stored in the database, data, metadata, and structural data, is stored on fixed sized pages. Pages come in different types: data, blob, index, generator, page inventory, transaction inventory, header, index root, and pointer. A generator page consists of a standard page header (~20 bytes) plus an array of 64 bit integers. Every page in the database is the same size, either 4, 8, or 16Kb. > > So, if I had a large number of generators, for workloads which accessed > generators on different pages (say, generator ID 10 and generator ID 4000) > there wouldn't be contention, right? > Depending on the page size, right. > > Is there a way of finding out how many pages are used to store a given > table? Or documentation about how the mapping from tables to memory and > disk pages are implemented on firebird? > Pretty simple. If you care about detail, look at ods.h for the size of the PAG structure and the exact layout of a generator page. For a rough approximation of the number of generators on a page, divide the page size by 8. Note that this algorithm works ONLY for generators which are not normal table data. Figuring out how many data records fit on a page is an advanced exercise. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Generator Performance
On 26/09/11 17:06, Ann Harrison wrote: >> Is there a way of finding out how many pages are used to store a given >> table? Or documentation about how the mapping from tables to memory and >> disk pages are implemented on firebird? Maybe http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-Internals.pdf might help, it's a work in [slow] progress though. :-( HTML version is on-line at http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/fb-internals.html. > Pretty simple. If you care about detail, look at ods.h for the size of the > PAG structure and the exact layout of a generator page. For a rough > approximation of the number of generators on a page, divide the page size by > 8. If I remember, (page-size-32)/8 gives the maximum number of sequences on a page. For a 4K page that's 507. There is also a maximum of 32,767 sequences per database. > Note that this algorithm works ONLY for generators which are not normal > table data. Figuring out how many data records fit on a page is an advanced > exercise. http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/fbint-page-5.html might help explain the difficulties - think Run Length Encoding and NULLs etc. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Generator Performance
Hello Matheus, >Is there a way of finding out how many pages are used to store a given table? Run gstat -r, there will be exact information - Data Pages: NNN CFG (149) Primary pointer page: 2736, Index root page: 2737 Average record length: 71.20, total records: 15549 Average version length: 10.54, total versions: 48, max versions: 16 Data pages: 875, data page slots: 875, average fill: 81% If you prefer convenient GUI interface, our IBAnalyst tool can show this and other information in more friendly way and even calculate tables/indices sizes in Mb. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Generator Performance
On Tue, Sep 27, 2011 at 3:39 AM, Alexey Kovyazin wrote: > > >Is there a way of finding out how many pages are used to store a given > table? > > Run gstat -r, there will be exact information Alexey is exactly right, but gstat reads every data page and counts the records and record versions on it, so don't bother reading the source to find an algorithm for estimating the number of pages a table will fill. Good luck, Ann > [Non-text portions of this message have been removed]
