[web2py] Re: PostgreSQL super slowdowns
I added your limitby code to some places. I haven't seen the same slowdowns recently, though, so it's hard to tell what happened - maybe it's from a fresh install of postgresql? We'll see if it comes back. On Nov 1, 11:01 pm, KMax mkostri...@gmail.com wrote: Please check .first() or .last() usage. With Postgres for some reason, all data are transfered to DAL, and on w2p side first or last recored selected. Solution also are easy use: .select(limitby=(0,1)).first() or select(limitby=(0,1),orderby=~db.papers.id).first() or .select(limitby=(0,1),orderby=db.papers.id).first(). orderby=~ and orderby= will give you last or first for .first() and .last() - since only one record are returned from db. Hope I score. On 29 ÏËÔ, 12:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I don't have the full explanation for what's going on yet but I do have some clues. I've noticed it happens locally, and in a specific way: 1) I load the page up in one browser and it loads and reloads fine. 2) When I open the page up in another browser or in an incognito session on the same machine, the page request hangs, usually for longer than two minutes. I don't know for sure that it's Postgres related, but I don't remember seeing this on SQLite. This is something I really need to fix! Can anyone reproduce this? Are there profilers or particular places in the code I can tag? Thanks! - Chris
Re: [web2py] Re: PostgreSQL super slowdowns
LIMITing results to max 1, is a good practice when you expect at most 1 result. On postgresql it is very important to create indexes to have huge performance boosts. Check your slow queries with the EXPLAIN command (see on postgresql.org) and make appropriate indexes. Also do cluster long tables (see the CLUSTER command) and maintain CLUSTERing at regular intervals. After that no other db can beat postgesql, no way ;-) mic 2010/11/18 Chris partyonais...@gmail.com: I added your limitby code to some places. I haven't seen the same slowdowns recently, though, so it's hard to tell what happened - maybe it's from a fresh install of postgresql? We'll see if it comes back. On Nov 1, 11:01 pm, KMax mkostri...@gmail.com wrote: Please check .first() or .last() usage. With Postgres for some reason, all data are transfered to DAL, and on w2p side first or last recored selected. Solution also are easy use: .select(limitby=(0,1)).first() or select(limitby=(0,1),orderby=~db.papers.id).first() or .select(limitby=(0,1),orderby=db.papers.id).first(). orderby=~ and orderby= will give you last or first for .first() and .last() - since only one record are returned from db. Hope I score. On 29 ÏËÔ, 12:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I don't have the full explanation for what's going on yet but I do have some clues. I've noticed it happens locally, and in a specific way: 1) I load the page up in one browser and it loads and reloads fine. 2) When I open the page up in another browser or in an incognito session on the same machine, the page request hangs, usually for longer than two minutes. I don't know for sure that it's Postgres related, but I don't remember seeing this on SQLite. This is something I really need to fix! Can anyone reproduce this? Are there profilers or particular places in the code I can tag? Thanks! - Chris
[web2py] Re: PostgreSQL super slowdowns
Don't know for sure, as I haven't seen it as much recently. Perhaps it goes away with frequent usage, but I don't have any good explanations. On Oct 30, 4:45 am, Thadeus Burgess thade...@thadeusb.com wrote: I guess its a problem with a postgresql setting, or old drivers maybe? -- Thadeus On Sat, Oct 30, 2010 at 12:45 AM, Chris partyonais...@gmail.com wrote: I was just trying the home page...some selects are run on every page but as far as I know that's it. The machine's got plenty of memory and this is a very basic application with probably less than 1MB data total. On Oct 29, 7:59 am, Johann Spies johann.sp...@gmail.com wrote: On 29 October 2010 08:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I have been using both SQLITE and Postgresql (sometimes with the same content) and did not notice a slowdown. Some of my datasets are quite large (a dump of a present project's database is about 3G) and I don't experience any problem on Postgresql. What exactly were you doing when you noticed this? Inserts? Queries? CSV-downloads? How much data? How much ram available? Regards Johann -- May grace and peace be yours in abundance through the full knowledge of God and of Jesus our Lord! His divine power has given us everything we need for life and godliness through the full knowledge of the one who called us by his own glory and excellence. 2 Pet. 1:2b,3a
[web2py] Re: PostgreSQL super slowdowns
Please check .first() or .last() usage. With Postgres for some reason, all data are transfered to DAL, and on w2p side first or last recored selected. Solution also are easy use: .select(limitby=(0,1)).first() or select(limitby=(0,1),orderby=~db.papers.id).first() or .select(limitby=(0,1),orderby=db.papers.id).first(). orderby=~ and orderby= will give you last or first for .first() and .last() - since only one record are returned from db. Hope I score. On 29 окт, 12:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I don't have the full explanation for what's going on yet but I do have some clues. I've noticed it happens locally, and in a specific way: 1) I load the page up in one browser and it loads and reloads fine. 2) When I open the page up in another browser or in an incognito session on the same machine, the page request hangs, usually for longer than two minutes. I don't know for sure that it's Postgres related, but I don't remember seeing this on SQLite. This is something I really need to fix! Can anyone reproduce this? Are there profilers or particular places in the code I can tag? Thanks! - Chris
Re: [web2py] Re: PostgreSQL super slowdowns
I guess its a problem with a postgresql setting, or old drivers maybe? -- Thadeus On Sat, Oct 30, 2010 at 12:45 AM, Chris partyonais...@gmail.com wrote: I was just trying the home page...some selects are run on every page but as far as I know that's it. The machine's got plenty of memory and this is a very basic application with probably less than 1MB data total. On Oct 29, 7:59 am, Johann Spies johann.sp...@gmail.com wrote: On 29 October 2010 08:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I have been using both SQLITE and Postgresql (sometimes with the same content) and did not notice a slowdown. Some of my datasets are quite large (a dump of a present project's database is about 3G) and I don't experience any problem on Postgresql. What exactly were you doing when you noticed this? Inserts? Queries? CSV-downloads? How much data? How much ram available? Regards Johann -- May grace and peace be yours in abundance through the full knowledge of God and of Jesus our Lord! His divine power has given us everything we need for life and godliness through the full knowledge of the one who called us by his own glory and excellence. 2 Pet. 1:2b,3a
[web2py] Re: PostgreSQL super slowdowns
I was just trying the home page...some selects are run on every page but as far as I know that's it. The machine's got plenty of memory and this is a very basic application with probably less than 1MB data total. On Oct 29, 7:59 am, Johann Spies johann.sp...@gmail.com wrote: On 29 October 2010 08:53, Chris partyonais...@gmail.com wrote: Hey, I've noticed some serious slowdowns ever since I switched from SQLite to Postgres. Pages will take MINUTES to load - it's kind of fascinating. I have been using both SQLITE and Postgresql (sometimes with the same content) and did not notice a slowdown. Some of my datasets are quite large (a dump of a present project's database is about 3G) and I don't experience any problem on Postgresql. What exactly were you doing when you noticed this? Inserts? Queries? CSV-downloads? How much data? How much ram available? Regards Johann -- May grace and peace be yours in abundance through the full knowledge of God and of Jesus our Lord! His divine power has given us everything we need for life and godliness through the full knowledge of the one who called us by his own glory and excellence. 2 Pet. 1:2b,3a
