More CPU or More RAM?

Posted by xqi on April 21, 2010 at 6:07pm

I have a 1.5G database which includes cache tables. about 40k users and 40k nodes.

Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. So I am planning to upgrade it to a dedicated serer.

Here are two choice of my server:

Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM
Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM.

They are roughly the same price. (150$)

I know the best way to do this is to benchmark the two servers, but I can't do that, can only pick one. Could anyone of you tell me which one is better for higher MySQL performance, based on your experience? or which one will you pick if you have to pick one? Thanks

this is my current's server vmstat's output

root@server:~ # vmstat -a

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free  inact active   si   so    bi    bo   in   cs us sy id wa st
 1  0   7704 244240 458168 698128    0    0     3   148    3    4  6  5 87  2  0

Comments

MySQL does not scale

Posted by philipnet on April 21, 2010 at 8:06pm

MySQL does not scale processor-wise; current versions are (IIRC) single threaded.
A dual-core processor would be plenty; MySQL could hog one core whilst the rest of the OS runs on the other.

MySQL loves RAM. The more you can give it, the happier it will be.

I would go with the Intel Pentium G6950 (Dual Core) and 8G RAM.
and I would software-RAID 1 (mirror) the two SATA HDDs as I wouldn't want to loose that data!

Find your bottleneck first

Posted by gansbrest on April 21, 2010 at 8:43pm

I would suggest to find out what exactly slows you down. It may be not a database or memory problem at all.

Install xdebug with KCachegrind and profile your slow pages.

Dedicated server selection also depends on what you will be running on it.

I would go with more cores if you want to run both web server and mysql server on the same box + you can always add more ram.

But again - find your bottleneck first before upgrading.

here is my iostat output

Posted by xqi on April 21, 2010 at 9:06pm

here is my iostat output

root@server:~ # iostat -m
Linux 2.6.28.3-linode17 (mail.viscosolutions.com)       04/21/2010

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.95    0.00    4.66    2.17    0.03   87.18

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
xvda             66.78         0.02         1.06      33821    1671072
xvdb              0.01         0.00         0.00         89        141

it has been a lot of writing to disk operation. i think it is mysql tmp table related. my site isn't a very high traffic site, but i always suspect the VPS's disk IO is pretty bad.

btw, it seems the disk has much more write than read, is this normal for drupal site?

Measure

Posted by gansbrest on April 21, 2010 at 9:32pm

Use iostat -dx 5 to get extended statistics.

Then you can calculate number of concurrent requests the device is serving:

concurrency = (r/s + w/s) * (svctm/1000)

CPU bound machine shows high number in vmstat 5 us column + big CPU queue (r column)

Note also that vmstat and iostat displays averages since server was booted in the first row, so you need to wait to see actual data which will be displayed every 5 seconds in our case.

I/O bound machine usually spend a lot of time waiting for I/O requests to complete. In this case expect many processes in uninterpretable sleep (vmstat b column)

Usually you also want your si and so (swap activity) columns in vmstat to be 0 most of the time.

Analyze your data carefully to get an idea of what your system currently doing.

how does this look to

Posted by xqi on April 21, 2010 at 9:43pm

