Any Linux pros able to lend a hand to tune a server for high load?

[mc_lovin]

Howdy folks!

Long story short, I just acquired CoinURL.com and it is configured on my server and functioning properly...  Except my server cannot handle the load.  But my server is the same as the previous owners' server except I have double the memory he had. 

The CPU utilization is nominal (20-30% load), the server can handle 30 megabit connections (easily) during file transfer but CoinURL is only requiring 100-300 kb/s and it seems to just be crawling under that amount.

Many, many small connections.  It would appear that a standard Ubuntu server with Apache, MySQL, and PHP at default settings cannot handle the load and I am having a hard time finding exactly what the bottleneck is. 

Any advice would be greatly appreciated. 

I can probably pay you a bit too but I'm a bit strapped at the moment, but if you could solve the problem it would inevitably save me from renting a dedicated server, which I'm not convinced would be necessary, it seems to be a configuration issue.  Solving the problem would not be forgotten and I would not hesitate to offer a reward of some sort.

[1714037586]

1714037586

[1714037586]

1714037586

[1714037586]

1714037586

[1714037586]

1714037586

[sounds]

Ok, maybe we can work this out on the forum (let anybody chime in who knows).

I just opened CoinURL.com and yes, it's slow. So I timed the request. It took 3 seconds just to get the first HTTP request back!



Can you try running 'top -d1' and look at what processes are using the most CPU. I know it's only 20-30% load, but it seems like a badly tuned process, so top should identify that.

Basically my first guess is that it's either the apache config (too few workers) or the PHP (bad config, bad SQL, or bad external library). Either of these will show apache using the most cpu, so if it's something else we'll know to look at that.

[Richy_T]

How was the software transferred to you? Presumably it's all custom software. First place I'd look would be the SQL. There are ways of seeing what's tying up mysql queries. Often all that's needed is an index in the right place.

[Red Emerald]

The default configuration for MySQL (especially innodb) are not optimized for heavy load. Do you know which storage engine you are using?

[mc_lovin]

Thanks sounds!

When I run top, it doesn't immediately look like anything is a muck:

Code:

top - 08:36:51 up  9:53,  4 users,  load average: 0.03, 0.03, 0.00
Tasks:  45 total,   1 running,  44 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.5%us,  0.5%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2097152k total,  1238692k used,   858460k free,        0k buffers
Swap:   524288k total,     5912k used,   518376k free,   477432k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2357 maplesyr  20   0 51232  14m  564 S    1  0.7   0:49.73 redis-server
    1 root      20   0 24132  956  340 S    0  0.0   0:00.24 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.00 kthreadd/136
    3 root      20   0     0    0    0 S    0  0.0   0:00.00 khelper/136
  107 root      20   0 17188  120   28 S    0  0.0   0:00.00 upstart-udev-br
  122 root      20   0 21292   88   32 S    0  0.0   0:00.00 udevd
  218 root      20   0 21288  152   20 S    0  0.0   0:00.00 udevd
  221 root      20   0 21288  168   20 S    0  0.0   0:00.00 udevd
  275 root      20   0 15144   56   24 S    0  0.0   0:00.00 upstart-socket-
  303 root      20   0 49912  568  280 S    0  0.0   0:00.27 sshd
  361 root      20   0 14924   72   32 S    0  0.0   0:00.00 xinetd
  375 root      20   0 19068  412  224 S    0  0.0   0:00.66 cron
  388 mysql     20   0 1338m 209m 4164 S    0 10.2  13:12.70 mysqld
  411 syslog    20   0 12708  532  364 S    0  0.0   0:02.15 syslogd
  429 bind      20   0  238m 9688 1688 S    0  0.5   0:00.04 named
  719 Debian-e  20   0 47472  544  104 S    0  0.0   0:00.01 exim4
  776 root      20   0 78620  728   28 S    0  0.0   0:00.00 saslauthd
  777 root      20   0 78620  700    0 S    0  0.0   0:00.00 saslauthd
  859 root      20   0 73432 1568  680 S    0  0.1   0:00.32 sshd
  916 root      20   0  301m 7068  268 S    0  0.3   0:00.98 apache2
  935 www-data  20   0  338m  49m 5932 S    0  2.4   0:57.38 apache2
  937 www-data  20   0  339m  50m 6176 S    0  2.5   1:15.47 apache2
  941 www-data  20   0  340m  51m 5964 S    0  2.5   1:01.56 apache2
  942 www-data  20   0  339m  45m 5560 S    0  2.2   0:56.89 apache2
  944 www-data  20   0  339m  50m 6000 S    0  2.5   1:11.28 apache2
  945 www-data  20   0  340m  51m 5432 S    0  2.5   1:23.35 apache2
  946 www-data  20   0  348m  59m 6096 S    0  2.9   1:14.65 apache2
  947 www-data  20   0  344m  55m 6036 S    0  2.7   1:17.84 apache2
  951 www-data  20   0  347m  57m 5980 S    0  2.8   1:14.52 apache2
  952 www-data  20   0  346m  57m 6092 S    0  2.8   1:02.23 apache2
  964 root      20   0 18240 1012  312 S    0  0.0   0:00.08 bash
 2013 maplesyr  20   0  249m 4104  128 S    0  0.2   0:00.02 php
 2356 maplesyr  20   0  4356  108   12 S    0  0.0   0:00.00 sh
 3211 memcache  20   0  353m  29m  356 S    0  1.4   0:09.54 memcached
 6595 root      20   0 73432 1916 1020 S    0  0.1   0:04.22 sshd
 6622 root      20   0 18240 1064  364 S    0  0.1   0:00.00 bash

