XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. XOOPS general usage questions 
  4.  / Number of DB queries increases during busy period?
Register
1
Gombunan
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
m0nty
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
wtravel
Re: Number of DB queries increases during busy period?

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
Gombunan
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
wtravel
Re: Number of DB queries increases during busy period?

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
Mithrandir
Re: Number of DB queries increases during busy period?

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
Gombunan
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?

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

259 user(s) are online (177 user(s) are browsing Support Forums)

Members: 0

Guests: 259

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits