-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Thanks for response.
Both problems must to be related: at this point all test are all done
with sequioa sql client. There is not application involved.
I think I have more useful information related to this problem. I have
set up postgres to log every SQL command and the timestamp.
When I load controller, it seems to check every object in database.
There are too many SQLs at database, but If this is once this is ok.
The problem is when I try to execute the SQL "SELECT * FROM table2 LIMIT
1": Then is where there is a really big number of SQL's against the
database.
I have attached a little more than one second postgres log since I
execute "SELECT * FROM table2". I have waited more than a minute and It
dind't finish. It also generated almost 100Mb log.
I guess cache is not working?
Emmanuel Cecchet escribió:
> Hi Ruben,
>
>> Always when I try to get some data from one especified table (it is
>> always the same table) top command shows postgres at 70%-80%
>>
>> If you try to know witch SQLs is being executed in postgres, executing:
>>
>> echo 'SELECT current_query FROM pg_stat_activity;' |
>> /usr/local/pgsql/bin/psql vacadb -p 5432
>>
>> It shows:
>> - ------
>> SELECT def.adsrc FROM pg_catalog.pg_class c JOIN
>> pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN
>> pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum =
>> def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE
>> '%nextval(%'
>> SELECT current_query FROM pg_stat_activity;
>> - -------
>>
> If I am not mistaken this is a query issued by the Postgres JDBC driver
> to retrieve the ResultSetMetaData. When you execute a query, Sequoia
> fetches the ResultSet from the database but also the ResultSetMetaData
> to get column names and other information in case you access the result
> using rs.getString("column_name"). It turns out that ResultSetMetaData
> fetching is very slow in Postgres. This is why we have implemented a
> MetadataCache in Sequoia. The first query execution should fetch the
> ResultSetMetadata but subsequent queries should get it from the cache.
>> If you get the controller log, you have a message that is repeating all
>> the time:
>>
>> [...]
>> 16:06:03,221 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:04,365 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:04,856 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:05,683 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:06,989 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:07,642 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:08,300 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:08,789 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> 16:06:09,277 WARN virtualdatabase.VirtualDatabaseWorkerThread.vvacadb
>> Client (login:vaca,host:192.168.12.8 closed connection with server)
>> [...]
>>
>> ... and go on, until I disable backend.
>>
> This looks like this is a separate issue. A client is dropping
> connections without calling Connection.close(). I cannot tell why this
> is happening but at least this is the diagnosis! This usually happens
> when you kill a client application or threads that had a private
> connection are terminated and garbage collected.
>> I have no idea what's going on. Any advise would be really appreciated.
>>
> Let me know if that helps,
> Emmanuel
>
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.6 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iD8DBQFJSmg9Io1XmbAXRboRAonmAJ9JJq18cNcrgAZk1RyBArGiBCC3kgCdEeSQ
>> hKAcWU+6Y+p0SVNcEu+RbSc=
>> =0k8/
>> -----END PGP SIGNATURE-----
>> _______________________________________________
>> Sequoia mailing list
>> [email protected]
>> https://forge.continuent.org/mailman/listinfo/sequoia
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFJS1h4Io1XmbAXRboRAkTEAKCK03qjfsuJ1vWQQJDiX7T4fKLlJwCfSsLm
sogklqRpUZIDW96kuMhQhOM=
=G0zE
-----END PGP SIGNATURE-----
select_from_table2.txt.tar.gz
Description: GNU Zip compressed data
begin:vcard fn:Ruben Rubio n:Rubio;Ruben org:Rentalia Holidays S.L adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain email;internet:[email protected] tel;work:+34915233104 url:http://www.rentalia.com version:2.1 end:vcard
_______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
