noodleman

iTrader: (13)

must be the twins thread in the picture share forum

charlie_rdstr_Z

iTrader: (2)

It could be all the noobs coming out and buffet posting.

silvrhand

Adjusted the indexes they were kind of messed up in the database, let me know if you guys notice better performance now.

35ounces

iTrader: (5)

still slow here and I get a lot of "Server is Busy" messages.

silvrhand

Thanks,

There were some duplicate indexes in the db, I was working on it a lot last night I found the query that is killing the server locking the thread/post tables for 60+ seconds, just gotta find out where it is coming from.

silvrhand

It's really weird too cause it's so "peaky" that even Sar isn't picking up the high load for the very short periods of time.

12:00:01 AM CPU %user %nice %system %iowait %idle
12:10:01 AM all 10.44 0.00 1.09 4.38 84.09
12:20:01 AM all 8.91 0.00 0.94 5.16 84.99
12:30:01 AM all 6.83 0.00 0.72 2.09 90.36
12:40:01 AM all 7.62 0.00 0.73 2.25 89.39
12:50:01 AM all 8.09 0.00 0.81 3.05 88.05
01:00:01 AM all 6.67 0.00 0.69 1.89 90.76
01:10:01 AM all 7.84 0.00 0.70 2.63 88.84
01:20:01 AM all 6.33 0.00 0.61 3.35 89.71
01:30:01 AM all 5.51 0.00 0.50 1.41 92.58
01:40:02 AM all 6.05 0.00 0.60 3.45 89.90
01:50:01 AM all 5.16 0.00 0.50 1.04 93.30
02:00:01 AM all 4.31 0.00 0.43 1.33 93.93
02:10:01 AM all 4.51 0.00 0.46 2.52 92.51
02:20:01 AM all 5.13 0.00 0.47 1.33 93.07
02:30:01 AM all 3.54 0.00 0.38 1.30 94.78
02:40:01 AM all 4.92 0.00 0.47 1.60 93.01
02:50:01 AM all 4.22 0.00 0.42 0.58 94.78
03:00:01 AM all 2.57 0.00 0.28 0.65 96.49
03:10:01 AM all 2.55 0.00 0.34 0.59 96.52
03:20:01 AM all 1.73 0.00 0.22 0.72 97.33
03:30:01 AM all 2.26 0.00 0.24 0.18 97.32
03:40:01 AM all 2.36 0.00 0.28 0.20 97.17
03:50:01 AM all 2.57 0.00 0.30 0.50 96.62
04:00:02 AM all 2.98 0.00 0.35 2.23 94.44
04:10:01 AM all 8.28 0.00 0.68 14.78 76.26
04:20:01 AM all 4.69 0.00 0.44 1.81 93.06
04:30:01 AM all 3.17 0.00 0.34 1.28 95.22
04:40:01 AM all 4.60 0.00 0.48 1.51 93.41
04:50:01 AM all 4.28 0.00 0.49 0.91 94.32
05:00:01 AM all 4.19 0.00 0.47 1.06 94.28
05:10:01 AM all 5.03 0.00 0.56 1.54 92.87
05:20:01 AM all 7.35 0.00 0.74 1.91 90.01
05:30:01 AM all 5.51 0.00 0.60 2.61 91.29
05:40:01 AM all 6.85 0.00 0.73 5.58 86.84
05:50:01 AM all 6.03 0.00 0.68 2.76 90.53
06:00:01 AM all 7.07 0.00 0.73 1.14 91.06
06:10:01 AM all 8.10 0.00 0.81 3.53 87.56

06:10:01 AM CPU %user %nice %system %iowait %idle
06:20:01 AM all 7.18 0.00 0.78 1.52 90.51
06:30:01 AM all 9.03 0.00 1.00 1.94 88.02
06:40:01 AM all 9.04 0.00 0.93 3.89 86.13
06:50:01 AM all 12.85 0.00 1.18 3.36 82.61
07:00:01 AM all 9.78 0.00 1.08 5.20 83.95
07:10:01 AM all 10.73 0.00 1.09 4.30 83.89
07:20:01 AM all 11.05 0.00 1.14 3.91 83.89
07:30:01 AM all 10.58 0.00 1.07 2.21 86.14
07:40:01 AM all 11.75 0.00 1.23 6.62 80.40
07:50:01 AM all 13.68 0.00 1.44 7.81 77.07
08:00:01 AM all 13.17 0.00 1.43 9.86 75.53
08:10:01 AM all 14.36 0.00 1.41 3.18 81.05
Average: all 6.76 0.00 0.70 2.83 89.71

EM-EFER

