Support for DB2 databases?

Well, I've been playing around.
I've compiled various versions of apache, with various config options.
Various versions of php with various config options.
Nothing seems to get rid of the initial stall. Although, httpd is now at the top of the list, instead of db2sysc.
I've tried APC, the Zend OPtimizer, and the Zend Core for IBM, AIX Compiler and gnu compiler. GNU compiler produced horribly slow setup. APC beats Zend OPtimizer hands down every time. I know they do different things (APC is caching, Zend Opt. optimizes code more effciently), but they don't play nice together, so you can only have 1.

This machine has been beat the last 3 days doing almost nothing but compiling.

Zend core was ok, it had a nice web interface for managing things, but it's not optimized for my machine. I can specifcy to AIX compiler some options specifically for my processors, number of processors, etc. Benchingmarkig with and without just these options is about a 3-8% improvment, depending on what I was doing.

The fastest setup I've been able to produce is:
php5.1.6, apache2.2.3,ibm_db21.5.1 (the first 1.5.1 I compiled earlier in the week, it picked up gnu compiler, that's why it was noticabbly slower)
Oddly enough, it's the latest levels of everything. php is a DSO
php4.4.4 and apache.1.3.37,ibm_db21.5.1 was a very close second. Almost negilible. Difference of 0.3 seconds. But always the 0.3 difference. This was php compiled statically into apache instead of being a DSO.
Oh, and this time around I figured out how to make DSO modules for php easily (where you just put the .so somewhere and declare it in php.ini). So ibm_db2 is now a DSO, albeit with no gain or loss in performance.

I also upgrade My os from AIX 5.3ml03 to ml05+.
DB2 is still FP11. I'm going to try 13, and to re-create the database entirely at FP13 level and just import g2 data into it.

I'm down to a 2-3 stall for most operations in g2, and httpd is at the top now, not db2sysc, but db2sysc is right there under it at 14% CPU.

BTW, don't try apache with mpm=worker configure option, seg faults on anything with ibm_db2 driver. Had to use prefork. (which is always what I had, but some reading I did, one guy said worker was faster for AIX)

Anyhow, I'm wiped out from all this building and reading (I was up till 5am last night, I really need something better to do. Actually, I have better things to do, these types of problems just eat at me though, I lay in bead just thinking of another possible solution, and of course I have to try it).

I can still produce the stall in a short test script with an IN clause (and I suspect other types of clauses do the same thing, I re-ordered all the g2 IN clauses with x=2 or x=3 or x=5 format, still a stall), under any build setup, so I'm pretty sure it's not me or how I built it at this point. DOing some reading, I found some things that say that ibm_db2 driver is optimized for windows, UNIX being an afterthought, so mabey it's just that way for us unix guys, or mabey it's just AIX. But that was just one guys blog, so don't know how much truth there is to that. I'll be posting up on the ibm_db2 mailing list and see what they have to say about it.

What happened to the connects. Still doing 40 connects per page with rewrite + mod_rewrite (not pathinfo)?
Since it's using persistent connections now, there shouldn't be a large performance penalty involved with that, but with x parallel users, you will run into connection limits pretty quickly.

And what about the IN () performance issue? Is that something the DB2 team should follow up with?

It's the db2_exec that is the problem. 'IN' clause problem goes away with db2_prepare/db2_exectute. I initially posted the other way then corrected myself.
So how does one emulate 200 connected users?
Yeah, I know the problem is still there, I've just masked it with caching, pconnect and more effiecient apache/php, but I know, the stall is still there, just not as in your face as it was.
I still see 42 calls to connect (pconnect now) when mod_rewrite module is active. If I deactivate it goes to 2, decativate image block and it goes to 1. Mostly what's improved is the loading of the actual images, already past the initial stall. So with regular connect, I get the stall, page starts to load, images show up one-by-one (keep in mind I'm on the same network here for testing) and the page takes another 2-4 seconds to finish loading, but at least you have something to look at. With pconnect, the page finishes loading in 0.5 seconds. But still the initial stall, persists.
Yeah, I would like to know *exactly* what's causing as well.
So, one thing I noticed, you say to create the database with UTF-8 codeset and territory US, how important is that? I never specified those, and DB2 defaults to ISO-8859-1 codeset.

When I enable "Download Item" my URL Rewrite module I also see a severe degradation, in which
I can see a definite pause between each image being loaded. Doing frequent "db2 list
applications" commands on the server while this is happening, I see many database connections
coming and going. Very similar to your findings.

