On Wednesday 21 March 2007, Jonathan Ben Avraham wrote:
> Hi Geoffrey,
> I beg to disagree. A simple redesign should do the trick. The problem IMHO
> is that the query in question has to go through the whole database each
> time. Busting the table up into smaller tables ordered by date keys and
> categories or whatever with keys to the articles should do the trick with
> MySQL and modest hardware.
>
Well actually Geoffrey has a point however ill-phrased is advice is. Most
non-x86 hardware is much faster than this junk we refer to as "PCs"
or "i386". The x86 CPU architecture is bad, the buses are over-complicated,
the assembler is ancient, the hardware is diverse and sub-standard and there
were some hardware issues like the famous Pentium 4 over-pipelining, which
caused it to be slower than a P3 with the same mega-hertz. For the record,
I've studied the i386 architecture in the Technion ("Micro-computers" course)
and can testify that from the relatively small but general amount I've
learned, it was not a pretty sight.[1] Computers of architectures like the
PPC, UltraSPARC, Alpha (RIP), etc. are much more reliable, and faster.
Probably the only reason they are not more popular is that they do not
natively run the (in)famous operating system by a certain Redmond-base
software company.
However, I disagree with Geoffrey on the fact that you need Oracle or DB2 or a
different operating system than Linux (or BSD). By all means, Linux runs very
well on this hardware, and PostgreSQL and MySQL are as fast as Oracle or DB2
or even more. Furthermore, the database vendors expect most of their
non-Windows customers to run their servers on Linux so they probably optimise
for it, or at least don't make it slower there on purpose. Now Oracle has
switched to developing primarily on Linux, and MySQL and PostgreSQL have been
the core of the LAMP (Linux, Apache, MySQL/PostgreSQL, and Perl/PHP/Python)
for years, and most of their developers are using Linux and on x86.
The problem with Pg and My is that their SQL is not as rich as Oracle (nothing
comes close to it at the moment) but they are very good nonetheless. MySQL
has some other problems:
http://www.shlomifish.org/open-source/anti/mysql/
(This link is work in progress, which I appreciate any additions to, and only
meant as a way to collect these MySQL criticisms, not to say that you should
completely refrain from using MySQL).
However, as far as what the OP has asked, they are mostly irrelevant.
That put aside even with x86 hardware there are better ways to scale. Off the
top of my head:
1. Buying a RAID array.
2. Upgrading to a better computer.
3. Clustering.
And all that put aside, if the query takes too long it's probably a problem
with the design of the database or the SQL itself. Of course, large datasets
imply larger query times, but there are ways to overcome this. After all,
this is what a database is all about, and why it's often (but not always)
better than one big text file (
http://www.joelonsoftware.com/articles/fog0000000319.html [2])
Regards,
Shlomi Fish
[1] - If you're studying Electrical Engineering in the Technion, I can
recommend the course though. It requires simulating signals on the bus wire,
which is tedious and error-prone, and it's a tough course, but highly
enlightening.
[2] - Back to Basics is a nice "Joel on Software" article, but it got some
facts wrong, and reached some wrong conclusions. It's still a good read.
> - yba
>
> On Wed, 21 Mar 2007, Geoffrey S. Mendelson wrote:
> > Date: Wed, 21 Mar 2007 16:27:56 +0200
> > From: Geoffrey S. Mendelson <[EMAIL PROTECTED]>
> > To: Elazar Leibovich <[EMAIL PROTECTED]>
> > Cc: [email protected]
> > Subject: Re: [Job] MySQL consultation
> >
> > On Wed, Mar 21, 2007 at 03:42:21PM +0200, Elazar Leibovich wrote:
> >> It seems that my question boils down to this:
> >> I'm having a 3Gb table with three TEXT columns and an ID.
> >> Counting it takes between 1 to 2 minutes, is it normal for such a size
> >> of a table?
> >
> > I think it's pretty obvious that you are pushing the practical
> > limits of MySQL and PC type hardware. Reading a 3gb table in two minutes
> > if pretty fast, that's 25 megabytes a second, assuming you are really
> > reading the table and not an index, which is more likely.
> >
> >
> > If you really want to have a database that large and do anything
> > except minor record insertion, updates and retrieval, you are IMHO
> > a canditate for Oracle or DB2 with the appropriate hardware and operating
> > system.
> >
> >
> > You should plan for the maximum size of your database, double it unless
> > you are 100% sure your estimates are perfect and then choose the
> > technology to match.
> >
> > There was someone on this list a week or two ago looking for a job
> > who had the skills you need now and in the future. You probably
> > should hire him now, before you spend a lot of time and money
> > and have to retool because you made a poor choice.
> >
> > Geoff.
--
---------------------------------------------------------------------
Shlomi Fish [EMAIL PROTECTED]
Homepage: http://www.shlomifish.org/
Chuck Norris wrote a complete Perl 6 implementation in a day but then
destroyed all evidence with his bare hands, so no one will know his secrets.
=================================================================
To unsubscribe, send mail to [EMAIL PROTECTED] with
the word "unsubscribe" in the message body, e.g., run the command
echo unsubscribe | mail [EMAIL PROTECTED]
