Ronald Van De Walle > > Dear list, > > We notice a serious degradation of MaxDB performance when a table is > accessed by a stored procedure many times concurrently. > This is what we see : When executing "SELECT LOCKMODE, LOCKSTATE, > ROWIDLENGTH, ROWID, TABLENAME, LASTWRITE FROM DOMAIN.LOCKS" over 2000 > lock entries on the table occur. At the same time, the temporary data > area grows massively (over 1 GB) during which time the whole database > becomes almost unresponsive. > > This table holds temporary results from stored procedures, so many > inserts/updates/deletes occur concurrently. To maintain database > session integrity, every set of rows in the table belonging to one > session has its own unique sequence number. > > Questions : > 1. Why does the temporary data area grow so heavily ? > 2. How can we solve the problem of the locks on the table ? > 3. Is there a way to obtain more information about what is written to > the temporary data area ? > 4. Can we use the ROWID column in DOMAIN.LOCKS to join with rows in our > table ? > > Thanks in advance. Regards, >
I understood that you use one permanent (not TEMP) table to store results which are produced in parallel by different sessions and are produced within dbprocedures. Is this correct? Why don't you use normal resultsets? Do you want to insert/update/delete some of these results? Then resultset would be not ok, table is ok. But do you need ONE table for all of them or would a TEMP table for each session alone would be ok? All sessions could use the same dbproc, use the same name of the TEMP table and would NOT come into conflict with each other. Usually it would be much better than using a normal permanent table. Or do you need the results for one session even id this session is finished? Then no TEMP table can be used. Each insert/update/delete in a permanent table will include a lock (exclusive lock) for the changed row until commit/rollback is done. Do you use commit/rollback often enough? Temporary data area is used for resultsets/TEMP tables/intermediate results. Depending on your selects intermediate results can be huge. Please check the selects in your dbproc, check them with explain, think about creating indexes to help the optimizer to find good search strategies in the tables you use in the FROM-clause of your selects, check if update statistics will help to find a good search strategy (if the optimizer has wrong info about table sizes, it can't decide for a good strategy). ROWID in DOMAIN.LOCKS cannot be joined to other columns as it is a combination of several of them(the primary key) and (depending on the length of the primary key), not even the full key, but partly the original value, partly hash to shorten it. If we do not know more about your dbproc/insert/update/lock-problem, we will not be able to help much. Elke SAP Labs Berlin > -- > Ronald van de Walle - Verzekeringen NV > Oostkaai 23 > B-2170 Merksem > > Tel. : +32 3 641 66 71 > GSM : +32 485 600 023 > > www.verzekeringen.be > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