When I disable "Download Item", the degradation and the multiple DB connections go away.

Do you have "Download Item" configured in your URL Rewrite module?

That's exactly why I decided to try it... I'm wondering if that's what 73blazer is doing.

Ah, OK... I didn't realize that you were distinguishing an "initial stall" from a "between images" stall.

In that case, perhaps the "initial stall" is the IN(...) predicate problem you're having?

> Microsoft Stress Tool

So running the Microsoft stress tool you didn't see any stalling??? OK, I'm stunned at that.
Could it be a browser issue then?

> How do I switch to use PHP pathinfo?

I think you might need to un-install and re-install the URL Rewrite module. I can't find any
way to change that setting post-install.

> version of G2 needed for "lib/tools"

I'm not sure of that, but I wouldn't mix-&-match... I suggest you grab a whole development
build.

Nope, there are still x queries per download request that would not exist without.
Just don't use pathinfo!
Use mod_rewrite instead.

disable and uninstall the g2's rewrite module. then install it again and when configuring, choose mod_rewrite instead of pathinfo.

True, queries are still passed, and they return quite quickly (0.007212 seconds) but at least I don't have to wait for a connect with persistent connections, which was causing a slowdown when I had connect instead of pconnect.

Anyhow, I did use pathinfo when I installed that. I was wondering what you guys were talking about with this path info stuff. I remember why now.
Trying to switch now, I can't get the mod_rewrite method to pass.
I use it in other applications, and can make my own quick test, and it's definiltly loaded and working in apache.
I'm looking at another thread here where people have said they have ths problem with g2.

> stall on page load

Well, now that you have identified that one query that runs slower when run via db2_exec() than
it does with separate db2_prepare() and db2_execute(), let's look at it from that angle.

• I wonder if you can use Explain to dump the Access Plan for that statement in each of the two
  scenarios. First of all, it should not be different between the two. Secondly, the DB2
  Optimizer should be using the most efficient Access Plan. I'm not a good judge of whether or
  not that is true, but I've seen you posting to DB2 newsgroups so you could ask there for
  confirmation.
• If you have the ability to run that same statement in a C or java app (not PHP) then maybe that
  could confirm or eliminate PHP and ibm_db2 as suspects.
• We could also try running a DB2 Monitor Snapshot for Dynamic SQL. Not sure if that will tell
  us anything that we don't already know, but it might be worth a try.

You may have read something about whether or not Static SQL is faster than Dynamic SQL,
that is pretty hotly debated. But that's more a result of caching. I'm talking about only
the selection of an access plan by the DB2 Optimizer. It should choose the same access plan
for a given statement regardless if it is processed as static or dynamic, and that's what I want to confirm is true in your case.

Is your C program a CLI application that uses SQLExecDirect()? I believe SQLExecDirect() is
the equivalent of db2_exec()... just like SQLPrepare() and SQLExecute() are the equivalent of
db2_prepare() and db2_execute().

If so, then I'd say that's a fair comparison. And if the access plan selected by
the DB2 Optimizer for that statement in the two PHP scenarios is the same, then I would
strongly suspect that the problem is somewhere in either PHP or ibm_db2. And in that case
I think we'd need to instrument (put timestamps into) the ibm_db2 driver to see where it's
spending its time in both scenarios.

Side note: In the CLI traces you collected, I see SQLPrepare() and SQLExecute() being used,
not SQLExecDirect(). But I think that might be because SQLExecDirect() calls SQLPrepare() and
SQLExecute() under the covers. After all, every statement needs to be Prepared at some
point before it can be Executed... it's just a question of when the Prepare is done.

Plus, if you have the time, I would like to know if using a database created with the UTF-8
codeset and territory makes any difference. We may as well compare apples to apples.

This is just too weird. God how I hate performance problems. Give me a nice solid functional
problem anytime, but I hate performance problems.

Ok, I'm gonna admit somthing I probably shouldn't, I hope my customers don't see this.
Truthfully, I don't know what my C++ application is doing. I started with some examples out of db2_08_01/samples/cpp directory in an attempt to solve a problem with a certain customer, and found it's performance and ease of use very easy, so I use it.
Why don't I know, I'll spare you the explaintion here in this forum, it has something to do with the fact that, well, I'm not well versed in certain things (read: I'm a hack)

