In a private communication it was found out, that a wrong trigger updating the entire table was the reason to the bad performance. The insert worked seamlessly when this was corrected.
Alexander Schröder SAP DB, SAP Labs Berlin -----Original Message----- From: Heckendorff. Ronald [mailto:[EMAIL PROTECTED] Sent: Dienstag, 1. November 2005 15:17 To: [email protected] Subject: poor insert performance Hi all, we have a performance problem with our tables during inserts. We have one table that has a lot of rows. For testing purposes I used a backup with approximately 4250 rows. On a productive system we will have a lot more. All rows are unique. That means we only need inserts and no updates. The table has a primary key, a unique index and three other indices. It has 32 columns, most of them varchar with a size between 12 and 256. The values for the primary keys are created by a sequence (under the responsibility of maxdb). Our tool that inserts the rows into the table uses jdbc. It only performs inserts and no updates (for this kind of table). Now I need 2 hours to insert those rows. On mssql the tool needs 30 seconds. It doesn't matter if I use a prepared statement or a batch insert. I checked if it is possible to use the loader to get better performance metrics. In this way I can import those 4250 rows within 30 minutes. It is better then 2 hours but still not acceptable. I use maxdb 7.5.0.30 on a w2k pro with 2 GB RAM. The data area has a size of 6 GB and the Log area a size of 2 GB. I tried 7.6.0.12 too, but the dbanalyzer doesn't work on that installation. It always crashes :-(. Now I tried several things. I checked the dbanalyzer output. I got the following messages: * W3 Log queue overflows: 329, configured log queue pages: 50 CON: LQ_Overflows > 0 VAL: 329 > 0 * W3 Lock list escalations: 58 CON: LocklEscalations > 0 VAL: 58 > 0 * W3 Catalog cache hitrate (SQL Pages) 74.41%, 851 of 3325 accesses failed CON: Cat_Hit < 80 && ( SQL_Cmds ) > INTERVAL VAL: 74.41 < 80 && ( 398 ) > 10 I changed the kernel parameters. I use CACHE_SIZE 131072 (2^17) pages. The page size is 2^13 bytes. The parameter LOG_IO_QUEUE has now the value 350. No changes. I changed the isolation level (0, 1, 2, 3). I saw no differences. I changed the connection method of the user to be exclusive. The loader crashes if this is the case :-(. I disabled the indices -- no change. I reduced the size of the table. Some rows contain only NULL values within the whole data file. Therefore I removed the rows from the table definition. Nothing changed, still 30 minutes for loading the data. If I check the activity of the database after the import I see that there are: -updates * 18117861 row reads * 18117861 row changes -inserts: * 9063161 rows added I don't understand why maxdb updates and inserts so many rows if it has to insert 4250 unique rows. What can we do for getting acceptable metrics for our database restore? Kind regards, Ronald -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
