Re: Optimizing POSTGRESQL config (Speed Test PHP Code) - Mailing list pgsql-novice

Thanks for all the suggestions!  We're really quite
new with database in general but were experimenting
with PostgreSQL.  Our knowledge of databases were
really from MySQL.  Anyway, we will be moving on to
trying to learn PostgreSQL.

We modified our old code, to 'batch' the processes
instead of just one whole command.  Based on some of
the suggestions earlier here is our modified PHP code:

******************************************************

$query = "VACUUM ANALYZE dbspeed2;";
$result = pg_query($connection, $query); //or die("
Error in query: $query. " .
pg_last_error($connection));
if (!$result)
{
    $time_elapsed = time() - $time_connect;
    echo "Database failure: Error during VACUUM ANALYZE
1<br>Time Elapsed: $time_elapsed <br><br> POSTGRESQL
ERROR:<br>" . pg_last_error($connection);
    exit;
}

$count = 1;
for ($i = 0; $i < 100; $i++)
{
    $query = "BEGIN;";
    for($j = 0; $j <1000; $j++)
    {
        $query = $query."INSERT INTO dbspeed2 VALUES
($count, 'This is loop pass # $i-$j',  random()*1000,
0, 0, 0, 0);";
        $count = $count + 1;
    }
    $query = $query."COMMIT;";
    $result = pg_query($connection, $query);
    if (!$result)
    {
        $time_elapsed = time() - $time_connect;
        echo "Database failure: INSERT GROUP $i-$j<br>Time
Elapsed: $time_elapsed <br><br> POSTGRESQL ERROR:<br>"
. pg_last_error($connection);
        exit;
    }
}



$query = "VACUUM ANALYZE dbspeed2;";
$result = pg_query($connection, $query);
if (!$result)
{
    $time_elapsed = time() - $time_connect;
    echo "Database failure: Error during VACUUM ANALYZE
2<br>Time Elapsed: $time_elapsed <br><br> POSTGRESQL
ERROR:<br>" . pg_last_error($connection);
    exit;
}

$count = 1;
for ($i = 0; $i < 100; $i++)
{
    $query = "BEGIN;";
    for ($j = 0; $j < 1000; $j++)
    {
        $query = $query."UPDATE dbspeed2 SET number1 =
number/4, number2 = number/4, number3 = number/4,
number4 = number/4 WHERE pky = $count;";
        $count++;
    }
    $query = $query."COMMIT;";
    $result = pg_query($connection, $query);
    if (!$result)
    {
        $time_elapsed = time() - $time_connect;
        echo "Database failure: UPDATE $i-$j<br>Time
Elapsed: $time_elapsed <br><br> POSTGRESQL ERROR:<br>"
. pg_last_error($connection);
        exit;
    }
}

******************************************************

The code above executes in about 20 minutes. We have
an placed indexes in columns that is being used by the
code.  I know the code is a little long...but is there
any way to improve the code and POSTGRESQL's
performance?  The basic goal of this code is to gauge
how fast POSTGRESQL will run given high volume data.

I'll post the config file of POSTGRESQL in a minute.

Once again thanks in advance!

Regards,
Joanne Formoso





__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com