So, anyway, sorry,
it's "sqC" code, that does query's in the format of:

 EXEC SQL BEGIN DECLARE SECTION;
  char pv_oid[33];
 EXEC SQL END DECLARE SECTION;
 EXEC SQL DECLARE c43 CURSOR FOR
                 SELECT hex(V514LASTVERSION) FROM LCADMIN.VPMPARTMASTER WHERE
                   hex(OID)=:partmaster FOR FETCH ONLY;
 EXEC SQL OPEN c43;
 EXEC SQL FETCH c43 INTO :pv_oid;
 EXEC SQL CLOSE c43;

or (prepare method, which is what I use/used for the g2 trapped SQL test)

EXEC SQL BEGIN DECLARE SECTION;
         char lcatable[16];
         char Stmt2[5000];
       EXEC SQL END DECLARE SECTION;

       MySelect="select rtrim(S_TYPE) from " + Templcaid + " where 
                 $COID=x'"+ Tempparent + "' FOR FETCH ONLY";

       strcpy(Stmt2,NULL);
       strncpy(Stmt2,MySelect,MySelect.GetLengthInChar()+1);

EXEC SQL PREPARE SStmt FROM :Stmt2;
       EXEC SQL DECLARE c5 CURSOR FOR SStmt;
       cout << "SQLCode C/P Prepare [" << sqlca.sqlcode << "]" << endl;

       EXEC SQL OPEN c5;
       EXEC SQL FETCH c5 INTO :lcatable;
       cout << "SQLCode C/P Fetch [" << sqlca.sqlcode << "]" << endl;
       cout << "COID [" << Tempid << "] PARENT [" << Tempparent << "]" << endl;
       EXEC SQL CLOSE c5;

Is that CLI, or something else? I don't really know(yes I know, sad). I don't see SQLExecute anywhere in api's/header files I"m using.

I'm in the midst of re-making my database under FP13 (as oppsed to migrating from FP4->FP11-FP13, which is where it is now, shouldn't be any difference, I know, but the old school part of me says migrations are usally inferior to fresh installs, so I eliminate the possiblity at the very least), and I'll just export/import the tables to it. I'll be sure to specify Territory and CODESET this time.

Thanks for the PHP script for explain stuff! Umm..it's 0 length?

I created a new Database with UTF-8 CODESET and TERRITORY US. No Change in performance. Performance Numbers match exactly.

> UTF-8

OK, at least we can eliminate that. Thanks.

> 0-byte

Oh, sorry I misunderstood... I thought you were saying the value it returned was 0-byte.

I've been having a lot of damn problems attaching files here. Here's the script:

<?php

//Does separate db2_prepare() and db2_execute() take longer than db2_exec()?
//$separate_prep_and_exec = true;
$separate_prep_and_exec = false;

$dbname = "gallery2";
$username = "........";
$password = "........";

if (key_exists('SERVER_SOFTWARE', $_SERVER))
{
  $br = '<br>';
}
else
{
  $br = "\n";
}

// Connect to the database with specified username and password
$dbconn = db2_connect($dbname, $username, $password);

