Solved

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

Posted on 2010-11-10
11
371 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
G'luck!
0
 

Author Comment

by:dasher3000
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 300 total points
Comment Utility
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
Comment Utility
Hey, this is not abandoned. We have just not figured out our solution yet....working on it....
0
 

Author Comment

by:dasher3000
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now