Sure, it looks like there isn't much going on, but the cpu is bouncing around.  I did notice the mysql process using 20% for a split second.

So I'm going to chase mysql down tonight.

Here is the CPU utilization for the last 3 hours by my zabbix monitoring:



I was reading this page:

http://www.cyberciti.biz/tips/linux-resource-utilization-to-detect-system-bottlenecks.html

And I tried the vmstat command.  Actually, this time when I run it, it doesn't show disk I/O going bonkers:

Code:

 # vmstat -S M 3 20
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0      5    867      0    436    1    0   224    75    0  210  2  1 96  1
 0  0      5    867      0    436    0    0    37    19    0 88820  1  1 98  0
 0  0      5    867      0    436    0    0     0     0    0 102309  2  0 98  0
 0  0      5    867      0    436    0    0     0     7    0 104936  0  0 100  0
 1  0      5    867      0    436    0    0    29   545    0 102288  2  1 97  0
 0  0      5    867      0    436    0    0     0     4    0 100529  1  1 98  0
 0  0      5    867      0    436    0    0     0     1    0 103242  0  0 99  0
 0  0      5    867      0    436    0    0     0     3    0 95056  2  0 98  0
 0  0      5    867      0    436    0    0     0   169    0 101337  0  0 100  0
 0  0      5    867      0    436    0    0     0     0    0 101188  0  0 100  0
 0  0      5    867      0    436    0    0     0     0    0 101558  0  0 100  0
 0  0      5    867      0    436    0    0     0     0    0 110792  0  0 100  0
 0  0      5    867      0    436    0    0     0     4    0 112995  0  0 100  0
 0  0      5    867      0    436    0    0     0     0    0 99006  0  0 100  0
 0  0      5    867      0    436    0    0     0     4    0 103363  0  0 100  0
 1  0      5    867      0    436    0    0     0     9    0 103413  2  1 98  0
 0  0      5    867      0    436    0    0     0     9    0 103190  1  0 99  0
 0  0      5    869      0    435    0    0     5     7    0 102159  3  2 95  0
 0  0      5    869      0    434    0    0     0     5    0 103262  1  0 99  0
 0  0      5    869      0    434    0    0     0   421    0 109350  0  0 100  0

Earlier, the disk I/O was going a bit more nuts:

Code:

 #vmstat -S M 2 5
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  2      6   1244      0    175    1    1   536    63    0  594  2  1 91  6
 0  2      6   1239      0    180    0    0  7404   340    0 58689  3  2 90  6
 0  2      6   1234      0    185    0    0  4230    98    0 109551  1  1 91  7
 1  2      6   1229      0    190    0    0  6016    26    0 54839  3  2 90  6
 1  1      6   1226      0    194    0    0  5594    50    0 51865  2  2 90  6

Hm.

I'm going to try some mysql tuning.  I found a program called mysqltuner that promises to feel over the box and find what to recommend.  I bet that's the key. 

[mc_lovin]

The default configuration for MySQL (especially innodb) are not optimized for heavy load. Do you know which storage engine you are using?

Storage engine.. you mean, just MySQL? 

[Red Emerald]

The default configuration for MySQL (especially innodb) are not optimized for heavy load. Do you know which storage engine you are using?

Storage engine.. you mean, just MySQL? 

MySQL is the database. It can use a bunch of different storage engines tho. You are most likely using MyISAM or InnoDB.

mysqltuner.pl should get you started and The MySQL docs do a pretty good job of explaining settings, too

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

[mc_lovin]

The default configuration for MySQL (especially innodb) are not optimized for heavy load. Do you know which storage engine you are using?

