Hello, I'm trying to figure out why I can't caching to work. I was
trying 2.10.9 for a day or so, and just switched to a nightly CVS tree
snapshot (just pulled down today). I've been trying all different
configurations with ResultCache but no matter what I do, I can't get
queries to stop hitting the database. I'm hoping someone here can
help me figure out what I'm doing wrong :-). Or maybe I'm
misunderstanding what caching can do. My virtualdatabase config is at
the end. TIA.
I'm looking at the "Cache hits" value from show sql statistics, I've
turned on request logging to see queries as they come in, and I've set
log_statement = 'all' in postgres to see what actually makes it to
postgres.
Just testing some select statements using the console's sql client
(sql client jdbc:sequoia://localhost:25322/myDB), Cache hits always
stays at zero, and I always see the query show up in Postgres' log.
jdbc:sequoia://localhost:25322/myDB (postgres) > select username from
person where id = 1
+----------+
| username |
+----------+
| admin |
+----------+
Query executed in 0 s 4 ms .
jdbc:sequoia://localhost:25322/myDB (postgres) > select username from
person where id = 1
+----------+
| username |
+----------+
| admin |
+----------+
Query executed in 0 s 4 ms .
request.log:
17:29:17,732 myDB S 0 0 select username from person where id = 1/
17:29:22,977 myDB S 1 0 select username from person where id = 1/
Postgres' serverlog:
LOG: statement: PREPARE <unnamed> AS select username from person
where id = 1
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE: select username from
person where id = 1]
LOG: statement: PREPARE <unnamed> AS select username from person
where id = 1
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE: select username from
person where id = 1]
Console:
myDB(admin) > show sql statistics
--------------------------------------------------------------
SQL statement: select username from person where id = 1
Count: 2
Error: 0
Cache hits: 0
% hits: 0.0
Min time (ms): 2
Max time (ms): 23
Avg time (ms): 12.5
Total time (ms): 25
--------------------------------------------------------------
My vdb-lb1.xml:
<?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="myDB">
<Monitoring>
<SQLMonitoring defaultMonitoring="on"/>
</Monitoring>
<Backup>
<Backuper backuperName="postgres"
className
=
"org
.continuent
.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper"
options="bindir=/opt/pgsql/bin,splitSize=1000m"/>
</Backup>
<AuthenticationManager>
<AdminUser username="admin" password="admin"/>
<VirtualUser vLogin="postgres" vPassword=""/>
</AuthenticationManager>
<DatabaseBackend name="pgsql-c1-db1"
driver="org.postgresql.Driver" url="jdbc:postgresql://10.10.3.52/
mirthdb" connectionTestStatement="select now()">
<ConnectionManager vLogin="postgres" rLogin="postgres"
rPassword="">
<VariablePoolConnectionManager initPoolSize="10"
minPoolSize="5" maxPoolSize="0" idleTimeout="180" waitTimeout="0"/>
</ConnectionManager>
</DatabaseBackend>
<DatabaseBackend name="pgsql-c1-db2"
driver="org.postgresql.Driver" url="jdbc:postgresql://10.10.3.53/
mirthdb" connectionTestStatement="select now()">
<ConnectionManager vLogin="postgres" rLogin="postgres"
rPassword="">
<VariablePoolConnectionManager initPoolSize="10"
minPoolSize="5" maxPoolSize="0" idleTimeout="180" waitTimeout="0"/>
</ConnectionManager>
</DatabaseBackend>
<DatabaseSchema/>
<RequestManager>
<RequestScheduler>
<RAIDb-1Scheduler level="passThrough"/>
</RequestScheduler>
<RequestCache>
<MetadataCache/>
<ParsingCache/>
<ResultCache granularity="columnUnique">
<DefaultResultCacheRule timestampResolution="1000">
<RelaxedCaching timeout="600" keepIfNotDirty="true"/>
</DefaultResultCacheRule>
</ResultCache>
</RequestCache>
<LoadBalancer>
<RAIDb-1>
<WaitForCompletion policy="first"
enforceTableLocking="true" />
<RAIDb-1-LeastPendingRequestsFirst/>
</RAIDb-1>
</LoadBalancer>
<EmbeddedRecoveryLog/>
</RequestManager>
</VirtualDatabase>
</SEQUOIA>
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
