Author Topic: Performance when database is getting large?  (Read 8593 times)

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Performance when database is getting large?
« on: March 13, 2010, 20:33:33 pm »
Hello there people.

I have an e-shop running on Joomla 1.5.11 and VirtueMart 1.1.3 with sh404SEF enabled. It has 1985 active products in 288 categories/subcategories. It's going very well the last year and I want to thank all of you for this :)

But recently I noticed some bugs when I try to add products or categories. Sometimes the products doesn't get added, sometime's they're added but they don't show up in the frontpage. Sometimes the categories are added 2 or 3 times etc. Small bugs in product/categories additions in other words.

I'm thinking to upgrade Joomla, VirtueMart and sh404SEF to their latest stable versions and maybe the problems will get fixed. I'm also thinking to perform a repair & optimize in the database's tables. What do you think? Should I perform these actions? Will it bring a difference?

I would also like to know until which number of products/categories VirtueMart's performs without any problems. Is there anyone here having more than 2k products in his/her database? If yes, how is VirtueMart running for you? I mean....what are the limits?

Thanks in advance! :)

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #1 on: March 13, 2010, 22:00:23 pm »
Limits of 65,536+ but that is due to mysql.

As for performance, you may want to optimize your mysql tables.

Likewise, an upgrade of your shop, of at least an upgrade of your sql query files may be useful.
http://forum.virtuemart.net/index.php?topic=65103.0

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #2 on: March 13, 2010, 22:36:44 pm »
Likewise, an upgrade of your shop, of at least an upgrade of your sql query files may be useful.
http://forum.virtuemart.net/index.php?topic=65103.0

If I just upgrade to the latest VirtueMart version will the SQL files be upgraded too without problems, or it's just an additional process?

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #3 on: March 13, 2010, 22:38:54 pm »
Without problems... ideally.

I suggest you get the latest nightly build, in which case, yes, these changes are made.

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #4 on: March 13, 2010, 23:05:15 pm »
And is it safe to install the latest nightly build on my active joomla installation? Excuse me but I've just searched about nightly builds (to be honest I heard the term for the first time).

Would you suggest me to install 1.1.4 or the latest nightly build? I'm talking in terms of stability, reliability and performance. Remember I'm having a shop running actually :)

By the way the link to the nightly build seems broken... https://dev.virtuemart.net/cb/wiki/1363

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #5 on: March 14, 2010, 01:33:04 am »
Nightly build should be fine, but no, I do not ever recommend just installing over a live store.

I recommend creating a test server and copying your live site to it, then do the upgrade. Make sure everything is good to go, then move it over live.

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #6 on: March 14, 2010, 13:22:18 pm »
Thanks for your help. I've just copied my live site and I've decided to follow these steps to the test server:

1. Upgrade Joomla: 1.5.11 to 1.5.15 (done!)
2. Upgrade VirtueMart: 1.1.3 to 1.1.4 (done but some problems occured)
3. Install the latest sh404SEF
4. Repair & optimize SQL tables
5. Install VirtueMart latest nightly build

If everything goes OK then I'll do these on the live site.

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #7 on: March 14, 2010, 13:57:31 pm »
Well I've successfully upgraded to Joomla 1.5.15. I've also upgraded to VM 1.1.4 but one problem occured: the thumbs of categories are bigger than they should be.

You can see it here: this is how they should be (live site) and this is how they are (the test site).

In some categories it's even worse, for example: live site(proper) and test site (wrong).

Note #1: I use custom thumbnails, not auto-generated. Can this be the reason?

By the way the images in the products are all fine.

Thanks in advance!

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #8 on: March 14, 2010, 18:31:06 pm »
Hmmm, overall this is a minor issue to fix. Does everything else work well now? Are you noticing a steed difference?

The thumbnails are being shown at the size you made them on your new site.

The issue is, you ARE NOT USING THE SAME THUMBNAIL! Check out the url of one image over the other.

Why are you using different images now?

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #9 on: March 14, 2010, 18:43:43 pm »
Hmmm, overall this is a minor issue to fix. Does everything else work well now? Are you noticing a steed difference?

The thumbnails are being shown at the size you made them on your new site.

The issue is, you ARE NOT USING THE SAME THUMBNAIL! Check out the url of one image over the other.

Why are you using different images now?

Well I didn't change anything in the settings, I've just made a copy of the live site and upgraded VM. I even compared the settings of the live VM and the new one (the copy) and they're the same.

The thumbnail setting in my new VM is the same as my old one: 150x100pixels

I've noticed too that the URL of the images are different, but not completely! What I mean:

the URL of an image in the live site (the old) is:
Code: [Select]
components/com_virtuemart/show_image_in_imgtag.php?filename=serie_ARTE_4a256a966f7b6.jpg&newxsize=150&newysize=100&fileout=
the URL of the same image in the test site (the new) is:
Code: [Select]
components/com_virtuemart/shop_image/category/serie_ARTE_4a256a966f7b6.jpg
As you notice the filename is the same, but VM 1.1.3 (live site) passes different queries to the URLs: (&newxsize=150&newysize=100&fileout=). VM 1.1.4 (prod. site) doesn't pass these queries...this must be the problem..

Thanks for your time, I appreciate it!

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #10 on: March 14, 2010, 18:56:55 pm »
Yes, we have dealt with this some.

You may try:
http://forum.virtuemart.net/index.php?topic=63812.0
or
http://forum.virtuemart.net/index.php?topic=63195.msg220277#msg220277
and you may need (if you see "00" at end of image urls)
http://forum.virtuemart.net/index.php?topic=65684.msg217646#msg217646

or in your category template, you might want to call out image with variables passed of 150x100.

There are a few ways of handling this issue in other words.

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #11 on: March 14, 2010, 19:53:04 pm »
The real question is: are you noticing a speed difference? Have you optimized your tables yet?

Forrest

  • Full Member
  • ***
  • Posts: 1972
  • Me and my baby
    • Web Developer
Re: Performance when database is getting large?
« Reply #12 on: March 14, 2010, 19:54:13 pm »
Likewise, have you turn on your plugin and site config cache settings? turn off "browser cache" setting in cache plugin.

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #13 on: March 14, 2010, 21:54:57 pm »
The real question is: are you noticing a speed difference? Have you optimized your tables yet?

The real question for me is if everything works ok now and I can fix the problems I have in the previous installation. No I haven't optimized yet, I want to make VirtueMart working and then proceed with the optimization of the SQL.

I'm going to try the first fix for the thumbs right now. I can't find the right file to edit...can you please tell me which is it? I'm referring to this post: http://forum.virtuemart.net/index.php?topic=63812.0 but which is the template file?

mandingueiro

  • Jr. Member
  • **
  • Posts: 179
Re: Performance when database is getting large?
« Reply #14 on: March 14, 2010, 22:05:12 pm »
Hmmm this is strange, I've just noticed that the image & width of the thumbnails are defined to 200x150 in the <img> attributes. Why this happens? I have 150x100 in the backend.

For example:
Quote
<img src="http://www.core-studios.net/e-bath-prod/components/com_virtuemart/shop_image/category/serie_ECO_4a256cb6f028e.jpg" height="150" width="200" alt="serie ECO" border="0">

Oh and no I don't use cache at all. It's disabled everywhere.