[firebird-support] Insert into Large Table is slowly!
[firebird-support] RE: Today#39;s performance question - index direction
[firebird-support] RE: Insert into Large Table is slowly!
RES: [firebird-support] RE: Insert into Large Table is slowly!
Do you use Classic or SuperServer? Version? How many cached pages? If you use Classic try increasing page buffers to 2000 and try again. Let me know the results, thanks. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de chmere...@gmail.com Enviada em: quinta-feira, 5 de setembro de 2013 11:58 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] RE: Insert into Large Table is slowly! --- In firebird-support@yahoogroups.com, firebird-support@yahoogroups.com wrote: Hello, every body! I have a 180GB database. and have a large table in it. table's DDL is: Create Table Table1 ( f_MeterID Integer, f_CMDID smallInt, f_Time timestamp, f_Value Numric(12,2), f_RecTime Timestamp ); Create Index idx_Table1 on(f_MeterID, f_CMDID, f_Time) about 50,000 rows be inserted every hour. about 1.2 millions rows be inserted every day. about 500 millions rows every year. CREATE GLOBAL TEMPORARY TableTemp ( f_MeterID Integer, ) ON COMMIT DELETE ROWS; ALTE R INDEX idx_Table1 INACTIVE; I insert data use follow: 1. start a transaction 2. Insert a batch records into temp table TableTemp, It's structure like table1 3. Insert into Table1 select * from TableTemp Delete Data From TableTemp (about 30,000 record every tiime) USE GLOBAL TEMPORARY 4. commit transaction ALTER INDEX idx_Table1 ACTIVE but now, insert data become very slowly. What should I do? thanks! jimmy Regards.
Re: [firebird-support] Insert into Large Table is slowly!
On Thu, Sep 5, 2013 at 3:50 AM, jym...@gmail.com wrote: I insert data use follow: 1. start a transaction 2. Insert a batch records into temp table TableTemp, It's structure like table1 3. Insert into Table1 select * from TableTemp Delete Data From TableTemp (about 30,000 record every tiime) 4. commit transaction but now, insert data become very slowly. Maybe I've missed something about temporary tables, but it looks to me as if you're doing two inserts and a delete per record. Why not just put the records into the real table to start with? Good luck, Ann
RE: [firebird-support] Insert into Large Table is slowly!
Hello, every body! I have a 180GB database. and have a large table in it. table's DDL is: Create Table Table1 ( f_MeterID Integer, f_CMDID smallInt, f_Time timestamp, f_Value Numric(12,2), f_RecTime Timestamp ); Create Index idx_Table1 on(f_MeterID, f_CMDID, f_Time) about 50,000 rows be inserted every hour. about 1.2 millions rows be inserted every day. about 500 millions rows every year. I insert data use follow: 1. start a transaction 2. Insert a batch records into temp table TableTemp, It's structure like table1 3. Insert into Table1 select * from TableTemp Delete Data From TableTemp (about 30,000 record every tiime) 4. commit transaction but now, insert data become very slowly. Which INSERT is getting slower? (You have 2 of them) Sean
[firebird-support] RE: Insert into Large Table is slowly!
[firebird-support] RE: Insert into Large Table is slowly!
Re: [firebird-support] RE: Insert into Large Table is slowly!
Em 5/9/2013 13:18, jym...@gmail.com escreveu: Thanks, I will try use GLOBAL TEMPORARY and INACTIVE index. The insert time will be faster without the index, but you will spend a lot of time when you reactivate it... And any query that will run during the insert time will not use the indice, so, it will be very slow to do a full table scan in a multi million record table Take into account that you will reactivate the index 24 times a day
Re: [firebird-support] Today's performance question - index direction
On 5-9-2013 18:02, Ann Harrison wrote: A major goal for Firebird's indexes was to minimize index locking and allow pages to be added to and dropped from indexes without blocking index reads. There's a long paper on the IBPhoenix web site that explains exactly how that works. The summary is that during index modifications, the forward pointers between pages in an index are always correct, but the back pointers are not guaranteed. It's a classic careful write problem - A points to C and C points to A and you want to stick B in between them. You set up B correctly, so it's got a forward pointer to C and a backward pointer to A, then change A so it points forward to B rather than C. Until you rewrite C so it points backward to B rather than A, a backward scan is going to miss B. I wonder though, when you are following the back pointers couldn't this be solved by checking the forward pointer from the 'current' page to see if it points back to your 'previous' page? And when it doesn't, you follow the forward pointers back to your 'previous' page adding those pages to a stack, and when you reach the 'previous' page then you process the page pages from the stack and start reading again from the 'current' page? Mark -- Mark Rotteveel
Re: [firebird-support] RE: Insert into Large Table is slowly!
GTT? Em 05/09/2013 13:35, jym...@gmail.com escreveu: Maybe I've missed something about temporary tables, but it looks to me as if you're doing two inserts and a delete per record.  Why not just put the records into the real table to start with? Ann Harrison is right. I just use GTT resolve a question at last week. ≧▽≦ jimmy --- In firebird-support@yahoogroups.com, aharrison@... wrote: On Thu, Sep 5, 2013 at 3:50 AM, jym013@... wrote: I insert data use follow: 1. start a transaction 2. Insert a batch records into temp table TableTemp, It's structure like table1 3. Insert into Table1 select * from TableTemp   Delete Data From TableTemp   (about 30,000 record every tiime) 4. commit transaction but now, insert data become very slowly. Maybe I've missed something about temporary tables, but it looks to me as if you're doing two inserts and a delete per record.  Why not just put the records into the real table to start with? Good luck, Ann -- Atenciosamente, Hugo Eyng
Re: [firebird-support] Today's performance question - index direction
On Thu, Sep 5, 2013 at 1:21 PM, Mark Rotteveel m...@lawinegevaar.nl wrote: On 5-9-2013 18:02, Ann Harrison wrote: A major goal for Firebird's indexes was to minimize index locking and allow pages to be added to and dropped from indexes without blocking index reads. There's a long paper on the IBPhoenix web site that explains exactly how that works. The summary is that during index modifications, the forward pointers between pages in an index are always correct, but the back pointers are not guaranteed. I wonder though, when you are following the back pointers couldn't this be solved by checking the forward pointer from the 'current' page to see if it points back to your 'previous' page? And when it doesn't, you follow the forward pointers back to your 'previous' page adding those pages to a stack, and when you reach the 'previous' page then you process the page pages from the stack and start reading again from the 'current' page? To avoid internal deadlocks, Firebird allows each connection only one hard page lock at a time. Other locks are just bookkeeping so the engine can figure out who has a page in cache and whether the page is dirty. When someone else wants a page that's in your cache, the engine writes the page (if it's dirty and Classic) and releases the lock. When you reference the page again, Firebird reads it in (in Classic) or gives you a reference to the new version of the page (in SS). So there's no guarantee that the page that was the prior is still the prior. In a contentious environment, pages change amazingly quickly. Vlad Khorson solved a very mysterious problem that showed up as a wrong page type when looking for an item in an index. As it happens, there are actually three pointers to each page in the index, left, right, and parent. Sometimes a page would be removed from an index and reallocated to some other use (generally a data page) between the time the parent pointer was read and the page it pointed to was read. Stacking page numbers really doesn't work. You have to come down from the top again. And yes, if you do the I went from C to A, does A point forward to C? check and start from the top again every time it fails, you could read the index backward. That's code that's never been written. Good luck, Ann
Re: [firebird-support] RE: RE: Insert into Large Table is slowly!
At 12:28 p.m. 6/09/2013, firebird_jimmy wrote: At the very start, I insert into real table, but it gradual become slowly. I think maybe records too much, so insert into temporary table. I think this is a wrong. It is the wrong solution to your slowdown problem. Mass inserts can get slower and slower if you are trying to commit too many inserts in a single transaction. Try performing a hard COMMIT ** after each ~8000 inserts and see whether this helps to keep things fast and even. (You can experiment with slightly larger or smaller batch sizes to establish what works best for your case.) In fact, your double insert is likely to be doubling the performance hit that the operation is already suffering due to overloading *two* Undo logs, which the engine is going to abandon anyway, eventually, when the logs simply grow too big! Also make sure your transaction for the mass inserts is in Snapshot isolation, not ReadCommitted. ReadCommitted has heavier overhead but has no purpose for an operation that only performs inserts. ** A hard COMMIT means calling COMMIT expressly, not using CommitWithRetain, which is often a default setting in application interfaces. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of The Firebird Book and The Firebird Book Second Edition http://www.firebird-books.net __
