Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-10
11
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 59

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 59

Assisted Solution

by:HainKurt
HainKurt earned 600 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 111

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 111

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 111

Accepted Solution

by:
Ray Paseur earned 900 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

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

715 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