You are here:  » Advice on servers

Active Forum Topics


Advice on servers

Submitted by wilkins on Fri, 2006-12-15 11:39 in

hi

i have now been building a site for the past two months, i have over4 00 merchants on split between 10 sub domains (well over 1,000,000 roducts0, the total size of the mysql is approcing 3gig. In the last few days the server has been crashing, with the message unable to allocate memory, this is down to search engine activity, so its time to put it on a new server.

Has anyone on this forum got this ammount of info on a server that is working effectivly, if so would you share the specs, any help would be great.

thanks

Brent

Submitted by support on Fri, 2006-12-15 11:47

Hi Brent,

What server spec are you working with at the moment?

Submitted by wilkins on Fri, 2006-12-15 12:09

Celeron® 2.0GHz1 processor
1x80 GB disk drive
1 GB RAM
Red Hat Fedora Core 4 OS
cpanel/whm

Increased ram from 512 to 1 gig to see if solved problem.

Submitted by support on Fri, 2006-12-15 12:37

Hi Brent,

It's not necessarily how much ram is installed, but how much is actually available to MySQL via its configuration. In particular;

key_buffer_size (default 8M)
sort_buffer_size (default 2M)

For max performance; the key_buffer_size is recommended to be 30% of server memory; for example 300M in your case). Also, as Price Tapestry relies heavily on sorted queries; a substantial increase in sort_buffer_size, say to 32M should help.

These parameters need to be set in your my.cnf (or similar file) and will require a restart of the server (don't forget to make a backup of the existing configuration if you try this!)...

Cheers,
David.

Submitted by wilkins on Fri, 2006-12-22 09:12

hi david

Did what you suggested, memory useage still high but server no loger crashes, thanks

Brent

Submitted by Harvey on Sun, 2007-01-07 20:33

Hi Brent,

I'm going to be starting a similarly large project soon and was looking into the server implications. Nice to see price tapestry's running pretty well for you!

Could I ask what your site is? Cheers.

Submitted by bwhelan1 on Sun, 2007-05-13 21:44

David,

In response to my question at:
http://www.pricetapestry.com/node/874, I followed your recommendations for changes to my.cnf on this page since my server is basically the same as "wilkins".

I used the following:

key_buffer_size=300M
sort_buffer_size=32M

Now when clicking on the "Merchant" link for a merchant with over 320K products, CPU goes to 2.5, Memory usage is about 32% but /tmp usage still goes high (82% at it's highest). Should I try something else?

CPU usage didn't seem to be affected by the changes as it always hovers at around 30%, It's "/tmp" filling up that concerns me.

Bill Whelan

Submitted by support on Tue, 2007-05-15 08:34

Hi Bill,

I wouldn't be too concerned regarding /tmp filling up as it should be partitioned to a specific size. You can find out by running the "df" command when logged in.

How quickly do the results appear for a merchant search with 320K products?

Submitted by bwhelan1 on Tue, 2007-05-15 19:45

David,

The site is: {link saved} and the merchant in question is Bike Bandit. There are over 300K products in the feed.

What kind of query is sent by clicking on the merchant link?

I set the following in the my.cnf:

key_buffer_size=300M
sort_buffer_size=64M

I have 1G of RAM, 2GHZ Opteron processor and /tmp is set to 1G. I have a 250GB SATA drive so it's pretty fast.

Any recommendations you have would be greatly appreciated.

Bill Whelan

Submitted by support on Mon, 2007-05-21 07:59

Hi Bill,

I'm pretty sure this is down to the sorting that has to go on when the merchant query is exectued. Basically, MySQL has to sort 300,000 rows into price descending (the default)! Further to my email to you, i'll look into this with you as soon as i'm back online full time - I think it will require an alternative way of providing the full merchant search as sorting 300,000 in real time is probably inpractical...

Cheers,
David.

Submitted by bwhelan1 on Wed, 2007-05-23 15:05

I found a great article on optimizing MySQL:
http://www.databasejournal.com/features/mysql/article.php/3367871

Could you tell me what type of query is executed by clicking the "merchant" link?

Bill Whelan

Submitted by support on Thu, 2007-05-24 12:32

Hi Bill,

When performing a merchant: search, MySQL will be searching against the merchant column, which is indexed. The performance issues in this respect will be as a result of the sort, which makes a partial table scan necessary...

This is one of the aspects I will look at with you when looking at some alternative database code for your large site, although we must bear in mind that sooner or later the limitations of MySQL will be reached regardless of any workarounds that may improve performance..

Cheers,
David.

Submitted by bwhelan1 on Sun, 2007-05-27 15:16

Perhaps an easy way out would be to eliminate any links to the "Merchants" page and block it in .htaccess.

Bill Whelan

Submitted by support on Tue, 2007-05-29 13:38

Hi Bill,

If you're happy not to have the merchant: search then it is easy to remove - simply delete the following line (36 in the distribution) from search.php:

    case "merchant":

The link to the Merchant A-Z can then be removed from the home page in index.php

Cheers,
David.

Submitted by clare on Wed, 2008-08-20 17:52

Hi David
Does the advice in this thread apply to server overload that occurs when importing very large feeds? I have read another thread about putting the sleep code in import.php which I am going to do, but wondered if this also would help.

Thankyou

Submitted by support on Wed, 2008-08-20 17:54

Hi Clare,

This is more relevant to search performance rather than import performance - the sleep() modifications seem to help a lot in terms of reducing server load.

Cheers,
David.

Submitted by Mark Hennessy on Sat, 2013-07-27 06:58

Hi David,

"sort_buffer_size, say to 32M should help"

Is this still relevant or should I give it more? My VPS has 4gig of memory and I have set key_buffer_size to 1300mb

Submitted by support on Sat, 2013-07-27 08:13

Hi Mark,

I need to study this in more detail, particularly now that dedicated and VPS/cloud servers with very large amounts of RAM are readily affordable; but what i've been tending to do is to set both to 20% of server memory. The script does rely heavily on sorting so if memory isn't an issue, allocating more than enough will ensure fastest possible performance.

I will look into how to evaluate ideal values more precisely and in particular how to measure usage so that they aren't unnecessarily high...

Cheers,
David.
--
PriceTapestry.com

Submitted by stevewales20 on Sat, 2013-07-27 20:31

Not sure if anyone is interested but I went down the route of using NginX with php-fpm instead of good old apache. I also installed varnish (http accelerator) and APC (Alternative PHP Cache) and it seems to be running like a dream compared to how it used too.

Also recommend David's index updates if your not running the latest version.