next »

[gnarlyhat]

Hi guys 

Been a while

I've got a dedi with high load and would love to get some good advice.

This is a Pentium D with 3G RAM and I have about 6k sites now. It performs scraping and cloaking. Lately, I added quite a bit of sites at one go and of course the load goes up. Depending on the traffic, it may shoot up to as high as 15. Also more than 50%wa (I believe it's wait time)

Although the load is high, sites still load but of course slower. When I try to do intensive queries on large tables like search & replace or exporting a large pool of links ... nothing happens (timeout probably) and load shoots up. The site loading speed does not really matter to me that much at this time. It's the inability to perform intensive queries on the DB that is bothering me. My tables are about 5-8gigs.

Now here are my questions.

1. Will upgrading to a faster CPU let me take in more traffic and lower the load?
2. From what I can tell, I'm not short of RAM. Or am I wrong?
3. Have I reached my server's peak? Am I crazy to install that many sites on a single server?
4. Are my tables too big? Should I take caution not to make them so big?

I am asking these questions because I need to know if it's better to get a powerful one or several cheap ones. Thank you guys very much in advance

Here's my top

Code:

top - 11:13:15 up 10:40,  1 user,  load average: 5.03, 5.42, 4.93
Tasks: 112 total,   2 running, 110 sleeping,   0 stopped,   0 zombie
Cpu(s): 76.0%us,  1.3%sy,  0.0%ni, 12.0%id,  9.8%wa,  0.2%hi,  0.7%si,  0.0%st
Mem:   3365580k total,  2421600k used,   943980k free,    20516k buffers
Swap:  4192924k total,      104k used,  4192820k free,  1995704k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1340 apache    15   0 31456  14m 4360 S   11  0.4   0:01.11 httpd
 1346 apache    15   0 36336  18m 4356 S   11  0.6   0:01.23 httpd
 1421 apache    15   0 31516  14m 4356 S   11  0.4   0:00.84 httpd
 1383 apache    15   0 33672  15m 4472 S   10  0.5   0:01.01 httpd
 1388 apache    15   0 31624  14m 4364 S   10  0.4   0:00.88 httpd
 1412 apache    15   0 31492  14m 4372 S    9  0.4   0:00.79 httpd
 1414 apache    15   0 35284  17m 4360 S    9  0.5   0:01.00 httpd
 1372 apache    15   0 31520  14m 4356 S    9  0.4   0:01.13 httpd
 1370 apache    15   0 31456  14m 4348 S    8  0.4   0:01.13 httpd
 1334 apache    15   0 31536  14m 4660 S    8  0.4   0:01.19 httpd
 1416 apache    15   0 31496  14m 4360 S    6  0.4   0:01.00 httpd
 1338 apache    18   0 31360  13m 4452 S    5  0.4   0:00.96 httpd
 1384 apache    15   0 31528  14m 4352 S    5  0.4   0:01.14 httpd
 1413 apache    15   0 31272  13m 4356 S    5  0.4   0:01.04 httpd
 1285 mysql     15   0  268m  86m 4208 S    5  2.6   0:01.38 mysqld
 1371 apache    15   0 31220  13m 4512 S    4  0.4   0:00.51 httpd
 1409 apache    15   0 31604  14m 4352 S    4  0.4   0:00.96 httpd

[perkiset]

First, the sites:
All PHP? If so, are you using APC? You have plenty RAM and that would drop your load potentially by 90%. I'd look at that straight away.

DB: you mention "heavy loads [sic]" - IMO, there should not be ANYTHING that could be classified as a "heavy load" on a database connected to a website. Everything in my rig (public facing) is highly optimized so that the work load for page turnaround is really really small. DB calls on a public facing website should *almost* be cacheable IMO.

I have way more sites than that with lots of traffic on my boxes. It's about efficiency, pure and simple. I wrestle with changes of a few thousandths of a second on my boxes, because the net-net is bad if I catch a sudden load.

@faster CPU: You will not experience much benefit with a faster CPU, unless you're going from an old 386 to a pentium. The load that your system describes (from the image you supplied) looks a lot like per-request load, which is why I'd look at APC double extra quick.

@ size of tables: well indexed and laid out tables will not make much of a difference in speed from 100 records to 100,000 to 10,000,000. It takes roughly 12 FSEEKs to find a record out of a million (old BTree math) and 11 to find one in 1/2 a million. If you have only a 100 records, MySQL will not even use an index, but instead walk and evaluate every single one! So as you can see, the size means less than the layout of the database.

The only important factor here is your actual page load. Are you at a couple hundred thousand per hour? What does that look like? How much can you cache what you must distribute?

[gnarlyhat]

Thanks perk

Yes All PHP. I will check out APC. I have memcached and zend optimizer installed. Will that be a problem?

How do I cache DB calls?

Layout of the tables .. using a commercial product, Don't wanna mess with it and break everything

Yes about 40K hits per hour average.

I also get this error messages from MYSQL when the load is too high.
MYSQL DIE could not connect to the database #2002:Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

When I check maxconnections are at 1024 and it don't seem to be maxing out. Probably becos of the load. I don't know.

[perkiset]

OK - you're at about 11 per second, solid. That's totally doable, but you will need to cache your brains out to make it efficient.

What is memcached doing for you?

What is Zend doing for you? It also has a cached code manager, it may be doing what APC will do out of the box. Perhaps no benefit there.

I'd recommend putting some markers in the code, around critical sections and see what's taking the longest. Include comments in your HTML:
<!-- Pre DB load: 0.0012 -->
<!-- After first SQL: 0.0036 -->

and such. See if you can profile the load a bit. If you have Zend, I believe it will do much of that for you, which is critical here.

Caching / Efficiency: I use my own DB class, which I'll instantiate, but not connect until absolutely necessary. Then I'll try to lump DB calls into as tight as few as possible. For example, I have a single stored procedure that I'll call which tells me if it's a surfer or a spider, tracks the incident etc. Are you storing state? You might consider storing your state vars in APC rather than a database. You might even consider setting up a tracking mechanism that dumps <the surfing incident> into a location in APC, then another process that casually walks the box and picks up expired sessions and runs them to the DB to lower DB hits.

Cached pages: for high-volume pages I keep them in APC, stored under the name of the URL. So for example, I'll have a cache record, "[siteid]/index.html" so that if a surfer comes in, and I have that already stored in the cache, I'll grab it and spit it out before I've done much of anything else. That is a BIG time saver. Cached queries: If you can store most-used queries as views or stored procedures, they will be pre-prepared and repetitive hits to the DB will be less impactful, because MySQL's internal caching mechanisms will come more into play. Another thing to do is, if you have highly-repetitive queries, pull them into PHP as an array, then APC them so that you don't have to hit the DB for them again. Or if you are REALLY aggressive, store them as code in a temporary PHP file - them "require" the file - it will already be compiled and will load up *astonishingly* fast. This is an excellent way to cache things IF you have APC running, because the image of the array is already in RAM - you don't have to pull the trigger on the OS at all.

Those are just a couple quick thoughts - I'm off for the evening. Ping back tomorrow if you need more.

/p

[gnarlyhat]

memcached is used for a new system which is being developed. It's about 80% of 1 part out of 3 parts done and I'm only running 2 sites with it for testing purposes. I knew that I need some kind of caching to be more efficient so the coder used memcached for it. It is far from done and it would really benefit from some caching cos I'm tracking every single bit of a visitor including the color of his underpants

Zend Optimizer is used becos the commercial code is encoded.

I'll keep the goodie bits of advise you have for the new system but 99.9% of the traffic is hitting me on the sites that are generated by the commercial product that I use and there's nothing much I can do with it's code nor DB.

Will APC be able to cache stuff by itself without any code insertions? Code is encoded

[perkiset]

APC is a compiled-code cacher, as well as a user slot cacher. So any code that PHP compiles will get let in RAM and then called again until the mtime on the original file changes. I don't know about encoded code, I haven't played with that. But it is also a simple RAM handle to a variable, so it is considerably faster than memcached as well. Rather than going through the TCP stack to get what you want, you just $newvar = apc_fetch('varname') and presto, you've got it. Another handy thing is that variables are stored in native format, so apc_store($myArray) will keep it in array form in the cache for use by other processes. Since it is bound to the Apache instance, there is only one cache and it is usable by all instances of PHP. It rocks, and is the reason I abandoned memcached myself.

[nutballs]

on that note, you dont actually do anything code side right? its just automatic if you compiled apache with it right? (or php can't remember which).

[perkiset]

It's actually a plug in, I don't think you need to recompile PHP with it, because you have to reference the SO in your php.ini... so I think it can be done completely post-compile. But yes, it is attached to PHP exclusively, and then once it's live, there's nothing more to do except configure the php.ini with how much RAM you want to allocate to it. I think it comes at 128M by default, which, on a big box is way too small. I think I give it a G on my production boxes.

And once it's installed, then the apc_fetch and apc_store functions are automatic. There's another great feature - you can ask for a list of everything in the cache - including last touch times etc ... so you can do your own garbage collection out of the flow of a web request as well, which is precisely what I do. Also, since I have many pages cached, and then will edit them, I wrote a little cache manager that shows me all items that preg_match(a pattern) and then I can view their contents or kill them if I want to. Vurrah handy indeed.

[gnarlyhat]

Ok ... lemme roll up that joint and see if I can take in more of this. Thanks a lot perks  

Would be awesome if I could use it without touching any code.