Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Re:Millions of sequence in a database to record the c

Re[2]: Re:Millions of sequence in a database to record the c

From: <dgoulet_at_vicr.com>
Date: Fri, 19 Jul 2002 07:23:28 -0800
Message-ID: <F001.0049C9CB.20020719072328@fatcity.com>


Chaos,

    First you will need to pull the counters out of this monster table which is only a good thing. I would assume the table to be on product_id and a the counter. Therefore doing this as an IOT is doable. And since a smaller amount of data is affected by the commit your logfile contention should be reduced. Also, doing the update in PL/SQL may not really reduce the commit overhead, it does relieve the from the parsing problems that multiple access points can create. Yeah, Yeah I know parsing only takes a few Msec, but add those up at 50/sec & we're talking real time now.

    One last item that I see here. It would appear that your disk array has been presented to the host as one big disk drive. Well that's just great, what a bottleneck!? One SCSI cable attached to several GB of disk with everyone wanting time on that one channel. What do you expect, but contention. Break that pile of disk up into several volumes with several SCSI channels and if possible get some type of load balancing software. This one item appears to be the largest reason for the problems and no amount of internal database work is going to fix it. Assume you do all of your counters as sequences, where do you think they are stored? In the SYSTEM tablespace, one of the most utilized tablespaces in any database instance. Also, if you use sequences all of your update statements will have to be dynamic, therefore up goes your parsing requirements. End result, no gain and possibly a reduction in response time.

Dick Goulet

____________________Reply Separator____________________
Author: chaos <chaospku_at_163.net>
Date:       7/18/2002 10:53 PM

dgoulet£¬

> Guess you like living up to your name! :)

        Thanks for your suggestions:)
> Anyway, having a sequence for each product would be a nightmare so I would
>not recommend it. Besides a sequence can get tossed off when you have caching
>turned on and bounce your DB. That being the case, your sequences will not
>reflect true values.

        Of course i know it and i will create the sequence with nocache option. This maybe will be a little slower, but can be ignored:), right?         

> Now I really don't see a problem with having an
>application that updates a table several million times a day, it should not be
>that much of a problem for the database. What you may have to do though is
>properly design the table and update mechanism.

        I am faced with the overcommit of my system, now it is about 30-40 commits per second, and during peak time, it is about 50/second.The top wait event is log file sync(70% of total time waited), we only have one raid-5 disk array,all datafile/logfile are in the same disk arrya and io wait is unacceptable(40% cpu io wait during busy time). And this is a big table(1.2GB,46 columns, with some varchar(2000) columns), it seems hard to use IOT.

        Commit in stored procedure and commit in Pro*c does not any difference, right? So, It seems your second choice is not useful, i think.

        The second way we are testing is do one commit every 100 update, but there will be incorrect data sometimes in the webpage, and multilple middleware machine cannot communicate on there update to the same productid.Now finished the development and doing test.

        By the way, how many commits/second in your guys' database? I write a simple procedure like:

create or replace procedure commit_frequency(time in number default 10) is

        v_user_commits number;
        v_commit_count number;
        v_begin_time date;
        v_commit_time number;
begin
        select value ,sysdate into v_user_commits, v_begin_time from v$sysstat
where name='user commits';
        dbms_lock.sleep(time);
        select value-v_user_commits,sysdate-v_begin_time 
        into v_commit_count, v_commit_time from v$sysstat where name='user
commits';
        dbms_output.put_line('Commit made between the '||time||' is
'||v_commit_count);
        dbms_output.put_line('There is

'||trunc(v_commit_count/(v_commit_time*3600*24))||' transactions per second during this time');
end;
/ and asked some of my friends run it in there production, the top one is 20 commits/second.Maybe 40commits/second is too much for my poor raid-5 array.
        
        

>What your looking for is a
>product_id and the counter value. That looks like a good Index Organized Table
>to me. Fast access. Second would be to have a function/procedure that does
the
>updates. It should accept the product_id as it's only input and do the
>update+commit in one action. I've a setup sort a like this that gets around 3M
>hits per day from the factory floor. Works like a dream with sub second time.
>

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

>____________________Reply Separator____________________
>Author: chaos <chaospku_at_163.net>
>Date: 7/18/2002 8:58 AM
>
>hi, dbas:
>
> Maybe this is some stupid idear, but this is the best idear i can think
>, the following is the requirement of our site:
> 1. every view to the one page(one product) is recorded, and the count
is
>added by one, doing a commit.
> 2. There is 1700K pageview to recorded everyday, thus at lease 1700K
>commit to the database, every second there is about 40 commits, in peak time,
>maybe 60/second.
> 3. The counter is needed for Data Analyse, so cannot drop it.
> The developer and the manager want to delay commit, that is , commit
>after every 100(or 1000) pageview, do a commit. But there is difficulty with
>multiple middleware that do the delayed commit, and lock contention with one
>statement to update 100-200 records every second by different middleware
>servers.
>
> I think use sequence is better to do this work. Every product have a
>unique product_id, and the old way is:
>update products set view_count=view_count+1 where product_id=v_product_id;
>So there is about 1700K commit of this statement now, and more and more this
>statement these days, database is burdened too much.
>
> Now i want to:
> after every view to some product (one single page), just do a:
> select seq_product_id.nextval from dual;
> THis shows the current pageview of the product.
> But there is about 80k products online, so it means that i have to
>create 80k sequence, and with the rapid growth of the products online, there
>will be more and more products online. Every new product is added to the
>database, i generate a unique sequence name like 'SEQ_product_ID';
> maybe someday 200k products on line, so it means i have to create 200k
>sequences, and with history products, i also have to keep the old sequence.
> This is foolish maybe, but is there any better way to count 80k
products
>with their view_count?
> Please share your opnion.
> Thanks.
>
>
>
>Good luck!
>
> chaos
> chaospku_at_163.net
>
>zhu chao
>DBA of Eachnet.com
>86-021-32174588-667
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: chaos
> INET: chaospku_at_163.net
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: dgoulet_at_vicr.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jul 19 2002 - 10:23:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US