how does this look to you?


Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.40   358.80  2.60 165.40    46.40  4193.60    25.24     1.30    7.77   4.94  82.92
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    10.20  4.80  6.80    60.80   136.00    16.97     0.19   16.09   6.57   7.62
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   206.20  1.60 119.20    12.80  2603.20    21.66     0.75    5.84   0.81   9.74
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   171.80  8.00 197.60   100.80  2955.20    14.86     1.44    7.21   1.00  20.66
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   131.20  0.60 104.00    16.00  1881.60    18.14     1.34   12.78   0.16   1.66
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00     3.60  4.20  0.80   102.40    35.20    27.52     0.03    5.28   4.92   2.46
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    44.60  4.40 15.40    52.80   480.00    26.91     0.08    4.01   1.77   3.50
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              2.80    14.80  6.00  8.00   264.00   182.40    31.89     0.04    2.86   1.29   1.80
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              2.00    25.40  3.60 10.00    99.20   283.20    28.12     0.05    3.53   2.99   4.06
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    17.80  4.40 14.20    41.60   256.00    16.00     0.04    2.22   1.32   2.46
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    79.40  0.60 180.40     4.80  2078.40    11.51     0.44    2.42   0.50   8.98
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   447.20  2.80 132.80    40.00  4640.00    34.51    15.08  111.24   1.26  17.12
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    24.40  0.00 16.60     0.00   328.00    19.76     0.05    3.00   0.08   0.14
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   453.40  0.40 176.40     3.20  4718.40    26.71     2.19    9.31   0.29   5.06
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   398.40  2.00 71.40    16.00  4078.40    55.78    19.76  276.54   4.39  32.24
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    19.60  1.00 11.00    24.00   244.80    22.40     0.02    1.97   0.38   0.46
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00   257.20  5.20 132.00   120.00  3113.60    23.57     0.91    6.62   0.33   4.52
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    12.60  2.80 12.80    38.40   203.20    15.49     0.04    2.65   1.23   1.92
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    16.37  1.60  9.18    39.92   204.39    22.67     0.03    2.48   0.74   0.80
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00     7.60  0.60  3.40     4.80    88.00    23.20     0.02    5.45   5.35   2.14
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    12.80  6.80  6.80    70.40   156.80    16.71     0.04    2.96   2.07   2.82
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    13.40  2.80  7.60    27.20   168.00    18.77     0.03    3.19   2.58   2.68
xvdb              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvda              0.00    13.80  1.00  8.00     8.00   174.40    20.27     0.02    2.40   1.18   1.06

Active Data Set

Posted by gansbrest on April 21, 2010 at 9:54pm

even if you got 1,5 GB of data I doesn't mean that you need at least 1,5 GB of RAM to hold it. The important thing here is Active Data Set which is different for every website but usually it much less than total data size.

There is a number of things you can do to speed up the website, many (if not all) of them were discussed in this group.

If your traffic mostly anonymous users, then you may want to check Boost module, or look into Revers Proxy like Squid or Varnish

For authenticated users you may want to use Memcached

some kind of opcode caching like APC

Your output looks good to me

Posted by gansbrest on April 21, 2010 at 10:20pm

Your output looks good to me for that sampling period. Looks like your OS successfully merge requests and group them for writing.

Are you running just MySQL on this server?

Could you also paste vmstat 5 output?

What makes you think there is something wrong with your I/O?

my server is Xen VPS and

Posted by xqi on April 21, 2010 at 11:25pm

my server is Xen VPS and known as bad IO.

each time I cleared my cache, all the http requests will be queued. once the server is running out memory, it is dead. so normally whenever i clear the cache, I have to shutdown apache first, then manually run php index.php, wait for 10-15 seconds until the output begins, finally restart the apache, which was really a pain.

