Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Server under heavy load. Any downside to this hack?

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 32 posts

Re: Ask HN: Server under heavy load. Any downside to this hack?

#11

Post by trevelyan »

If you haven't already done this, when the mysql server is having trouble make sure you connect through the terminal and try:

> SHOW PROCESSLIST

This will show all active queries and the time they have taken to execute. The fact that the server seems to churn to a halt and then work its way through the problem suggests the issues are related to specific queries you can catch this way. Then use the EXPLAIN command on the slow queries to figure out why they are hanging your server and add indexes or tweak that part of your code (avoid joins on large tables, etc.) as necessary.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#12

Post by webvet »

trevelyan wrote: post #11

If you haven't already done this, when the mysql server is having trouble make sure you connect through the terminal and try: > SHOW PROCESSLIST This will show all active queries and the time they have taken to execute. The fact that the server seems to churn to a halt and then work its way through the problem suggests the issues are related to specific queries you can catch this way. Then use the EXPLAIN command on…

Yes, as mentioned in the other thread, we're way ahead of this - but thanks for your input all the same.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#13

Post by webvet »

AznHisoka wrote: post #9

I've had load issues in Postgres, not MySQL before and it was due to autovacuum running on tables getting updated/inserted frequently. I'm not sure what the equivalent in MySQL is, but if you have a ton of insert/update queries, consider archiving your tables after a certain period of time, so that your main table doesn't have ton of rows. You can consider sharding of course, but also consider sharding the actual tab…

Thanks for your in-depth reply and suggestions.

MySQL performance tuning and table structure/query optimization-wise, we've left few stones unturned (but I'm sure there still is a scope for further improvement - isn't there always?).

Not using Rails.

Server specs: 4 cores, 3.2GB RAM

>and the easiest route is just to upgrade your server.

Please refer to the other thread for more on this.

Thanks again.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#14

Post by webvet »

cpncrunch wrote: post #10

Restarting the processes isn't really solving the problem. What is the actual bottleneck? It should be possible to figure this out. I think your first problem is that you are using a VPS. You should never use a VPS in a high load situation like this - buy a dedicated server! They only cost about $70/month, which you should be able to afford if you have a successful site. Ideally you should get as much RAM as you can…

>What is the actual bottleneck?

As mentioned in OP, it is MySQL.

>you can't run a massive database on crappy hardware and expect it to work smoothly.

Absolutely... but we do wish to milk the VPS for every cent of its worth and we're not sure we've got there yet.

Thanks for your input.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#15

Post by stevekemp »

webvet wrote: post #14
cpncrunch wrote: post #10

Restarting the processes isn't really solving the problem. What is the actual bottleneck? It should be possible to figure this out. I think your first problem is that you are using a VPS. You should never use a VPS in a high load situation like this - buy a dedicated server! They only cost about $70/month, which you should be able to afford if you have a successful site. Ideally you should get as much RAM as you can…

>What is the actual bottleneck? As mentioned in OP, it is MySQL. >you can't run a massive database on crappy hardware and expect it to work smoothly. Absolutely... but we do wish to milk the VPS for every cent of its worth and we're not sure we've got there yet. Thanks for your input.

> As mentioned in OP, it is MySQL.

That's not specific enough.

Yes, the problem is manifesting itself via MySQL, but the bottleneck will be somewhere else; whether RAM, CPU, or I/O-wait.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#16

Post by cpncrunch »

stevekemp wrote: post #15
webvet wrote: post #14

Earlier quoted context omitted.

>What is the actual bottleneck? As mentioned in OP, it is MySQL. >you can't run a massive database on crappy hardware and expect it to work smoothly. Absolutely... but we do wish to milk the VPS for every cent of its worth and we're not sure we've got there yet. Thanks for your input.

> As mentioned in OP, it is MySQL. That's not specific enough. Yes, the problem is manifesting itself via MySQL, but the bottleneck will be somewhere else; whether RAM, CPU, or I/O-wait.

Exactly. If the OP can post the output of top, vmstat and iostat both when the problem occurs and after restarting the processes, that would be a start. There might be some simple thing you can tweak to improve things.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#17

Post by staunch »

It seems pretty clear that you don't have enough experience on your own to resolve this properly, so call in some help. It may be possible to make an architectural change that significantly reduces the resources you need, or perhaps you'll find that you unavoidably need more resources to do what you want. Someone who knows how to diagnose and analyze this properly can tell you that.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#18

Post by staunch »

webvet wrote: post #13
AznHisoka wrote: post #9

I've had load issues in Postgres, not MySQL before and it was due to autovacuum running on tables getting updated/inserted frequently. I'm not sure what the equivalent in MySQL is, but if you have a ton of insert/update queries, consider archiving your tables after a certain period of time, so that your main table doesn't have ton of rows. You can consider sharding of course, but also consider sharding the actual tab…

Thanks for your in-depth reply and suggestions. MySQL performance tuning and table structure/query optimization-wise, we've left few stones unturned (but I'm sure there still is a scope for further improvement - isn't there always?). Not using Rails. Server specs: 4 cores, 3.2GB RAM >and the easiest route is just to upgrade your server. Please refer to the other thread for more on this. Thanks again.

Your server barely has more RAM than my phone. Your disk almost certainly has less reliable performance than my laptop's.

If you're doing something that makes any money you should get a real server before wasting days of effort or intentionally causing repeated outages.

Re: Ask HN: Server under heavy load. Any downside to this hack?

#19

Post by AznHisoka »

webvet wrote: post #13
AznHisoka wrote: post #9

I've had load issues in Postgres, not MySQL before and it was due to autovacuum running on tables getting updated/inserted frequently. I'm not sure what the equivalent in MySQL is, but if you have a ton of insert/update queries, consider archiving your tables after a certain period of time, so that your main table doesn't have ton of rows. You can consider sharding of course, but also consider sharding the actual tab…

Thanks for your in-depth reply and suggestions. MySQL performance tuning and table structure/query optimization-wise, we've left few stones unturned (but I'm sure there still is a scope for further improvement - isn't there always?). Not using Rails. Server specs: 4 cores, 3.2GB RAM >and the easiest route is just to upgrade your server. Please refer to the other thread for more on this. Thanks again.

I hope you're paying less than $10/month because you can get a dedicated server with 10 times more RAM here: http://www.ovh.com/us/dedicated-servers/

And how many reads are there usually a min? writes?

Re: Ask HN: Server under heavy load. Any downside to this hack?

#20

Post by webvet »

AznHisoka wrote: post #19
webvet wrote: post #13

Earlier quoted context omitted.

Thanks for your in-depth reply and suggestions. MySQL performance tuning and table structure/query optimization-wise, we've left few stones unturned (but I'm sure there still is a scope for further improvement - isn't there always?). Not using Rails. Server specs: 4 cores, 3.2GB RAM >and the easiest route is just to upgrade your server. Please refer to the other thread for more on this. Thanks again.

I hope you're paying less than $10/month because you can get a dedicated server with 10 times more RAM here: http://www.ovh.com/us/dedicated-servers/ And how many reads are there usually a min? writes?

Paying way more than $10/mo for the VPS, close to 200 - and hey!! thanks for the ovh.com link - looks very interesting, will certainly try them out soon. Thanks.
Post reply on HN