Solved

MySQL Server - CRE Loaded - 150-250 Customer Groups - Server Hangs

Posted on 2010-11-10
11
381 Views
Last Modified: 2012-05-10
We have a customer that has custom pricing for each customer at the product level.

We decided to program a way for each customer to be their own Customer Group.

There are 2000 products, and about 200 Customers.
So that's 400,000 records in the Customer Groups Pricing data table.

When we go to browse the site after loading this data, the site hangs.
It's obvious that this quantity of data is the cause.

Does anyone have some suggestions on a way to better handle this situation?

Would rebuilding indexes help?
Reduce the data amount so only *different from retail* prices are imported?

Any suggestions would be most appreciated.

Thanks,
Dasher
0
Comment
Question by:dasher3000
  • 6
  • 3
  • 2
11 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34101850
what are you trying to do on your page? print 400K price in one page? if yes, implement paging or filter the data by product...

also instead of adding p records into pricving table for each customer, just put the price if it is different than org price (which is in products table)
or if there is a formula (like 10% discount for a customer for all products) then no need for pricing table, just products and customer is enough...

anyway, adding an index to group_pricing table may help PK (prdid, custid) and another index (custid, prdid) may help...

what does hangs mean? no display at all or it displays in 10 minutes or gives error msg after a while? maybe you are doing something wrong in your page, like infinite loops...
0
 

Author Comment

by:dasher3000
ID: 34102071
Hi, HainKurt.

The page displays up to 75 products in a list.
No 400K on one page. )
...I'll reduce that to 25 or so - thanks for that.

No % discounts - all custom pricing, but sometimes it only is 5 or 6 products out of the 2000, so I will implement your next suggestions as well (insert custom price record only if different from original).

Thanks for the suggestions on which columns to index - we'll try that too!

Hang = No response at all. Getting error message that MySQL has disappeared.

I'll let you know how it goes.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 34105843
you should post involving queries in the page...
there must be something weird in your queries which needs to be re-written I guess...
with those number of records no query should hang/kill the server ;)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 34105912
If you want to post the CREATE TABLE statements here, I will be glad to take a look.

You should have indexes on every column used in WHERE, JOIN, ORDER BY and GROUP BY.

You can use EXPLAIN to see what the queries are doing.

Is the "Customer Groups Pricing" table a pivot table?  Have you installed ySlow?
0
 

Author Comment

by:dasher3000
ID: 34107702
Just a quick update on progress:

We scripted the removal of all records that were the same as the original product pricing.

It has not significantly improved speed, although there is a noticeable difference (not quite *improvement*) but it is in the right direction.

Tomorrow we are going to look at the SEARCH Page MySQL Queries (the slowest of them all when we have lots of Custom Pricing) and check all the stuff you mention, Ray.

Thanks, you guys.

- Dasher
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 34108163
G'luck!
0
 

Author Comment

by:dasher3000
ID: 34118175
Here is some more information. Basically...we have made no major progress, yet.
-------------------------------------------------------------
here is slightly simplified search query:

SELECT DISTINCT p.products_image, p.products_model, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price AS final_price
FROM
(((products p LEFT JOIN products_groups pg ON p.products_id = pg.products_id)
LEFT JOIN specials s ON p.products_id = s.products_id)
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id), products_description pd
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND (MATCH (pd.products_name, pd.products_description) AGAINST ('bread*' IN boolean MODE) OR p.products_model = 'bread')

in the last line it performs search in 2 tables - 'products_description' and 'products'.
'products_description' has fulltext index for given 2 columns, 'products' doesn't seem to need one.

i tried to remove parts of search expression - run this query ending like this -
AND (MATCH (pd.products_name, pd.products_description) AGAINST ('bread*' IN boolean MODE))

..and like this -
AND (p.products_model = 'bread')

search in tables separately is ultra-fast (<0.1s for both tables), and search in both at once is sloooow (3.5s average)


