Advice on servers

Hi Brent,

What server spec are you working with at the moment?

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.

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.

hi david

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

Brent

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.

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:

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

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?

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:

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

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.

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

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.

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

Bill Whelan

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:

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

Cheers,
David.

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

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.

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

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

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.