You are viewing a plain text version of this content. The canonical link for it is here.
Posted to log4j-dev@logging.apache.org by Ceki Gülcü <ce...@qos.ch> on 2002/05/22 00:17:22 UTC
Comments on JDBCAppender
Kevin and others,
Given the recent remarks on JDBCAppender performance, one possibility
for improvement is to use prepared statements and batch processing.
I have created a table called logEvents for testing purposes:
CREATE TABLE logEvents (
loggerName varchar(200),
timestamp bigint,
levelName varChar(32),
message varchar(1024),
NDC varchar(1024)
);
(I am using PostgresQL 7.1.3).
Here is a some JDBC code to exercise the table.
import java.sql.*;
import org.apache.log4j.*;
import org.apache.log4j.spi.*;
public class JDBCTest {
public static void main(String[] args) throws Exception {
Logger root = Logger.getRootLogger();
Connection conn = null;
String driver = "org.postgresql.Driver";
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(args[0], args[1], args[2]);
double start;
double end;
int LOOP_LEN = 100;
int counter = 0;
// -------------------------- Normal statement:
start = System.currentTimeMillis();
Statement s = null;
for(int i = 0; i < LOOP_LEN; i++) {
s = conn.createStatement();
NDC.push("counter "+(counter++));
LoggingEvent event = new LoggingEvent(Category.class.getName(),
root, Level.DEBUG,
"message " + i,
null);
s.executeUpdate("INSERT INTO logEvents (loggerName, timestamp, "
+ "levelName, message, NDC)"
+ "VALUES ('"+ event.logger.getName()+ "', "
+ event.timeStamp + ", '"
+ event.level.toString() + "', '"
+ event.getRenderedMessage() + "', '"
+ event.getNDC() + "')");
NDC.pop();
}
s.close();
end = System.currentTimeMillis();
System.out.println("Overall (simple statement) : "+(end-start));
System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
microsecs.");
PreparedStatement stmt;
// Prepared statement
start = System.currentTimeMillis();
stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
timestamp, "
+ "levelName, message, NDC)"
+ "VALUES (?, ?, ?, ?, ?)");
for(int i = 0; i < LOOP_LEN; i++) {
NDC.push("counter "+(counter++));
MDC.put("hello", "x");
LoggingEvent event = new LoggingEvent(Category.class.getName(),
root, Level.DEBUG,
"message " + i,
null);
stmt.setString(1, event.logger.getName());
stmt.setLong(2, event.timeStamp);
stmt.setString(3, event.level.toString());
stmt.setString(4, event.getRenderedMessage());
stmt.setString(5, event.getNDC());
NDC.pop();
stmt.executeUpdate();
}
stmt.close();
end = System.currentTimeMillis();
System.out.println("Overall (prepared statement) : "+(end-start));
System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
microsecs.");
// --- Batch mode -----------------------
start = System.currentTimeMillis();
stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
timestamp, "
+ "levelName, message, NDC)"
+ "VALUES (?, ?, ?, ?, ?)");
for(int i = 0; i < LOOP_LEN; i++) {
NDC.push("counter "+(counter++));
LoggingEvent event = new LoggingEvent(Category.class.getName(),
root, Level.DEBUG,
"message" + i,
null);
stmt.setString(1, event.logger.getName());
stmt.setLong(2, event.timeStamp);
stmt.setString(3, event.level.toString());
stmt.setString(4, event.getRenderedMessage());
stmt.setString(5, event.getNDC());
NDC.pop();
stmt.addBatch();
}
stmt.executeBatch();
stmt.close();
end = System.currentTimeMillis();
System.out.println("Overall (prepared statement) : "+(end-start));
System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
microsecs.");
conn.close();
}
}
Running this test code gives:
~/ >java JDBCTest jdbc:postgresql://somehost/someDatabaseName ceki ****
Overall (simple statement) : 411.0
Average: 4110.0in microsecs.
Overall (prepared statement) : 421.0
Average: 4210.0in microsecs.
Overall (prepared statement) : 150.0
Average: 1500.0in microsecs.
As you can see prepared batch statements are significantly faster (3x)
than prepared statements, whereas prepared statements are only
marginally faster than simple statements. These results depend on the
database and your mileage may vary.
Notice that I did not insert the MDC or the trowable string
representation. IMHO, these fields are best represented as blobs
which PostgresQL version 7.1.3 does not support, although 7.2 does
reportedly support them.
(The only alternative I see to BLOBs are bit fields or arrays.)
Once we settle on the best representation of the table, I think a
table structure (table name and column names) should be fixed once and
fall all. This would allow other components to query the database and
present the results to the user in a convenient form. This cannot be
done if the underlying table name and columns are not fixed.
That is it for the moment.
--
Ceki
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Niclas Hedhman <ni...@ewarna.com>.
On Tuesday 28 May 2002 05:45, Ceki Gülcü wrote:
> At 14:48 25.05.2002 +0800, Niclas Hedhman wrote:
> >When I say "evil" I mean "current specification is not strict enough and
> >allows for the implementation vendors to extend it in all kinds of
> >incompatible directions", hence making "pluggability" a paper-tiger.
>
> Log4j-dev mailing list enters the Guiness world records book for the
> first Java vs. SQL discussion. By the way, Python rules! Long live Fortran.
:o) I know this is WAY off topic, but I can't resist. It has been boiling in
me for quite a while.
SQL92 is a standard(!), but has three levels, and all vendors implement a
mishmash from each level plus their own extensions, and yet unshamely claim
SQL92 compliance. If you develop against a DB, you end up with the smallest
common denominator, which can be compared to the C/C++ war of the late 80's
early 90's, until JAVA showed up and rectified it. IF SQL wasn't so
incompatible, the need of JDO wouldn't be a big thing.
> If I understand correctly Niclas, you just volunteered to write a
> fixed-table JDBCAppender and a JDBCRetreiver, did you?
Well, temptation is great, but at the moment our development schedule is worse
than ever. Perhaps after July 1st deadline.
> >My highly opinionated assertion is most humble, of course.
>
> If it is backed up by code then it no longer needs to be humble.
You must be a gambling man ;o) "Put your money where your mouth is..." Very
challanging.
Niclas
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Ceki Gülcü <ce...@qos.ch>.
At 14:48 25.05.2002 +0800, Niclas Hedhman wrote:
>On Friday 24 May 2002 23:37, Kevin Steppe wrote:
> > Niclas Hedhman wrote:
> > > SQL is by default an evil thing and should not be exposed to users, nor
> > > programmers.
> >
> > That is rather opinionated, just like the following:
> > SQL is by default an incredibly elegant, simple, and powerful
> > programming language -- in some ways more elegant and powerful than
> > Java. All programmers would benefit from learning SQL.
>
>When I say "evil" I mean "current specification is not strict enough and
>allows for the implementation vendors to extend it in all kinds of
>incompatible directions", hence making "pluggability" a paper-tiger.
Log4j-dev mailing list enters the Guiness world records book for the
first Java vs. SQL discussion. By the way, Python rules! Long live Fortran.
>However, I am arguing that a rather fixed format is established, or ChainSaw
>and others will never manage to pick up the info, and you would discourage
>small 3rd party tools to analyze the data.
>
>So, to re-phrase;
>Instead of talking about JDBC appenders, let met introduce the concept of
>Retrievers, which are implemented as a companion to Appenders.
>The rationale is that exactly the same is true for non-JDBC appenders,
>such as
>for XML and even text files.
>If the Retriever interface is established, then it is up to the Appender
>writer to provide, or risking that people won't use it due to inaccessability
>of the output.
>
>As for JDBCAppender, I think it will evolve a little bit, so that the
>Retriever interface would/can be supported, and free format Insert statements
>are probably not reasonable.
If I understand correctly Niclas, you just volunteered to write a
fixed-table JDBCAppender and a JDBCRetreiver, did you?
>My highly opinionated assertion is most humble, of course.
If it is backed up by code then it no longer needs to be humble.
>Niclas
--
Ceki
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Niclas Hedhman <ni...@ewarna.com>.
On Tuesday 28 May 2002 13:57, Kevin Steppe wrote:
> The assanine attitude of the vendors towards the standards is indeed
> evil. I agree with you whole heartedly on that one. In the abstract
> however, I think SQL is wonderful.
Then we basically agree. If you could enforce the SQL standard onto the
vendors, then I wouldn't mind SQL either.
> > However, I am arguing that a rather fixed format is established, or
> > ChainSaw and others will never manage to pick up the info, and you would
> > discourage small 3rd party tools to analyze the data.
>
> This is why I proposed the 2 tier implementation.
> I really like the Retrievers idea! The Retrievers pattern should be
> able to read the configuration files so as to properly set itself up. I
> still feel that the Layout pattern is more important than the actual
> appender in reading log information, however.
Well, the Retriever (once it get instantiated) should know which Appenders it
is compatible with and be able to locate that information in the
Configuration. BUT, on the other hand it may be that the Retriever is not
executing at the same host and doesn't have access to the Configuration
in-JVM, and then it may be that several configuration files must be merged
inside, for instance, ChainSaw, which may be problematic.
I have no clue at this point in time how to proceed, and time limitations
prevent me to concentrate whole-heartedly on it.
Niclas
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Kevin Steppe <ks...@pacbell.net>.
Niclas Hedhman wrote:
> On Friday 24 May 2002 23:37, Kevin Steppe wrote:
>
>> Niclas Hedhman wrote:
>>
>>> SQL is by default an evil thing and should not be exposed to users, nor
>>> programmers.
>>
>> That is rather opinionated, just like the following:
>> SQL is by default an incredibly elegant, simple, and powerful
>> programming language -- in some ways more elegant and powerful than
>> Java. All programmers would benefit from learning SQL.
>
>
> When I say "evil" I mean "current specification is not strict enough and
> allows for the implementation vendors to extend it in all kinds of
> incompatible directions", hence making "pluggability" a paper-tiger.
The assanine attitude of the vendors towards the standards is indeed
evil. I agree with you whole heartedly on that one. In the abstract
however, I think SQL is wonderful.
>
>
>>> I suggest that the SQL is completely hidden from Log4J, so that each
>>> JDBC-using appender has a method, or a client class, that takes care of
>>> the retrieval of the exact same information as what can be stored, plus
>>> some basic search methods (not SQL based) and optionally(!) have an
>>> exposure of SQL statement executions.
>>
>> Under the current design appenders in log4j only store LoggingEvents not
>> retrieve them, so it would be rather odd for the JDBCAppender to have
>> search methods. Log reading is delegated to Chainsaw/user/etc. In fact
>> the log formats are primarily the domain of the Layout classes, not the
>> appenders. I believe that this was a very wise design choice. I
>> suspect it's useful for appenders to expose information about their
>> output formating/etc for the benefit of a reader, but I'll let Chainsaw
>> folks take authority on that one.
>
>
> Badly formulated on my part.
> The MySQLAppender, should be accompanied by a MySQLRetriever, that is
> pluggable into Chainsaw and other viewers. This is not violating the Log4J
> design philosophy, because the SocketAppenders has the corresponding classes
> "for the other end", which in your argument should not be the case.
>
>
>> The programmer writes: log.debug("a debug message") [no SQL]
>> The deployment person puts in the config: sql=insert into logTable
>> (logMessage) values ('%m') [only enough to pick the right output]
>> This supports all DBs, and the SQL is completely hidden from log4j and
>> it's user.
>
>
> If the deployer is allowed to send the log record into any database in any
> format, you yell "Bravo", and I whisper "too bad". I will bet that most
> deployers only require that he/she specifies the Host+port+DB and the name of
> the Table (plus user/pass of course).
>
> However, I am arguing that a rather fixed format is established, or ChainSaw
> and others will never manage to pick up the info, and you would discourage
> small 3rd party tools to analyze the data.
This is why I proposed the 2 tier implementation.
>
> So, to re-phrase;
> Instead of talking about JDBC appenders, let met introduce the concept of
> Retrievers, which are implemented as a companion to Appenders.
> The rationale is that exactly the same is true for non-JDBC appenders, such as
> for XML and even text files.
> If the Retriever interface is established, then it is up to the Appender
> writer to provide, or risking that people won't use it due to inaccessability
> of the output.
>
> As for JDBCAppender, I think it will evolve a little bit, so that the
> Retriever interface would/can be supported, and free format Insert statements
> are probably not reasonable.
I really like the Retrievers idea! The Retrievers pattern should be
able to read the configuration files so as to properly set itself up. I
still feel that the Layout pattern is more important than the actual
appender in reading log information, however.
What are other people's opinions on the Retrievers idea?
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Niclas Hedhman <ni...@ewarna.com>.
On Friday 24 May 2002 23:37, Kevin Steppe wrote:
> Niclas Hedhman wrote:
> > SQL is by default an evil thing and should not be exposed to users, nor
> > programmers.
>
> That is rather opinionated, just like the following:
> SQL is by default an incredibly elegant, simple, and powerful
> programming language -- in some ways more elegant and powerful than
> Java. All programmers would benefit from learning SQL.
When I say "evil" I mean "current specification is not strict enough and
allows for the implementation vendors to extend it in all kinds of
incompatible directions", hence making "pluggability" a paper-tiger.
> > I suggest that the SQL is completely hidden from Log4J, so that each
> > JDBC-using appender has a method, or a client class, that takes care of
> > the retrieval of the exact same information as what can be stored, plus
> > some basic search methods (not SQL based) and optionally(!) have an
> > exposure of SQL statement executions.
>
> Under the current design appenders in log4j only store LoggingEvents not
> retrieve them, so it would be rather odd for the JDBCAppender to have
> search methods. Log reading is delegated to Chainsaw/user/etc. In fact
> the log formats are primarily the domain of the Layout classes, not the
> appenders. I believe that this was a very wise design choice. I
> suspect it's useful for appenders to expose information about their
> output formating/etc for the benefit of a reader, but I'll let Chainsaw
> folks take authority on that one.
Badly formulated on my part.
The MySQLAppender, should be accompanied by a MySQLRetriever, that is
pluggable into Chainsaw and other viewers. This is not violating the Log4J
design philosophy, because the SocketAppenders has the corresponding classes
"for the other end", which in your argument should not be the case.
> The programmer writes: log.debug("a debug message") [no SQL]
> The deployment person puts in the config: sql=insert into logTable
> (logMessage) values ('%m') [only enough to pick the right output]
> This supports all DBs, and the SQL is completely hidden from log4j and
> it's user.
If the deployer is allowed to send the log record into any database in any
format, you yell "Bravo", and I whisper "too bad". I will bet that most
deployers only require that he/she specifies the Host+port+DB and the name of
the Table (plus user/pass of course).
However, I am arguing that a rather fixed format is established, or ChainSaw
and others will never manage to pick up the info, and you would discourage
small 3rd party tools to analyze the data.
So, to re-phrase;
Instead of talking about JDBC appenders, let met introduce the concept of
Retrievers, which are implemented as a companion to Appenders.
The rationale is that exactly the same is true for non-JDBC appenders, such as
for XML and even text files.
If the Retriever interface is established, then it is up to the Appender
writer to provide, or risking that people won't use it due to inaccessability
of the output.
As for JDBCAppender, I think it will evolve a little bit, so that the
Retriever interface would/can be supported, and free format Insert statements
are probably not reasonable.
My highly opinionated assertion is most humble, of course.
Niclas
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Kevin Steppe <ks...@pacbell.net>.
Niclas Hedhman wrote:
> Sorry, but I don't buy this argument.
> SQL is by default an evil thing and should not be exposed to users, nor
> programmers.
That is rather opinionated, just like the following:
SQL is by default an incredibly elegant, simple, and powerful
programming language -- in some ways more elegant and powerful than
Java. All programmers would benefit from learning SQL.
Now that doesn't mean that all programmers -should- learn SQL or need to
in order to log into a database (see below for a continuation of this).
Heck, I would benefit from learning compiler theory, which doesn't mean
I'm going to do it.
>
> The others suggestion that a prefixed table format would improve the chances
> of additional tools is extremely valid.
>
> I suggest that the SQL is completely hidden from Log4J, so that each
> JDBC-using appender has a method, or a client class, that takes care of the
> retrieval of the exact same information as what can be stored, plus some
> basic search methods (not SQL based) and optionally(!) have an exposure of
> SQL statement executions.
Under the current design appenders in log4j only store LoggingEvents not
retrieve them, so it would be rather odd for the JDBCAppender to have
search methods. Log reading is delegated to Chainsaw/user/etc. In fact
the log formats are primarily the domain of the Layout classes, not the
appenders. I believe that this was a very wise design choice. I
suspect it's useful for appenders to expose information about their
output formating/etc for the benefit of a reader, but I'll let Chainsaw
folks take authority on that one.
>
> Then it is a matter of creating variants for different databases, possibly
> including a very generic one that can easily be extended by the project
> programmers. The "user", very often i.e deployment people, tech support
> people and so forth, has to know what DB they want to store to, pick up the
> appropriate DB Appender, and go,go,go...
Exactly. And this is the current state of affairs.
The programmer writes: log.debug("a debug message") [no SQL]
The deployment person puts in the config: sql=insert into logTable
(logMessage) values ('%m') [only enough to pick the right output]
This supports all DBs, and the SQL is completely hidden from log4j and
it's user.
>
> By having a proper client API to such appenders, it is possible to not only
> write user interfaces, but often, more importantly, allow JUnit testing to be
> more thorough in its response evaluation.
>
> Along the same line, JDO is such a powerful SQL abstractor, which I don't
> recommend to be part of the main distribution, but perhaps Log4J is getting
> to the point where such additional support is in separate JARs (like Ant) for
> people who wants/needs it. JDO structures has the additional advantage that
> the DB structure is, claimed to be, multi-language supported. Meaning a OMG
> Data Object Model compliant implementation in a non-Java language can be
> processing the output. (Haven't seen that yet, but the Java side of things is
> very good from a persistence point of view, albeit implementation specific.)
I've heard good things about JDO, which is the extent of my knowledge
about it. I'd be happy to be enlightened.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Niclas Hedhman <ni...@ewarna.com>.
Sorry, but I don't buy this argument.
SQL is by default an evil thing and should not be exposed to users, nor
programmers.
The others suggestion that a prefixed table format would improve the chances
of additional tools is extremely valid.
I suggest that the SQL is completely hidden from Log4J, so that each
JDBC-using appender has a method, or a client class, that takes care of the
retrieval of the exact same information as what can be stored, plus some
basic search methods (not SQL based) and optionally(!) have an exposure of
SQL statement executions.
Then it is a matter of creating variants for different databases, possibly
including a very generic one that can easily be extended by the project
programmers. The "user", very often i.e deployment people, tech support
people and so forth, has to know what DB they want to store to, pick up the
appropriate DB Appender, and go,go,go...
By having a proper client API to such appenders, it is possible to not only
write user interfaces, but often, more importantly, allow JUnit testing to be
more thorough in its response evaluation.
Along the same line, JDO is such a powerful SQL abstractor, which I don't
recommend to be part of the main distribution, but perhaps Log4J is getting
to the point where such additional support is in separate JARs (like Ant) for
people who wants/needs it. JDO structures has the additional advantage that
the DB structure is, claimed to be, multi-language supported. Meaning a OMG
Data Object Model compliant implementation in a non-Java language can be
processing the output. (Haven't seen that yet, but the Java side of things is
very good from a persistence point of view, albeit implementation specific.)
My 0.02 euros worth...
Niclas
On Thursday 23 May 2002 03:26, Kevin Steppe wrote:
> Ceki et al.,
>
> First, my apologies for being terse in my message, I'm just short on
> time this month.
>
> Problems:
> addBatch is an optional method in Statement. It is not guarenteed to
> be implemented by all drivers (in fact it is not implemented in the
> mySQL driver I use).
>
> Your logEvents table definition is not valid on all databases. And
> in fact it's not valid on -most- databases. And any table we come up
> with will lack something a user may want, until we have some much that
> it's overly bloated for 95% of the users. Then we would likely need to
> write several different versions of the table creation to support the
> different databases.
>
> Note that your prepared statements were in fact slower than simple
> statements and not all databases truely support prepared statements
> (mySQL is the only slacker that I know of but there may be others).
>
> Suggestions/Solutions/Previous design considerations:
>
> When I first wrote the JDBCAppender I knew that it would be used on a
> variety of databases in different circumstances for users with different
> logging needs. The base level JDBCAppender needs to support that level
> of generality. The only way to support that variety is to leave the sql
> and the table definition to the user. The current JDBCAppender supports
> this in an easy and straight forward way. In particular, if users want
> prepared statements they can set the sql to call a stored procedure of
> their choice (in essence what the prepared statement does). The
> advantage here is that the users' table can be matched to their
> particular needs -- for example, storing Thread but not NDC, or Class &
> line number, or an Event extension... It also allows them to log to an
> existing table in a legacy installation.
> Due to the problems above and the design goals in the previous
> paragraph I am strong opposed to tieing the basic JDBCAppender to a
> particular table definition.
>
> There clearly is value to having other components able to query the
> database for log events. Chainsaw is the obvious example. Someone just
> posted to the user list that Chainsaw can already do this. It's my
> opinion that the database reader bears a burden of being configurable to
> the proper database.
> Alternately, log4j could have a -preffered- table definition. A few
> scripts and default configuration files could be included with the
> distribution for this preferred definition. Then log4j would integrate
> on that 'out of the box', but would easily be configurable to something
> else. Richard Post's points about views and item-mapping are
> particularly relevant here.
>
> Along those same lines, making the JDBCAppender asynchronous via the
> AsyncAppender is clearly of value to users who can't wait for the insert
> time. An example config file in the distribution should solve this
> issue very neatly.
>
> Ceki Gülcü wrote:
> > Kevin and others,
> >
> > Given the recent remarks on JDBCAppender performance, one possibility
> > for improvement is to use prepared statements and batch processing.
> >
> > I have created a table called logEvents for testing purposes:
> >
> > CREATE TABLE logEvents (
> > loggerName varchar(200),
> > timestamp bigint,
> > levelName varChar(32),
> > message varchar(1024),
> > NDC varchar(1024)
> > );
> >
> > (I am using PostgresQL 7.1.3).
> >
> > Here is a some JDBC code to exercise the table.
> >
> > import java.sql.*;
> > import org.apache.log4j.*;
> > import org.apache.log4j.spi.*;
> >
> > public class JDBCTest {
> >
> > public static void main(String[] args) throws Exception {
> >
> > Logger root = Logger.getRootLogger();
> >
> > Connection conn = null;
> > String driver = "org.postgresql.Driver";
> >
> > Class.forName(driver).newInstance();
> >
> > conn = DriverManager.getConnection(args[0], args[1], args[2]);
> >
> > double start;
> > double end;
> > int LOOP_LEN = 100;
> > int counter = 0;
> >
> > // -------------------------- Normal statement:
> > start = System.currentTimeMillis();
> > Statement s = null;
> >
> > for(int i = 0; i < LOOP_LEN; i++) {
> > s = conn.createStatement();
> > NDC.push("counter "+(counter++));
> > LoggingEvent event = new LoggingEvent(Category.class.getName(),
> > root, Level.DEBUG,
> > "message " + i,
> > null);
> >
> > s.executeUpdate("INSERT INTO logEvents (loggerName, timestamp, "
> > + "levelName, message, NDC)"
> > + "VALUES ('"+ event.logger.getName()+ "', "
> > + event.timeStamp + ", '"
> > + event.level.toString() + "', '"
> > + event.getRenderedMessage() + "', '"
> > + event.getNDC() + "')");
> > NDC.pop();
> > }
> > s.close();
> > end = System.currentTimeMillis();
> > System.out.println("Overall (simple statement) : "+(end-start));
> > System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> > microsecs.");
> >
> > PreparedStatement stmt;
> >
> > // Prepared statement
> > start = System.currentTimeMillis();
> > stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
> > timestamp, "
> > + "levelName, message, NDC)"
> > + "VALUES (?, ?, ?, ?, ?)");
> >
> > for(int i = 0; i < LOOP_LEN; i++) {
> > NDC.push("counter "+(counter++));
> > MDC.put("hello", "x");
> > LoggingEvent event = new LoggingEvent(Category.class.getName(),
> > root, Level.DEBUG,
> > "message " + i,
> > null);
> >
> > stmt.setString(1, event.logger.getName());
> > stmt.setLong(2, event.timeStamp);
> > stmt.setString(3, event.level.toString());
> > stmt.setString(4, event.getRenderedMessage());
> >
> > stmt.setString(5, event.getNDC());
> > NDC.pop();
> > stmt.executeUpdate();
> > }
> > stmt.close();
> > end = System.currentTimeMillis();
> > System.out.println("Overall (prepared statement) : "+(end-start));
> > System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> > microsecs.");
> >
> > // --- Batch mode -----------------------
> > start = System.currentTimeMillis();
> > stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
> > timestamp, "
> > + "levelName, message, NDC)"
> > + "VALUES (?, ?, ?, ?, ?)");
> >
> > for(int i = 0; i < LOOP_LEN; i++) {
> > NDC.push("counter "+(counter++));
> > LoggingEvent event = new LoggingEvent(Category.class.getName(),
> > root, Level.DEBUG,
> > "message" + i,
> > null);
> >
> > stmt.setString(1, event.logger.getName());
> > stmt.setLong(2, event.timeStamp);
> > stmt.setString(3, event.level.toString());
> > stmt.setString(4, event.getRenderedMessage());
> >
> > stmt.setString(5, event.getNDC());
> > NDC.pop();
> > stmt.addBatch();
> > }
> > stmt.executeBatch();
> > stmt.close();
> > end = System.currentTimeMillis();
> > System.out.println("Overall (prepared statement) : "+(end-start));
> > System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> > microsecs.");
> >
> > conn.close();
> >
> > }
> > }
> >
> >
> > Running this test code gives:
> >
> > ~/ >java JDBCTest jdbc:postgresql://somehost/someDatabaseName ceki ****
> >
> > Overall (simple statement) : 411.0
> > Average: 4110.0in microsecs.
> >
> > Overall (prepared statement) : 421.0
> > Average: 4210.0in microsecs.
> >
> > Overall (prepared statement) : 150.0
> > Average: 1500.0in microsecs.
> >
> >
> > As you can see prepared batch statements are significantly faster (3x)
> > than prepared statements, whereas prepared statements are only
> > marginally faster than simple statements. These results depend on the
> > database and your mileage may vary.
> >
> > Notice that I did not insert the MDC or the trowable string
> > representation. IMHO, these fields are best represented as blobs
> > which PostgresQL version 7.1.3 does not support, although 7.2 does
> > reportedly support them.
> >
> > (The only alternative I see to BLOBs are bit fields or arrays.)
> >
> > Once we settle on the best representation of the table, I think a
> > table structure (table name and column names) should be fixed once and
> > fall all. This would allow other components to query the database and
> > present the results to the user in a convenient form. This cannot be
> > done if the underlying table name and columns are not fixed.
> >
> > That is it for the moment.
> >
> >
> > --
> > Ceki
> >
> >
> > --
> > To unsubscribe, e-mail:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Ceki Gülcü <ce...@qos.ch>.
At 23:06 27.05.2002 -0700, Kevin Steppe wrote:
>>Thank you very much. You can find the JDBC 2.0 spec at
>>http://java.sun.com/products/jdk/1.2/docs/guide/jdbc/index.html Note
>>that there is no section 6.2 in this version of the spec.
>
>
>I think we should be working off the 3.0 spec at this point. It is
>intended to be backwards compatible and thus an acceptable reference for log4j.
Isn't the JDBC 3.0 very recent? How can the new features of a recent
spec. can be compatible with previous versions of the spec?
I better read JDBC 3.0 before asking other silly questions.
>>We could state the requirements on the database structure without
>>creating it. This is somewhat similar to the JDBC Realm in Tomcat. See
>>http://jakarta.apache.org/tomcat/tomcat-4.0-doc/realm-howto.html for more
>>details.
>
>I will read up on this in the next day or so.
Excellent. It will take you about 45 seconds.
>>>Have a two-layer solution. First a totally flexible JDBCAppender, such
>>>as the current implementation, for the sql savvy user. Second, build a
>>>defined-table implementation on top of that -- as a subclass or
>>>whatever. This way the savvy user gets some core functionality, the
>>>basic user gets a well verified implementation, and other components
>>>have a table definition they can write to.
>>
>>How is the current implementation of the JDBCAppender (based on
>>patterns) compatible with batch statements? Am I missing something
>>here?
>
>
>the addBatch(String sql) command will take any sql statement (including
>the current layout-based creations) and place them all into a batch for
>execution. This could (and should) be done within the flushBuffer()
>method of the JDBCAppender. Use of addBatch would of course be contingent
>on the DatabaseMetaData indicating support for it. This would take a
>small update to the current code. Would end of the week be early enough
>for this improvement?
Time is almost never an issue in log4j development. We deliver good
software when it is good and ready. Since log4j is used by so many
developers to detect and isolate problems, log4j itself must be
reliable. Thus, it is better to take your time and not rush. Of
course, this does not mean we can fall asleep either... :-)
The JDBCAppender must also log exceptions (throwables to be exact) if
the logging event contains any. We can't just ignore bug #8529 forever,
can we? (http://nagoya.apache.org/bugzilla/show_bug.cgi?id=8529)
>For other things:
> I can put forward an example of my two-tier idea sometime in June. I
> could include a Retriever implementation at that time as well if log4j
> developers want to go that direction.
The Retriever idea is very sound although at this stage I am more
concerned about the JDBCAppender implementation.
--
Ceki
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Kevin Steppe <ks...@pacbell.net>.
>
> Thank you very much. You can find the JDBC 2.0 spec at
> http://java.sun.com/products/jdk/1.2/docs/guide/jdbc/index.html Note
> that there is no section 6.2 in this version of the spec.
I think we should be working off the 3.0 spec at this point. It is
intended to be backwards compatible and thus an acceptable reference for
log4j.
>
>> Now fortunately the DatabaseMetaData will tell us whether it is
>> supported or not and we can cover both cases intelligently.
>
>
> Right.
>
>>> Yes, table creation syntax admits variation between the different DNMS
>>> vendors. However, table creation is not under the responsibility of
>>> JDBCAppender.
>>
>>
>> However, if we confine the JDBCAppender to a single pre-determined
>> definition, then it is our/log4j's responsibility to provide that
>> syntax.
>
>
> We could state the requirements on the database structure without
> creating it. This is somewhat similar to the JDBC Realm in Tomcat. See
> http://jakarta.apache.org/tomcat/tomcat-4.0-doc/realm-howto.html for
> more details.
I will read up on this in the next day or so.
>
>> Have a two-layer solution. First a totally flexible JDBCAppender,
>> such as the current implementation, for the sql savvy user. Second,
>> build a defined-table implementation on top of that -- as a subclass
>> or whatever. This way the savvy user gets some core functionality,
>> the basic user gets a well verified implementation, and other
>> components have a table definition they can write to.
>
>
> How is the current implementation of the JDBCAppender (based on
> patterns) compatible with batch statements? Am I missing something
> here?
the addBatch(String sql) command will take any sql statement (including
the current layout-based creations) and place them all into a batch for
execution. This could (and should) be done within the flushBuffer()
method of the JDBCAppender. Use of addBatch would of course be
contingent on the DatabaseMetaData indicating support for it. This
would take a small update to the current code. Would end of the week be
early enough for this improvement?
For other things:
I can put forward an example of my two-tier idea sometime in June. I
could include a Retriever implementation at that time as well if log4j
developers want to go that direction.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Ceki Gülcü <ce...@qos.ch>.
At 08:14 24.05.2002 -0700, Kevin Steppe wrote:
>>>Problems:
>>> addBatch is an optional method in Statement. It is not guarenteed to
>>> be implemented by all drivers (in fact it is not implemented in the
>>> mySQL driver I use).
>>> Your logEvents table definition is not valid on all databases. And
>>> in fact it's not valid on -most- databases. And any table we come up
>>> with will lack something a user may want, until we have some much that
>>> it's overly bloated for 95% of the users. Then we would likely need to
>>> write several different versions of the table creation to support the
>>> different databases.
>>
>>addBatch is an optional method? Version 2.0 of the JDBC spec. clearly
>>mentions Batch statements in Chapter 6. Can you please point me to the
>>where in the JDBC spec. Batch Statements are declared as optional?
>
>JDBC2.1 spec (I couldn't find 2.0 on the sun site):
>"6.2 What's Required
>
>Support for batch updates is optional. If a JDBC driver supports batch
>updates, the the DatabaseMetaData.supportsBatchUpdates() method must
>return true, else it must return false. In addition, to preserve backward
>compatibility, JDBC drivers that do not continue processing after a
>failure are not required to return a value of -2 as described in Section
>6.1, however, this is encouraged. JDBC drivers that continue processing
>are required to support both of the negative return values.
>
>Note: In the future, the JDBC API shall define symbolic constatns for the
>negative array entry values described in Section 6.1. These values have
>been added as an addendum to the original JDBC2.0 API specification."
>
>Also note JDBC 3.0 final release Chapter 6: "Any features not identified
>here [add Batch is not listed] are optional. In general, a driver is not
>required to implement any feature that its underlying data source does not
>support."
>
>And of course the JavaDoc (where I first found the optional note):
>http://java.sun.com/j2se/1.4/docs/api/java/sql/Statement.html#addBatch(java.lang.String)
Thank you very much. You can find the JDBC 2.0 spec at
http://java.sun.com/products/jdk/1.2/docs/guide/jdbc/index.html Note
that there is no section 6.2 in this version of the spec.
>Now fortunately the DatabaseMetaData will tell us whether it is supported
>or not and we can cover both cases intelligently.
Right.
>>Yes, table creation syntax admits variation between the different DNMS
>>vendors. However, table creation is not under the responsibility of
>>JDBCAppender.
>
>However, if we confine the JDBCAppender to a single pre-determined
>definition, then it is our/log4j's responsibility to provide that syntax.
We could state the requirements on the database structure without creating
it. This is somewhat similar to the JDBC Realm in Tomcat. See
http://jakarta.apache.org/tomcat/tomcat-4.0-doc/realm-howto.html for more
details.
>>First, what any log4j appender does is tp utput an
>>o.a.log4j.spi.LoggingEvent object to some output device. A
>>JDBCAppender does the same. It outputs a LoggingEvent to a DBMS.
>>Second the number of databases in the world is limited. If we could
>>reliably write to say DB2, MySQL, Oracle, PostgreSQL (note the
>>alphabetical order) we'd be in good shape.
>
>I would add MS-SQL Server and Sybase to that list. Still, as you point
>out, a fairly managable number, all with reasonable adherence to SQL92.
Yes, MS-SQL and Sybase are equally important platforms.
>>As for the argument about optional NDC, class/line number/method name
>>fields, we can expect the table to contain *all* fields but choose not
>>to insert the information if that is what the user desires. This is
>>similar to what is done with SocketAppender where location info is
>>optional but the LoggingEvent contains the field, even when it is
>>null.
>
>I suspect Ceki won't like this suggestion, but I'll make it anyway:
If I don't like it is probably because I am too thick headed to understand
it. :-)
>Have a two-layer solution. First a totally flexible JDBCAppender, such as
>the current implementation, for the sql savvy user. Second, build a
>defined-table implementation on top of that -- as a subclass or
>whatever. This way the savvy user gets some core functionality, the basic
>user gets a well verified implementation, and other components have a
>table definition they can write to.
How is the current implementation of the JDBCAppender (based on
patterns) compatible with batch statements? Am I missing something
here?
--
Ceki
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Kevin Steppe <ks...@pacbell.net>.
>
>> Problems:
>> addBatch is an optional method in Statement. It is not guarenteed
>> to be implemented by all drivers (in fact it is not implemented in
>> the mySQL driver I use).
>> Your logEvents table definition is not valid on all databases. And
>> in fact it's not valid on -most- databases. And any table we come up
>> with will lack something a user may want, until we have some much
>> that it's overly bloated for 95% of the users. Then we would likely
>> need to write several different versions of the table creation to
>> support the different databases.
>
>
> addBatch is an optional method? Version 2.0 of the JDBC spec. clearly
> mentions Batch statements in Chapter 6. Can you please point me to the
> where in the JDBC spec. Batch Statements are declared as optional?
JDBC2.1 spec (I couldn't find 2.0 on the sun site):
"6.2 What's Required
Support for batch updates is optional. If a JDBC driver supports batch
updates, the the DatabaseMetaData.supportsBatchUpdates() method must
return true, else it must return false. In addition, to preserve
backward compatibility, JDBC drivers that do not continue processing
after a failure are not required to return a value of -2 as described in
Section 6.1, however, this is encouraged. JDBC drivers that continue
processing are required to support both of the negative return values.
Note: In the future, the JDBC API shall define symbolic constatns for
the negative array entry values described in Section 6.1. These values
have been added as an addendum to the original JDBC2.0 API specification."
Also note JDBC 3.0 final release Chapter 6: "Any features not identified
here [add Batch is not listed] are optional. In general, a driver is
not required to implement any feature that its underlying data source
does not support."
And of course the JavaDoc (where I first found the optional note):
http://java.sun.com/j2se/1.4/docs/api/java/sql/Statement.html#addBatch(java.lang.String)
Now fortunately the DatabaseMetaData will tell us whether it is
supported or not and we can cover both cases intelligently.
>
> Yes, table creation syntax admits variation between the different DNMS
> vendors. However, table creation is not under the responsibility of
> JDBCAppender.
However, if we confine the JDBCAppender to a single pre-determined
definition, then it is our/log4j's responsibility to provide that syntax.
>
> First, what any log4j appender does is tp utput an
> o.a.log4j.spi.LoggingEvent object to some output device. A
> JDBCAppender does the same. It outputs a LoggingEvent to a DBMS.
>
> Second the number of databases in the world is limited. If we could
> reliably write to say DB2, MySQL, Oracle, PostgreSQL (note the
> alphabetical order) we'd be in good shape.
I would add MS-SQL Server and Sybase to that list. Still, as you point
out, a fairly managable number, all with reasonable adherence to SQL92.
>
> Third, although there are variations in the SQL syntax, the bulk of
> the work of the JDBCAppender consists of sending an INSERT statement
> to the database.
Unless the user wants to speed things up with stored procedures, or do
something fancy.
>
> And as far as I know, in most databases, in particualr as DB2, MySql,
> PostgreSQL (could not check Oracle yet), the syntax of the INSERT
> command is
>
> INSERT INTO tablename (col1, col2, ...) VALUES (val1, val2, ...)
>
> Looks simple enough to me. Unless someone does it first, I'll write an
> appender that can reliably insert complete logging events to a table
> on MySQL and PostgresQL. When that is done, I am sure someone else
> will continue on DB2, Oracle and what have you.
>
>> Note that your prepared statements were in fact slower than simple
>> statements and not all databases truely support prepared statements
>> (mySQL is the only slacker that I know of but there may be others).
>
>
> The difference is minimal and the winner changes between runs.
> Sometimes simple statements are win and sometimes prepared statements
> do, although batch statements are always way ahead.
>
>> Suggestions/Solutions/Previous design considerations:
>>
>> When I first wrote the JDBCAppender I knew that it would be used on
>> a variety of databases in different circumstances for users with
>> different logging needs. The base level JDBCAppender needs to
>> support that level of generality. The only way to support that
>> variety is to leave the sql and the table definition to the user.
>> The current JDBCAppender supports this in an easy and straight
>> forward way. In particular, if users want prepared statements they
>> can set the sql to call a stored procedure of their choice (in
>> essence what the prepared statement does). The advantage here is
>> that the users' table can be matched to their particular needs -- for
>> example, storing Thread but not NDC, or Class & line number, or an
>> Event extension... It also allows them to log to an existing table
>> in a legacy installation.
>> Due to the problems above and the design goals in the previous
>> paragraph I am strong opposed to tieing the basic JDBCAppender to a
>> particular table definition.
>
>
> There are obviously advantages to being totally flexible. On the other
> hand, total flexibility by the way of delegation to the user does not
> give you any foundation to build upon.
The current JDBCAppender does provide a foundation while delegating
flexibility to the user. ~300 lines of code for logging to a database
becomes 4 lines of configuration. I call that a nice foundation. As is
under discussion, however, it does not provide a foundation for other
log4j components.
>
> As for the argument about optional NDC, class/line number/method name
> fields, we can expect the table to contain *all* fields but choose not
> to insert the information if that is what the user desires. This is
> similar to what is done with SocketAppender where location info is
> optional but the LoggingEvent contains the field, even when it is
> null.
I suspect Ceki won't like this suggestion, but I'll make it anyway:
Have a two-layer solution. First a totally flexible JDBCAppender, such
as the current implementation, for the sql savvy user. Second, build a
defined-table implementation on top of that -- as a subclass or
whatever. This way the savvy user gets some core functionality, the
basic user gets a well verified implementation, and other components
have a table definition they can write to.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Ceki Gülcü <ce...@qos.ch>.
At 12:26 22.05.2002 -0700, Kevin Steppe wrote:
>Ceki et al.,
>
>First, my apologies for being terse in my message, I'm just short on time
>this month.
Sure.
>Problems:
> addBatch is an optional method in Statement. It is not guarenteed to
> be implemented by all drivers (in fact it is not implemented in the mySQL
> driver I use).
> Your logEvents table definition is not valid on all databases. And in
> fact it's not valid on -most- databases. And any table we come up with
> will lack something a user may want, until we have some much that it's
> overly bloated for 95% of the users. Then we would likely need to write
> several different versions of the table creation to support the different
> databases.
addBatch is an optional method? Version 2.0 of the JDBC spec. clearly
mentions Batch statements in Chapter 6. Can you please point me to the
where in the JDBC spec. Batch Statements are declared as optional?
Yes, table creation syntax admits variation between the different DNMS
vendors. However, table creation is not under the responsibility of
JDBCAppender.
First, what any log4j appender does is tp utput an
o.a.log4j.spi.LoggingEvent object to some output device. A
JDBCAppender does the same. It outputs a LoggingEvent to a DBMS.
Second the number of databases in the world is limited. If we could
reliably write to say DB2, MySQL, Oracle, PostgreSQL (note the
alphabetical order) we'd be in good shape.
Third, although there are variations in the SQL syntax, the bulk of
the work of the JDBCAppender consists of sending an INSERT statement
to the database.
And as far as I know, in most databases, in particualr as DB2, MySql,
PostgreSQL (could not check Oracle yet), the syntax of the INSERT
command is
INSERT INTO tablename (col1, col2, ...) VALUES (val1, val2, ...)
Looks simple enough to me. Unless someone does it first, I'll write an
appender that can reliably insert complete logging events to a table
on MySQL and PostgresQL. When that is done, I am sure someone else
will continue on DB2, Oracle and what have you.
> Note that your prepared statements were in fact slower than simple
> statements and not all databases truely support prepared statements
> (mySQL is the only slacker that I know of but there may be others).
The difference is minimal and the winner changes between runs. Sometimes
simple statements are win and sometimes prepared statements do, although
batch statements are always way ahead.
>Suggestions/Solutions/Previous design considerations:
>
> When I first wrote the JDBCAppender I knew that it would be used on a
> variety of databases in different circumstances for users with different
> logging needs. The base level JDBCAppender needs to support that level
> of generality. The only way to support that variety is to leave the sql
> and the table definition to the user. The current JDBCAppender supports
> this in an easy and straight forward way. In particular, if users want
> prepared statements they can set the sql to call a stored procedure of
> their choice (in essence what the prepared statement does). The
> advantage here is that the users' table can be matched to their
> particular needs -- for example, storing Thread but not NDC, or Class &
> line number, or an Event extension... It also allows them to log to an
> existing table in a legacy installation.
> Due to the problems above and the design goals in the previous
> paragraph I am strong opposed to tieing the basic JDBCAppender to a
> particular table definition.
There are obviously advantages to being totally flexible. On the other
hand, total flexibility by the way of delegation to the user does not
give you any foundation to build upon.
As for the argument about optional NDC, class/line number/method name
fields, we can expect the table to contain *all* fields but choose not
to insert the information if that is what the user desires. This is
similar to what is done with SocketAppender where location info is
optional but the LoggingEvent contains the field, even when it is
null.
--
Ceki
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Comments on JDBCAppender
Posted by Kevin Steppe <ks...@pacbell.net>.
Ceki et al.,
First, my apologies for being terse in my message, I'm just short on
time this month.
Problems:
addBatch is an optional method in Statement. It is not guarenteed to
be implemented by all drivers (in fact it is not implemented in the
mySQL driver I use).
Your logEvents table definition is not valid on all databases. And
in fact it's not valid on -most- databases. And any table we come up
with will lack something a user may want, until we have some much that
it's overly bloated for 95% of the users. Then we would likely need to
write several different versions of the table creation to support the
different databases.
Note that your prepared statements were in fact slower than simple
statements and not all databases truely support prepared statements
(mySQL is the only slacker that I know of but there may be others).
Suggestions/Solutions/Previous design considerations:
When I first wrote the JDBCAppender I knew that it would be used on a
variety of databases in different circumstances for users with different
logging needs. The base level JDBCAppender needs to support that level
of generality. The only way to support that variety is to leave the sql
and the table definition to the user. The current JDBCAppender supports
this in an easy and straight forward way. In particular, if users want
prepared statements they can set the sql to call a stored procedure of
their choice (in essence what the prepared statement does). The
advantage here is that the users' table can be matched to their
particular needs -- for example, storing Thread but not NDC, or Class &
line number, or an Event extension... It also allows them to log to an
existing table in a legacy installation.
Due to the problems above and the design goals in the previous
paragraph I am strong opposed to tieing the basic JDBCAppender to a
particular table definition.
There clearly is value to having other components able to query the
database for log events. Chainsaw is the obvious example. Someone just
posted to the user list that Chainsaw can already do this. It's my
opinion that the database reader bears a burden of being configurable to
the proper database.
Alternately, log4j could have a -preffered- table definition. A few
scripts and default configuration files could be included with the
distribution for this preferred definition. Then log4j would integrate
on that 'out of the box', but would easily be configurable to something
else. Richard Post's points about views and item-mapping are
particularly relevant here.
Along those same lines, making the JDBCAppender asynchronous via the
AsyncAppender is clearly of value to users who can't wait for the insert
time. An example config file in the distribution should solve this
issue very neatly.
Ceki Gülcü wrote:
>
> Kevin and others,
>
> Given the recent remarks on JDBCAppender performance, one possibility
> for improvement is to use prepared statements and batch processing.
>
> I have created a table called logEvents for testing purposes:
>
> CREATE TABLE logEvents (
> loggerName varchar(200),
> timestamp bigint,
> levelName varChar(32),
> message varchar(1024),
> NDC varchar(1024)
> );
>
> (I am using PostgresQL 7.1.3).
>
> Here is a some JDBC code to exercise the table.
>
> import java.sql.*;
> import org.apache.log4j.*;
> import org.apache.log4j.spi.*;
>
> public class JDBCTest {
>
> public static void main(String[] args) throws Exception {
>
> Logger root = Logger.getRootLogger();
>
> Connection conn = null;
> String driver = "org.postgresql.Driver";
>
> Class.forName(driver).newInstance();
>
> conn = DriverManager.getConnection(args[0], args[1], args[2]);
>
> double start;
> double end;
> int LOOP_LEN = 100;
> int counter = 0;
>
> // -------------------------- Normal statement:
> start = System.currentTimeMillis();
> Statement s = null;
>
> for(int i = 0; i < LOOP_LEN; i++) {
> s = conn.createStatement();
> NDC.push("counter "+(counter++));
> LoggingEvent event = new LoggingEvent(Category.class.getName(),
> root, Level.DEBUG,
> "message " + i,
> null);
>
> s.executeUpdate("INSERT INTO logEvents (loggerName, timestamp, "
> + "levelName, message, NDC)"
> + "VALUES ('"+ event.logger.getName()+ "', "
> + event.timeStamp + ", '"
> + event.level.toString() + "', '"
> + event.getRenderedMessage() + "', '"
> + event.getNDC() + "')");
> NDC.pop();
> }
> s.close();
> end = System.currentTimeMillis();
> System.out.println("Overall (simple statement) : "+(end-start));
> System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> microsecs.");
>
> PreparedStatement stmt;
>
> // Prepared statement
> start = System.currentTimeMillis();
> stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
> timestamp, "
> + "levelName, message, NDC)"
> + "VALUES (?, ?, ?, ?, ?)");
>
> for(int i = 0; i < LOOP_LEN; i++) {
> NDC.push("counter "+(counter++));
> MDC.put("hello", "x");
> LoggingEvent event = new LoggingEvent(Category.class.getName(),
> root, Level.DEBUG,
> "message " + i,
> null);
>
> stmt.setString(1, event.logger.getName());
> stmt.setLong(2, event.timeStamp);
> stmt.setString(3, event.level.toString());
> stmt.setString(4, event.getRenderedMessage());
>
> stmt.setString(5, event.getNDC());
> NDC.pop();
> stmt.executeUpdate();
> }
> stmt.close();
> end = System.currentTimeMillis();
> System.out.println("Overall (prepared statement) : "+(end-start));
> System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> microsecs.");
>
> // --- Batch mode -----------------------
> start = System.currentTimeMillis();
> stmt = conn.prepareStatement("INSERT INTO logEvents (loggerName,
> timestamp, "
> + "levelName, message, NDC)"
> + "VALUES (?, ?, ?, ?, ?)");
>
> for(int i = 0; i < LOOP_LEN; i++) {
> NDC.push("counter "+(counter++));
> LoggingEvent event = new LoggingEvent(Category.class.getName(),
> root, Level.DEBUG,
> "message" + i,
> null);
>
> stmt.setString(1, event.logger.getName());
> stmt.setLong(2, event.timeStamp);
> stmt.setString(3, event.level.toString());
> stmt.setString(4, event.getRenderedMessage());
>
> stmt.setString(5, event.getNDC());
> NDC.pop();
> stmt.addBatch();
> }
> stmt.executeBatch();
> stmt.close();
> end = System.currentTimeMillis();
> System.out.println("Overall (prepared statement) : "+(end-start));
> System.out.println("Average: "+((end-start)*1000)/LOOP_LEN + "in
> microsecs.");
>
> conn.close();
>
> }
> }
>
>
> Running this test code gives:
>
> ~/ >java JDBCTest jdbc:postgresql://somehost/someDatabaseName ceki ****
>
> Overall (simple statement) : 411.0
> Average: 4110.0in microsecs.
>
> Overall (prepared statement) : 421.0
> Average: 4210.0in microsecs.
>
> Overall (prepared statement) : 150.0
> Average: 1500.0in microsecs.
>
>
> As you can see prepared batch statements are significantly faster (3x)
> than prepared statements, whereas prepared statements are only
> marginally faster than simple statements. These results depend on the
> database and your mileage may vary.
>
> Notice that I did not insert the MDC or the trowable string
> representation. IMHO, these fields are best represented as blobs
> which PostgresQL version 7.1.3 does not support, although 7.2 does
> reportedly support them.
>
> (The only alternative I see to BLOBs are bit fields or arrays.)
>
> Once we settle on the best representation of the table, I think a
> table structure (table name and column names) should be fixed once and
> fall all. This would allow other components to query the database and
> present the results to the user in a convenient form. This cannot be
> done if the underlying table name and columns are not fixed.
>
> That is it for the moment.
>
>
> --
> Ceki
>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>