Menu
▾
▴
Re: [Testdev] Re: [osdldbt-general] Problem load stored procedures
|
From: Jenny Z. <je...@os...> - 2003-04-23 23:50:54
|
Hello Thierry,
Can you tell us what the goal of your project? I did not know that you
are working with Satoshi. Do you have performance number that we can
share?
The reason I am asking this is that the database has to be tuned for
each system. Do you want us to help with tuning SAPDB?
Virgine mentioned:
> > > Hardware configuration: Mono Pentium IV 1.8Ghz, 256 MB , 3 Disks
of 8GB
> > > (stripped)
> > > Software configuration : Redhat 7.3, SapDB 7.4
1. We did not tune DBT1 for SAPDB 7.4, so we might missed some
database kernel parameters.
2. As I mentioned in my previous e-mail, 256 MB memory is not
enough for SAPDB.
3. By 3 disks(stripped), did you mean you have one data
devspace(SAPDB term) and this one devsapce is stripped on 3
disks, or 3 data devspaces on 3 disks? Stripping one
devspace on 3 disks is not good since SAPDB has one
writer/reader per devspace. Also, how did you map the log
devspaces to your Logival disk devices (LUNs).
How did you map your data devspaces to your LUNs?
4. I do not know how PostGres does checkpoint, for SAPDB we
used
the default checkpoint, which is every 10 minutes if there
are enough (I forgot the exact number) dirty data. Maybe
PostGres is not doing that much checkpoints.
5. Are SAPDB and PostGres database using the same indexes?
SAPDB automatically creates an index for columns defined in
the table create statement as a primary key. PostGres
probably needs you to specify them explicitly in an index
create statement.
The Full Disclosures of some TPC-W results on other databases
(www.tpc.org) is a good source for a list of beneficial indexes to try
that we may have passed over for our SAPDB implementation. If you need
help finding this information, let us know.
Can you e-mail us the SAPDB parameters(running
dbt1/scripts/stats/db_stats.sh should collect the data)?
Also we noticed that your system size is fine for developing the kit
but might not good for performance testing. You can request a project
at OSDL in order to use one of our lab machines to do the performance
test.
Jenny
On Wed, 2003-04-23 at 01:38, Thierry Missimilly wrote:
> Hi Jenny,
>
> I try to replace Virginie. Even, if she is out of her office, i can join her
> by phone or e-mail in case of problems.
> As she has writen, we do not use the DBT1 Cache Server. We have system
> monitor tools as iostat, sar, top, vmstat. Generaly we check iostat in the
> same time DBT1 is running and with a think time of 1.6, we don't see Wait
> I/O.
> The most strange is the result differences between SAPDB and Postgres. So, i
> check the transactions ported on Postgres to see, if Virginie has missed
> something. But even in that case, one or two transactions will have big
> response time differences but not all.
> Bull has planed to post our Postgres DBT1 code and i am in charge of doing
> it. Our contact for that is Mr Satoshi Nagayasu <sn...@sn...>, as he is
> the project manager for DBT1 on Postgres.
> But i'll put you in copy when i'll send the tar file.
> I hope to be able to do that before the end of this week. I just have to
> check with Virginie that everything is ready.
>
> Best regrads
> Thierry.
>
>
> Jenny Zhang wrote:
>
> > Congratulations! I wish I live in France and had six month of maternity
> > leave.
> >
> > I don't know how I came up with the 1 minute number. You are right, It
> > is around 2 or 3 seconds. Since about 15% of the interactions are
> > BestSeller, NewProducts, and SearchResult by subject, and those three
> > interactions are basically the same (We violated TPC-W spec here, since
> > we can not find a way to implement in the way the spec requires using
> > SAPDB), we saw a lot of contentions on Item and Author tables. Using
> > the cache helped a lot.
> >
> > Another thing might be the memory. 256MB may be not enough. Do you
> > have other system monitor data(like vmstat, sar, iostat)?
> >
> > Have you ported DBT1 to Postgres SQL? Your number would be the first
> > Postgres number. Can you post your number and the kit?
> >
> > Thanks for your contribution,
> > Jenny
> > On Tue, 2003-04-22 at 02:11, Virginie Megy wrote:Hi,
> >
> > As I'am pregnant and I'll be out of my office for the next six months.
> > So Thierry Missimilly at the e-mail address Thi...@bu...
> > will continue to work dbt1-sapdb and try to get the best results.
> >
> > ps: See my responses to your questions in blue
> >
> > Jenny Zhang wrote:
> > On Wed, 2003-04-16 at 07:57, Virginie Megy wrote:
> > > Hello Jenny,
> > >
> > > I fixed the stored procedures, and I launched the bench DBT-1
> > (version
> > > 1.1.4). I've obtained some bad results. The response times are
> > very long
> > > (between 52 and 91 seconds). How can I optimize?
> > The database configuration is 100,000 item and 500 customer. The
> > spreadsheet(under doc/sapdb_dbt1_sizing.sxc) calculates a total of
> > 2.6G
> > data. But this is just an estimate and does not include the
> > auxiliary
> > data structures. I can not tell if the result is bad or not. The
> > dbt1
> > result we have are posted at:
> > http://www.osdl.org/projects/dbt1prfrns/results/
> > Though we tried to optimize the queries, most of the response time
> > is
> > around 1 minute.
> > It's strange because on your web link, the results are best, lower than
> > 5 seconds for each interaction. Furthermore, on the same configuration
> > with Postgres SQL, the lowest interaction tooks 22 seconds. So, that
> > would mean that SapDB would be 4 to 5 times slower than Postgres. For
> > us, it seems that indexes existe but are not used.
> > I have some questions for you:
> > How many users did you start?
> > 100 dbdriver users but only 20 AppServer database connections.
> >
> > What is your hardware configuration?
> > Hardware configuration: Mono Pentium IV 1.8Ghz, 256 MB , 3 Disks of 8GB
> > (stripped)
> > Software configuration : Redhat 7.3, SapDB 7.4
> > The three DBT1 tiers are configured on the same machine (so no network
> > resources ). The DBT1 benchmark isn't run with the Server cache.
> > > It seems to me that the indexes are not used or not correctly
> > used. How
> > > can I see my database schema, indexes, stored procedures with
> > WebSql or
> > > an other tool? Is there an "Analyse command" ?
> > > ( I generated the data with the command ./datagen 100000 500, so I
> > > obtained a medium database 4GB ).
> > > When I use the odbc_test_p1 program to test each individual
> > interaction,
> > > the response time is vey long too. (for example, BS intercation
> > :48s).
> > >
> > I used dbmcli tool.
> > $> dbmcli -d DBT1 -u dbm,dbm
> > dbmcli> sql_connect dbt,dbt
> > to show schema:
> > dbmcli> sql_execute select * from columns where tablename='CUSTOMER'
> > to show index info:
> > dbmcli> sql_execute select * from indexes
> > dbmcli> sql_execute select * from ALL_IND_COLUMNS
> > to show stored procedure(but I think there is a bug there, everytime
> > I
> > use it, it exit
> > dbmcli> sql_execute select * from db_proc
> > There are other database monitor tables which collects accumulative
> > data. For the details of the command, please refer to
> > dbt1/scripts/stats/db_stats.sh
> >
> > SAPDB 7.4 has an additional analyze tool, but I have not tried out.
> >
> > If you have any suggestions on tuning dbt1, please do not hesitate
> > to
> > send to the mailing list.
> >
> > We are curious to see your result.
> >
> > Thanks,
> > Jenny
> >
> > -------------------------------------------------------
> > This sf.net email is sponsored by:ThinkGeek
> > Welcome to geek heaven.
> > http://thinkgeek.com/sf
> > _______________________________________________
> > osdldbt-general mailing list
> > osd...@li...
> > https://lists.sourceforge.net/lists/listinfo/osdldbt-general
> > Best Regards,
> > Virginie and Thierry
> >
> > --
> > --------------------------------------------------------------------
> > Virginie Megy Bullcom: 229 70 08
> > Office : B1-181 Tel : 04 76 29 70 08
> > Phone : +33 4 76 29 75 00 70 08
> > BULL S.A.
> > 1 rue de Provence mailto:Vir...@bu...
> > BP 208
> > 38432 Echirolles Cedex http://intranet.frec.bull.fr/projet/perf/
> > France
> >
> > --
> > Jenny Zhang
> > Open Source Development Lab Inc
> > 15275 SW Koll Parkway - Suite H
> > Beaverton, OR 97006
> > (503)626-2455 ext 31
--
Jenny Zhang
Open Source Development Lab Inc
15275 SW Koll Parkway - Suite H
Beaverton, OR 97006
(503)626-2455 ext 31
|