Обсуждение: Server memory exhausted on ODBC 8.3.0400
Hi, Last weekend, I update 2 customers servers with the latest Postgresql 8.3.5 vrsion. The databases in each customer have +/- 50GB of data. I changed some ODBCs versions in clients machines from 7.3.0208 to 8.3.0400. In this machines, when I run some reports (thats run million of small queries), the Postgresql server crashes because this processes use all physical and swap memory in the server. If I disable both 'Use declare/fetch' and 'Updatable Cursor', the error does not occurs. I don't need the 'Updatable Cursors', but the 'Use Declare Fetch' is crucial to me. In ODBC 7.3.0208, with this option enabled, the error doesn't occurs. I think that use of memory grows in sort operations (If I reduce the work_mem, the memory grows not so fast). But the queries are too fast (average 0.005 ms). I think that after the query is executed, the work memory should be released. Is SAVEPOINT/RELEASE statements generated by ODBC 8.3.0400 (and not generated by 7.3) the cause of the problem ??? Best regards, Alexandre Server 1: Debian 4.0 etch Postgresql 8.3.5 compiled from sources. 16 Gb RAM shared buffers = 2GB work_mem = 64MB Server 2: CentOS 5 Postgresql 8.3.5 compiled from sources. 8 Gb RAM shared buffers = 1GB work_mem = 32MB
alepaes - aldeia digital wrote: > Hi, > > Last weekend, I update 2 customers servers with the latest Postgresql > 8.3.5 vrsion. The databases in each customer have +/- 50GB of data. > > I changed some ODBCs versions in clients machines from 7.3.0208 to > 8.3.0400. > In this machines, when I run some reports (thats run million of small > queries), the Postgresql server crashes because this processes use all > physical and swap memory in the server. > > If I disable both 'Use declare/fetch' and 'Updatable Cursor', the error > does not occurs. I don't need the 'Updatable Cursors', but the 'Use > Declare Fetch' is crucial to me. > In ODBC 7.3.0208, with this option enabled, the error doesn't occurs. Does your code close the statements used in the applications? regards, Hiroshi Inoue
Hi Hiroshi, Hiroshi Inoue wrote: > alepaes - aldeia digital wrote: >> Hi, >> >> Last weekend, I update 2 customers servers with the latest Postgresql >> 8.3.5 vrsion. The databases in each customer have +/- 50GB of data. >> >> I changed some ODBCs versions in clients machines from 7.3.0208 to >> 8.3.0400. >> In this machines, when I run some reports (thats run million of small >> queries), the Postgresql server crashes because this processes use all >> physical and swap memory in the server. >> >> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the >> error does not occurs. I don't need the 'Updatable Cursors', but the >> 'Use Declare Fetch' is crucial to me. >> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs. > > Does your code close the statements used in the applications? Basically, the program starts with a BEGIN and make a lot of simple SELECT queries and some insert and update queries. After this we have a COMMIT. Very simple. Like I sad, If I disable "Use declare" and "Updatable", the error does not occurs. If I enable one of this, the error occurs. I test in some other heavy applications and the error persists. If I run this in an older ODBC driver, everything if fine. Regards, Alexandre
alepaes - aldeia digital wrote: > Hi Hiroshi, > > Hiroshi Inoue wrote: >> alepaes - aldeia digital wrote: >>> Hi, >>> >>> Last weekend, I update 2 customers servers with the latest Postgresql >>> 8.3.5 vrsion. The databases in each customer have +/- 50GB of data. >>> >>> I changed some ODBCs versions in clients machines from 7.3.0208 to >>> 8.3.0400. >>> In this machines, when I run some reports (thats run million of small >>> queries), the Postgresql server crashes because this processes use >>> all physical and swap memory in the server. >>> >>> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the >>> error does not occurs. I don't need the 'Updatable Cursors', but the >>> 'Use Declare Fetch' is crucial to me. >>> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs. >> >> Does your code close the statements used in the applications? > > Basically, the program starts with a BEGIN and make a lot of simple > SELECT queries and some insert and update queries. After this we have a > COMMIT. Very simple. What kind of tool are you using? Do you close cursors before "COMMIT" using SQLCloseCursor() or the coressponding command in your tool? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > alepaes - aldeia digital wrote: >> Hi Hiroshi, >> >> Hiroshi Inoue wrote: >>> alepaes - aldeia digital wrote: >>>> Hi, >>>> >>>> Last weekend, I update 2 customers servers with the latest >>>> Postgresql 8.3.5 vrsion. The databases in each customer have +/- >>>> 50GB of data. >>>> >>>> I changed some ODBCs versions in clients machines from 7.3.0208 to >>>> 8.3.0400. >>>> In this machines, when I run some reports (thats run million of >>>> small queries), the Postgresql server crashes because this processes >>>> use all physical and swap memory in the server. >>>> >>>> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the >>>> error does not occurs. I don't need the 'Updatable Cursors', but the >>>> 'Use Declare Fetch' is crucial to me. >>>> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs. >>> >>> Does your code close the statements used in the applications? >> >> Basically, the program starts with a BEGIN and make a lot of simple >> SELECT queries and some insert and update queries. After this we have >> a COMMIT. Very simple. > > What kind of tool are you using? > Do you close cursors before "COMMIT" using SQLCloseCursor() or > the coressponding command in your tool? I'm 'only' the DBA... ;) The software was made by a CASE Tool called Genexus, thats generate the software in Visual Basic 6 and use ODBC for connectivity. This tool doesn't make any use of cursors, functions, triggers or stored procedures, only simple SELECTs and DML statements. The cursors are generated by 'Use Declare/Fetch' in ODBC. This improves performance a lot. Analyzing the log, The only differences that I notice between the old and the new driver is the use of SAVEPOINT/RELEASE and some changes in the way of creation of DECLARE/FETCH. I will try to reproduce this by making a simple application that loops some SELECT, INSERT and UPDATE statement to see what happens. Best regards, Alexandre