Re: Database storage overhead
Yep, definitely can do that, thanks. I was just wondering if you had some heuristics I could use. On Fri, Feb 5, 2010 at 11:30 AM, Bryan Pendleton wrote: > But that's assuming a "perfect" layout for the database. I am wondering, >> how much overhead should I account for? >> > > My feeling is: try it and see. It shouldn't take longer than a > few hours to construct a test program which manufactures semi-random > data along the profile that you anticipate. Then just generate that > data, store it into the db, and see how big it turns out to be. > > You surely don't need all 1 million rows; a few thousand rows is > probably plenty to allow you to observe the overheads and extrapolate. > > thanks, > > bryan > > -- David W. Van Couvering http://www.linkedin.com/in/davidvc http://davidvancouvering.blogspot.com http://twitter.com/dcouvering
Re: Database storage overhead
But that's assuming a "perfect" layout for the database. I am wondering, how much overhead should I account for? My feeling is: try it and see. It shouldn't take longer than a few hours to construct a test program which manufactures semi-random data along the profile that you anticipate. Then just generate that data, store it into the db, and see how big it turns out to be. You surely don't need all 1 million rows; a few thousand rows is probably plenty to allow you to observe the overheads and extrapolate. thanks, bryan
Database storage overhead
Say I have a database with 1 million rows, each row has a BLOB column so can vary in size from about 30bytes to 1MB, but averaging around 1K/row There are two indexes - one for the primary key and a secondary index for one integer column. If I didn't account for overhead, I'd say that this would take up around 1million x 1000 bytes or 1GB + 1 million x 8bytes or 8MB for the secondary index. But that's assuming a "perfect" layout for the database. I am wondering, how much overhead should I account for? Thanks! David -- David W. Van Couvering http://www.linkedin.com/in/davidvc http://davidvancouvering.blogspot.com http://twitter.com/dcouvering
Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby
DerbyServer thread seems to still be running, yet the server is unresponsive. In addition to your own DerbyServer thread, which I don't think actually *needs* to still be running, there should be a separate thread which is started by the Derby network server code itself, which has the job of accepting connections and delivering them to other threads to be processed. In my running network server, for example, when I look at the threads that are active, I see: "NetworkServerThread_2" prio=6 tid=0x03044c80 nid=0x27c runnable [0x033cf000..0x033cfae8] at java.net.PlainSocketImpl.socketAccept(Native Method) at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:384) - locked <0x22a857d8> (a java.net.SocksSocketImpl) at java.net.ServerSocket.implAccept(ServerSocket.java:450) at java.net.ServerSocket.accept(ServerSocket.java:421) at org.apache.derby.impl.drda.ClientThread$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at org.apache.derby.impl.drda.ClientThread.run(Unknown Source) Do you see a thread like that? When your server becomes stuck/wedged/unresponsive, why don't you try this: - collect a thread dump of the entire JVM - look through the threads for any which mention "org.apache.derby" in their stacks - edit out all the other threads from your thread dump Then post a message with a cut-and-paste of just the derby-related threads in your wedged server, and maybe it will be more clear to somebody else what the problem is. thanks, bryan
Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby
Thank you for your prompt response, Bryan. 1) I will peruse the docs you recommended. 2) No, the m_done variable is not triggered in the app and the DerbyServer thread seems to still be running, yet the server is unresponsive. Is there the best recommended practice to wrap the NetworkServerControl for proper functioning? Bryan Pendleton 02/05/2010 10:22 AM Please respond to "Derby Discussion" To Derby Discussion cc Subject Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby > 1) how can actions of those connecting to the NetworkServerControl via > JDBC be controled (for instance, limited to read only)? Derby has a variety of access control facilities. Here's a good place to start learning about them: http://db.apache.org/derby/docs/10.5/devguide/cdevcsecuree.html > But even with this thread, the server still stops (connection breaks) if > the application's main threads get into a wait/sleep or hang. It definitely sounds like something in your application is interfering with the Derby processing, but it's hard to know what. I noticed, however, that in the sample code that your posted, your logic not only starts the network server, but also shuts it down. Perhaps somehow the "m_done" variable is being set, and your code is exiting the "while" loop and shutting the server down itself? thanks, bryan Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby
1) how can actions of those connecting to the NetworkServerControl via JDBC be controled (for instance, limited to read only)? Derby has a variety of access control facilities. Here's a good place to start learning about them: http://db.apache.org/derby/docs/10.5/devguide/cdevcsecuree.html But even with this thread, the server still stops (connection breaks) if the application's main threads get into a wait/sleep or hang. It definitely sounds like something in your application is interfering with the Derby processing, but it's hard to know what. I noticed, however, that in the sample code that your posted, your logic not only starts the network server, but also shuts it down. Perhaps somehow the "m_done" variable is being set, and your code is exiting the "while" loop and shutting the server down itself? thanks, bryan
Re: Unable to connect. Cannot establish connection to
But all of a sudden the table disappeared from the list, i decided then to close everything and try to connect again, where i get the following message: "Unable to connect. Cannot establish a connection to using One possibility is that when you closed your previous application, you didn't fully terminate it, and the underlying JVM was still running and Derby still had the database booted. Derby won't let you open the database a second time from a second JVM until the first JVM fully terminates. I then renamed the database folder, of the database, set a new jdbc:derby connection up to the database and all of a sudden my schema is available, and everything is back to normal again. What happened? If your connection URL says "create=true", Derby will automatically create a fresh new database if the database doesn't exist when you ask to open it. Derby will also auto-create a schema with a name which matches your user name, so I'm guessing that your schema is the same name as your Derby user name. You didn't say much about your application, but I'm guessing that it automatically creates your database tables if they don't exist? Many applications are coded in such an automatic fashion. It's easy to get confused when you're first starting to learn a complex piece of system software. Take your time, learn to read the Derby logs and operating system process viewing tools, and things will start to make more sense, I believe. thanks, bryan
Re: Perhaps a stupid question
Hi Ron, A response inline... Ronald Rudy wrote: Interesting, I'll take a look... In the interest of portability (in case we ever swap out Derby for something else) I think for now I'll use a separate column I build and use specifically for sorting. On a somewhat related question as it pertains to indexes - if I have this prepared statement (used frequently): SELECT col0, col1 FROM mytable WHERE col0 = ? AND col1 = ? ORDER BY sortcol Does it buy me anything to include the "sortcol" in the index along with "col0" and "col1"? If I should only index "col0" and "col1", does it buy me anything to add a second index with just "sortcol" in it? If your index includes sortcol as a trailing column, then it is what we call a "covering index". This means that the index contains all of the columns needed to evaluate the query. This can speed up your query because Derby does not have to probe into the base table for extra information. So an index like this might help you: create index myIndex on myTable( col0, col1, sortcol ); Hope this helps, -Rick On Feb 5, 2010, at 9:25:00 AM, Rick Hillegas wrote: Hi Ron, If what you need is a custom sort order for string data, you can plug in your own custom collator. See Knut's blog for details on how to do this: http://blogs.sun.com/kah/entry/user_defined_collation_in_apache You can also use generated columns to build user-defined ordering. See the following writeup for advice on how to do this: http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns If these solutions don't work for you, tell us more about what you need. We may be able to suggest another solution or at least capture your requirements for a future enhancement. Thanks, -Rick Ronald Rudy wrote: Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column? I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :). And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ? My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable. Thanks, -Ron
Re: Perhaps a stupid question
Interesting, I'll take a look... In the interest of portability (in case we ever swap out Derby for something else) I think for now I'll use a separate column I build and use specifically for sorting. On a somewhat related question as it pertains to indexes - if I have this prepared statement (used frequently): SELECT col0, col1 FROM mytable WHERE col0 = ? AND col1 = ? ORDER BY sortcol Does it buy me anything to include the "sortcol" in the index along with "col0" and "col1"? If I should only index "col0" and "col1", does it buy me anything to add a second index with just "sortcol" in it? On Feb 5, 2010, at 9:25:00 AM, Rick Hillegas wrote: > Hi Ron, > > If what you need is a custom sort order for string data, you can plug in your > own custom collator. See Knut's blog for details on how to do this: > http://blogs.sun.com/kah/entry/user_defined_collation_in_apache > > You can also use generated columns to build user-defined ordering. See the > following writeup for advice on how to do this: > http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns > > If these solutions don't work for you, tell us more about what you need. We > may be able to suggest another solution or at least capture your requirements > for a future enhancement. > > Thanks, > -Rick > > > Ronald Rudy wrote: >> Is there any way programmatically or otherwise to specify a >> java.util.Comparator type object to sort a specific column? I realize this >> flies in the face of any SQL standard or convention, but it would be really >> helpful to me at the moment :). And since Derby is Java-based, I was >> wondering if there might be something "non-standard" I could do to support >> it ? >> >> My alternative is to create an additional column in my database that will >> give me the sorting I want (as it would be given by a custom Comparator >> class) using standard varchar sorting, but this isn't preferable. >> >> Thanks, >> -Ron >> >
Using NetworkServerControl to access in-memory (only) tables of Embedded Derby
Hello:
In my application Derby is used as an in-memory database (only) and the
NetworkServerControl (NSC) is used to provide JDBC access to the tables.
There are two questions:
1) how can actions of those connecting to the NetworkServerControl via
JDBC be controled (for instance, limited to read only)?
2) a more important question: it seems that if my application stops,
waits, hangs, then the JDBC connection to NSC is broken.
It seems that NSC just stops running. I couldn't figure out how to put it
in a separate thread, so that it's independent of other
application threads. The only other thread I could create is the
"monitoring" thread which starts and then is continuously pinging the
server.
But even with this thread, the server still stops (connection breaks) if
the application's main threads get into a wait/sleep or hang.
public class DerbyServer implements Runnable {
private NetworkServerControl m_server;
final public void start() throws Exception {
new Thread(this, "Derby Server").start();
}
public void run() {
m_server = new NetworkServerControl(m_host, m_port);
m_server.start(new PrintWriter(new LoggingOutputStream(logger,
Level.INFO), true));
try {
while (!m_done.get()) {
m_server.ping();
try {
Thread.sleep(m_sleep);
} catch (InterruptedException e) {
/* log */
}
}
} catch (Exception e) {
/* log - errror pinging server */
}
m_server.shutdown();
}
}
Thanks,
Pavel
Jefferies archives and monitors outgoing and incoming e-mail. The contents of
this email, including any attachments, are confidential to the ordinary user of
the email address to which it was addressed. If you are not the addressee of
this email you may not copy, forward, disclose or otherwise use it or any part
of it in any form whatsoever. This email may be produced at the request of
regulators or in connection with civil litigation. Jefferies accepts no
liability for any errors or omissions arising as a result of transmission. Use
by other than intended recipients is prohibited. In the United Kingdom,
Jefferies operates as Jefferies International Limited; registered in England:
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the
Financial Services Authority.
Re: Perhaps a stupid question
Hi Ron, If what you need is a custom sort order for string data, you can plug in your own custom collator. See Knut's blog for details on how to do this: http://blogs.sun.com/kah/entry/user_defined_collation_in_apache You can also use generated columns to build user-defined ordering. See the following writeup for advice on how to do this: http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns If these solutions don't work for you, tell us more about what you need. We may be able to suggest another solution or at least capture your requirements for a future enhancement. Thanks, -Rick Ronald Rudy wrote: Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column? I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :). And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ? My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable. Thanks, -Ron
Re: Unable to connect. Cannot establish connection to
Hi Theuns, These sound like issues with the database support in Netbeans. I think that you will get answers faster if you post your issues to the Netbeans user list. Hope this helps, -Rick theuns wrote: I would appreciate it if somebody can give me explanation of what happend with my derby database. The database was operational for sometime without any troubles, and was behaving. Today i removed a record from a table by using the "Delete Selected Record(s)" button from the SQL Command page in Netbeans. I may had finger trouble and used the "Truncate Table" button. But all of a sudden the table disappeared from the list, i decided then to close everything and try to connect again, where i get the following message: "Unable to connect. Cannot establish a connection to using org.apache.derby.jdbc.Clientdriver(DERBY SQL Error: SQLCODE: -1, SQLSTATE XJ040, XSQLERRMC: Failed to start database . see the next exception for more details" While playing around trying to get the database back online again, i noticed that when setting up a jdbc:derby network connection, that when the wizard ask to select a schema from the database, it by default choose the "APP" schema and my schema is not available in the list. I then renamed the database folder, of the database, set a new jdbc:derby connection up to the database and all of a sudden my schema is available, and everything is back to normal again. What happened? 1. After a truncate of a table, the schema is gone? 2. After renaming the database folder, the schema is back? Regards Theuns Heydenrych
Perhaps a stupid question
Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column? I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :). And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ? My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable. Thanks, -Ron
Unable to connect. Cannot establish connection to
I would appreciate it if somebody can give me explanation of what happend with my derby database. The database was operational for sometime without any troubles, and was behaving. Today i removed a record from a table by using the "Delete Selected Record(s)" button from the SQL Command page in Netbeans. I may had finger trouble and used the "Truncate Table" button. But all of a sudden the table disappeared from the list, i decided then to close everything and try to connect again, where i get the following message: "Unable to connect. Cannot establish a connection to using org.apache.derby.jdbc.Clientdriver(DERBY SQL Error: SQLCODE: -1, SQLSTATE XJ040, XSQLERRMC: Failed to start database . see the next exception for more details" While playing around trying to get the database back online again, i noticed that when setting up a jdbc:derby network connection, that when the wizard ask to select a schema from the database, it by default choose the "APP" schema and my schema is not available in the list. I then renamed the database folder, of the database, set a new jdbc:derby connection up to the database and all of a sudden my schema is available, and everything is back to normal again. What happened? 1. After a truncate of a table, the schema is gone? 2. After renaming the database folder, the schema is back? Regards Theuns Heydenrych
