473,219 Members | 1,453 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,219 software developers and data experts.

Select for update, locks and transaction levels

Hi,

I am trying to gather stats about how many times a resource in our web
app is viewed, i.e. just a COUNT. There are potentially millions of
resources within the system.

I thought of two methods:

1. An extra column in the resource table which contains a count.
a. Each time a resource is viewed an UPDATE statement is run.

UPDATE res_table SET view_count = view_count + 1 WHERE
res_id=2177526::bigint;

b. The count is just SELECTed from the resource table.
2. A separate table that contains a count using an algorithm similar
to the method presented here:

http://archives.postgresql.org/pgsql...1/msg00059.php

a. Each time a resource is viewed a new row is inserted with a count
of 1.
b. Each time the view count is needed, rows from the table are SUMmed
together.
c. A compression script runs regularly to group and sum the rows
together.

I personally did not like the look of 1 so I thought about using 2. The
main reason being there would be no locks that would interfere with
"updating" the view count because in fact this was just an INSERT
statement. Also vacuuming on the new table is preferred as it is
considerably thinner (i.e. less columns) than the resource table. The
second method allows me to capture more data too, such as who viewed the
resource, which resource they viewed next, but I digress :-).

Q1.Have I missed any methods?

I thought I would have a further look 2 and have some questions about
that too.

The schema for this new table is shown below.

-- SCHEMA
---------------------------------------------------------------
CREATE TABLE view_res (
res_id int8,
count int8
) WITHOUT OIDS;

CREATE INDEX view_res_res_id_idx ON view_res (res_id);
------------------------------------------------------------------------

And the compression script should reduce the following rows:

-- QUERY ---------------------------------------------------------------
db_dev=# select * from view_res where res_id=2177526::bigint;
res_id | count
---------+-------
2177526 | 1
2177526 | 1
2177526 | 1
2177526 | 1
2177526 | 1
2177526 | 1
2177526 | 1
2177526 | 1
(8 rows)
------------------------------------------------------------------------

to the following

-- QUERY
---------------------------------------------------------------db_dev=#
select * from view_res where res_id=2177526::bigint;
res_id | count
---------+-------
2177526 | 8
(1 rows)
------------------------------------------------------------------------

Now I must admit I have never really played around with select for
update, locks or transaction levels, hence the questions. I have looked
in the docs and think I figured out what I need to do. The following is
pseudo-code for the compression script.

------------------------------------------------------------------------
BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT res_id, sum(count) AS res_count FROM view_res GROUP BY res_id FOR
UPDATE;

For each row
{
DELETE FROM view_res WHERE res_id=<res_id>::biignt

INSERT INTO view_res (res_id, count) VALUES (<res_id>,
<res_count>);
}

COMMIT;
------------------------------------------------------------------------

Right the questions for this method:

Q2.Will a "group by" used with a "select . for update" lock all the rows
used for the sum?
Q3.Am I right in saying freshly inserted rows will not be affected by
the delete because of the SERIALIZABLE transaction level?
Q4.Are there any other concurrency issues that I have not though of?
BTW, this is still at the planning phase so a complete redesign is
perfectly fine. Just seeing if anyone has greater experience than me at
this sort of thing.
TIA
Nick Barr

Nov 22 '05 #1
4 11260
"Nick Barr" <ni*******@webbased.co.uk> writes:
I personally did not like the look of 1 so I thought about using 2. The
main reason being there would be no locks that would interfere with
"updating" the view count because in fact this was just an INSERT
statement.
INSERTs are good.
Q2.Will a "group by" used with a "select . for update" lock all the rows
used for the sum?


No; it won't work at all.

regression=# select hundred,count(*) from tenk1 group by hundred for update;
ERROR: SELECT FOR UPDATE is not allowed with GROUP BY clause
regression=#

However, AFAICS it will not matter if you are using a serializable
transaction. If two such transactions try to delete the same row,
one of them will error out, so you do not need FOR UPDATE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2
on 2/16/04 10:51 AM, ni*******@webbased.co.uk purportedly said:
I am trying to gather stats about how many times a resource in our web
app is viewed, i.e. just a COUNT. There are potentially millions of
resources within the system.

I thought of two methods:

1. An extra column in the resource table which contains a count.
Not a good idea if you expect a high concurrency rate--you will create a
superfluous bottleneck in your app.
2. A separate table that contains a count using an algorithm similar
to the method presented here:

http://archives.postgresql.org/pgsql...1/msg00059.php

a. Each time a resource is viewed a new row is inserted with a count
of 1.
b. Each time the view count is needed, rows from the table are SUMmed
together.
c. A compression script runs regularly to group and sum the rows
together.


I am assuming that you are concerned about storage size, which is why you
want to "compress". You are probably better off (both by performance and
storage) with something like the following approach:

CREATE TABLE view_res (
res_id int8,
stamp timestamp
) WITHOUT OIDS;

CREATE TABLE view_res_arch (
res_id int8,
cycle date,
hits int8
);

By using a timestamp instead of count you can archive using a date/time
range and avoid any concurrency/locking issues:

INSERT INTO view_res_arch (res_id, cycle, hits)
SELECT res_id, '2003-12-31', COUNT(res_id) FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'
GROUP BY res_id;

then:

DELETE FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'

With this kind of approach you have historicity and extensibility, so you
could, for example, show historical trends with only minor modifications.

Best regards,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #3
Maybe filesystem fragmenttion is a problem ??