--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_quantity` int(4) NOT NULL default '0',
  `products_model` varchar(25) default NULL,
  `products_image` varchar(64) default NULL,
  `products_image_med` varchar(64) default NULL,
  `products_image_lrg` varchar(64) default NULL,
  `products_image_sm_1` varchar(64) default NULL,
  `products_image_xl_1` varchar(64) default NULL,
  `products_image_sm_2` varchar(64) default NULL,
  `products_image_xl_2` varchar(64) default NULL,
  `products_image_sm_3` varchar(64) default NULL,
  `products_image_xl_3` varchar(64) default NULL,
  `products_image_sm_4` varchar(64) default NULL,
  `products_image_xl_4` varchar(64) default NULL,
  `products_image_sm_5` varchar(64) default NULL,
  `products_image_xl_5` varchar(64) default NULL,
  `products_image_sm_6` varchar(64) default NULL,
  `products_image_xl_6` varchar(64) default NULL,
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_date_available` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  `products_ordered` int(11) NOT NULL default '0',
  `products_parent_id` int(11) NOT NULL default '0',
  `products_price1` decimal(15,2) NOT NULL default '0.00',
  `products_price2` decimal(15,2) NOT NULL default '0.00',
  `products_price3` decimal(15,2) NOT NULL default '0.00',
  `products_price4` decimal(15,2) NOT NULL default '0.00',
  `products_price5` decimal(15,2) NOT NULL default '0.00',
  `products_price6` decimal(15,2) NOT NULL default '0.00',
  `products_price7` decimal(15,2) NOT NULL default '0.00',
  `products_price8` decimal(15,2) NOT NULL default '0.00',
  `products_price9` decimal(15,2) NOT NULL default '0.00',
  `products_price10` decimal(15,2) NOT NULL default '0.00',
  `products_price11` decimal(15,2) NOT NULL default '0.00',
  `products_price1_qty` int(11) NOT NULL default '0',
  `products_price2_qty` int(11) NOT NULL default '0',
  `products_price3_qty` int(11) NOT NULL default '0',
  `products_price4_qty` int(11) NOT NULL default '0',
  `products_price5_qty` int(11) NOT NULL default '0',
  `products_price6_qty` int(11) NOT NULL default '0',
  `products_price7_qty` int(11) NOT NULL default '0',
  `products_price8_qty` int(11) NOT NULL default '0',
  `products_price9_qty` int(11) NOT NULL default '0',
  `products_price10_qty` int(11) NOT NULL default '0',
  `products_price11_qty` int(11) NOT NULL default '0',
  `products_qty_blocks` int(11) NOT NULL default '1',
  `products_group_access` varchar(50) NOT NULL default 'G,0',
  `products_nav_access` varchar(255) NOT NULL default 'G,0',
  `products_ready_to_ship` tinyint(1) NOT NULL default '0',
  `sort_order` smallint(3) NOT NULL default '0',
  `vendors_id` int(11) default '1',
  `vendors_product_price` decimal(15,4) NOT NULL default '0.0000',
  `vendors_prod_id` varchar(24) NOT NULL default '',
  `vendors_prod_comments` text,
  `products_ship_price` decimal(15,2) NOT NULL default '0.00',
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `modelnumber` (`products_model`(10))
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3437 ;


--
-- Table structure for table `products_description`
--

CREATE TABLE IF NOT EXISTS `products_description` (
  `products_id` int(11) NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `products_name` varchar(64) NOT NULL default '',
  `products_blurb` text,
  `products_description` text,
  `products_url` varchar(255) default NULL,
  `products_url2` varchar(255) default NULL,
  `products_viewed` int(5) default '0',
  `products_head_title_tag` varchar(80) default NULL,
  `products_head_desc_tag` longtext NOT NULL,
  `products_head_keywords_tag` longtext NOT NULL,
  PRIMARY KEY  (`products_id`,`language_id`),
  KEY `products_name` (`products_name`(20)),
  KEY `descrip` (`products_description`(10)),
  FULLTEXT KEY `idx_name_desc` (`products_name`,`products_description`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3437 ;

0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 34120482
Well, I don't see anything that looks like a "Customer Groups Pricing" table there, but there may be some germ of an idea in the queries.

Try running EXPLAIN on that query and see what you get.  Also, try running it without DISTINCT and see if the time is different.  IIRC, SELECT DISTINCT may cause repeated table scans - perhaps EXPLAIN will give a clue about a better way to formulate the query.

Another strategy I have used successfully with long-running queries is to do a "down-select" copying part of a larger results set into a temporary table in memory.  Then the complicated activities can be performed against a smaller subset of the data.

Another strategy you might consider is the use of soundex() and metaphone() values for keyword matching.  You might be able to use these instead of a FULLTEXT index.
0
 

Author Comment

by:dasher3000
ID: 34219906
Hey, this is not abandoned. We have just not figured out our solution yet....working on it....
0
 

Author Comment

by:dasher3000
ID: 34876540
I found a part of the MySQL search query that referred to the Customer Groups Pricing tables.

Since these prices are irrelevant to the search results in our case, I just commented that part out so it just searches the products tables for string matches.

Thanks, Ray.
0
 

Author Closing Comment

by:dasher3000
ID: 34876552
I just hacked a solution myself, in my opinion.
Ray helped point me in the right direction.
Ultimately, there is probably a more correct solution.
I hope this helps someone, though!
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to fix Datetime in MySQL? 4 51
How to make a good PHP + MySQL + JS pagination system? 3 34
Wordpress Pagination 1 30
Giant ibd file for our biggest table on mysql 2 18
In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question