Hacker News new | comments | show | ask | jobs | submit login

Where is your current bottleneck? mysql? check your slow query log, turn on the log-queries-not-using-indexes, do a quick analysis of those to see if you have queries that aren't using an index or queries that can be optimized. Probably 90% of the scaling problems are database issues.

The quick solution is to scale up your current linode package, however, I think you might find that your traffic is going to peak and will wane next week after everyone has gone back to school/work and is out of the holiday mindset at which point you can scale back.

Put in a throttle - /proc/loadavg

Limit it to X signups per hour, asking them to check back next hour. Collect email and notify when they can sign up. Point them at a facebook page, ask them to like the page, do announcements when the next 'batch' of users is able to sign up.

If you want some help analyzing the slow query logs and/or the normal logs, email me a url of a .gz/.bz2 of a reasonable snippet at hnusername@hnusername.com.

Since you're running nginx, consider doing an alias and use proxy caching. I would doubt your static files are causing too many issues, but, if you can cache computationally expensive pages a little, that can help.

Last week I optimized some MySQL queries which triggered the 'not-using-index' warning, even when I had all fields which were used for selecting and sorting indexed. I think a JOIN on a nested subselect somehow confused MySQL: when I duplicated some extra restraining WHERE clauses into the subselect, response shot up from 800msec to 12msec per query. Very nice. But the query still shows up as 'not-using-index', which is strange.


Sometimes when you have complex queries it can make sense to create a temp table, create indexes on that, and then query off of that. But queries like that should show up in reporting, not in serving live web pages.

That said, if you're selecting more than 5% or so of the rows in a table, you're probably better off not using an index.

Another random tip. There is a world of difference between putting indexes on 2 fields, and putting a composite index on 2 fields. If you have restrictive conditions on 2 fields, MySQL has to work with the entire list of rows that match at least one of those conditions. (I don't know whether MySQL has the trick of joining indexes together before looking at rows, but even if it does it has to work with a list of rows matching the condition.) By contrast with a composite index MySQL can only look at rows that match both conditions. Much more efficient.


Very nice improvements. If you haven't already gone through these multiple times, I literally soaked in these last night:



Good sets of additional tips/things to keep an eye out for.


MySQL's query plan is odd at times. What does an explain look like on that query? If any portion of the query, including subselects isn't able to use an index, it will report it. You can try to force indicies with USE INDEX/FORCE INDEX which can sometimes help.


http://cd34.com/blog/scalability/mysql-query-optimization/ http://cd34.com/blog/scalability/mysql-5-1s-query-optimizer/


Ah, I was having trouble with another slow query; however, the EXPLAIN showed that the correct indices were used. I managed to speed it up by restricting the size of the inner SELECT with some extra constraints. From 4 seconds downto 0.1 seconds!


Thank you for these tips. I'll try them out later today.


Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact