On Fri, Dec 02, 2005 at 05:28:46AM -0500, IvorW wrote:
Surely if you want to make it as easy as possible, SQLite would be a better option?
Sorry, SQLite won't scale. Concurrent access and locking is something that SQLite doesn't do, though it does have transactions and rollback.
Does it scale *enough* though? I can't imagine that the London guide (which I imagine is still the biggest) ever has more than ten concurrent users reading it and more than 2 or 3 writing it.
On Fri, Dec 02, 2005 at 10:56:56PM +0000, David Cantrell wrote:
On Fri, Dec 02, 2005 at 05:28:46AM -0500, IvorW wrote:
Surely if you want to make it as easy as possible, SQLite would be a better option?
Sorry, SQLite won't scale. Concurrent access and locking is something that SQLite doesn't do, though it does have transactions and rollback.
Does it scale *enough* though? I can't imagine that the London guide (which I imagine is still the biggest) ever has more than ten concurrent users reading it and more than 2 or 3 writing it.
I ran a guide using SQLite at one point (Manchester, NH) and even my personal use (one person) would bump into weird SQLite errors.
Also, as I said, SQLite is probably *less* likely to be supported with the appropriate perl modules for DBI than MySQL is.
Given the problems with SQLite, I think that it would be better to just stick to MySQL for any major uses.
I'm already seeing some slowdowns on Boston in some pages, so I may be looking to add some caching to different aspects of the page - specifically, when loading ?action=index;format=map (which is still one of the more common page requests I see), which loads *everything* in the DB, it's slow. So, I think that if we're going to go for the whizbang nature here, loading all the data may be more likely, and SQLite is not going to be scaling "enough".
Skip the pain. MySQL isn't that big of a requirement. SQLite may be more painful. Let's not hurt OG by encouraging using a possibly non-scaling database product which users would then later have to move from to serve their users. The "Slo-penguides" joke has already been made even with the "real" databases :)
-- Chris
On Fri, 2 Dec 2005, Christopher Schmidt wrote:
I'm already seeing some slowdowns on Boston in some pages, so I may be looking to add some caching to different aspects of the page - specifically, when loading ?action=index;format=map (which is still one of the more common page requests I see), which loads *everything* in the DB, it's slow. So, I think that if we're going to go for the whizbang nature here, loading all the data may be more likely, and SQLite is not going to be scaling "enough".
indexes.
Skip the pain. MySQL isn't that big of a requirement. SQLite may be more painful. Let's not hurt OG by encouraging using a possibly non-scaling database product which users would then later have to move from to serve their users. The "Slo-penguides" joke has already been made even with the "real" databases :)
indexes. also the fact that it loads half of cpan.
which is really noticable if you dont have a powerful machine.
On Fri, Dec 02, 2005 at 11:16:22PM +0000, Bob Walker wrote:
On Fri, 2 Dec 2005, Christopher Schmidt wrote:
I'm already seeing some slowdowns on Boston in some pages, so I may be looking to add some caching to different aspects of the page - specifically, when loading ?action=index;format=map (which is still one of the more common page requests I see), which loads *everything* in the DB, it's slow. So, I think that if we're going to go for the whizbang nature here, loading all the data may be more likely, and SQLite is not going to be scaling "enough".
indexes.
Perhaps I'm misunderstanding you here, but my MySQL tables already *define* indexes on the name column. What more could be done?
The problem is that it's selecting 700 nodes, and all the metadata for them, which is just a lot of frickin data. (The latest database dump is 1.95 meg.)
Skip the pain. MySQL isn't that big of a requirement. SQLite may be more painful. Let's not hurt OG by encouraging using a possibly non-scaling database product which users would then later have to move from to serve their users. The "Slo-penguides" joke has already been made even with the "real" databases :)
indexes. also the fact that it loads half of cpan.
The loading half of CPAN happens on every page. The loading 1.95MB of data and building the associated structures inside the request (attaching all the metadata and so on) only happens on the index page.
Home node for boston: 3 second load time, including network. ?action=index node for boston: 13 second load time, including network.
Part of that is the network - about 3 seconds, for the index node, it looks like. However, the other 7 seconds of difference are pure load time.
hm. Dumping the whole database using mysqldump only takes .3 seconds, so that's probably not our blocker. However, how long would it take to load all the data with mysqlite? Anyone got a mysql->sqlite conversion script I could use, so I could test that?
which is really noticable if you dont have a powerful machine.
I'm not. It's 2.0GHz box, and nothing else on it runs slow. and as I said, the guide itself only runs slow when it has to load a lot of data.
I'm not saying that the speed isn't related to other aspects, but at least some of this will start to be limited by the database once it gets bigger.
-- Chris
indexes.
Perhaps I'm misunderstanding you here, but my MySQL tables already *define* indexes on the name column. What more could be done?
Have you turned on mysql's slow query logging? Sometimes it's a painful query that's not quite what you expect. Once you know what query you're talking about, "EXPLAIN ..." can be lots of help.
The problem is that it's selecting 700 nodes, and all the metadata for them, which is just a lot of frickin data. (The latest database dump is 1.95 meg.)
(Do you mean megs or gigs?)
The loading half of CPAN happens on every page. The loading 1.95MB of data and building the associated structures inside the request (attaching all the metadata and so on) only happens on the index page.
Has anyone done a FastCGI version of the openguides CGI? That can help a LOT.
I'm not saying that the speed isn't related to other aspects, but at least some of this will start to be limited by the database once it gets bigger.
A couple of orders of magnitude bigger. at 2 megs of data, everything should be coming straight out of ram inside mysql.
-- Chris
-- OpenGuides-Dev mailing list - OpenGuides-Dev@openguides.org http://openguides.org/mm/listinfo/openguides-dev
On Fri, Dec 02, 2005 at 06:29:29PM -0500, jesse wrote:
indexes.
Perhaps I'm misunderstanding you here, but my MySQL tables already *define* indexes on the name column. What more could be done?
Have you turned on mysql's slow query logging? Sometimes it's a painful query that's not quite what you expect. Once you know what query you're talking about, "EXPLAIN ..." can be lots of help.
I haven't, and don't know how, but as I realized later on, the problem probably isn't the database itself.
Nope. But I'm pretty sure CGI::Wiki doesn't do any hard queries.
The problem is that it's selecting 700 nodes, and all the metadata for them, which is just a lot of frickin data. (The latest database dump is 1.95 meg.)
(Do you mean megs or gigs?)
It's megs. I say "it's a lot" mostly because it takes the raw data and then builds all the nodes up in memory, which is the painful part, not the database access itself.
The loading half of CPAN happens on every page. The loading 1.95MB of data and building the associated structures inside the request (attaching all the metadata and so on) only happens on the index page.
Has anyone done a FastCGI version of the openguides CGI? That can help a LOT.
Not that I'm aware of.
I'm not saying that the speed isn't related to other aspects, but at least some of this will start to be limited by the database once it gets bigger.
A couple of orders of magnitude bigger. at 2 megs of data, everything should be coming straight out of ram inside mysql.
That's assuming that the guide is the only thing using the instance of MySQL. I have 50 other sites, about 20 of which use MySQL, most of them much more frequently than the openguide, despite the fact that it's getting a couple thousand hits a day. If we move into shared hosting, you're dealing with the same situation on a larger scale: mysql is shared. The data isn't always going to come out of memory.
But the argument here is that "MySQL isn't slow loading 2 megs of data." I'm not arguing that - I'm arguing that SQLite might be. And I don't know enough about SQLite to know if that's a ridiculous claim or not, but my thought is that it might be.
On Fri, Dec 02, 2005 at 06:26:30PM -0500, Christopher Schmidt wrote:
hm. Dumping the whole database using mysqldump only takes .3 seconds, so that's probably not our blocker. However, how long would it take to load all the data with mysqlite? Anyone got a mysql->sqlite conversion script I could use, so I could test that?
Has anyone profiled Openguides?
On Fri, Dec 02, 2005 at 06:03:19PM -0500, Christopher Schmidt wrote:
The "Slo-penguides" joke has already been made even with the "real" databases :)
I'm really interested to see how we do speed-wise under mod_perl.... <deja-thread />
On Fri, Dec 02, 2005 at 06:03:19PM -0500, Christopher Schmidt wrote:
The "Slo-penguides" joke has already been made even with the "real" databases :)
I'm really interested to see how we do speed-wise under mod_perl....
<deja-thread />
If anybody can give me sensible metrics to collect, http://saintpaul.openguides.org is now running under Apache::Registry with Apache::DBI and MySQL.
Tell me what to collect (and probably how) and I will collect it.
-Chris
On Fri, Dec 02, 2005 at 09:05:45PM -0800, chris@prather.org wrote:
If anybody can give me sensible metrics to collect, http://saintpaul.openguides.org is now running under Apache::Registry with Apache::DBI and MySQL.
I notice you pasted some stuff to IRC about how to make that work. I think we probably need to do some thorough testing to make sure that we don't have any naughty uninitialised variables anywhere (I remember seeing values leaking between page loads when they shouldn't have been the last time I saw someone get an existing CGI working under mod_perl) but if you could submit your patches and/or (if it's just really trivial changes) a comment to http://dev.openguides.org/ticket/6 that'd be wonderful.
Cheers,
Dominic.
openguides-dev@lists.openguides.org
-
Bob Walker -
chris＠prather.org -
crschmidt＠crschmidt.net -
David Cantrell -
Dominic Hargreaves -
Earle Martin -
jesse