Re: [h2] MVStore with MVCC performance degradation
Sorry this took so long, I have pushed a performance fix for the metadata query in your trace. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] 1.3.176 bug with automaticly created index for foreign key (unique constraint)
I have 2 tables: CREATE CACHED TABLE SAB_UNITS( ADDR INTEGER NOT NULL, ORG INTEGER NOT NULL, SCHEME INTEGER NOT NULL ); ALTER TABLE SAB_UNITS ADD CONSTRAINT PK_SAB_UNITS PRIMARY KEY(ADDR); CREATE CACHED TABLE SAB_MANAGE_ASSIGNMENT( ADDR INTEGER NOT NULL, IO INTEGER NOT NULL, CFGNAME VARCHAR(1024), CFGBY INTEGER NOT NULL, IOONOFF INTEGER NOT NULL, PATTERNA BOOLEAN NOT NULL, PATTERNB BOOLEAN NOT NULL, PATTERNC BOOLEAN NOT NULL ); ALTER TABLE SAB_MANAGE_ASSIGNMENT ADD CONSTRAINT PK_SAB_MANAGE_ASSIGNMENT PRIMARY KEY(ADDR, IO); ALTER TABLE SAB_MANAGE_ASSIGNMENT ADD CONSTRAINT FK_SAB_MANAGE_ASSIGNMENT_SAB_UNITS FOREIGN KEY(ADDR) REFERENCES SAB_UNITS(ADDR) NOCHECK; H2 will create the following index for the table 'SAB_MANAGE_ASSIGNMENT': CREATE UNIQUE INDEX PUBLIC.FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F ON PUBLIC.SAB_MANAGE_ASSIGNMENT(ADDR); TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_NAME ORDINAL_POSITION COLUMN_NAME CARDINALITY PRIMARY_KEY INDEX_TYPE_NAME IS_GENERATED INDEX_TYPE ASC_OR_DESC PAGES FILTER_CONDITION REMARKS SQL ID SORT_TYPE CONSTRAINT_NAME INDEX_CLASS DB1 PUBLIC SAB_MANAGE_ASSIGNMENT false FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F 1 ADDR 0 false UNIQUE INDEX true 3 A 0 CREATE UNIQUE INDEX PUBLIC.FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F ON PUBLIC.SAB_MANAGE_ASSIGNMENT(ADDR) 244 0 FK_SAB_MANAGE_ASSIGNMENT_SAB_UNITS org.h2.index.PageBtreeIndex The Problem is that the index is unique! I don't know how to fix that. I can't drop the index nor change it: Error code 90085, SQL state 90085: Index FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F belongs to constraint FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT; SQL statement: Drop index FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F [90085-176] -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: 1.3.176 bug with automaticly created index for foreign key (unique constraint)
Database attached with user sa no password. The problem: Error code 23505, SQL state 23505: Unique index or primary key violation: FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F ON PUBLIC.SAB_MANAGE_ASSIGNMENT(ADDR) VALUES ( /* 13 */ 64 ); SQL statement: INSERT INTO DB1.PUBLIC.SAB_MANAGE_ASSIGNMENT (ADDR, IO, CFGNAME, CFGBY, IOONOFF, PATTERNA, PATTERNB, PATTERNC) VALUES (64, 5, 'Gerd', 1, 3, false, false, false) [23505-176] -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. DB1.h2.db Description: Binary data
[h2] Re: 1.3.176 bug with automaticly created index for foreign key (unique constraint)
I attached the db: Error by trying to insert: Error code 23505, SQL state 23505: Unique index or primary key violation: FK_SAB_MANAGE_ASSIGNMENT_CFG_SAB_MANAGE_ASSIGNMENT_INDEX_F ON PUBLIC.SAB_MANAGE_ASSIGNMENT(ADDR) VALUES ( /* 13 */ 64 ); SQL statement: INSERT INTO DB1.PUBLIC.SAB_MANAGE_ASSIGNMENT (ADDR, IO, CFGNAME, CFGBY, IOONOFF, PATTERNA, PATTERNB, PATTERNC) VALUES (64, 5, 'Gerd', 1, 3, false, false, false) [23505-176] -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. DB1.h2.db Description: Binary data
Re: [h2] QUERY_CACHE_SIZE default value too low?
Hi Thomas,
Thanks for the tip. I spent time today trialling various connection pools
with H2. Surprisingly, nothing I found compares with using H2's own query
cache + connection pool for both speed and ease.
On Thursday, 6 August 2015 20:01:47 UTC+2, Thomas Mueller wrote:
Hi,
Did you try using a more advanced connection pool? One that re-uses
prepared statements. The H2 one is really simple and prevents that.
Regards,
Thomas
On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote:
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for
n+1 characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code
is not caching PreparedStatement 's properly, and the rest of H2 is so
fast, that the only thing left in the profile is the parser initialisation
:)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my
app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping
solve the bigger issue of why it seems to take a comparatively significant
time to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is
not working, which would indicate a problem with
your connection pool, or something similar.
--
You received this message because you are subscribed to the Google
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send
an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send an
email to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
[h2] File Encryption Password
Is there a limitation to the characters that can be used when encrypting a database file? I've successfully encrypted and opened a file using simple characters and numbers, but when I introduce special characters (!@#$%^*-~) I get an encryption error on open. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Postgresql Update with 'Returning clause'
I'm trying to execute the following against an h2database in postgresql compatibility mode: UPDATE ctable c SET c.status = 1 FROM ( SELECT co.id FROM ctable co INNER JOIN htable a ON a.id = secretId AND a.coupon_group_id = co.coupon_group_id AND a.status = 1 WHERE co.status = 0 LIMIT 1 FOR UPDATE ) ic WHERE c.id = ic.id RETURNING c.code Against Postgres, this performs an update and returns a field from the row that was updated. When I try to execute this against an h2database, I am getting a mysterious syntax error [42000-187]. This syntax is inspired by http://dba.stackexchange.com/questions/69471/postgres-update-limit-1 -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
