1
trabis
What about caching queries?
  • 2008/9/4 11:29

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Some pages on my site do around 100 queries.
Most of this queries are equal and return the same result.
Many of them are simple things like getting the username or groups permissions.

I have made a test hack in mysqldatabase.php to cache all sql results and only make queries that are unique.

This have reduced queries from 100 to 65.

I know that this can have negative impacts as results can become incacurate, for example

querie users online -> 10
update user online -> now become 11
querie users online -> 10(because it returns the result of the first querie)

This example is not important. I was wondering if you can find me a example where this hack could become inconvenient.
If you cannot find one, then we should think in adding this as an core option.

2
Mamba
Re: What about caching queries?
  • 2008/9/4 11:50

  • Mamba

  • Moderator

  • Posts: 11409

  • Since: 2004/4/23


There were some good discussions/tips about database performance:

1) Wizanda

2) Herve

Maybe based on your tests, we could merge the best tips into one "XOOPS Database optimization" article.
Support XOOPS => DONATE
Use 2.5.11 | Docs | Modules | Bugs

3
phppp
Re: What about caching queries?
  • 2008/9/6 14:52

  • phppp

  • XOOPS Contributor

  • Posts: 2857

  • Since: 2004/1/25


In 2.3 we have XoopsCache introduced, thus I believe db query cache is now ready to go.

4
jdseymour
Re: What about caching queries?

Setting the optimization of the database with the correct query cache size will go a long way in reducing calls to the database. This is set in the my.cnf file on the server. The default my.cnf file is very basic and optimization needs to be performed on any new installation to match your server.

5
trabis
Re: What about caching queries?
  • 2008/9/16 18:33

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Quote:

jdseymour wrote:
Setting the optimization of the database with the correct query cache size will go a long way in reducing calls to the database. This is set in the my.cnf file on the server. The default my.cnf file is very basic and optimization needs to be performed on any new installation to match your server.

Can you point some good tutorial about it, or maybe help me on that? I recently adquired a dedicated server for a single XOOPS site. I have about 60000 page views/days and sql is always in the red line. My linux knowledge is almost none but I know enough to ruin the server.

6
jdseymour
Re: What about caching queries?

Sure will. I am at work now, but will copy some good links for you as soon as I arrive home.

7
trabis
Re: What about caching queries?
  • 2008/9/16 18:46

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Thank you

8
jdseymour
Re: What about caching queries?

I have found this helpful:

http://mysqldatabaseadministration.blogspot.com/2005/11/mysql-5-optimization-and-tuning-guide.html

It will give you a good start. Remember, optimization is a long process of making small changes and watching the results.

Begin by doing a "mv my.cnf my.cnf.old" to back up your current one. then move to /var/lib/mysql and do cp my-medium.cnf /etc/my.cnf .

my-medium.cnf will also give you a good basic starting point to build on.


9
trabis
Re: What about caching queries?
  • 2008/9/16 21:35

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Ok, I will try that and report back. Thanks again!

10
jdseymour
Re: What about caching queries?

Hi trabis,

Any luck for you with this?

Login

Who's Online

408 user(s) are online (261 user(s) are browsing Support Forums)


Members: 0


Guests: 408


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