Thread: Yet one more stupid question
Good time of the day! One more question related to performance. I have a VERY BIG table (abot 20 million records) which is accessed relatively seldom, like once a day (basically, it's archived data which sometimes may be needed). How this table affect performance of the database? Is it worse to create another database for storing this data, and keep the main DB small? Best regards, Dmitri ( mailto:dmitri@listsoft.ru ) New SOFT daily (RUS): http://www.listsoft.ru/ (ENG): http://www.listsoft.com/ Articles, tips : http://www.diskovod.ru/ --- Спирт - это концентрированная водка.
On Wed, 2002-12-04 at 18:00, Dmitri Touretsky wrote: > One more question related to performance. I have a VERY BIG table > (abot 20 million records) which is accessed relatively seldom, like > once a day (basically, it's archived data which sometimes may be > needed). How this table affect performance of the database? Is it > worse to create another database for storing this data, and keep the > main DB small? It will make no difference to performance except when it is being used. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Behold, happy is the man whom God correcteth. Therefore despise thou not the chastening of the Almighty." Job 5:17
On Wed, Dec 04, 2002 at 08:39:48PM +0000, Oliver Elphick wrote: > On Wed, 2002-12-04 at 18:00, Dmitri Touretsky wrote: > > > One more question related to performance. I have a VERY BIG table > > (abot 20 million records) which is accessed relatively seldom, like > > once a day (basically, it's archived data which sometimes may be > > needed). How this table affect performance of the database? Is it > > worse to create another database for storing this data, and keep the > > main DB small? > > It will make no difference to performance except when it is being used. True. But you might want to separate them if it will make it more flexible for you (being able to dump all of the smaller database and move it to a different machine, etc.) But if you separate them, it will be harder to do cross-database queries (but see dblink/ in contrib for a solution). All things being equal, I'd probably keep them together. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
Good time of the day! >> > One more question related to performance. I have a VERY BIG table >> > (abot 20 million records) which is accessed relatively seldom, like >> > once a day (basically, it's archived data which sometimes may be >> > needed). How this table affect performance of the database? Is it >> > worse to create another database for storing this data, and keep the >> > main DB small? >> >> It will make no difference to performance except when it is being used. JB> True. But you might want to separate them if it will make it more JB> flexible for you (being able to dump all of the smaller database and JB> move it to a different machine, etc.) But if you separate them, it will JB> be harder to do cross-database queries (but see dblink/ in contrib for a JB> solution). All things being equal, I'd probably keep them together. Thanks to Joel and Oliver for the input! Actually, I don't need to move DB or do something like that (I make dumps on a table level, so it's easy to avoid large dumps). But I have an extended question: is there a cost-effective (in terms of performance) way to add record to a large table? I don't need indexing or such - just to add some data, e.g., a log record. Currently I insert records into small table, than at night run a batch which is moving added records from small table to the large one. Trying to add records to large table "on the fly" causes noticable performance degradation, but would be desirable if it's possible... Best regards, Dmitri ( mailto:dmitri@listsoft.ru ) New SOFT daily (RUS): http://www.listsoft.ru/ (ENG): http://www.listsoft.com/ Articles, tips : http://www.diskovod.ru/ --- "Apple" (c) 1767, Sir Isaac Newton.
On Wed, 2002-12-04 at 15:44, Dmitri Touretsky wrote: > Good time of the day! [snip] > But I have an extended question: is there a cost-effective (in terms > of performance) way to add record to a large table? I don't need > indexing or such - just to add some data, e.g., a log record. Currently > I insert records into small table, than at night run a batch which is > moving added records from small table to the large one. Trying to add > records to large table "on the fly" causes noticable performance > degradation, but would be desirable if it's possible... Slowdown when inserting into a high-tuple table vs. a low-tuple table? -- +------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "they love our milk and honey, but preach about another | | way of living" | | Merle Haggard, "The Fighting Side Of Me" | +------------------------------------------------------------+
On Wed, 2002-12-04 at 18:29, Dmitri Touretsky wrote: > Good time of the day! > > >> But I have an extended question: is there a cost-effective (in terms > >> of performance) way to add record to a large table? I don't need > >> indexing or such - just to add some data, e.g., a log record. Currently > >> I insert records into small table, than at night run a batch which is > >> moving added records from small table to the large one. Trying to add > >> records to large table "on the fly" causes noticable performance > >> degradation, but would be desirable if it's possible... > > RJ> Slowdown when inserting into a high-tuple table vs. a low-tuple > RJ> table? > > Yes. And the difference is pretty big... Or I misunderstood your > question? When the table had 1,000 rows in it, inserting was faster than now that it has 20M records? Sounds like you understood me... I've done the same thing and didn't notice all that much of a drop- off in speed. (IDE drive using the EXT3 filesystem.) -- +------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "they love our milk and honey, but preach about another | | way of living" | | Merle Haggard, "The Fighting Side Of Me" | +------------------------------------------------------------+
Good time of the day! >> RJ> Slowdown when inserting into a high-tuple table vs. a low-tuple >> RJ> table? >> >> Yes. And the difference is pretty big... Or I misunderstood your >> question? RJ> When the table had 1,000 rows in it, inserting was faster than now RJ> that it has 20M records? Sounds like you understood me... RJ> I've done the same thing and didn't notice all that much of a drop- RJ> off in speed. (IDE drive using the EXT3 filesystem.) I use SCSI drive with UFS. Perhaps the slowdown comes from multiple simultaneous INSERTs - I've tried to record server visits into table (something like 70-100 thousand records a day). It works fine for one-two persons (even with loaded table) and works fine under load with "empty" table (up to around 500 - 700 thousand records). After that every insert starts to require like 3-5 seconds, which causes locks for other processes (I have about 100-150 connections) - every INSERT is a transaction and other processes can't add record till this transaction finish. Slowdown is steady, but at some point server just hangs - I belive it happens when time needed for transaction to finish is increased to the point when new visitors can't connect to the database (due to no available connections) and receive "too many clients already". Best regards, Dmitri ( mailto:dmitri@listsoft.ru ) New SOFT daily (RUS): http://www.listsoft.ru/ (ENG): http://www.listsoft.com/ Articles, tips : http://www.diskovod.ru/ --- Computer - A device designed to speed and automate errors.