[firebird-support] Re: Firebird and sharding ? - Email found in subject
Ann, thanks you so much for such good explanation ! can not wait more for the V3, hope soom the first beta will be ready
Re: [firebird-support] Re: Firebird and sharding ? - Email found in subject
On Fri, 30 Mar 2012 06:40:39 -, "nathanelrick" wrote: > http://www.addsimplicity.com/downloads/eBaySDForum2006-11-29.pdf > > No business logic in database > no stored procedure > only very simple triggers (default population) > > Move CPU intensive work to applications > Referential integrity > joins > sorting Which doesn't really work when a database is used by different systems. > Extensive use of prepared statements and variables > > and yes you are right they move their C++/ISAPI to JAVA :) by the way that > a curious choice ... Why would that be a curious choice? Mark
[firebird-support] Re: Firebird and sharding ? - Email found in subject
> I think you also mentioned that Ebay doesn't allow joins and uses the > application to do the joining. If that's the case, I'm glad I don't work > for Ebay. I'll be willing to bet that the Ebay application is written in > Java - that sounds like the sort of thing that the Java developers (and > vendors) I come into contact always do, treat the database as a bit > bucket and reinvent the wheel - caching results, joins, referential > integrity, check constraints etc. http://www.addsimplicity.com/downloads/eBaySDForum2006-11-29.pdf No business logic in database no stored procedure only very simple triggers (default population) Move CPU intensive work to applications Referential integrity joins sorting Extensive use of prepared statements and variables and yes you are right they move their C++/ISAPI to JAVA :) by the way that a curious choice ...
Re: [firebird-support] Arithmetic overflow or division by zero has occurred.
Perfect 1 million of thanks = || ISMAEL || = - Original Message - From: Huan Ruan To: firebird-support@yahoogroups.com Sent: Thursday, March 29, 2012 5:13 PM Subject: Re: [firebird-support] Arithmetic overflow or division by zero has occurred. On 30 March 2012 03:21, Ismael L. Donis Garcia wrote: > ** > > > 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 > Cast one of the numbers in your calculation as "double precision", e.g. SELECT cast(cast(54311.999455 as double precision)/1.01 as numeric(16,6)) as mount FROM rdb$database Cheers Huan [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
Re: [firebird-support] Arithmetic overflow or division by zero has occurred.
On 30 March 2012 03:21, Ismael L. Donis Garcia wrote: > ** > > > 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 > Cast one of the numbers in your calculation as "double precision", e.g. SELECT cast(cast(54311.999455 as double precision)/1.01 as numeric(16,6)) as mount FROM rdb$database Cheers Huan [Non-text portions of this message have been removed]
Re: [firebird-support] Primary key with negative value
On 29/03/2012 14:25, jakefeed wrote: > > We have an database in which we supply product catalogs with pricing. > The end user can create their own items, too. In order to separate the > user's items from the items we supply, we're contemplating using > negative primary keys for our items and positive primary keys for the > users items. We are using Firebird 2.5.1. Are there any known issues > using negative primary key values in this configuration. Thank you. > If this is your database (?) if so, its a lot easier to add a new field which you can control with a domain such as a YES/NO on a field called Our_Product_YN. The default of the domain could be 'Y' or 'N' according to your needs. That way you just have one PK on Product_No and Our_Product_YN. Much simpler to maintain. HTH Alan [Non-text portions of this message have been removed]
Re: [firebird-support] Primary key with negative value
> We have an database in which we supply product catalogs with pricing. The > end user can create their own items, too. In order to separate the user's > items from the items we supply, we're contemplating using negative primary > keys for our items and positive primary keys for the users items. We are > using Firebird 2.5.1. Are there any known issues using negative primary key > values in this configuration. Thank you. Not from a numeric POV, but I wonder what method you use to assign the primary key values? One generator per table might be hard in that scenario. -- 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/
Re: [firebird-support] Primary key with negative value
On Thu, Mar 29, 2012 at 9:25 AM, jakefeed wrote: > We have an database in which we supply product catalogs with pricing. The > end user can create their own items, too. In order to separate the user's > items from the items we supply, we're contemplating using negative primary > keys for our items and positive primary keys for the users items. We are > using Firebird 2.5.1. Are there any known issues using negative primary > key values in this configuration. Thank you. > No, I don't know of any issues with negative primary keys. The solution makes me queasy though. Maybe a compound key that clearly identifies things as theirs and ours? Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Primary key with negative value
We have an database in which we supply product catalogs with pricing. The end user can create their own items, too. In order to separate the user's items from the items we supply, we're contemplating using negative primary keys for our items and positive primary keys for the users items. We are using Firebird 2.5.1. Are there any known issues using negative primary key values in this configuration. Thank you.
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]
Re: [firebird-support] Identifying damanged pages
2012/3/29 Josef Koke¹ > > 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". > IBFirstAid is your friend, as is backup. But before you backup the database, make a copy, use gfix to set it to read only, and back it up with gbak, using the -g switch to suppress garbage collection. If that works, you're unlikely to have lost data. Backup walks each table in storage order. The structures are linked, so a lost or damaged data page will show up. Good luck, Ann [Non-text portions of this message have been removed] ++ 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 = 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] 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/
[firebird-support] Identifying damanged pages
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 <*> 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
Morning Thomas, > 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. This much is true, and unfortunate. I think Oracle work on the principle of getting as much out of you as possible, by up front means, or sneaky back door ones. :-( > 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. We don't tend to use parallelism, but if the partitioning (or indeed sub-partitioning) is on a particular column and that column is included in the query, then the performance is much better. Assuming that the partitioning is correct of course. > 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. Aye, but beware, DELETE is protected by UNDO as it is DML. Dropping partitions is DDL and isn't. That's why it's faster. > 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. Me too. I love Firebird. But I'm not sure about the merits of spreading data over partiotions that are thmeselves spread over different "spindles" in todays environment. All the databases I work with are on EVAs or NAS storage and those have arrays of spindles. All our databases appear to have their files in one directory, but that's actually spread over hundreds of spindles in the array. > 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. ;-) True, but Oracle had the same problem until recently when the ONLINE option for index rebuilds came about. Now it takes a lock at the start and at the end of the process, and releases it in between. Any index updates are applied from REDO after the initial build has finished. Anyway, I think we are staying too far from the topic now, best we quieten down (or go private) before Helen sees what we are up to! ;-) 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 ++ 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] 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/
