Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: mysql usage is terrible, how to improve?  (Read 4282 times)

0 Members and 1 Guest are viewing this topic.

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
mysql usage is terrible, how to improve?
« on: November 14, 2006, 02:39:47 pm »

Hi guys, today my ISP told me that if I don't change the gallery account will be closed because of huge mysql usage.
as I was told, I don't use indexes and there is a lot of queries that last over 3sec.
my gallery www.agrofoto.pl has 6500pictures, every day there is 1500visits and about 60.000 page views.
how can I improve mysql? it's nearly impossible to find better ISP in Poland than my current one is.
What should I do?

Thanks in advance
BR

YacentY
« Last Edit: November 15, 2006, 09:53:07 am by Hein »
Logged

Hein Traag

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: nl
  • Offline Offline
  • Gender: Male
  • Posts: 2166
  • A, B, Cpg
    • Personal website - Spintires.nl
Re: mysql usage is terrible, how to improve?
« Reply #1 on: November 14, 2006, 02:43:10 pm »

Why not go to a provider outside of Poland ? There are lots of internet space providers with good prices and good results. Look around and you will find several who also support coppermine installations.

Cheers!
Hein
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #2 on: November 14, 2006, 03:04:53 pm »

because of pings, and quality of service, till today I always told that home.pl is best provider in my sourounding (central europe), but today I am totally dissappointed with their behaviour?

any suggestions?

BR
YacentY
Logged

Hein Traag

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: nl
  • Offline Offline
  • Gender: Male
  • Posts: 2166
  • A, B, Cpg
    • Personal website - Spintires.nl
Re: mysql usage is terrible, how to improve?
« Reply #3 on: November 14, 2006, 04:10:21 pm »

Searching with the keyword "providers" gives you this page: http://forum.coppermine-gallery.net/index.php?topic=19645.0 which lists quite a lot of hosts which support CPG.

If you want to stay with your current provider and want advice on improving your MySql then maybe somebody with more knowledge on MySql can step in and offer advice.

Cheers!
Hein
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: mysql usage is terrible, how to improve?
« Reply #4 on: November 14, 2006, 05:31:48 pm »

Make sure that you have detailed stats turned off in coppermine's config (huge resource eaters).
You have way too many details (that burn queries) on your page, reduce them. Turn off the online stats, and only display the first level of categories and no albums on your index page. This will reduce the number of queries dramatically.
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #5 on: November 14, 2006, 05:38:59 pm »

what do You mean no albums on index page?
I have no stats, it was disabled at the begining because of fast growing of database size,
what kind of details are talking about?

best regards,
YacentY
Logged

Hein Traag

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: nl
  • Offline Offline
  • Gender: Male
  • Posts: 2166
  • A, B, Cpg
    • Personal website - Spintires.nl
Re: mysql usage is terrible, how to improve?
« Reply #6 on: November 14, 2006, 06:20:35 pm »

0n the url http://www.agrofoto.pl/gallery/ you have a lot of albums on the frontpage of the gallery. This eats up speed as wel. Try moving them into a categorie. Also your album and catlist are set to 2 or more rows, try trimming that down to 1 or none at all. Users wil click on anyway. Less albums posted on your frontpage, either because they are not in a category or alblist and catlist are set to 2 or more rows, means less heavy work for the MySql.

Cheers!
Hein
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #7 on: November 14, 2006, 07:25:52 pm »

ok, now it's reduced, I hope it will be enough for my ISP.
Thanks for remarks,

BR
YacentY
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #8 on: November 18, 2006, 11:14:29 pm »

sorry for responding for my post but I would like to make this topic visible :)
what is the reason to put 'and aid in' in this querry?
SELECT COUNT(*) from cpg143_pictures WHERE ((aid='41' ) OR (keywords like '%Ci�gnik tractor JD John Deree%' )) AND approved='YES' AND aid IN (6,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,130,135,136,137,140,141,142,144,146,147,149,151,152,153,154,155);

I hope this AND aid IN (... make this querry more complex and for me make no special sense to use it, it only make longer time for this querry.
What do You think? is this AND aid IN necessary?

best regards,
YacentY
Logged

Nibbler

  • Guest
Re: mysql usage is terrible, how to improve?
« Reply #9 on: November 19, 2006, 01:03:26 am »

It is necessary if you want to use the album keywords feature. If you don't want it then stop using the feature.
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #10 on: November 19, 2006, 09:28:45 am »

where can I turn it off? sorry but I don't see setting for this feature. Or should I do it via replacing php code or by deleting some properties of categories, albums, etc.?

BR,
YacentY
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: mysql usage is terrible, how to improve?
« Reply #11 on: November 19, 2006, 11:14:34 am »

Enable clickable keywords in search. Don't turn options on in the first place if you don't understand what they do. Please read the docs before asking questions.
Logged

yacenty

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 73
Re: mysql usage is terrible, how to improve?
« Reply #12 on: November 19, 2006, 11:29:11 am »

I've used it when my gallery was small and it was working very fine, I didn't know that this option may slow my gallery so much.

BR
YacentY
Logged
Pages: [1]   Go Up
 

Page created in 0.025 seconds with 21 queries.