if ($dbconn)
{

// Un-comment these to capture EXPLAIN tabular data (requires the EXPLAIN tables to exist).
// After execution, do "select total_cost from explain_statement".
//
//  $stmt = "SET CURRENT EXPLAIN MODE = YES";
//  $result = run_statement($stmt);

  // Begin constructing the statement
  $stmt = "SELECT g2_PhotoItem.g_width, g2_PhotoItem.g_height, g2_DataItem.g_mimeType,";
  $stmt .= " g2_DataItem.g_size, g2_Item.g_canContainChildren, g2_Item.g_description,";
  $stmt .= " g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title,";
  $stmt .= " g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp,";
  $stmt .= " g2_FileSystemEntity.g_pathComponent, g2_ChildEntity.g_parentId, g2_Entity.g_id,";
  $stmt .= " g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId,";
  $stmt .= " g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType,";
  $stmt .= " g2_Entity.g_onLoadHandlers FROM g2_PhotoItem, g2_DataItem, g2_Item,";
  $stmt .= " g2_FileSystemEntity, g2_ChildEntity, g2_Entity";
  $stmt .= " WHERE g2_PhotoItem.g_id IN (";

  // If using separate prep and exec, use parameter markers & pass an array of values
  // for the IN clause, else hard-code the values for the IN clause.
  if ($separate_prep_and_exec)
  {
    for ($ctr = 1; $ctr <= 40; $ctr++)  // parameter markers ("?")
    {
      $stmt .= "?";
      if ($ctr < 40)
      {
        $stmt .= ",";
      }
    }
    // 73blazer's values for the IN clause
    $parameterArray = array(4592,4595,4598,4601,4604,4607,4610,4613,4616,4619,
                            4622,4625,4628,4631,4634,4637,4640,4643,4646,4649,
                            4652,4655,4658,4661,4664,4667,4670,4673,4676,4679,
                            4682,4685,4688,4691,4694,4697,4700,4703,4706,4709);
    // Larry's values for the IN clause
    //for ($ctr = 0; $ctr < 40; $ctr++)
    //{
    //  $parameterArray[] = 46 + $ctr * 2;
    //}
    //var_dump($parameterArray);
  }
  else
  {
    // 73blazer's values for the IN clause
    $stmt .= "4592,4595,4598,4601,4604,4607,4610,4613,4616,4619,";
    $stmt .= "4622,4625,4628,4631,4634,4637,4640,4643,4646,4649,";
    $stmt .= "4652,4655,4658,4661,4664,4667,4670,4673,4676,4679,";
    $stmt .= "4682,4685,4688,4691,4694,4697,4700,4703,4706,4709";
    // Larry's values for the IN clause
    //$stmt .= "46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,";
    //$stmt .= "86,88,90,92,94,96,98,100,102,104,106,108,110,112,114,116,118,120,122,124";
  }

  // Finish constructing the statement
  $stmt .= ") AND g2_DataItem.g_id=g2_PhotoItem.g_id AND g2_Item.g_id=g2_PhotoItem.g_id";
  $stmt .= " AND g2_FileSystemEntity.g_id=g2_PhotoItem.g_id";
  $stmt .= " AND g2_ChildEntity.g_id=g2_PhotoItem.g_id";
  $stmt .= " AND g2_Entity.g_id=g2_PhotoItem.g_id";
  $starttime = microtime(true);
  if ($separate_prep_and_exec)
  {
    $result = run_statement($stmt, $parameterArray);
  }
  else
  {
    $result = run_statement($stmt);
  }
  $endtime = microtime(true);
  $elapsedtime = $endtime - $starttime;
  printf ("${br}Elapsed time = %lf.${br}", $elapsedtime);

  // Disconnect from the database
  db2_close($dbconn);
}
exit;

function run_statement($statement, $parameterArray=null)
{
  global $separate_prep_and_exec, $br, $dbconn;

  printf("${br}Processing statement:${br}\"%s\"${br}", $statement);
  if ($separate_prep_and_exec)
  {
    $result = db2_prepare($dbconn, $statement);
    error_check("db2_prepare");
    db2_execute($result, $parameterArray);
    error_check("db2_execute");
  }
  else
  {
    $result = db2_exec($dbconn, $statement);
    error_check("db2_exec");
  }

  return ($result);
}

function error_check ($action)
{
  global $api, $br;
  if (db2_stmt_error())
  {
    printf("${br}%s.${br}", db2_stmt_errormsg());
  }
  else
  {
    printf("${br}$action successful.${br}");
  }
}

?>

All right, that's excellent! Now we're cookin' with gas!

We've confirmed that the Optimizer is selecting the same Access Path for the statement in both
cases. (The value "TOTAL_COST" represents basically the amount of processing it will take DB2
to process the statement, expressed in units called "timerons".)

And we've also confirmed that, in PHP, that statement takes much longer to execute via
db2_exec() than it does via db2_prepare() and db2_execute().

Now let's see if we can simplify our repro scenario. You can disable the EXPLAIN stuff, but now
let's simplify that SQL statement in stages, and see what happens to the time. For example,
let's reduce it to only 1 value in the IN clause. Does the 'false' time decrease?

(BTW, the EXPLAIN stuff contributes some overhead, so get a few runs of the statement AS-IS but
with EXPLAIN disabled, and then make sure you compare the "1 value" times to the "non-EXPLAIN
40-value" times.)

If the time decreases, then we know it's related to the number of values in the IN clause.

If the time does not decrease, I'd suggest making the statement as simple as possible (say,
"select g_id from g2_item") and see if the disparity still exists.

