|
So stumbling through Django-powered websites, I came across OSQA which just happened to have the perfect structure for a project I have been working on. So I grabbed a copy and set it up. I haven't made any other major modification other than adding a foreign key field to the Node model, and a corresponding model for a new table the key points to. This new table has ~11 million rows. Now all that need to be done was for every row in the new table, run AskAction() to create a corresponding question entry. The problem is, now every time I try to load mywebsite.com/questions/ it takes upward of 50 seconds to complete the request. As the title says, there are 121,027 "questions" loaded, with only 2 "answers". What I've tried so far:
Additional information:
I should admit, I'm no expert programmer, so it wouldn't surprise me if I missed something stupid. If I can provide any other useful information, just ask.
This question is marked "community wiki".
|
|
After some reassurance that it isn't an OSQA problem, I went back to looking at the database. It seems when I converted from sqlite3 to PostgreSQL the indexes got nuked. After I dropped them and recreated them (using manage.py sqlindexes forum) everything magically started working. Less than 4 seconds uncached for /questions/ and much faster for other pages. 1
4 Seconds is still very slow, but much better :) Btw, don't use local memory cache, it doesn't work at all.
(10 Jan '11, 14:49)
Hernani Cerq... ♦♦
Indeed, 4 seconds is slow, but not when you've been pulling your hair out for a few days trying to figure out why it's taking 50. I setup memcached and got the time down to 2 secs on /questions/. I've tried some generic postgres performance tips, but still haven't gotten it to under a second. If you happen have any links related to postgres performance that would apply well to OSQA, I'd really appreciate it. :) I've been digging through meta.osqa.net trying to find any hints, but so far nothing I haven't already tried.....
(10 Jan '11, 17:10)
corey
Did you had the chance to compare mySql to Postgres?
(12 Sep '11, 16:09)
kerem
|
Just as curiosity, can you run a count on the node table? There is on site hosted at this same server, with nearly 110.000 nodes (including questions answers and comments), and it runs very well. According to your description you should have about 10% more than that, so I guess the difference shouldn't be that big :). Also, can you post the code you used to create the questions?
121031 rows on the Node table. Glad to know someone else has that big a table, I was getting a little worried there. :) Am I insane for thinking OSQA can handle ~11 million questions with light to moderate traffic? I added the script I used to create the questions.
There's no intrinsic reason why OSQA wouldn't be able to handle the queires and/or the results, but you should be clear where the line between OSQA performance and database performance lies. You may need to manage issues an ordinary OSQA admin would not need to.
The responsiveness of your database will have EVERYTHING to do with perceived performance of your whole system. Also, if you have large result sets, then they will need to get processed and probably cached, too. Your selection of a caching method, and the amount of memory you give it will probably matter a lot.