Storage engine.. you mean, just MySQL? 

MySQL is the database. It can use a bunch of different storage engines tho. You are most likely using MyISAM or InnoDB.

mysqltuner.pl should get you started and The MySQL docs do a pretty good job of explaining settings, too

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

Interesting.

It would appear that InnoDB is the 'default', with support for the other. 

The CoinURL database is like 1.5GB.  It's nutters.  And we blanked the statistics for the move, so...  I could only imagine how much bigger it could get. 

I spent hours yesterday investigating the network interfaces and editing /etc/sysctl to no avail.  It's one of those things that I always suspected the network but yea, DB, definitely. 

kk everything backed up, now to try mysqltuner.

[sounds]

One thing to keep in mind when using MySQLTuner --

Allow MySQL server to run for at least 24-48 hours before trusting suggestions

It's right in the MySQLTuner help page.

It looks like the server has been up ~10 hours now. I suspect the problem is not only mysql: there is probably some SQL running on the main page that is eating 90% of the page's load time. If we can find it and fix it, that'll help a lot.

memcached is also running

This machine only has 2 GB of ram. It may not make sense to run memcached with so little RAM, especially while you're busy tuning the underlying LAMP stack.

[mc_lovin]

okay I am in love with mysqltuner.

and yea, I saw that warning as well, at least it has been up for 10 hours.

I'll run it again tomorrow, but the recommendations speak SO much right now.  128MB buffer pool?  that aught to do it.  So maybe I need like, Moar ram?  I should give it like, a whole gig.  or more.

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line
[--] Assuming 2048 MB of physical memory
[!!] Assuming 0 MB of swap space (use --forceswap to specify)

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.24-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 368M (Tables: 912)
[--] Data in InnoDB tables: 1G (Tables: 121)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 175

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@richard' has no password set.
[!!] User 'asciiadmin@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 39m 15s (161K q [4.204 qps], 16K conn, TX: 1B, RX: 26M)
[--] Reads / Writes: 42% / 58%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (29% of installed RAM)
[OK] Slow queries: 0% (179/161K)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/165.0M
[OK] Key buffer hit rate: 99.6% (760K cached / 2K reads)
[!!] Query cache efficiency: 15.2% (7K cached / 50K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (11 temp sorts / 3K sorts)
[OK] Temporary tables created on disk: 14% (208 on disk / 1K total)
[OK] Thread cache hit rate: 99% (10 created / 16K connections)
[!!] Table cache hit rate: 1% (400 open / 23K opened)
[OK] Open file limit used: 62% (644/1K)
[OK] Table locks acquired immediately: 99% (189K immediate / 190K locks)
[!!] InnoDB data size / buffer pool: 1.5G/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 1G)

[sounds]

mysqltuner is awesome

Can you upgrade the ram in the machine? 2 GB is so little - I just picked up 16 GB for a machine for less than $100

memcached is currently running. It may be hiding problems - until the underlying LAMP stack is running smoothly I recommend disabling it. (Then enable it for sure, but get some more ram for the machine first maybe?)

[nomnomnom]

Are you running php as suphp? If so that is pretty slow, apache also isn't the fastest.
I would switch to something like lighttpd/nginx with php-fpm + some mysql tuning.

If that is not enough add some catching, recently read something about https://www.varnish-cache.org/. Seems pretty nice.

[uck]

You might consider making your initial landing page one that doesn't have any SQL interaction on it at all, so that users don't get slowed by SQL when all they are doing is finding out what Coinurl.com is...

[cosmicone]

How many disks are you running, and in what configuration?

With only 2GB of ram, is your /var/lib/mysql and /home on the same drive? raid?

[Red Emerald]

I think MySQL tuning should be your top priority. I doubt you would notice a switch from apache to nginx at this point considering your CPU load is fine and you don't have a huge number of concurrent connections.

You should also definitely get more RAM.  Large sites typically dedicate an entire high memory server for the master database and do all the web traffic on another server.

[mc_lovin]

mysqltuner is awesome

Can you upgrade the ram in the machine? 2 GB is so little - I just picked up 16 GB for a machine for less than $100

memcached is currently running. It may be hiding problems - until the underlying LAMP stack is running smoothly I recommend disabling it. (Then enable it for sure, but get some more ram for the machine first maybe?)

2GB isn't much at all, but upgrading from 1GB to 2GB costs $40 per month.  They cut me a deal and they are doing me $25 per month for that extra memory (@ BitVPS).  

I work in IT and I have been asking nicely and I think I've almost convinced my boss to let me either bring a server down to the datacenter or give me a VM or two in one of their servers.

If allowed, I'll bring a totally sweet server down there.  

