List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 17 1999 9:33am
Subject:Performance And, What am I Missing
View as plain text  
>>>>> "Van" == Van  <vanboers@stripped> writes:

Van> Okay, Monty,
Van> About a week ago, someone had posted something with respect to a huge database

Van> they wanted to use MySQL for, and I responded with some stats I'd come up with on

Van> a 2.5 Million row database that was about a 200 Meg Data file that took about 60

Van> seconds to syphon through, and, you'd mentioned that you can query even larger
> databases

Van> on a 486 with 16 Meg RAM in less than seconds.  I believe you, but, I'm not
> getting this

Van> kind of performance on the following table.  Before I start:  Before tonight, all
> but the

Van> 1st field was defined as accepting of NULL.  Originally created in 3.21.13, and
> has been

Van> migrated along the way.  The table is attached to quite a few PHP 3.06 web pages
> to poll

Van> client data when they hit the page.  Essentially, I don't have issues with the
> table staying

Van> up to date.  Issues I have are with querying the table to determine activity.  At
> the command

Van> prompt, or otherwise (i.e., Mysql-WebAdmin/PhPAdmin;Kmysqladmin;Xmysqladmin). 
> Slow queries.

Van> It all starts with:

Van> /usr/local/libexec/mysqld --user=mysql -l/usr/local/var/tempe.log \
Van>   -O key_buffer=16M -O table_cache=128 -O sort_buffer=8M \
Van>   -O record_buffer=4M --big-tables &
Van> I'm running Linux Slackware 3.6 with kernel 2.0.34.  (I'm also running another
> kernel 2.2.2 that

Van> exhibits the same types of performance.  Ram is at 64 Meg; filesystems is
> unlimited for practical

<cut>

Van> And, newstats does use up the entire space on many records in the table, which
> shouldn't matter.

Van> And, now the query:

mysql> select distinct ScriptFilename from newstats;

<cut>
47 rows in set (7.55 sec)


Van> And 7.55 secs seems pretty long when the table is:

<cut>

mysql> select count(*) from newstats;
Van> +----------+
Van> | count(*) |
Van> +----------+
Van> |    38800 |
Van> +----------+
Van> 1 row in set (0.00 sec)

Van> I've tried indexing on various rows but the queries still take this long.

Van> On my larger tables/databases it can become quite time-consuming.

Van> Perhaps I need to recompile with some different flags.

Van> Reading specs from /usr/lib/gcc-lib/i486-unknown-linux-gnulibc1/2.7.2.3/specs
Van> gcc version 2.7.2.3.

Van> Anyway, I'm stumped.

Hi!

First note that my reply was the speed is depending on what kind of
queries you are doing.  For example, queries of type WHERE
key_column=constant are fast with any hardware and any database size,
while GROUP or DISTINCT on the whole table is depending on the size of 
the data file.

How big is newstats.ISD and newstats.ISM in your case ?
If you have a key on ScriptFilename, then MySQL must scan through the
.ISM file.  If not, then MySQL must scan through the .ISD file.

If you are using MySQL 3.22.17 or newer, you can try to use:

select /*! SQL_SMALL_RESULT */ distinct ScriptFilename from newstats;

This should be somewhat faster than your original query.

Regards,
Monty

Thread
glibc2 bug ???Phil Wilshire17 Mar
  • glibc2 bug ???Michael Widenius17 Mar
    • Re: glibc2 bug ???Michael Hall17 Mar
      • Re: glibc2 bug ???Michael Widenius17 Mar
        • Re: glibc2 bug ???Michael Hall17 Mar
  • Performance And, What am I MissingVan17 Mar
    • Performance And, What am I MissingMichael Widenius17 Mar
RE: glibc2 bug ???Robin Bowes17 Mar