Thread: Dbt2 with postgres issues on CentOS-5.3
Hi,
I am using dbt2 on Linux 64 (CentOS release 5.3 (Final)) . I have compiled latest postgresql-8.4.3 code on the machine and run dbt2 against it. I am little confused about the results. I ran dbt2 with the following configuration i.e.
DBT2 Options :
WAREHOUSES=75
DB_CONNECTIONS=20
REGRESS_DURATION=1 #HOURS
REGRESS_DURATION_SEC=$((60*60*$REGRESS_DURATION))
DBT2 Command :
./dbt2-pgsql-create-db
./dbt2-pgsql-build-db -d $DBDATA -g -r -w $WAREHOUSES
./dbt2-run-workload -a pgsql -c $DB_CONNECTIONS -d $REGRESS_DURATION_SEC -w $WAREHOUSES -o $OUTPUT_DIR
./dbt2-pgsql-stop-db
I am not able to understand the sar related graphs. Iostat,mpstat and vmstat results are similar but sar results are strange. I tried to explore the dbt2 source code to find out the how graphs are drawn and why sar results differ.DBT2.pm : 189 reads sar.out and parse it and consider 1 minute elapsed time between each record i.e.
ActivePerl-5.10.1.1007-i686-linux-glibc-2.3.2-291969/inst/lib/Test/Parser/Sar.pm : 266
elapsed_time is a counter, with every record it increment to 1 (++$elapsed_time)
Sar.out shows the following results i.e.
08:54:47 PM cswch/s
..
..
09:21:47 PM 1809.46
09:22:47 PM 2251.26
09:23:47 PM 2151.27
09:24:47 PM 2217.33
09:27:01 PM 2189.83
09:29:02 PM 2155.13
09:30:02 PM 2048.04
09:32:19 PM 2033.16
09:34:20 PM 2032.47
09:36:20 PM 2006.02
09:37:20 PM 1966.03
09:39:35 PM 1974.77
09:41:37 PM 1973.88
09:42:37 PM 1960.65
09:44:56 PM 1993.15
09:45:56 PM 1989.46
09:47:57 PM 2430.77
09:48:57 PM 2416.64
09:51:08 PM 2330.02
09:53:19 PM 1738.46
09:54:19 PM 2182.27
09:55:19 PM 2221.31
09:56:19 PM 2131.81
09:57:19 PM 2183.47
09:59:31 PM 2156.70
10:01:32 PM 2114.38
10:02:32 PM 2030.05
10:04:51 PM 2059.56
10:05:51 PM 1995.06
10:08:09 PM 1355.43
10:09:09 PM 218.73
10:10:09 PM 175.13
10:11:09 PM 168.30
10:12:09 PM 168.58
..
..
It shows that sar results for each record is not after every 1 minute duration, it varies. Is it expected or there are some bugs in CentOS default sar package (sysstat-7.0.2-3.el5). I tried latest package sysstat-9.0.6.1 from but it behaving the same. Systat utilities depends on procfs, is there something wrong with the system ?. Thanks.
Best Regards,
Asif Naeem
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now.
2010/4/20 MUHAMMAD ASIF <anaeem.it@hotmail.com>: > Hi, > > I am using dbt2 on Linux 64 (CentOS release 5.3 (Final)) . I have compiled > latest postgresql-8.4.3 code on the machine and run dbt2 against it. I am > little confused about the results. I ran dbt2 with the following > configuration i.e. > > DBT2 Options : > WAREHOUSES=75 > DB_CONNECTIONS=20 > REGRESS_DURATION=1 #HOURS > REGRESS_DURATION_SEC=$((60*60*$REGRESS_DURATION)) > > DBT2 Command : > ./dbt2-pgsql-create-db > ./dbt2-pgsql-build-db -d $DBDATA -g -r -w $WAREHOUSES > ./dbt2-run-workload -a pgsql -c $DB_CONNECTIONS -d > $REGRESS_DURATION_SEC -w $WAREHOUSES -o $OUTPUT_DIR > ./dbt2-pgsql-stop-db > > I am not able to understand the sar related graphs. Iostat,mpstat and vmstat > results are similar but > sar results are strange. I tried to explore the dbt2 source code to find > out the how graphs are drawn and why sar results differ.DBT2.pm : 189 reads > sar.out and parse it and consider 1 minute elapsed time between each record > i.e. That is certainly a weakness in the logic of the perl modules in plotting the charts accurately. I wouldn't be surprised if the other stat tools suffer the same problem. Regards, Mark
I am facing sar related issues on Redhat Enterprise Linux64 5.4 too (60G Ram, No Swap space, Xeon Processor).
sar -o /var/dbt2_data/PG/Output/driver/dbt2-sys1/sar_raw.out 60 204
|___ sadc 60 205 -z /var/dbt2_data/PG/Output/driver/dbt2-sys1/sar_raw.out
It generates following sar data i.e.
….
….
03:52:43 AM 2.31
03:53:43 AM 2.31
03:54:43 AM 2.28
03:55:43 AM 2.31
03:56:43 AM 1.67
03:57:43 AM 0.29
03:58:43 AM 0.29
04:00:43 AM 0.30
04:04:00 AM 3.52
04:07:07 AM 0.30
04:09:36 AM 0.23
04:12:04 AM 0.36
04:14:25 AM 0.23
04:16:45 AM 0.26
04:19:10 AM 0.24
04:21:30 AM 0.38
04:23:55 AM 0.24
04:26:25 AM 0.35
04:28:48 AM 0.24
04:31:10 AM 0.27
04:33:40 AM 0.33
04:36:45 AM 0.41
04:39:12 AM 0.27
04:41:41 AM 0.26
04:44:11 AM 0.33
04:46:35 AM 0.25
04:49:06 AM 0.33
04:51:27 AM 0.27
04:53:56 AM 0.23
04:56:19 AM 0.36
04:58:43 AM 0.24
05:01:10 AM 0.35
05:03:43 AM &nbs p; 0.33
05:06:53 AM 0.29
05:09:25 AM 0.23
….
….
To fix this issue I have modified the sysstat-9.1.2/sadc.c and replaced signal based pause (That is not real time) with "select" based pause. That fixed the issue. Thanks.
-------------------------------------------------------------------------------------------------------------------------------------------------------
sadc.c.patch
-------------------------------------------------------------------------------------------------------------------------------------------------------
--- sadc.c.org 2010-06-14 21:44:18.000000000 +0500
+++ sadc.c 2010-06-14 22:52:51.693211184 +0500
@@ -33,6 +33,10 @@
#include <sys/stat.h>
#include <sys/utsname.h>
+#include <sys/types.h>
+#include <sys/time.h>
+#include <time.h>
+
#include "version.h"
#include "sa.h"
#include "rd_stats.h"
@@ -792,6 +796,15 @@
}
}
+void pause_new( void )
+{
+ struct timeval tvsel;
+ tvsel.tv_sec = interval;
+ tvsel.tv_usec = 0;
+
+ select( 0, NULL, NULL, NULL, &tvsel );
+}
+
/*
************************************ ***************************************
* Main loop: Read stats from the relevant sources and display them.
@@ -899,7 +912,7 @@
}
if (count) {
- pause();
+ pause_new();
}
/* Rotate activity file if necessary */
-------------------------------------------------------------------------------------------------------------------------------------------------------
Best Regards,
Asif Naeem
> Date: Wed, 21 Apr 2010 18:10:35 -0700
> Subject: Re: [PERFORM] Dbt2 with postgres issues on CentOS-5.3
> From: markwkm@gmail.com
> To: anaeem.it@hotmail.com
> CC: pgsql-performance@postgresql.org
>
> 2010/4/20 MUHAMMAD ASIF <anaeem.it@hotmail.com>:
> > Hi,
> >
> > I am using dbt2 on Linux 64 (CentOS release 5.3 (Final)) . I have compiled
> > latest postgresql-8.4.3 code on the machine and run dbt2 against it. I am
> > little confused about the results. I ran dbt2 with the following
> > configuration i.e.
> >
> > DBT2 Options :
> > WAREHOUSES=75
> > DB_CONNECTIONS=20
> > REGRESS_DURATION=1 #HOURS
> > REGRESS_DURATION_SEC=$((60*60*$REGRESS_DURATION))
> >
> > DBT2 Command :
> > ./dbt2-pgsql-create-db
> > ./dbt2-pgsql-build-db -d $DBDATA -g -r -w $WAREHOUSES
> > ./dbt2-run-workload -a pgsql -c $DB_CONNECTIONS -d
& gt; > $REGRESS_DURATION_SEC -w $WAREHOUSES -o $OUTPUT_DIR
> > ./dbt2-pgsql-stop-db
> >
> > I am not able to understand the sar related graphs. Iostat,mpstat and vmstat
> > results are similar but
> > sar results are strange. I tried to explore the dbt2 source code to find
> > out the how graphs are drawn and why sar results differ.DBT2.pm : 189 reads
> > sar.out and parse it and consider 1 minute elapsed time between each record
> > i.e.
>
> That is certainly a weakness in the logic of the perl modules in
> plotting the charts accurately. I wouldn't be surprised if the other
> stat tools suffer the same problem.
>
> Regards,
> Mark
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now.
sar -o /var/dbt2_data/PG/Output/driver/dbt2-sys1/sar_raw.out 60 204
|___ sadc 60 205 -z /var/dbt2_data/PG/Output/driver/dbt2-sys1/sar_raw.out
It generates following sar data i.e.
….
….
03:52:43 AM 2.31
03:53:43 AM 2.31
03:54:43 AM 2.28
03:55:43 AM 2.31
03:56:43 AM 1.67
03:57:43 AM 0.29
03:58:43 AM 0.29
04:00:43 AM 0.30
04:04:00 AM 3.52
04:07:07 AM 0.30
04:09:36 AM 0.23
04:12:04 AM 0.36
04:14:25 AM 0.23
04:16:45 AM 0.26
04:19:10 AM 0.24
04:21:30 AM 0.38
04:23:55 AM 0.24
04:26:25 AM 0.35
04:28:48 AM 0.24
04:31:10 AM 0.27
04:33:40 AM 0.33
04:36:45 AM 0.41
04:39:12 AM 0.27
04:41:41 AM 0.26
04:44:11 AM 0.33
04:46:35 AM 0.25
04:49:06 AM 0.33
04:51:27 AM 0.27
04:53:56 AM 0.23
04:56:19 AM 0.36
04:58:43 AM 0.24
05:01:10 AM 0.35
05:03:43 AM &nbs p; 0.33
05:06:53 AM 0.29
05:09:25 AM 0.23
….
….
To fix this issue I have modified the sysstat-9.1.2/sadc.c and replaced signal based pause (That is not real time) with "select" based pause. That fixed the issue. Thanks.
-------------------------------------------------------------------------------------------------------------------------------------------------------
sadc.c.patch
-------------------------------------------------------------------------------------------------------------------------------------------------------
--- sadc.c.org 2010-06-14 21:44:18.000000000 +0500
+++ sadc.c 2010-06-14 22:52:51.693211184 +0500
@@ -33,6 +33,10 @@
#include <sys/stat.h>
#include <sys/utsname.h>
+#include <sys/types.h>
+#include <sys/time.h>
+#include <time.h>
+
#include "version.h"
#include "sa.h"
#include "rd_stats.h"
@@ -792,6 +796,15 @@
}
}
+void pause_new( void )
+{
+ struct timeval tvsel;
+ tvsel.tv_sec = interval;
+ tvsel.tv_usec = 0;
+
+ select( 0, NULL, NULL, NULL, &tvsel );
+}
+
/*
************************************ ***************************************
* Main loop: Read stats from the relevant sources and display them.
@@ -899,7 +912,7 @@
}
if (count) {
- pause();
+ pause_new();
}
/* Rotate activity file if necessary */
-------------------------------------------------------------------------------------------------------------------------------------------------------
Best Regards,
Asif Naeem
> Date: Wed, 21 Apr 2010 18:10:35 -0700
> Subject: Re: [PERFORM] Dbt2 with postgres issues on CentOS-5.3
> From: markwkm@gmail.com
> To: anaeem.it@hotmail.com
> CC: pgsql-performance@postgresql.org
>
> 2010/4/20 MUHAMMAD ASIF <anaeem.it@hotmail.com>:
> > Hi,
> >
> > I am using dbt2 on Linux 64 (CentOS release 5.3 (Final)) . I have compiled
> > latest postgresql-8.4.3 code on the machine and run dbt2 against it. I am
> > little confused about the results. I ran dbt2 with the following
> > configuration i.e.
> >
> > DBT2 Options :
> > WAREHOUSES=75
> > DB_CONNECTIONS=20
> > REGRESS_DURATION=1 #HOURS
> > REGRESS_DURATION_SEC=$((60*60*$REGRESS_DURATION))
> >
> > DBT2 Command :
> > ./dbt2-pgsql-create-db
> > ./dbt2-pgsql-build-db -d $DBDATA -g -r -w $WAREHOUSES
> > ./dbt2-run-workload -a pgsql -c $DB_CONNECTIONS -d
& gt; > $REGRESS_DURATION_SEC -w $WAREHOUSES -o $OUTPUT_DIR
> > ./dbt2-pgsql-stop-db
> >
> > I am not able to understand the sar related graphs. Iostat,mpstat and vmstat
> > results are similar but
> > sar results are strange. I tried to explore the dbt2 source code to find
> > out the how graphs are drawn and why sar results differ.DBT2.pm : 189 reads
> > sar.out and parse it and consider 1 minute elapsed time between each record
> > i.e.
>
> That is certainly a weakness in the logic of the perl modules in
> plotting the charts accurately. I wouldn't be surprised if the other
> stat tools suffer the same problem.
>
> Regards,
> Mark
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now.