Re: [firebird-support] Re: Firebird and sharding ? - Email found in subject
Hi Norman, yes i understand, but now with my 50 millions rows table i start to meet the limit of firebird where a simple prepare can take around 1 s to 1 min dependantly the charge of the server (see my previous post). next year it's will be around 100 millions rows and i will have no solutions ... this why i start to thing about sharding in an easy way, in a way out in fact Hmm. I've not really heard of sharding as such, but what you propose as a solution to your huge table problem, may not be the best one. As an Oracle DBA, I work with tables holding hundreds of millions of rows. Now I'm not 100% sure what preparing a statement on Firebird should take so long on bigger tables and I can see how, with the present state of things, that that will be a problem for you. However, where I have these huge tables I can use Oracle Partitioning to split them up into logical units based on the value in (a) specific column(s) of the table. As long as this partitioning column is included in a query, then a full table scan turns into a scan of one or two partitions. Instead of searching hundreds of millions of rows, I search a few thousand instead. Obviously, that assumes that an index cannot be used for that particular query. However, the indexes can be partitioned to match the partitioning of the table, so an index scan is then reduced to a few partitions rather than a complete index lookup. Performance is far better when partitioned, and there's no need for cross database communications and synchronisation. Partitioning in Oracle is top-notch, definitely. Using that in a project as well, but needless to say, it's pricey, as you need Enterprise and partitioning is an additional option you have to pay separately. Performance-wise, it depends. It might get faster, but it can get slower as well. It depends on the query patterns. Usually partitioning helps if you query (a vast amount of) records, which can be read in parallel. Beside performance, we really like the fact, that largish table and index data can be administrated more efficiently, when it comes to e.g. rebuilding an partitioned index. A smaller index usually rebuilds faster than a larger one. Needless to say that purging/removing data from an entire partition is not a DELETE on the largish table, but simply an operation on the physical partition. But talking to an experienced Oracle DBA, that's nothing new. ;-) While one could dream having something similar in Firebird, I'm not sure if it currently makes sense at all, as long as one can't configure the underlaying physical location (aka tablespace in Oracle) of database objects on different disks etc. But I'm in favour of Firebird's simplicity than adding more and more Enterprise-level stuff. Managing largish tables in Firebird can be tricky/annoying though. E.g. the need for an exclusiv lock on the table when managing indexes etc. ;-) Just my €0.02. -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ Do you care about the future of Firebird? Join the Firebird Foundation: http://www.firebirdsql.org/en/firebird-foundation/ ++ 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] Arithmetic overflow or division by zero has occurred.
SELECT cast((5411.000455/0.20) as numeric(16,6)) as mount FROM MON$ATTACHMENTS r This particular query can be rewritten as SELECT 5411.000455*cast(1/0.20 as integer) as mount FROM MON$ATTACHMENTS r Though it will not work equally well with other numbers, at least not if cast(1/0.xx as numeric(16, 4)) doesn't yield sufficient precision when doing 5411.000455*ResultFromAbove. HTH, Set
Re: [firebird-support] Identifying damanged pages
Hello Josef, Look into firebird.log - there should be information about corrupted record/table. You can also check your database with FBFirstAID Diagnostician. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) Hi! Due to hardware malfunction, my database got damaged. GFIX (-v -f -i) tells me that a database has 1 record level error, 9 data page errors, 41 index page errors and 322 database page errors. I am not concerned about Index page errors - is seems obvious these will get fixed by a backup/restore cycle. But I would like to know: 1) What do the individual error types mean? 2) Is there a way to identify which tables were affected? I can live with damage to many tables, but not all of them. Thanks, Josef Kokes ++ 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 ++ 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] Re: Firebird and sharding ? - Email found in subject
Norm, yes i understand, but now with my 50 millions rows table i start to meet the limit of firebird where a simple prepare can take around 1 s to 1 min dependantly the charge of the server (see my previous post). next year it's will be around 100 millions rows and i will have no solutions ... this why i start to thing about sharding in an easy way, in a way out in fact Now I'm not 100% sure what preparing a statement on Firebird should take so long on bigger tables and I can see how, with the present state of things, that that will be a problem for you. When Norm says he isn't quite sure about something, I have to assume that lots of people are also in the dark. The performance problem in preparing queries comes from the algorithm Firebird uses to estimate the cardinality (number of records) of a table. In deciding how to execute a query, Firebird considers the cardinality of each table involved and the selectivity of each index that could be used. Firebird keeps the selectivity in the index system table, updating it when the index is recreated or when somebody says set selectivity. However, the cardinality is computed for each query. (That's not as odd as it seems. The distribution of key values is unlikely to change (much) after the initial batch of data is stored, but the number of record in a table changes often. Jim and I had a bit of experience with a database that actually stored the number of records in a table in its system tables - horrible hot spot that consumed a significant fraction of the cpu time.) To understand how Firebird calculates the approximate cardinality of a table, you need to understand a little bit about how records are found. The system table RDB$PAGES contains records that give the page number for pointer pages for a table. The pointer page contains an array of data page numbers. To find a record, Firebird first decomposes the record number into three values: 1) the ordinal position of the pointer page in RDB$PAGES (think select page_number from rdb$pages where table = my table and type = 'Pointer Page' and position = 1, then the same with position = 2, etc.) 2) the offset in the array of data pages on that pointer page. 3) is the index into an array of offset/length pairs on the data page that locate the actual record. OK? Read RDB$PAGES to find pointer page, index into pointer page to find data page, index into data page to find offset and length of record. It was clear, even those early days in the dark ages of computing that counting the actual records to get the cardinality would be a disaster. But, the number of data pages gives a pretty good approximation. Divide the page size by an approximation of the record size to get the number of records per page, then multiply that by the number of data pages and there's your estimated cardinality. Back then, disks were expensive and tables were small, so a big table might have three or four pointer pages, each pointing to about 120 data pages. At that size, knowing whether a pointer page is full (~124 pages on a 1K page ... remember this was a long time ago) or just started and containing only one data page. So actually reading the pointer pages was important. That makes some sense when you've got maybe as many as a dozen pointer page. With 50 million records, you've got more than a thousand pointer pages. Reading all of them takes time, and probably isn't all that much more accurate than just estimating the number of data pages based on the number of pointer pages, just as it now estimates the number of records based on the number of data pages. I have no idea how V3 handles the estimate of cardinality, but one way to reduce the cost for large tables is to read the pointer pages only if there are relatively few of them, and for large tables guess based on the number of entries in RDB$PAGES. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Arithmetic overflow or division by zero has occurred.
That operation does not give the precision that I need ( 6 digits after decimal point ) SELECT 54311.999455*cast((1/1.01) as integer) as mount FROM MON$ATTACHMENTS r = 54311.999455 54311.999455 / 1.01 = 54311,945143 Thank you for everything = || ISMAEL || = - Original Message - From: Svein Erling Tysvær To: 'firebird-support@yahoogroups.com' Sent: Thursday, March 29, 2012 3:24 AM Subject: RE: [firebird-support] Arithmetic overflow or division by zero has occurred. SELECT cast((5411.000455/0.20) as numeric(16,6)) as mount FROM MON$ATTACHMENTS r This particular query can be rewritten as SELECT 5411.000455*cast(1/0.20 as integer) as mount FROM MON$ATTACHMENTS r Though it will not work equally well with other numbers, at least not if cast(1/0.xx as numeric(16, 4)) doesn't yield sufficient precision when doing 5411.000455*ResultFromAbove. HTH, Set [Non-text portions of this message have been removed]
