Maximum result set size
From PTAGISWiki
The PTAGIS query builder currently executes queries on behalf of the user within the JVM, marshalling the data through JDBC into the JVM and then zipping the file and saving to the filesystem.
This procedure imposes a limit on the result set that is equal to the size of the JVM minus overhead of the query builder code itself.
A way to eliminate that size limit is to have the query builder hand off the task of performing the query to a process outside the JVM. This is a fundamental, but fairly simple change to make due to the way that the query builder was designed.
Contents |
Java query runner
The bulk of the complexity of the query builder is in the many forms of the user interface that accept and validate parts of the SQL command. The code that actually runs the SQL command is fairly simple.
- log start of the start of the request
- spawn a thread
- the thread reads the sql command
- executes the command
- logs the end of the request
- creates a message in the user's queue
- zips and saves the result
The means of communication to the thread is a Log object that contains the following variables:
Timestamp start;
Timestamp end;
String user;
String repname;
String status;
String sql;
int rowCount;
The Java code that actually runs the query is this:
try {
// log the start time of the query
System.out.println("executeQuery thread is running");
oReq.getRowSet().setCommand(oLog.getSql());
oReq.getRowSet().execute();
oLog.setRowCount(oReq.getRowSet().size());
oLog.setStatus("completed");
System.out.println("executeQuery thread is writing to end log");
boolean result = logEnd(oLog);
System.out.println("executeQuery thread is writing zip");
String shortName = oReq.getSavePath().substring(oReq.getSavePath().lastIndexOf("/")+1);
writeZipOutput(shortName, oReq.getSavePath(), oReq.getRowSet());
System.out.println("executeQuery thread has created the csv.zip");
}
Perl query runner
The code that would run the query outside of the JVM might look like this:
import java.lang.Runtime;
...
try {
System.out.println("executeQuery thread is running");
String sUser = oLog.getUser();
String sRepname = oLog.getRepname();
Runtime rt = Runtime.getRuntime();
rt.exec("/dsk2/ptagis-1.0/bin/query-runner.pl -u " + sUser + " -r " + sRepname);
createMessage(sUser, "Query notification", "Query finished: +"sRepname, "Your query has been executed.");
}
The perl code (Query-runner.pl) would handle the logging, the query execution, and storing the result set in the file system.
The createMessage method (which generates an entry in the user's My Messages folder) was being performed during the final write to the log, but that is now being done by perl.
Dealing with null dates
Testing the perl query runner from the command line, I find that it runs without error, but corrupts the wqb_query_history table when it tries to insert this row:
INSERT INTO wqb_query_history (userid, report_name, start_time, end_time, status, sql) VALUES (?, ?, ?, ?, ?, ?)
The problem seems to be the start_time and end_time fields which are timestamps.
The values that are going into those timestamps are a start time of the form: 2009-03-13 11:36:23.0753 And a null end time. When I provide the null end time as a null string, the perl code works, but the table is corrupted and must be dropped and recreated.
When I specify the end time as the string "null", I get an "invalid timestamp value" error from perl.
When I specify the end time as the string "0000-00-00 00:00:00.00" I get this error:
DBD::Ingres::st execute failed: E_US10CF 0 is not a valid year for a date/time column.
When I provide the same timestamp value for start and end, the perl succeeds with no error and the table is valid and contains the row as inserted.
The problem is with the null timestamp.
I may use the token time of "1900:01:01 00:00:00" as a value for null if I can't use a blank or null value.
I could also copy the start time, but that might be more confusing.
Resolution: don't insert a null
Doug mentioned that I can simply insert the fields for which I know the values and leave the others alone. That works just fine.
I added better error handling ala eval. I figured that passing the sql statement as a commandline parameter to the perl process would be fraught with errors. So I instead will just pass the userid and report name and the perl process will look up the sql statement in the wqb_query_details. But now, that lookup isn't working properly. The problem was that I was trying to query for the sql statement before opening up a database handle. That was easy to fix.
Better resolution: insert a null
Not writing a value for end_time leaves an old end_time and makes a long running query look like it ended before it started. I was able to get a null to overwrite the old end_time by constructing my update like this:
my $update = "UPDATE wqb_query_details SET start_time = ?, end_time = null, status = ?, row_count =
? WHERE userid = \'$logrow{user}\' and report_name = \'$logrow{repname}\'";
print "$update\n" if ($DEBUG);
eval {
$sth = $dbh->prepare($update);
$sth->execute($dbstart, $logrow{status}, 0);
$sth->finish;
$dbh->commit;
};
I had tried passing the null in through the execute(), but that didn't work.
permissions
query-runner.pl fails if it runs under the user "webjsp" as it should. But it works if I run it as the user "pittag". This should be corrected before moving to production.
query-runner.pl needs access to the following tables and views:
- wqb_query_details
- read, write
- wqb_query_history
- read, write
- wqb_obs_details
- read
- wqb_tag_details
- read
- wqb_recap_details
- read
- wqb_mort_details
- read
- wqb_recap_only
- read
The current grants for these tables and views look like this:
- wqb_query_details
GRANT ALL ON TABLE wqb_query_details TO USER websr; GRANT ALL ON TABLE wqb_query_details TO USER webjsp; GRANT ALL ON TABLE wqb_query_details TO USER pittag;
- wqb_query_history
GRANT ALL ON TABLE wqb_query_history TO USER websr; GRANT ALL ON TABLE wqb_query_history TO USER webjsp;
- all others
GRANT SELECT ON wqb_obs_details TO GROUP ptagis_rpt;
It looks like I could either add the group ptagis_rpt to the query_history and query_details tables or I could add the user webjsp to the views. Or I could run as the user pittag, which seems to work.
The path of least downtime is probably adding ptagis_rpt to the query_history and query_details tables. While I'm at it, I should add user pittag to the history table to make it consistent.
Resolution
Updated permissions on wqb_query_details are:
GRANT ALL ON TABLE wqb_query_history TO USER websr; GRANT ALL ON TABLE wqb_query_history TO USER webjsp; GRANT ALL ON TABLE wqb_query_history TO USER pittag; GRANT ALL ON TABLE wqb_query_history TO GROUP ptagis_rpt;
and wqb_query_history:
GRANT ALL ON TABLE wqb_query_history TO USER websr; GRANT ALL ON TABLE wqb_query_history TO USER webjsp; GRANT ALL ON TABLE wqb_query_history TO USER pittag; GRANT ALL ON TABLE wqb_query_history TO GROUP ptagis_rpt;
query-runner.pl now connects in this manner:
"dbi:Ingres:$DB_Name;-Gptagis-rpt"
ready to change queryrunner.java
The perl query launcher is working and ready to be called from the existing java code.
I've been launching query-runner.pl from the command line like this for testing:
query-runner.pl -u rday -r blah
after making sure that I've created a valid query named "blah" in query builder.
I've verified that the beginning and ending logs are updated properly, the query is executed and the results are written to /tmp/qb/$user-$reportname.csv
Even when executed as the user nobody with no environment variables set, it runs successfully.
Testing invoking perl query runner from java
I made the changes to the java queryRunner class as follows:
diff -r1.21 queryRunner.java
14a15
> import java.lang.Runtime;
373d373
< // log the start time of the query
375,385c375,381
< oReq.getRowSet().setCommand(oLog.getSql());
< oReq.getRowSet().execute();
< oLog.setRowCount(oReq.getRowSet().size());
< oLog.setStatus("completed");
< System.out.println("executeQuery thread is writing to end log");
< boolean result = logEnd(oLog);
< System.out.println("executeQuery thread is writing zip");
< //writeOutput(oReq.getSavePath(), oReq.getRowSet());
< String shortName = oReq.getSavePath().substring(oReq.getSavePath().lastIndexOf("/")+1);
< writeZipOutput(shortName, oReq.getSavePath(), oReq.getRowSet());
< System.out.println("executeQuery thread has created the csv.zip");
---
> String sUser = oLog.getUser();
> String sRepname = oLog.getRepname();
> Runtime rt = Runtime.getRuntime();
> rt.exec("/dsk2/ptagis-1.0/bin/query-runner.pl -u "+sUser+" -r "+sRepname);
> createMessage(sUser,"Query notification",
> "Query finished: "+sRepname, "Your query has been executed.");
> System.out.println("executeQuery thread has created the csv");
Compiled the class and deployed it to ptagis-1.0/web/sree/WEB-INF/classes/util and restarted weblogic on development.
Wow, it just works.
However there is at least one glitch. The completion message now arrives immediately after the report is started. I have to find a way to get perl to send the completion message or fork another java thread and get it to block on the perl thread. Maybe I can spawn a thread that blocks until it sees a completion time written in the log for the query, and then send the message.
I need to come up with a query that returns more that was previously allowed within the JVM. One year of interrogations at PRO is only 20,000 records.
Still need to implement zipping of report files.
Test query:
- coordinator IDs: DMM, WDM, WPC, MLS, BDL, BDA
- obs sites: GRJ, GOJ, LMJ, ICH, MCJ, JDJ, B2J, BCC, TWX
- obs date between: 1/1/2005 and 1/1/2006
The test query runs for about 15 minutes with iimerge on sockeye taking 100% of a cpu. Sockeye is still about 80% idle. Load on pitblade is negligible.
After about 15 minutes, query-runner.pl starts to use about 1% of cpu on pitblade. iimerge on sockeye is still at 100%.
At 1:12, query-runner.pl started to spike up to 20% cpu and iimerge fell to 25% cpu. At 1:13, query-runner.pl was using 31% cpu, iimerge 27% cpu. At 1:14, query-runner.pl was back down to 4% cpu and iimerge 95% cpu.
Stress test successful
The test start at 2009-03-17 12:53:13 and completed at 2009-03-17 13:39:34 yielding 2,127,307 rows.
Add file compression
The command that I need to add to the perl is something like this:
zip -m report.csv.zip report.csv
Added this code to query-runner.pl:
my $zip = "/bin/zip";
...
# zip the results
my $user = $logrow{user};
my $repname = $logrow{repname};
my $result = `$zip -m $outpath/$user/$repname.csv.zip $outpath/$user/$repname.csv`;
bug: zip file is created with full nested filesystem
/dsk2/ptagis-1.0/web/sree/cvswd//rday/report.csv
Fix:
my $result = `cd $outpath/$user/; $zip -m $repname.csv.zip $repname.csv`;
Fix completion message
My plan is to create another thread when launching the perl process. The second thread will loop waiting for the wqb_query_detail table to contain a completion time for the query that is greater than the start time.
I implemented a thread to wait for end time to be greater than start time and then send the message. Next, to copy that to pitblade, compile and test.
It works. The thread looks like this:
public void run() {
try {
Timestamp start;
Timestamp end;
String sUser = oLog.getUser();
String sReportname = oLog.getRepname();
System.out.println("sendMessage thread is running");
boolean done = false;
while (!done) {
// watch for semaphore, then send message
WLCachedRowSet oRowSet = m_oDBConnection.newCachedRowSet();
String sCmd = "SELECT start_time, end_time from wqb_query_details WHERE userid = ? and report_name = ?";
oRowSet.setCommand(sCmd);
oRowSet.setString(1, sUser);
oRowSet.setString(2, sReportname);
oRowSet.execute();
oRowSet.next();
start = oRowSet.getTimestamp(1);
end = oRowSet.getTimestamp(2);
try {
if (end.after(start)) {
done = true;
createMessage(oLog.getUser(), "Query notification",
"Query finished:"+ oLog.getRepname(),
"Your report "+oLog.getRepname()+" has been executed. " +
"Please visit the My Report Files area to see it. "+
"Alternatively, the following is a direct link to "+
"<a href=\"/ptagis/home/myreports/display.jsp?action=view&user=" +
oLog.getUser()+"&name="+oLog.getRepname()+".csv.zip\"" +
" target=\"_blank\">" + oLog.getRepname() + ".csv</a>.");
} else {
// sleep 5 seconds
Thread.sleep(5000);
}
} catch (NullPointerException e) {
}
}
} catch (Exception e) {
e.printStackTrace();
}
Fix date format
The java query-runner displays dates in this format:
"2007-05-23 11:35:00"
The perl query-runner displays dates in this format:
"23-may-2007 11:35:00"
Resolution
> sub convert_date_format{
> # converts a date from the form 10-feb-2004 to the form 2004-02-10
> my $indate = shift;
> #print "got $indate\n";
> my ($day, $month, $year) = ($indate =~ m/(\d{2})-(\w{3})-(\d{4})/);
> my $m = $month_names{$month};
> #print "sending $year-$m-$day\n";
> return "$year-$m-$day";
> }
Deploy to production
update tables
- modify grants on wqb_query_details
- modify grants on wqb_query_history
deploy java classes
- update src/util/queryRunner.java
- update web/sree/WEB-INF/classes/util/queryRunner*
deploy perl program
- update bin/query-runner.pl
