SQL/Python question -- slow... What is the fixed cost?

[Leonardo B Lopes]

Thanks Mark! Actually I am doing my best to keep my system independent
of mysql. And my db is quite particular: It is only written to once.
After that, it is only queried, and even so in a quite particular way.
But my db is pretty big. This instance, for example, has a table with
83000+ records. It is not so much that the query joining that table with
2 others takes .001 cpusec that worries me. That is OK. The problem is
that some other pretty simple queries also take the same time. If that
is always the case, then I will have to find another quite more
sophisticated solution for data storage, and that is what I am trying to
avoid.

Cheers,
Leo. 

Mark Berry wrote:
> 
> >       What I am wondering is what the fixed cost for this configuration:
> >(python + MySQLdb + MySQL). I am about to design a sophisticated caching
> >system, and before I do that, I was hoping someone with some experience
> >with this setup would be able to tell me if it is worth my time... Any
> >help would be appreciated. I can also share code if it helps.
> 
> I would first ask yourself how big is the system going to get.  In my
> personal experience I have had much better luck with scaling Postgres
> on LARGE tables.  MySQL is a very quick database but under heavy load
> it was a bit weaker than PGSQL.  Now, mysql is being improved on a
> daily basis and maybe these problems have been resolved, but I have
> been able to do things like write stored procedures in C on postgres
> and resolve complex queries like the ones you have described and more.
> 
> Just becuase a query take .001 seconds, doesn't mean that it will
> perform that well with 100k records.
> 
> I would (for testing) do the following.  Imagine how big that database
> COULD get with your application in a reasonable amount of time. Times
> that by 2 and add 10% on top of it in test data into the database
> tables.  (you can write a script to do this...)  Then run your tests.
> 
> Thats probably the biggest mistake I have made in the past while
> normalizing the database, didn't really test it enough under stress
> and size.
> 
> Another database that would be worth looking at is SAPDB.  It's free
> (GPL) and is VERY feature rich (more so than even postgres), though I
> am not sure if there are python database drivers.
> 
> Good luck.
> 
> .mark

-- 
=======================================================================
Leonardo B. Lopes                                      leo at iems.nwu.edu 
Ph.D. Student                                             (847)491-8470
IEMS - Northwestern University             http://www.iems.nwu.edu/~leo