Number of DB queries increases during busy period? [XOOPS general usage questions]

1

Number of DB queries increases during busy period?

2005/6/7 17:43
Gombunan
Just popping in
Posts: 30
Since: 2005/5/14

I am wondering why does the number of database queries increases for my site when it gets busier.

For example, my Poll page gets 17 to 20 queries when I am the only one logged on (when I disabled the website from other visitors/users). But as soon as I re-open it and visitors started coming in, the number of queries jumps to 45 and more.

The only blocks on that Poll page are the Poll List, Main Menu, User Menu and Who's Online block.

I suspect the Who's Online block is the one causing the problem. Can anybody confirm this if they have had similar experience?

If it's the Who's Online block, why it is causing the problem?

Thank you.

2

Re: Number of DB queries increases during busy period?

2005/6/7 18:09
m0nty
XOOPS is my life!
Posts: 3337
Since: 2003/10/24

because it uses a query per user in laymans terms

you can reduce this by making use of caching on blocks and modules..

note tho. do not cache user menu block, or who's online or contact us module.

also caching of dynamic modules such as forum or photo galleries can affect those.. if youdo cache them, then use as little time as possible, otherwise people might not get to see newly submitted items.. as the page will be cached from previous..

3

Re: Number of DB queries increases during busy period?

2005/6/7 18:10
wtravel
Posts: 987
Since: 2003/8/27

True, most likely it is one of the block creating the more queries. It could be polls or users online. You can check the code behind the blocks, but as far as I remember one block requires one query per user displayed. I cannot remember which one though

Martijn

4

Re: Number of DB queries increases during busy period?

2005/6/7 18:44
Gombunan
Just popping in
Posts: 30
Since: 2005/5/14

Thank you mOnty and wtravel for responding

I tried disabling the Who's Online block and the number of queries dropped to 10 or 11. Wow that's such a big difference!

The Who's Online block is very useful and informative and I want it to be accessible to the registered users. So may be I'll cache it for 30s or something. But I know some weird effect may appear because of this caching.

During peak time, my site gets over 150-200 or more visitors on the main XOOPS pages and over 200-300 in the forum alone. And the number will only gets bigger in the coming weeks due to the seasonal nature of my website.

Earlier today my server crashed and I have to repair the xoops_session table as a result. I hope this didn't cause the problem. Is there a way to optimize the Who's Online block besides caching?

5

Re: Number of DB queries increases during busy period?

2005/6/7 19:41
wtravel
Posts: 987
Since: 2003/8/27

As far as I can see there is not much you can do, you could reduce the number of queries by 1 or 2, but that would not be worth it. I would go for caching too.

6

Re: Number of DB queries increases during busy period?

2005/6/7 20:44
Mithrandir
XOOPS is my life!
Posts: 6320
Since: 2003/6/21

The who's online block should only have a maximum of 3 queries.

Can you turn on MySQL debug and perhaps see where those queries come from?

For example, look for repeated queries where only an ID changes

7

Re: Number of DB queries increases during busy period?

2005/6/8 15:59
Gombunan
Just popping in
Posts: 30
Since: 2005/5/14

I get many of these queries when the number of visitors increases which I assume for each visitors.


SELECT * FROM xoops_online 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=1 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=1276 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0 

SELECT member_id as uid FROM xoops_session WHERE login_type='1' AND member_id>0 AND member_id=0

Is there a way to reduce the number of query?

Stats
Goal:	$100.00
Due Date:	Jun 30
Gross Amount:	$0.00
Net Balance:	$0.00
Left to go:	$100.00

Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Re: Number of DB queries increases during busy period?

Login

Search

Recent Posts

Re: Test 3

Test 3

Re: Test2

Re: Test2

Re: Test2

Test2

Re: Xlanguage Module - Default Language Issue

Re: Xlanguage Module - Default Language Issue

Re: Xlanguage Module - Default Language Issue

Re: Xlanguage Module - Default Language Issue

Who's Online

Donat-O-Meter

Latest GitHub Commits

{{ record.sha.slice(0, 7) }} - {{ record.commit.message | truncate }}