Queries Overloading Server

I just had an account banned for excessive MySQL queries - totalling 2000 per single page view. I think it has something to do with the number of options available on some products. Is there any way to minimize the number of queries while still keeping those options available?

	     15 Query       select master_categories_id from products where products_id = '9'
	     15 Query       select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,4,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0001-01-01') and (sale_date_end >= now() or sale_date_end = '0001-01-01') and (sale_pricerange_from <= '0.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '0.0000' or sale_pricerange_to = '0')
	     15 Query       select options_id from products_attributes where products_attributes_id='888'
	     15 Query       select products_options_type from products_options where products_options_id='16'
	     15 Query       select products_attributes_filename, products_attributes_maxdays, products_attributes_maxcount
	     15 Query       select max(products_attributes_id) + 1
	     15 Query       select products_model
	     15 Query       select products_name
	     15 Query       select * from products_options
	     15 Query       select * from products_options_values
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id
	     15 Query       select products_options_name
	     15 Query       select products_options_id

what version of zen cart are you using?

there isnt a solution at this time,.
wilt has said that in the next major release that the db querries are going to be much smaller...

The host is reporting that even with category counts disabled, the number of options and values queries are still in the range of 200 on single page.

As for the version, I can't double check, but I thought it was 1.3... this is what's in the index.php file

// $Id: index.php 1591 2005-07-15 19:57:46Z ajeh $

I'm having them check the version number in install.txt

Looks like 1.2.6 - are there improvements in 1.3?

Yes as the tables are indexed better.

I have customers that have tons of products in tons of categories with loads of options and we dont really have this issue....

Who is your host?

Dathorn.

I have a grep of the queries that are being run that I could show you, but I can't attach files and its too long to post in this message (10000 character limit).

Now having upgraded to 1.3...

I'm getting:

Fatal error: main(): Failed opening required '' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /includes/templates/custom/templates/tpl_index_categories.php on line 45

On the index page.

Warning: main(DIR_WS_BLOCKSblk_address_format_us.php): failed to open stream: No such file or directory in ./includes/templates/custom/templates/tpl_login_default.php on line 194

Warning: main(DIR_WS_BLOCKSblk_address_format_us.php): failed to open stream: No such file or directory in ./includes/templates/custom/templates/tpl_login_default.php on line 194

Fatal error: main(): Failed opening required 'DIR_WS_BLOCKSblk_address_format_us.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /includes/templates/custom/templates/tpl_login_default.php on line 194

On the login page.....

and none of the products are showing up in the categories - even though they're all showing up and active in the admin.

Please tell me you didnt upgrade a live site

change your hosting
it is better solution

The site is still in development, its not live. I have backups of the database and all files.

Changing the host is not an option at this point, but I am considering it in the future.

If your host is chocking your connection,

do you have SSH access?

If so can you run

the following and post the information here...
this is just info on the CPU and RAM nothing critical

cat /proc/cpuinfo
then
cat /proc/meminfo

I've rolled back a copy of the 1.26 version and have both 1.3 and .126 running on the server with separate databases and files..

It would seem that for each option on a product, 2 queries get fired. one for the option_value and one for the option_name?

This particular product has 96 potential option values for Colour - giving us 200 queries on that page?

Did you run the CPU and MEM tests for me?

The host isn't choking the connection with any particular quotas or limits, but manually suspends the account if they determine that a script is using too much processor or memory.

CPU:
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2791.709
cache size : 512 KB
physical id : 3
siblings : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 5570.56

cat /proc/meminfo
MemTotal: 2074920 kB
MemFree: 64508 kB
Buffers: 127012 kB
Cached: 1431516 kB
SwapCached: 32 kB
Active: 1344748 kB
Inactive: 523116 kB
HighTotal: 1179520 kB
HighFree: 2176 kB
LowTotal: 895400 kB
LowFree: 62332 kB
SwapTotal: 819304 kB
SwapFree: 819244 kB
Dirty: 2880 kB
Writeback: 0 kB
Mapped: 264552 kB
Slab: 126188 kB
CommitLimit: 1856764 kB
Committed_AS: 916896 kB
PageTables: 4060 kB
VmallocTotal: 118776 kB
VmallocUsed: 3528 kB
VmallocChunk: 113848 kB
HugePages_Total: 0
HugePages_Free: 0
Hugepagesize: 2048 kB

they are running a high end processor,
and 2 GB of ram,

they need to put less accounts on a server.

I run celeron 2.4 with 2 GB ram and dont have this issue

Yeah, I'm seriously considering moving in a couple months when we have another application to justify the cost of a dedicated server.

In the meantime, you don't have any ideas on the 1.3 errors do you?

Thanks again for all your help!

There is a short term work around, please refer to it.

Rotating Db Users

.