Re: Fw: Is Coldfusion REALLY multi threaded?
Stephen Richards wrote: > > The top output only shows one postgres connection as not being idle. And if you run the offending query from psql and ColdFusion at the same time? > The fake queries separated by a time delay showed interesting results: while > that was executing, I could perform CF operations from another screen. > > This tends to point to CF rather than the ODBC driver, no? CF makes the > connection to the database, executes a query, gets the result, then knows > that it has to wait around, so looks about for other queries to do, and does > them. > > Whereas, with the long query, it issues the query and then just waits for > the answer, not doing anything else until it gets one or times out. This proves that unixODBC can have more as one open connection, but it still could be unixODBC not being able to have more as one active connection. I think that you need to be able to look deeper into ColdFusion/unixODBC as the default compilation allows to find out more. I'm sorry, all I can say for sure is that the problem does not occur for me with CF MX and the stock JDBC driver and didn't occur for me with CF 4.5 on windows with the default ODBC driver. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Fw: Is Coldfusion REALLY multi threaded?
> It is a possibility. top output would help. Also, you could try the > following code: > > > > >SELECT 1; > > > > > > > > > >SELECT 1; > > > > > This will hold on to a connection for 10 seconds. In the mean time, run > a simple "SELECT 1" in another template and see if that comes back > before the connection is released. > > > > Can anyone say for sure that this problem would be solvd if we bought the > > Enterprise edition which has, I beleive, a native postgres driver? > > Are you sure it has one? Testing MX & JDBC might be a better idea. > > Jochem > The top output only shows one postgres connection as not being idle. The fake queries separated by a time delay showed interesting results: while that was executing, I could perform CF operations from another screen. This tends to point to CF rather than the ODBC driver, no? CF makes the connection to the database, executes a query, gets the result, then knows that it has to wait around, so looks about for other queries to do, and does them. Whereas, with the long query, it issues the query and then just waits for the answer, not doing anything else until it gets one or times out. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Fw: Is Coldfusion REALLY multi threaded?
Stephen Richards wrote: > Thanks for the tips Jochem. But as I say, I know we can tidy the queries up > and make them more efficient, but what I don;t know how to do is make CF not > wait for one query to finish before starting to execute another. No matter > what I do to the queries, unless that is fixed we're storing up trouble for > the future as usage increases. > > One suggestion here was that it was the Unix Postgres ODBC driver that was > single threaded, and therefore the bottleneck. Do you have any views on > that? It is a possibility. top output would help. Also, you could try the following code: SELECT 1; SELECT 1; This will hold on to a connection for 10 seconds. In the mean time, run a simple "SELECT 1" in another template and see if that comes back before the connection is released. > Can anyone say for sure that this problem would be solvd if we bought the > Enterprise edition which has, I beleive, a native postgres driver? Are you sure it has one? Testing MX & JDBC might be a better idea. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Fw: Is Coldfusion REALLY multi threaded?
Thanks for the tips Jochem. But as I say, I know we can tidy the queries up and make them more efficient, but what I don;t know how to do is make CF not wait for one query to finish before starting to execute another. No matter what I do to the queries, unless that is fixed we're storing up trouble for the future as usage increases. One suggestion here was that it was the Unix Postgres ODBC driver that was single threaded, and therefore the bottleneck. Do you have any views on that? Can anyone say for sure that this problem would be solvd if we bought the Enterprise edition which has, I beleive, a native postgres driver? - Stephen Richards 020 7903 3226 -- - Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 10:34 PM Subject: Re: Fw: Is Coldfusion REALLY multi threaded? > Jochem van Dieten wrote: > > > > I am betting that it is the LIKE that is killing you here, it is pretty > > much impossible to index one using a normal index. Which is why you need > > a partial index on the predefined search pattern: > > CREATE INDEX test_idx ON orderhistory USING HASH (changes) > > WHERE (changes ~~ '%ST:%,%'::text); > > Whether it works depends on the cardinality of the table and the > > expected resultset. Just remember that you need to create partial > > indexes for each query pattern. > > Correction: > CREATE INDEX t1_idx ON orderhistory (get_status(orderhistory.changes)) > WHERE (changes ~~ '%ST:%,%'::text); > > Some other thing: > - remove the order by from the view, ordering is no longer guaranteed > after the next join anyway > - use count(1) instead of count(*) so toast tables do not have to be fetched > > Jochem > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Fw: Is Coldfusion REALLY multi threaded?
Jochem van Dieten wrote: > > I am betting that it is the LIKE that is killing you here, it is pretty > much impossible to index one using a normal index. Which is why you need > a partial index on the predefined search pattern: > CREATE INDEX test_idx ON orderhistory USING HASH (changes) > WHERE (changes ~~ '%ST:%,%'::text); > Whether it works depends on the cardinality of the table and the > expected resultset. Just remember that you need to create partial > indexes for each query pattern. Correction: CREATE INDEX t1_idx ON orderhistory (get_status(orderhistory.changes)) WHERE (changes ~~ '%ST:%,%'::text); Some other thing: - remove the order by from the view, ordering is no longer guaranteed after the next join anyway - use count(1) instead of count(*) so toast tables do not have to be fetched Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Fw: Is Coldfusion REALLY multi threaded?
Stephen Richards wrote: > I don't know if this is what you were looking for, but here it is: It is. > The thing is, I know there is plenty I can do to make these queries run > faster - it is just a general question: why does CF just hang for all the > other users when this query is executed from a CF page, and not when > executed from the db itself? I don't know. > You sound as though you > have experience of CF with Postgres on a Lunux box? CF on Windows and PostgreSQL on OpenBSD and Windows. > EXPLAIN ANALYZE > SELECT count(*) as count, > ROUND(sum(order_value(h.orderid))/100.0,2) as value > FROM statusstats3 h > INNER JOIN orders o ON (o.orderid = h.orderid) > WHERE h.time::date BETWEEN '2002-10-01' AND '2002-10-31' > AND h.tostatus = 'sales_approved' > AND o.status != 'order_cancelled' > AND o.existing = false > > NOTICE: QUERY PLAN: > > Aggregate (cost=5459.05..5459.05 rows=1 width=8) (actual > time=52296.17..52296.17 rows=1 loops=1) > -> Nested Loop (cost=5453.01..5459.04 rows=1 width=8) (actual > time=39563.71..39704.95 rows=571 loops=1) > -> Subquery Scan h (cost=5453.01..5453.02 rows=1 width=36) (actual > time=39563.47..39574.80 rows=742 loops=1) > -> Unique (cost=5453.01..5453.02 rows=1 width=36) (actual > time=39563.46..39570.71 rows=742 loops=1) > -> Sort (cost=5453.01..5453.01 rows=1 width=36) > (actual > time=39563.46..39565.25 rows=849 loops=1) > -> Seq Scan on orderhistory (cost=0.00..5453.00 > rows=1 width=36) (actual time=9998.10..39559.34 rows=849 loops=1) This seqscan is taking 40 seconds out of a total 53, so it is the obvious candidate for improvement. > -> Index Scan using orders_pkey on orders o (cost=0.00..6.01 > rows=1 > width=4) (actual time=0.15..0.16 rows=1 loops=742) > Total runtime: 52296.58 msec > > messina=# select count(*) from orders; > count > --- > 4914 > (1 row) > > messina=# select count(*) from orderatoms; > count > --- > 9222 > (1 row) > > messina=# select count(*) from statusstats3; > count > --- > 21452 > (1 row) > > > Statusstats3 is a view of the orderhistory table, hence the reference to > orderhistory in the explain above. How many rows are there in orderhistory? > messina=# \d statusstats3 > View "statusstats3" > Column |Type | Modifiers > --+-+--- > orderid | integer | > tostatus | character varying | > time | timestamp without time zone | > View definition: SELECT DISTINCT orderhistory.orderid, > get_status((orderhistory.changes)::"varchar") AS tostatus, > min_status_time(orderhistory.orderid, > get_status((orderhistory.changes)::"varchar")) > AS "time" FROM orderhistory WHERE ((orderhistory.tablename = > 'orders'::"varchar") AND (orderhistory.changes ~~ '%ST:%,%'::text)) ORDER BY > orderhistory.orderid, get_status((orderhistory.changes)::"varchar"), > min_status_time(orderhistory.orderid, > get_status((orderhistory.changes)::"varchar")); I am betting that it is the LIKE that is killing you here, it is pretty much impossible to index one using a normal index. Which is why you need a partial index on the predefined search pattern: CREATE INDEX test_idx ON orderhistory USING HASH (changes) WHERE (changes ~~ '%ST:%,%'::text); Whether it works depends on the cardinality of the table and the expected resultset. Just remember that you need to create partial indexes for each query pattern. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Fw: Is Coldfusion REALLY multi threaded?
moving the CF server onto another box was indeed the next line of attack, until I saw that if the query was executed from outstide CF, then other users, invluding CF users couls access the DB fine. It's definitely not table locks unless the DB puts the lock on when the query comes from CF, and not when it comes from a client. SR - Stephen Richards 020 7903 3226 -- - Original Message - From: "Jason Lees (National Express)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 4:50 PM Subject: RE: Is Coldfusion REALLY multi threaded? > It might not Be CF thats causing the problem, its most likley the DB thats > escalating the Lock to a full table lock and thus preventing other users > from running the queries until the table is free. > > Also try moving the DB onto another server, as we had a simular problem, > with ingres and moving the CF app to a new server solved the problem. > > Jason Lees > Systems Developer > National Express Coaches Ltd. > > > > -Original Message- > From: Stephen Richards [mailto:[EMAIL PROTECTED]] > Sent: 05 December 2002 16:23 > To: CF-Talk > Subject: Fw: Is Coldfusion REALLY multi threaded? > > > - Original Message - > From: "Jochem van Dieten" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, December 05, 2002 4:14 PM > Subject: Re: Is Coldfusion REALLY multi threaded? > > > > Stephen Richards wrote: > > > > > > We have 24 connections at the moment, as reported by the database. > (can't > > > use netstat because the CF server is running on the same machine as the > > > database, so it's a unix socket rather than port 5423. > > > > And how many of them are idle? In the mean time you might want to get > > somebody to look at that query, since 2 minutes is rather long. > > > how do I tell which ones are active? > > Yep, I agree with you about the query, and we're taking action on that, but > it still raises the point that CF is unresponsive while a query is > executing, and I am sure it is not supposed to be. And the more users log > on, the worse it is going to become. > - > Stephen Richards > 020 7903 3226 > -- > > > > ~| > Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Fw: Is Coldfusion REALLY multi threaded?
I don't know if this is what you were looking for, but here it is:
The thing is, I know there is plenty I can do to make these queries run
faster - it is just a general question: why does CF just hang for all the
other users when this query is executed from a CF page, and not when
executed from the db itself?
thanks, by the way, for your suggestions so far. You sound as though you
have experience of CF with Postgres on a Lunux box?
SR
EXPLAIN ANALYZE
SELECT count(*) as count,
ROUND(sum(order_value(h.orderid))/100.0,2) as value
FROM statusstats3 h
INNER JOIN orders o ON (o.orderid = h.orderid)
WHERE h.time::date BETWEEN '2002-10-01' AND '2002-10-31'
AND h.tostatus = 'sales_approved'
AND o.status != 'order_cancelled'
AND o.existing = false
NOTICE: QUERY PLAN:
Aggregate (cost=5459.05..5459.05 rows=1 width=8) (actual
time=52296.17..52296.17 rows=1 loops=1)
-> Nested Loop (cost=5453.01..5459.04 rows=1 width=8) (actual
time=39563.71..39704.95 rows=571 loops=1)
-> Subquery Scan h (cost=5453.01..5453.02 rows=1 width=36) (actual
time=39563.47..39574.80 rows=742 loops=1)
-> Unique (cost=5453.01..5453.02 rows=1 width=36) (actual
time=39563.46..39570.71 rows=742 loops=1)
-> Sort (cost=5453.01..5453.01 rows=1 width=36)
(actual
time=39563.46..39565.25 rows=849 loops=1)
-> Seq Scan on orderhistory (cost=0.00..5453.00
rows=1 width=36) (actual time=9998.10..39559.34 rows=849 loops=1)
-> Index Scan using orders_pkey on orders o (cost=0.00..6.01
rows=1
width=4) (actual time=0.15..0.16 rows=1 loops=742)
Total runtime: 52296.58 msec
messina=# select count(*) from orders;
count
---
4914
(1 row)
messina=# select count(*) from orderatoms;
count
---
9222
(1 row)
messina=# select count(*) from statusstats3;
count
---
21452
(1 row)
Statusstats3 is a view of the orderhistory table, hence the reference to
orderhistory in the explain above.
messina=#
messina=# \d statusstats3
View "statusstats3"
Column |Type | Modifiers
--+-+---
orderid | integer |
tostatus | character varying |
time | timestamp without time zone |
View definition: SELECT DISTINCT orderhistory.orderid,
get_status((orderhistory.changes)::"varchar") AS tostatus,
min_status_time(orderhistory.orderid,
get_status((orderhistory.changes)::"varchar"))
AS "time" FROM orderhistory WHERE ((orderhistory.tablename =
'orders'::"varchar") AND (orderhistory.changes ~~ '%ST:%,%'::text)) ORDER BY
orderhistory.orderid, get_status((orderhistory.changes)::"varchar"),
min_status_time(orderhistory.orderid,
get_status((orderhistory.changes)::"varchar"));
messina=# \d orderhistory
Table "orderhistory"
Column |Type | Modifiers
---+-+--
-
tablename | character varying(32) | not null
orderid | integer | not null
time | timestamp without time zone | not null default now()
who | character varying(32) | not null default "current_user"()
action| character(1)| not null
changes | text|
Indexes: orderhistory_key
Check constraints: "orderhistory_action" ((("action" = 'I'::bpchar) OR
("action" = 'U'::bpchar)) OR ("action" = 'D'::bpchar))
---------
Stephen Richards
020 7903 3226
--
- Original Message -
From: "Jochem van Dieten" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, December 05, 2002 4:50 PM
Subject: Re: Fw: Is Coldfusion REALLY multi threaded?
> Stephen Richards wrote:
> > - Original Message -
> > From: "Jochem van Dieten" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Thursday, December 05, 2002 4:14 PM
> > Subject: Re: Is Coldfusion REALLY multi threaded?
> >
> >>And how many of them are idle?
> >
> > how do I tell which ones are active?
>
> top
>
>
> > Yep, I agree with you about the query, and we're taking action on that
>
> Send me the EXPLAIN ANALYZE output together with table metrics and I
> will have a look.
>
> Jochem
>
>
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Fw: Is Coldfusion REALLY multi threaded?
How your's works is how I would have expected. At the moment , limit simultaneous requests is not ticked - perhaps I should and set it to 40. I'll try it and let you know. - Stephen Richards 020 7903 3226 -- - Original Message - From: <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 4:42 PM Subject: RE: Is Coldfusion REALLY multi threaded? > ..with regards to the long query...we have an app that has several queries that run quite long, a couple in the 3 or 4 minute range. These do not bring my CF server to a halt at all. We average almost 200 users per hour on busy days on this box. Have a look at all your CFAdmin settings. You may need to raise the 'limit simultaneous requests'. We have it set to 40 for a four proc machine. I have found through research and experience that if your app is very DB intense, raising this helps. Also look at lowering the 'restart at x unresponsive requests' We have it set at 5. Also make sure you have 'timeout requests after x seconds' checked and set reasonably. > > note that my experiences are with winblows boxen. on Linux boxen, this may very. > > Doug > > >-Original Message- > >From: Stephen Richards [mailto:[EMAIL PROTECTED]] > >Sent: Thursday, December 05, 2002 11:23 AM > >To: CF-Talk > >Subject: Fw: Is Coldfusion REALLY multi threaded? > > > > > >- Original Message - > >From: "Jochem van Dieten" <[EMAIL PROTECTED]> > >To: "CF-Talk" <[EMAIL PROTECTED]> > >Sent: Thursday, December 05, 2002 4:14 PM > >Subject: Re: Is Coldfusion REALLY multi threaded? > > > > > >> Stephen Richards wrote: > >> > > >> > We have 24 connections at the moment, as reported by the database. > >(can't > >> > use netstat because the CF server is running on the same > >machine as the > >> > database, so it's a unix socket rather than port 5423. > >> > >> And how many of them are idle? In the mean time you might want to get > >> somebody to look at that query, since 2 minutes is rather long. > >> > >how do I tell which ones are active? > > > >Yep, I agree with you about the query, and we're taking action > >on that, but > >it still raises the point that CF is unresponsive while a query is > >executing, and I am sure it is not supposed to be. And the > >more users log > >on, the worse it is going to become. > >- > >Stephen Richards > >020 7903 3226 > >-- > > > > > > > > > ~| > Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Get the mailserver that powers this list at http://www.coolfusion.com
Re: Fw: Is Coldfusion REALLY multi threaded?
Stephen Richards wrote: > - Original Message - > From: "Jochem van Dieten" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, December 05, 2002 4:14 PM > Subject: Re: Is Coldfusion REALLY multi threaded? > >>And how many of them are idle? > > how do I tell which ones are active? top > Yep, I agree with you about the query, and we're taking action on that Send me the EXPLAIN ANALYZE output together with table metrics and I will have a look. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Fw: Is Coldfusion REALLY multi threaded?
- Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 4:14 PM Subject: Re: Is Coldfusion REALLY multi threaded? > Stephen Richards wrote: > > > > We have 24 connections at the moment, as reported by the database. (can't > > use netstat because the CF server is running on the same machine as the > > database, so it's a unix socket rather than port 5423. > > And how many of them are idle? In the mean time you might want to get > somebody to look at that query, since 2 minutes is rather long. > how do I tell which ones are active? Yep, I agree with you about the query, and we're taking action on that, but it still raises the point that CF is unresponsive while a query is executing, and I am sure it is not supposed to be. And the more users log on, the worse it is going to become. - Stephen Richards 020 7903 3226 --
