mc_lovin (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 03:29:54 AM |
|
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.
|
|
|
|
sounds
Full Member
Offline
Activity: 140
Merit: 100
1221iZanNi5igK7oAA7AWmYjpsyjsRbLLZ
|
|
January 25, 2013, 04:34:45 AM |
|
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
Legendary
Offline
Activity: 2562
Merit: 2264
1RichyTrEwPYjZSeAYxeiFBNnKC9UjC5k
|
|
January 25, 2013, 04:45:47 AM |
|
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.
|
1RichyTrEwPYjZSeAYxeiFBNnKC9UjC5k
|
|
|
Red Emerald
|
|
January 25, 2013, 04:55:38 AM |
|
The default configuration for MySQL (especially innodb) are not optimized for heavy load. Do you know which storage engine you are using?
|
|
|
|
mc_lovin (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 04:58:51 AM |
|
Thanks sounds! When I run top, it doesn't immediately look like anything is a muck: 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.htmlAnd I tried the vmstat command. Actually, this time when I run it, it doesn't show disk I/O going bonkers: # 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: #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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 05:00:18 AM |
|
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
|
|
January 25, 2013, 05:05:49 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 05:17:03 AM |
|
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.htmlInteresting. 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
Full Member
Offline
Activity: 140
Merit: 100
1221iZanNi5igK7oAA7AWmYjpsyjsRbLLZ
|
|
January 25, 2013, 05:25:15 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 05:29:48 AM |
|
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. >> 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
Full Member
Offline
Activity: 140
Merit: 100
1221iZanNi5igK7oAA7AWmYjpsyjsRbLLZ
|
|
January 25, 2013, 05:33:48 AM |
|
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
|
|
January 25, 2013, 05:38:58 AM |
|
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
Member
Offline
Activity: 94
Merit: 10
|
|
January 25, 2013, 05:41:53 AM |
|
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
Member
Offline
Activity: 105
Merit: 10
|
|
January 25, 2013, 05:43:13 AM |
|
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
|
|
January 25, 2013, 05:43:49 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 05:53:35 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 06:08:20 AM |
|
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
aka psy
Legendary
Offline
Activity: 1358
Merit: 1002
|
|
January 25, 2013, 07:10:41 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 08:28:55 AM |
|
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 (OP)
Legendary
Offline
Activity: 1190
Merit: 1000
www.bitcointrading.com
|
|
January 25, 2013, 08:34:18 AM |
|
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!
|
|
|
|
|