They told that fragmentation on multiuser system is not a problem (for
example on ext2 filesystem), because many users/ many tasks shared hdd IO
subsytem and there is not benefit for having disk low fragmented
but......
In my situation I use postgresql, PHP as apache module. I make a backup and
run e2fs defragmentation program on related partitions (ie /home and /var/ ,
where php files and database cluster lives )

Result ? About 40% (!) performance boost...

----- Original Message -----
From: "Keary Suska" <hi********@pcisys.net>
To: "Postgres General" <pg***********@postgresql.org>
Sent: Thursday, February 19, 2004 8:52 PM
Subject: Re: [GENERAL] Select for update, locks and transaction levels

on 2/16/04 10:51 AM, ni*******@webbased.co.uk purportedly said:
I am trying to gather stats about how many times a resource in our web
app is viewed, i.e. just a COUNT. There are potentially millions of
resources within the system.

I thought of two methods:

1. An extra column in the resource table which contains a count.


Not a good idea if you expect a high concurrency rate--you will create a
superfluous bottleneck in your app.
2. A separate table that contains a count using an algorithm similar
to the method presented here:

http://archives.postgresql.org/pgsql...1/msg00059.php

a. Each time a resource is viewed a new row is inserted with a count
of 1.
b. Each time the view count is needed, rows from the table are SUMmed
together.
c. A compression script runs regularly to group and sum the rows
together.


I am assuming that you are concerned about storage size, which is why you
want to "compress". You are probably better off (both by performance and
storage) with something like the following approach:

CREATE TABLE view_res (
res_id int8,
stamp timestamp
) WITHOUT OIDS;

CREATE TABLE view_res_arch (
res_id int8,
cycle date,
hits int8
);

By using a timestamp instead of count you can archive using a date/time
range and avoid any concurrency/locking issues:

INSERT INTO view_res_arch (res_id, cycle, hits)
SELECT res_id, '2003-12-31', COUNT(res_id) FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'
GROUP BY res_id;

then:

DELETE FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'

With this kind of approach you have historicity and extensibility, so you
could, for example, show historical trends with only minor modifications.

Best regards,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
Maybe filesystem fragmenttion is a problem ??

They told that fragmentation on multiuser system is not a problem (for
example on ext2 filesystem), because many users/ many tasks shared hdd IO
subsytem and there is not benefit for having disk low fragmented
but......
In my situation I use postgresql, PHP as apache module. I make a backup and
run e2fs defragmentation program on related partitions (ie /home and /var/ ,
where php files and database cluster lives )

Result ? About 40% (!) performance boost...

----- Original Message -----
From: "Keary Suska" <hi********@pcisys.net>
To: "Postgres General" <pg***********@postgresql.org>
Sent: Thursday, February 19, 2004 8:52 PM
Subject: Re: [GENERAL] Select for update, locks and transaction levels

on 2/16/04 10:51 AM, ni*******@webbased.co.uk purportedly said:
I am trying to gather stats about how many times a resource in our web
app is viewed, i.e. just a COUNT. There are potentially millions of
resources within the system.

I thought of two methods:

1. An extra column in the resource table which contains a count.


Not a good idea if you expect a high concurrency rate--you will create a
superfluous bottleneck in your app.
2. A separate table that contains a count using an algorithm similar
to the method presented here:

http://archives.postgresql.org/pgsql...1/msg00059.php

a. Each time a resource is viewed a new row is inserted with a count
of 1.
b. Each time the view count is needed, rows from the table are SUMmed
together.
c. A compression script runs regularly to group and sum the rows
together.


I am assuming that you are concerned about storage size, which is why you
want to "compress". You are probably better off (both by performance and
storage) with something like the following approach:

CREATE TABLE view_res (
res_id int8,
stamp timestamp
) WITHOUT OIDS;

CREATE TABLE view_res_arch (
res_id int8,
cycle date,
hits int8
);

By using a timestamp instead of count you can archive using a date/time
range and avoid any concurrency/locking issues:

INSERT INTO view_res_arch (res_id, cycle, hits)
SELECT res_id, '2003-12-31', COUNT(res_id) FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'
GROUP BY res_id;

then:

DELETE FROM view_res
WHERE stamp >= '2003-12-01' AND stamp <= '2003-12-31 23:59:59'

With this kind of approach you have historicity and extensibility, so you
could, for example, show historical trends with only minor modifications.

Best regards,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st connection SELECT * FROM nextkey WHERE tblname = 'PLCN'...
29
by: Mainlander | last post by:
An ISP I belong to uses Majordomo for their mailing list system. I'd like to encourage them to move to a system that uses a database, preferably psql which they already run on their server....
7
by: Paul Serby | last post by:
Why does '*select count(id) from "tblContacts"'* do a sequential scan when the field '*id*' is indexed using a btree? MySql simply looks at the index which is keeping a handy record of the...
4
by: Ed L. | last post by:
I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
4
by: Lada 'Ray' Lostak | last post by:
Hello there, I am thinking how to solve another typical problem of online systems with combination of thin client... Imagine simple case, 2 users are going to edit 'same' datas. Both see on the...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
3
by: ThunderMusic | last post by:
Hi, We have a web application developped in asp.net (I think it's not relevant, but well, it's so you know)... Yesterday, we received the following message "Transaction (Process ID 69) was...
5
by: sticky | last post by:
Hi I need to be able to lock a table against INSERT and UPDATE, but not SELECT, for the duration of a transaction. The transaction will be defined at the application level in c#, and then use...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.