I just picked up a Dell server with dual 771 Xeons, and I put dual quad cores in there, it's crazy, the FB-DIMM memory is a bit expensive, I was able to buy 8x 1GB modules for $4 each, but if I could bring a server to the DC, I wouldn't hesitate to max the board out (8x 4GB = ~$300-400).  A DDR3-based system would be pretty cool too.  In that case, since I like to go absolutely nutters overkill with systems, I would put 64GB+ in there and even that would only cost a few hundies.  I constantly look at boards that can take 768GB and I'm like, why not me?  But, >2GB is all we need, and it's a good thing I picked up that extra gig from BitVPS.  As a temporary fix, I'll probably get BitVPS to sell me even more memory, another gig is going to be pricey but if that's the bottleneck, let's dooo eeeet.  But yeah, if they let me bring a server to the DC, I'd put SSDs and basically just take the specs way over the top.  I want the site to be able to scale.  

He ran the site with a server that had only ONE gig.  That's the mind-boggler.  But maybe if CoinURL took the whole gig, the site would run.  I need to trim down some excess services.  

Disabling memcached would be the wrong direction.  The banner-server is specifically designed to use memcached, and it wouldn't work without it.  I'm actually getting him to cache other aspects of the site, he says redesigning interstitial ads for memcached will greatly increase performance.  If that's true, I'm totally down for an upgrade.  Except if it needlessly uses more memory, which isn't cheap in this configuration.

W00t I notice the site is faster already.

Thanks guys for the help.  I see its still a bit slow but it's greatly improved.

[mc_lovin]

I think MySQL tuning should be your top priority. I doubt you would notice a switch from apache to nginx at this point considering your CPU load is fine and you don't have a huge number of concurrent connections.

You should also definitely get more RAM.  Large sites typically dedicate an entire high memory server for the master database and do all the web traffic on another server.

Yeah if I can get a server in the physical world, I'll dedicate fortunes of resources towards the DB.  I originally rented a VM server since it was 100x the power I would ever need, and now it seems like a VM is a fraction of the power I need.  I could buy a physical server for the price that I am charged every month for a virtual one, but the price I am charged is similar to the market pricing, a 4GB virtual server at rackspace is almost $200 a month.  In the past years, I have wasted so much money buying computer hardware and it's very excited to have a use case and actually utilize hardware.  When I see high utilization, makes me happy.  *sigh* if I can bring this dual quad server down to the DC tomorrow, I would.  And I'll try.

[Raoul Duke]

Jesus, man. $40/month for an extra GB of RAM on a VPS? Did I read that right?
With $20/month more you would pay for a dedi with 8GB at least, not a crappy VPS. Maybe they would give you a crappy desktop computer instead of a real server, but it wouldn't be as crappy as a VPS, that's for sure.

[mc_lovin]

Jesus, man. $40/month for an extra GB of RAM on a VPS? Did I read that right?
With $20/month more you would pay for a dedi with 8GB at least, not a crappy VPS. Maybe they would give you a crappy desktop computer instead of a real server, but it wouldn't be as crappy as a VPS, that's for sure.

Yeah, I should definitely look into getting a dedi.  Their dedi they offer only has an intel ATOM cpu and the juicier server is really expensive.  But i mean, I could just buy a damn server, oh wait i have servers and tons of ram lol, i need to just mobilize it.  I'd quote you their dedicated server page on the website but that page apparently doesnt work anymore!

BUT I SOLVED THE PROBLEMS.

1) tuning mysql, as expected, is seemingly doing a better job.

2) tuning apache. 

    I went through some apache configuration sites and it turns out that YEAH, it was set really low.  Very few simultaneous connections were allowed.  So everyone was taking turns.  I cranked up the numbers, and VWOOOOM.  All the ram disappeared lmfao.  But the website is soooo much faster.  Test some pages now!  Fast enough that the 2GB will work. 

And I know there are better solutions out there, but its like, I needed a boost right then and there.  They wanted like 0.3 BTC per 128GB, but that number might have been set prior to the price hikes, and they just took over the BitVPS company like a week or so ago so i can understand the numbers not making sense.  So it's like, throw some money at the server, its faster now and paid till march, between now and then I can probably find a much better solution. 

Wow the bandwidth is starting to become utilized now that everyone can connect.  I can increase the numbers further but it already ate all the ram, I should have configured apache first and mysql second because its like i dont have memory for both.  Meh.  Seems to be working better now.  Going to bed.  lmao. 

[mc_lovin]

Now instead of having 1.7GB memory free, no CPU usage, and no network utilization, the resources are fully utilized and the pages are being served!  Yay!