Re: Support for thread-pools (log/lock-threads)
I just deduce this from your comment that 'there are many idle threads floating around' because if the thread is short living you won't see much threads around (and thats where a Threadpool can help reduce the thread allocation costs), but if the thread is sitting around (waiting for I/O, blocked on wait/monitors) he is from the OS point of view "idle" or "blocked" but a threadpool won't change anything here (in fact with a bound threadpool you are effectifly limiting the number of active connection(threads)). Am 20.02.2012 20:23, schrieb cowwoc: On 20/02/2012 11:15 AM, Christoph Läubrich wrote: > Quoting from the Javadoc of the first link: "Threads that have not been used for sixty seconds are terminated and removed from the cache. > Thus, a pool that remains idle for long enough will not consume any resources." I think you are getting the doc wrong! 'Idle' here means: 'Threads that have not been used' aka finished executing the run() method of the executable! If your thread is runnin in an endless loop, it won't be reclaimed! I agree, but why would the thread run in an endless loop? Doesn't H2 have the ability to spin off a new thread per request? Gili -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Using "ALTER TABLE ADD..." to add multiple columns
Hi Thomas & co, Currently if I want to add 2 or more columns to a table in H2, I need to execute multiple ALTER TABLE ADD commands: ALTER TABLE customer ADD foo INTEGER; ALTER TABLE customer ADD foo2 INTEGER; Oracle allows adding two columns at once: ALTER TABLE customer ADD (foo INTEGER, foo2 INTEGER); So does Microsft SQL Server's T-SQL: ALTER TABLE customer ADD foo INTEGER, foo2 INTEGER; I'd like to have a go at adding this to H2. If I do, is this something you'd be likely to add to the SVN trunk? If so, is the SQL Server syntax or the Oracle syntax preferable? As far as I can tell, the SQL-92 standard doesn't cover this scenario. Regards, Steve -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Support for thread-pools (log/lock-threads)
On 20/02/2012 11:15 AM, Christoph Läubrich wrote: > Quoting from the Javadoc of the first link: "Threads that have not been used for sixty seconds are terminated and removed from the cache. > Thus, a pool that remains idle for long enough will not consume any resources." I think you are getting the doc wrong! 'Idle' here means: 'Threads that have not been used' aka finished executing the run() method of the executable! If your thread is runnin in an endless loop, it won't be reclaimed! I agree, but why would the thread run in an endless loop? Doesn't H2 have the ability to spin off a new thread per request? Gili -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
backup encrypted database
Hi H2 Group, I have an H2 database that I use the built in AES file encryption. I would like to find a good backup solution in which the backup is also encrypted using the same file password I could copy the database file, but the database will always be running, so simply copying the file may not be a good solution. And, if I understand the sql SCRIPT and BACKUP commands correctly they will not encrypt the output file. Any ideas? Thanks, -Adam -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
Hi, If you want, I have some code that could be used as a starting point. It's work in progress. Warning: I havent test it; I didn't run it at all. It could be completely wrong, and possibly corrupt the database. Also, the term "TRANSACTION_TIMEOUT" is misleading and has to be changed (it should be something like "really maximum transaction log size; if the transaction log is larger than this then the transaction of the session with the oldest open transaction is rolled back"). http://h2database.com/p.html#29e32a33bca7541cb70ea3cc9d483814 Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Support for thread-pools (log/lock-threads)
Hi, > Poor cache hit rate > Why is that? Wouldn't each database maintain its own cache? Yes, but with some many databases each cache would need to be extremely small, otherwise it would run out of memory. Or, as an alternative, the cache sizes would need to be actively managed, which would complicate the code a lot. > "Too many open files" > According to > http://blogs.technet.com/b/markrussinovich/archive/2009/09/29/3283844.aspx > the limit is 16 million handles per process. I don't think you will run out > of file handles anytime soon. For Windows, I think there is no limit. But most other operating systems the limit is 256 or 1024 or so (ulimit -n). Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: use of csvread ignoring comments
Hi,
Yes, there is an option "lineComment". Set it to "#" and the commented
lines will be ignored. See the docs for details.
CSVREAD('test.csv', 'ID|NAME|LEVEL', 'charset=UTF-8 fieldSeparator=|
lineComment=#')
Regards,
Thomas
On Saturday, February 18, 2012, h2 wrote:
> Hi
>
> I have a csv file in the format
> #Comment1
> #Comment2
> #Comment3
> ID,NAME,LEVEL
> 1,JOHN,3
> 2,MIKE,4
> .
> .
>
> One way of reading this might be to specify the columns in csvread as
> CSVREAD('test.csv', 'ID|NAME|LEVEL', 'charset=UTF-8
> fieldSeparator=|')
>
> and use a 'where' clause when reading the result set to ignore the
> comments row and column names row. Is there a better way to achieve
> this?
>
> Thank You.
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-database@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: User-Defined Function Values
Hi, Sorry, but I don't understand the problem. Is there some error message and stack trace? If yes could you post it? Do you have a simple reproducible test case? Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
Please, can you send me directly the jar file also. I don't have the
build environment yet.
On 20 fév, 17:25, Noel Grandin wrote:
> Make sure you can build and test the H2 source code locally, and I'll
> make you a patch tomorrow for displaying the transaction start time in
> the SESSIONS table.
>
> Then you can log the output of that table regularly to see who the culprit is.
>
>
>
>
>
>
>
> On Mon, Feb 20, 2012 at 18:16, IntensiveH2 wrote:
> > For information
> > My connection string is:
> > jdbc:h2:file:D:\ProductDev\cockpitconsole\build\projects\mserver\dist
> > \h2\cockpit
> > \cockpit;MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;CACHE_TYPE=SOFT_LRU
> > I change also some settings like:
> > statement.execute("set WRITE_DELAY 0");
> > statement.execute("SET MAX_MEMORY_ROWS 1");
> > statement.execute("SET MAX_LOG_SIZE 32");
>
> > Also, When I shutdow defrag the DB size moves from 12GB to 800 MB but
> > after a Recover tool to extract SQL and a redo in a fresh DB the size
> > is 200MB.
> > After, I shutdown compact again and the DB size is 80 MB.
>
> > I don't really understand how I can progress on the subject.
> > It's very important for me because the DB grows on customer side
> > (200MB per day for nothing) and I don't know how to fix it.
>
> > On 18 fév, 08:27, Thomas Mueller wrote:
> >> Hi,
>
> >> > Can I add some debug settings on customer side?
>
> >> There are two options:
>
> >> - Use the trace options to analyze the JDBC method calls (append
> >> ;TRACE_LEVEL_FILE=3 to the database URL)
>
> >> - Use the Recover tool to find out if there are uncommitted
> >> transactions in the (large) database file (or a copy of the file; in
> >> this case you could even copy the database file while it's in use,
> >> because you don't need a 'save' copy of the database just to analyze
> >> the transaction log).
>
> >> > Perhaps we need to implement some kind of default timeout on how long a
> >> > transaction can stay open?
>
> >> Thats a very good idea. I wonder if other databases support something
> >> like this?
>
> >> At the very least there should be a warning in the .trace.db file. I
> >> will have a look at that.
>
> >> Regards,
> >> Thomas
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "H2 Database" group.
> > To post to this group, send email to h2-database@googlegroups.com.
> > To unsubscribe from this group, send email to
> > h2-database+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
Make sure you can build and test the H2 source code locally, and I'll
make you a patch tomorrow for displaying the transaction start time in
the SESSIONS table.
Then you can log the output of that table regularly to see who the culprit is.
On Mon, Feb 20, 2012 at 18:16, IntensiveH2 wrote:
> For information
> My connection string is:
> jdbc:h2:file:D:\ProductDev\cockpitconsole\build\projects\mserver\dist
> \h2\cockpit
> \cockpit;MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;CACHE_TYPE=SOFT_LRU
> I change also some settings like:
> statement.execute("set WRITE_DELAY 0");
> statement.execute("SET MAX_MEMORY_ROWS 1");
> statement.execute("SET MAX_LOG_SIZE 32");
>
> Also, When I shutdow defrag the DB size moves from 12GB to 800 MB but
> after a Recover tool to extract SQL and a redo in a fresh DB the size
> is 200MB.
> After, I shutdown compact again and the DB size is 80 MB.
>
> I don't really understand how I can progress on the subject.
> It's very important for me because the DB grows on customer side
> (200MB per day for nothing) and I don't know how to fix it.
>
> On 18 fév, 08:27, Thomas Mueller wrote:
>> Hi,
>>
>> > Can I add some debug settings on customer side?
>>
>> There are two options:
>>
>> - Use the trace options to analyze the JDBC method calls (append
>> ;TRACE_LEVEL_FILE=3 to the database URL)
>>
>> - Use the Recover tool to find out if there are uncommitted
>> transactions in the (large) database file (or a copy of the file; in
>> this case you could even copy the database file while it's in use,
>> because you don't need a 'save' copy of the database just to analyze
>> the transaction log).
>>
>> > Perhaps we need to implement some kind of default timeout on how long a
>> > transaction can stay open?
>>
>> Thats a very good idea. I wonder if other databases support something
>> like this?
>>
>> At the very least there should be a warning in the .trace.db file. I
>> will have a look at that.
>>
>> Regards,
>> Thomas
>
> --
> You received this message because you are subscribed to the Google Groups "H2
> Database" group.
> To post to this group, send email to h2-database@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
For information
My connection string is:
jdbc:h2:file:D:\ProductDev\cockpitconsole\build\projects\mserver\dist
\h2\cockpit
\cockpit;MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;CACHE_TYPE=SOFT_LRU
I change also some settings like:
statement.execute("set WRITE_DELAY 0");
statement.execute("SET MAX_MEMORY_ROWS 1");
statement.execute("SET MAX_LOG_SIZE 32");
Also, When I shutdow defrag the DB size moves from 12GB to 800 MB but
after a Recover tool to extract SQL and a redo in a fresh DB the size
is 200MB.
After, I shutdown compact again and the DB size is 80 MB.
I don't really understand how I can progress on the subject.
It's very important for me because the DB grows on customer side
(200MB per day for nothing) and I don't know how to fix it.
On 18 fév, 08:27, Thomas Mueller wrote:
> Hi,
>
> > Can I add some debug settings on customer side?
>
> There are two options:
>
> - Use the trace options to analyze the JDBC method calls (append
> ;TRACE_LEVEL_FILE=3 to the database URL)
>
> - Use the Recover tool to find out if there are uncommitted
> transactions in the (large) database file (or a copy of the file; in
> this case you could even copy the database file while it's in use,
> because you don't need a 'save' copy of the database just to analyze
> the transaction log).
>
> > Perhaps we need to implement some kind of default timeout on how long a
> > transaction can stay open?
>
> Thats a very good idea. I wonder if other databases support something
> like this?
>
> At the very least there should be a warning in the .trace.db file. I
> will have a look at that.
>
> Regards,
> Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: Support for thread-pools (log/lock-threads)
> Quoting from the Javadoc of the first link: "Threads that have not been used for sixty seconds are terminated and removed from the cache. > Thus, a pool that remains idle for long enough will not consume any resources." I think you are getting the doc wrong! 'Idle' here means: 'Threads that have not been used' aka finished executing the run() method of the executable! If your thread is runnin in an endless loop, it won't be reclaimed! -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Java Start Parameter: -Dh2.identifiersToUpper=false not working
Hi Thomas, Sorry about the delay in responding - I've been caught up with other things. My problem is that when I append DATABASE_TO_UPPER=FALSE to the database URL and reload the database, none of my tables are loaded and I get an error message like "There exists no table named 'PRIVACCTS.PUBLIC.TransHeader' " (This is with h2-1.3.162.jar loaded in the Java Class Path.) As a test, I removed DATABASE_TO_UPPER=FALSE from the database URL and reloaded the database. The result was that all of my tables loaded normally, but of course I get an error message like 'Table "TRANSDETAILS" not found'. (My table name is TransDetails.) Does appending DATABASE_TO_UPPER=FALSE to the URL in some way affect the loading of the tables? I seem to be stuck on this one. Any ideas on what I might be doing wrong? Thanks, Noel On Feb 6, 6:41 pm, Thomas Mueller wrote: > Hi, > > You can't use the same database sometimes with DATABASE_TO_UPPER=FALSE and > sometimes without this option. If you use the option, you need to use it > every time you open the database. The setting is not persisted. > > Regards, > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
It should be useful to add this kind of information as JMX operation (like list sessions). On 20 fév, 10:59, Noel Grandin wrote: > Hi > > In order to monitor for bad transactions, how about we add a > TRANSACTION_START column to the SESSIONS meta-table? > > Regards, Noel Grandin > > On 2012-02-18 09:27, Thomas Mueller wrote: > > > > > > > > > Hi, > > >> Can I add some debug settings on customer side? > > There are two options: > > > - Use the trace options to analyze the JDBC method calls (append > > ;TRACE_LEVEL_FILE=3 to the database URL) > > > - Use the Recover tool to find out if there are uncommitted > > transactions in the (large) database file (or a copy of the file; in > > this case you could even copy the database file while it's in use, > > because you don't need a 'save' copy of the database just to analyze > > the transaction log). > > >> Perhaps we need to implement some kind of default timeout on how long a > >> transaction can stay open? > > Thats a very good idea. I wonder if other databases support something > > like this? > > > At the very least there should be a warning in the .trace.db file. I > > will have a look at that. > > > Regards, > > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Support for thread-pools (log/lock-threads)
Hello, > > > IMHO permanently reducing the number of idle threads can always be > > beneficial > > H2 doesn't use that many threads - just one per database usually. I > think using a thread pool would cause more problems that it would > solve, so I'm against going in this direction. in my case its 2 per database and that means that ~2/3 of the threads of the application are H2 Lock/Log threads. The application uses so many small databases because it creates one per customer (data needs to be separated) and having them embedded allows for easy/automatic creation of new ones. Once a database grows to big/ demanding it will be moved to a DB-server to also improve cache- efficiency but so far all DBs are well within limits. I will investigate the use of FS locking for my application, that would kill 1 thread per DB. The application can check that no DB is opened twice but sometimes we need to do maintenance with external tools (eg. H2 console) and therefor the DB needs to be protected when in use by the application. I agree that thread-pools are not the ideal solution here so when I have a look at the code my aim will be to get rid of the log-thread. If there was a possibility to do threadless logging I could get rid of all the H2 threads. regards Wolfgang -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
Hi In order to monitor for bad transactions, how about we add a TRANSACTION_START column to the SESSIONS meta-table? Regards, Noel Grandin On 2012-02-18 09:27, Thomas Mueller wrote: Hi, Can I add some debug settings on customer side? There are two options: - Use the trace options to analyze the JDBC method calls (append ;TRACE_LEVEL_FILE=3 to the database URL) - Use the Recover tool to find out if there are uncommitted transactions in the (large) database file (or a copy of the file; in this case you could even copy the database file while it's in use, because you don't need a 'save' copy of the database just to analyze the transaction log). Perhaps we need to implement some kind of default timeout on how long a transaction can stay open? Thats a very good idea. I wonder if other databases support something like this? At the very least there should be a warning in the .trace.db file. I will have a look at that. Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Automatically compact databases from time to time (as a background process)
I used the recover tool. What should I look for in the SQL file generated by the recover? On 18 fév, 08:27, Thomas Mueller wrote: > Hi, > > > Can I add some debug settings on customer side? > > There are two options: > > - Use the trace options to analyze the JDBC method calls (append > ;TRACE_LEVEL_FILE=3 to the database URL) > > - Use the Recover tool to find out if there are uncommitted > transactions in the (large) database file (or a copy of the file; in > this case you could even copy the database file while it's in use, > because you don't need a 'save' copy of the database just to analyze > the transaction log). > > > Perhaps we need to implement some kind of default timeout on how long a > > transaction can stay open? > > Thats a very good idea. I wonder if other databases support something > like this? > > At the very least there should be a warning in the .trace.db file. I > will have a look at that. > > Regards, > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Error on version: 1.3.164 (2012-02-03)
Hi Thomas, I have reduced the test case to a minimal version: create table a (id int) select * from a union select * from a ORDER BY a.id But I don't understand the code in that section well enough to fix it. Regards, Noel Grandin On 2012-02-17 22:49, Victor wrote: sorry that got. the problem in sorting. begin TEST 1 (- error) drop table if exists a, b; create table a (id int); create table b (id int, d date, n numeric(12,2)); insert into a values (1); insert into b values (1, now(), 100); select * from a --where id - (select coalesce(sum(id), 0) from b where id=a.id and d null)<0 union select * from a --ORDER BY a.id begin TEST 1 (- error) drop table if exists a, b; Update count: 0 (0 ms) create table a (id int); Update count: 0 (0 ms) create table b (id int, d date, n numeric(12,2)); Update count: 0 (1 ms) insert into a values (1); Update count: 1 (0 ms) insert into b values (1, now(), 100); Update count: 1 (0 ms) select * from a --where id - (select coalesce(sum(id), 0) from b where id=a.id and d null)<0 union select * from a --ORDER BY a.id; ID 1 end TEST 1 (- error) begin TEST 2 (+ error) drop table if exists a, b; create table a (id int); create table b (id int, d date, n numeric(12,2)); insert into a values (1); insert into b values (1, now(), 100); select * from a --where id - (select coalesce(sum(id), 0) from b where id=a.id and d null)<0 union select * from a ORDER BY a.id begin TEST 2 (+ error) drop table if exists a, b; Update count: 0 (1 ms) create table a (id int); Update count: 0 (1 ms) create table b (id int, d date, n numeric(12,2)); Update count: 0 (0 ms) insert into a values (1); Update count: 1 (0 ms) insert into b values (1, now(), 100); Update count: 1 (0 ms) select * from a --where id - (select coalesce(sum(id), 0) from b where id=a.id and d null)<0 union select * from a ORDER BY a.id; General error: "java.lang.NullPointerException" [5-164] HY000/5 (Help) end TEST 2 (+ error) -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
