Hello Sequoia Developers, at my company we're conducting tests with the Sequoia 3.0-beta branch, using web applications running on JBoss 4.0.x and PostgreSQL 8.1. The stored procedure semantic configuration appears to work correctly, no longer triggering the schema rehash that plaged our tests on branch 2.8.x, but there is a overhead on every access we do on a page of every application test that is a dynamic query performed on the backends looking for the nextval function.
Example: [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: PREPARE <unnamed> AS SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AN D attnum = $2 [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: <BIND> [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: EXECUTE <unnamed> [PREPARE: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attreli d = $1 AND attnum = $2] [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: PREPARE <unnamed> AS SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attrib ute 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(%' [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: <BIND> [2007-01-19 16:43:13 BRST] [EMAIL PROTECTED] LOG: comando: EXECUTE <unnamed> [PREPARE: 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(%'] These queries are repeated several times a second and I suspect that it's happening for every query sent to Sequoia, therefore impacting deep on the performance of the application. When the web application connects directly to the database there is no such sequence of queries. We suspected that Hibernate was to blame, but the problem occurred also on applications that used no object-relational mapper but JDBC directly, therefore it looks like a Sequoia issue. The application does not use nextval in anyplace and the issue does not happen when the virtual database is accessed through SquirrelSQL. We even tried to put the nextval function on the virtual database configuration (attached) but it was a no goer. We want to know if anyone here know how to avoid this problem or if there is a starting place (class) where we can debug Sequoia to try to find the origin of the problem. Thanks for the attention, João Eduardo
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE SEQUOIA PUBLIC "-//Continuent//DTD SEQUOIA 3.0-beta2//EN" "http://sequoia.continuent.org/dtds/sequoia-3.0-beta2.dtd";> <SEQUOIA> <VirtualDatabase name="sentinela"> <Distribution hederaPropertiesFile="/hedera_appia_sentinela.properties"> <MessageTimeouts/> </Distribution> <Monitoring> <SQLMonitoring defaultMonitoring="off"> <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton ="false" monitoring="on"/> </SQLMonitoring> </Monitoring> <Backup> <Backuper backuperName="Octopus" className="org.continuent.sequoia.controller.backup.backupers.OctopusBackuper" options="zip=true"/> <Backuper backuperName="postgresql" className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper" options="zip=true"/> </Backup> <AuthenticationManager> <AdminUser username="admin" password=""> <!-- <AccessControl defaultPolicy="acsentinelatAll" /> --> </AdminUser> <VirtualUser vLogin="user" vPassword=""> <!-- <AccessControl defaultPolicy="acsentinelatAll" /> --> </VirtualUser> </AuthenticationManager> <DatabaseBackend name="pga1" driver="org.postgresql.Driver" url="jdbc:postgresql://10.15.117.11/sentinela" connectionTestStatement="select 1 + 1"> <ConnectionManager vLogin="user" rLogin="sa_sentinela" rPassword=""> <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/> </ConnectionManager> </DatabaseBackend> <DatabaseBackend name="pga2" driver="org.postgresql.Driver" url="jdbc:postgresql://10.15.117.12/sentinela" connectionTestStatement="select 1 + 1"> <ConnectionManager vLogin="user" rLogin="sa_sentinela" rPassword=""> <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/> </ConnectionManager> </DatabaseBackend> <DatabaseSchema useStoredProcedures="true" gatherSystemTables="false" useViews="false" /> <!-- Pelo dtd, vem certo por padrão. <Semantic> <DefaultStoredProcedureSemantic> <SemanticBehavior /> </DefaultStoredProcedureSemantic> </Semantic> --> <Semantic> <DefaultStoredProcedureSemantic> <SemanticBehavior altersDatabaseSchema="false" /> </DefaultStoredProcedureSemantic> <StoredProcedureSemantic procedureName="sa_dut.proximo_parametro" parameterCount="1"> <SemanticBehavior altersDatabaseSchema="false" /> </StoredProcedureSemantic> <StoredProcedureSemantic procedureName="nextval" parameterCount="1"> <SemanticBehavior altersDatabaseSchema="false" /> </StoredProcedureSemantic> <StoredProcedureSemantic procedureName="pg_catalog.nextval" parameterCount="1"> <SemanticBehavior altersDatabaseSchema="false" /> </StoredProcedureSemantic> </Semantic> <RequestManager> <RequestScheduler> <RAIDb-1Scheduler level="passThrough"/> </RequestScheduler> <LoadBalancer> <RAIDb-1> <WaitForCompletion policy="first"/> <MacroHandling> <RandomMacro macroName="rand()"/> <DateMacro macroName="now()" dateFormat="timestamp"/> <DateMacro macroName="current_date" dateFormat="date"/> <DateMacro macroName="timeofday()" dateFormat="time"/> <DateMacro macroName="current_timestamp" dateFormat="timestamp"/> <DateMacro macroName="current_time" dateFormat="time"/> </MacroHandling> <RAIDb-1-LeastPendingRequestsFirst/> </RAIDb-1> </LoadBalancer> <!-- <RecoveryLog driver="org.hsqldb.jdbcDriver" login="SA" password="" url="jdbc:hsqldb:file:/opt/continuent/sequoia-3.0-alpha-bin/recovery/seq/recoveryLog;shutdown=true"> <RecoveryLogTable/> <CheckpointTable/> <BackendTable/> <DumpTable/> </RecoveryLog> --> <!-- <EmbeddedRecoveryLog dataPath="../recoverylog/seqDB/seqdb/recoverylog"/> --> <EmbeddedRecoveryLog dataPath="/opt/continuent/sequoia-3.0-beta2/recoverylog/sentinela/db"/> </RequestManager> </VirtualDatabase> </SEQUOIA>
_______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