iTrader: (6)

CPU Is barely even being touched.. you guys running HP's, IBM or what?

silvrhand

Dell's hardware, I'm not much one for IBM/Compaq due to some of the propiertary devices they use which can create driver issues for the open source community.

Dell is pretty much off the shelf stuff, it's not the fastest but it's pretty damn close and a damn good price.

EM-EFER

iTrader: (6)

Looks like what you guys have already stated.. That there is a problem with the SQL DB.

No need for another server from what the log above is telling me.

EM-EFER

iTrader: (6)

I like Dell's, we have no problems with them here either.. We just found HP DL760/580's are best with handling SQL DB's. Esp within a clustered enviroment.

silvrhand

The HP DL585 rocks, it's a very nice machine espicially with the dual core quad opteron configuration. We ordered 90 of them at work for a new application deployment, very very fast system.

We are looking for a new machine soon though, the number you see are a bit misleading due to 10 minute averages, so to handle peak periods we need to actually move the DB to seperate machine and put two webservers in front to scale to the future.

I'm waiting on the new intel woodcrest platform to hit the market in a couple months, which will be the new db platform, and we'll recycle the existing db machines to the front end. Woodcrest crushed the HP DL585 by like 30% in the db tests.. mm good stuff, of course I'll add more spindles than we have now too, hopefully we'll get a 5x72G spindle with a hot spare which should provide us some good I/O as well so we can go to full index searches again in the DB.

EM-EFER

iTrader: (6)

Thank god my350z is rich and can afford one of those badboys! I saw woodcrests benchmarks and was pretty impressed just a little to expensive in a 2400 server datacenter!

silvrhand

Warning: High Tech Geek stuff..

As you can see we are pretty good shape for the most part there are a few things that I need to address.


__ Key_________________________________________________________ _____
Buffer usage 56.63M of 64.00M %Used: 88.49 Watching this closely to make sure we have enough for all the indexes.Write ratio 0.71
Read ratio 0.00

__ Questions ___________________________________________________________
Total 927.20k 97.97/s
QC Hits 330.70k 34.94/s %Total: 35.67
DMS 304.48k 32.17/s 32.84
Com_ 146.27k 15.46/s 15.78
COM_QUIT 145.75k 15.40/s 15.72
+Unknown 1 0.00/s 0.00
Slow 47 0.00/s 0.01 %DMS: 0.02
DMS 304.48k 32.17/s 32.84
SELECT 177.93k 18.80/s 19.19 58.44
UPDATE 105.75k 11.17/s 11.40 34.73
INSERT 16.71k 1.77/s 1.80 5.49
DELETE 2.47k 0.26/s 0.27 0.81
REPLACE 1.63k 0.17/s 0.18 0.53
Com_ 146.27k 15.46/s 15.78
change_db 146.01k 15.43/s 15.75
show_status 113 0.01/s 0.01
show_proces 94 0.01/s 0.01

__ SELECT and Sort _____________________________________________________
Scan 14.22k 1.50/s %SELECT: 7.99
Range 41.34k 4.37/s 23.24
Full join 12 0.00/s 0.01
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 7.33k 0.77/s
Sort range 44.32k 4.68/s
Sort mrg pass 5 0.00/s

__ Query Cache _________________________________________________________
Memory usage 52.02M of 128.00M %Used: 40.64 Updated this morning to allow more query_cache.

Block Fragmnt 7.04%
Hits 330.70k 34.94/s
Inserts 176.76k 18.68/s
Prunes 1 0.00/s
Insrt:Prune 176.76k:1 18.68/s
Hit:Insert 1.87:1

__ Table Locks _________________________________________________________
Waited 4.88k 0.52/s %Total: 0.88 This is the killer right here, table locks are the SUQ..
Immediate 546.79k 57.78/s

__ Tables ____________________________________________________________ __
Open 1.58k of 2500 %Cache: 63.20 Updated this morning, we had a table cache of 1200, I doubled it to see if we could reach 2000+ open tables, this should help as well.
Opened 1.59k 0.17/s

__ Connections _________________________________________________________
Max used 240 of 1000 %Max: 24.00
Total 145.86k 15.41/s

__ Created Temp ________________________________________________________
Disk table 3.42k 0.36/s I'm not sure why we are going to disk table when I have tmp table size set to 64M already.. I guess I'll have to adjust more here
Table 6.03k 0.64/s
File 10 0.00/s

silvrhand

Woodcrest in a 2U rack mount will not be much more than what you are paying now. We are *NOT* rich by far, the bandwidth cost is of course the killer, hardware is pretty easy these days and cheaper than it has ever been.