see my vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0  63912 485492  30832 528196    0    0     3   151    3    4  6  5 87  2  0
 1  0  63912 490792  30832 528128    0    0     0  3262 3208 2263 15 15 70  1  0
 1  0  63912 491324  30832 528128    0    0     0  4153 1984 1016  6  5 89  0  0
 3  3  63912 469268  30880 534484    0    0     3  8228 5761 10414 20 21 50  9  0
 4  1  63912 469432  31032 528180    0    0     2  6090 3989 3657 16 18 45 21  0
 0  1  63912 490820  31300 528284    0    0     0  4702 3750 7565  9 11 57 23  0
 5  1  63912 438128  31488 544816    0    0     0  5248 4172 5236 17 16 36 32  0
 1  2  63912 473348  31556 528472    0    0     4 10305 6159 7168 22 27 35 16  0
 2  0  63912 473732  31588 530352    0    0     0   510 2357 2746  7  6 76 11  0
 1  0  63912 475924  31624 532420    0    0     0  5637 3363 1608 14 14 62 10  0
 1  0  63912 475304  31668 528376    0    0    16  5206 3277 2195 13 12 70  5  0
 2  0  63912 470488  31700 528608    0    0    39  4304 2492 2076  8  7 69 16  0
 2  0  63912 449232  31700 532708    0    0     2   853 1576 2012  5  3 91  0  0
 3  0  63912 425488  31720 547080    0    0     0  8204 5107 5380 21 22 48  8  0
 2  0  63912 460348  31752 528620    0    0     0  6982 3292 2726 13 14 73  0  0
 2  0  63912 492916  31788 528664    0    0     5  7579 3922 2493 15 16 61  8  0
 1  0  63912 484580  31804 532912    0    0    21  2634 3252 2495 13 11 72  5  0
 0  2  63912 471272  31828 528976    0    0     0  6962 3509 2307 14 16 46 24  0
 1  0  63912 486508  31856 528780    0    0     2  4243 3413 2041 14 14 67  5  0
 0  0  63912 492640  31880 529268    0    0    28  5374 3839 2689 18 16 65  1  0
 3  1  63912 447804  31904 543728    0    0     1  3756 4409 4035 18 17 65  0  0
 0  0  63912 492668  31948 529372    0    0     0  5374 3096 2176 11 12 65 12  0
 2  0  63912 467388  31980 541748    0    0     1  5118 3781 2251 17 18 57  8  0
 2  0  63912 466872  32012 541688    0    0     1  4228 3580 1936 14 14 72  0  0
 3  0  63912 475576  32032 529480    0    0     0  4610 2777 2293 10  8 81  1  0
 3  3  63912 451736  32092 543320    0    0     2  8322 5596 5790 25 29 40  6  0
 3  0  63912 448856  32120 531624    0    0     0  4020 3364 2453 13 12 62 13  0
 0  0  63912 452704  32148 529600    0    0     1  5904 4475 4665 21 19 50 10  0
 4  1  63912 409812  32156 546088    0    0    10  3602 2838 1890 11 10 79  0  0
 5  0  63912 409460  32176 540080    0    0     1  5308 5282 5652 20 24 44 11  0
 3  0  63912 434260  32196 544292    0    0     3  8251 6039 5993 27 28 44  0  0
 2  1  63912 446248  32220 542276    0    0     0  4912 3641 2545 15 15 68  2  0
 2  0  63912 467696  32240 529912    0    0     0  8002 6092 6694 28 31 37  3  0
 0  0  63912 487188  32272 529940    0    0     0  4152 3165 1627 11 12 76  1  0

I'd highly recommend

Posted by dalin on April 21, 2010 at 11:49pm

I'd highly recommend @gansbrest's original post of finding the cause of your problems first:

Use devel module to see if there's any terrible queries that are running on important pages. Remember that Views' queries won't show up here. You need to also enable query stats in Views.
Do some PHP profiling to find out what parts of page generation are taking the longest.
Enable the MySQL slow query log to find slow queries.

If you don't know what the problem is then it's highly probable that bigger hardware won't make an ounce of difference.

Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

Oh and run a MySQL tuning

Posted by dalin on April 21, 2010 at 11:55pm

Oh and run a MySQL tuning script like mysqltunner2.
https://launchpad.net/mysqltuner

But don't implement any of the suggestions until you know what each of the parameters does and if it will really fix your problem.

Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

Are you running both web

Posted by gansbrest on April 22, 2010 at 4:27pm

Are you running both web server and mysql on the same box?

It sounds to me that you might have a high value MaxClients in your Apache configuration file, so when you clear cache for example, you got requests queued up (for example if your website pages have too many database requests or external requestst, which is not uncommon in Drupal world) waiting for a data from your database, and new Apache processes will be forking at higher rate.. Eventually it will eat all your RAM and crash the server since PHP scirpts can be demanding, and each process can eat 50 - 100 Mb of memory..

To calculate appropriate to your system number of MaxClients you should take into consideration OS memory and DB memory + leave some RAM just in case.

plus keep in mind that when Apache completes request it return most of it memory to OS, but not all of it and next time when request is coming for a static file (CSS or image) you'll wind up with a big fat process serving simple request. You may want to set up some kind Reverse Proxy or recompile php to use Worker instead Prefork.. It may significantly reduce your memory footprint..

All of it is just assumptions, so you have to profile and measure to find the root of your problems.

Comments

MySQL does not scale

Find your bottleneck first

here is my iostat output

Measure

how does this look to

Active Data Set

Your output looks good to me

my server is Xen VPS and

I'd highly recommend

Oh and run a MySQL tuning

Are you running both web

High performance

Group organizers

New groups

Group notifications