If the time disparity does go away with the simple statement, then I suggest taking the original
statement and repeatedly making it slightly more simple and observing the times. Keep going
until the disparity does go away. When the disparity does go away, the problem is related to
the last thing you removed from the statement.

(Note that simpler statements will obviously take less time than more complex ones, so we'll
need to be able to recognize 'normal' time changes from 'abnormal' time changes. For a baseline value, use the db2_prep/db2_execute time for each statement.)

Try that, and let me know what happens.

Also, it's still a good idea to do the CLI test too. It'll tell us if the problem is in CLI
or 'ibm_db2'. And if it is in 'ibm_db2', then we should be ready to open a detailed bug record
against 'ibm_db2'.

Hey, look up ahead... could that be light at the end of the tunnel?

Ah, I just found out that the SET CURRENT EXPLAIN MODE causes only Dynamic SQL to be EXPLAINed.
"db2_exec()" is Dynamic SQL, but "db2_prepare()/db2_execute()" is actually Static SQL, so it is
not being EXPLAINed.

Sigh... I don't see a way to get EXPLAIN info for Static SQL in a PHP application. So, let's
forget about the EXPLAIN and Access Plan and all that stuff... it's highlyunlikely that the
Optimizer is selecting a different Access Plan for the same statement. Let's just assume that
it isn't.

If you can get back your other script that does show the 2 second difference, then we can try
simplifying the statement in it, as described above. Just try to find one attribute of the
statement that (significantly) affects the time taken by db2_exec().

I'm getting dizzy...

Well, I'm sure the earth under your feet changed when you upgraded all your software. Probably
a good idea to go back to square 1, and not rely on anything we saw earlier.

OK, so we know that the time is being spent outside of _query(). I think it follows then that
that also means the time is not being spent in anything beyond _query() in the call stack.
Specifically, I'm thinking about ibm_db2 and DB2 itself... if they were slow, that time would
show up in the _query() times.

So the problem now seems to be somewhere in either ADOdb (before _query()), Gallery, PHP, Apache, or AIX.

I'd suggest we try to eliminate them in that order:

1. Remove all existing timings, put some timings in the ADOdb code at the precise point where it takes over from the Gallery code and when it returns to the Gallery code, and re-run the test. Look at the proportion of time spent in ADOdb (and beyond) versus the time NOT spent in ADOdb (and beyond).
2. Remove all existing timings, put some timings in Gallery at the precise point where it starts and the exact point where it returns. Look at the proportion of time spent in Gallery
   (and beyond) versus the time NOT spent in Gallery (and beyond).

Or instead of trying to time one layer at a time, you could just leave all your timings in the
code and do the analysis of all layers at once.

> anything useful in [devel version of g2], timing tests, or anything I can use there?

Not that I know of, but I don't know for certain. If Valiant is still hanging around, perhaps
he knows?

> IN predicate performance

I agree it is alarming, but G2 does not do separate prepare/execute... it does only db2_exec().
So yes it is an issue that I'd like to follow up on eventually, but more important right now
are things that affect G2.

Ah-haa... No problem. Like you said there are positives that came out of it. Another positive
is... it looks like you've now upgraded your entire system.

Can you still repro that IN predicate problem? If so, could you attach or post your script
here? Thanks.

Confirmed:
A simple compiled CLI application exhibits same performance problem.
6 items in IN clause, speeds right through, 7 items, time for a sip of tea.

Yeah..i'll post it.
I may have place the time function in not such a god place though...i'm getting inconsistent results. php program shows it, all the time.
give me a few minutes with the CLI thing here while I carefully place the time functions.

Oh yeah.
I'm smokin' now.
Turned back on APC, turnd on partial accelleration..
I can't click fast enough....

CPU Clock cycles....that's what I'm smokin!

I tried to pick one that was. I thought struct timeval and
gettimeofday were good for windows.
Try to put
#include "win32/time.h" instead

I took them out of php's microtime function for just that reason. They should be good.

Ah, the header file for time functions is "WinSock2.h". That's not bloody obvious, is it?

I still can't compile it though... now I get a lot of errors about the SQLINTEGER type. I
think the root error is:

But when I take that structure definition and paste it into the existing tbread sample, it
compiles fine. So there must be something else in your tbread that is breaking it?

I think the SQLBindParameter is having trouble with it. I had to play with the bind line a few times to get it right.
Try to change the array definition line from SQLINTEGER to SQL_C_NUMERIC