List:General Discussion« Previous MessageNext Message »
From:Van Date:March 17 1999 6:32am
Subject:Performance And, What am I Missing
View as plain text  
Okay, Monty,

About a week ago, someone had posted something with respect to a huge database

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

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

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

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

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

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

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

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

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

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

It all starts with:

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

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

purposes.

The table:

mysql> describe newstats;
+----------------+--------------+------+-----+---------------------+----------------+
| Field          | Type         | Null | Key | Default             | Extra          |
+----------------+--------------+------+-----+---------------------+----------------+
| nid            | int(10)      |      | PRI | 0                   | auto_increment |
| Referer        | varchar(150) |      |     |                     |                |
| UserAgent      | varchar(50)  |      |     |                     |                |
| ScriptFilename | varchar(150) |      |     |                     |                |
| RemoteHost     | varchar(50)  |      |     |                     |                |
| RemoteAddr     | varchar(15)  |      |     |                     |                |
| HitTime        | datetime     |      |     | 0000-00-00 00:00:00 |                |
| Host           | varchar(25)  |      |     |                     |                |
| AcceptLanguage | varchar(15)  |      |     |                     |                |
| QryString      | varchar(150) |      |     |                     |                |
+----------------+--------------+------+-----+---------------------+----------------+
10 rows in set (0.01 sec)

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

And, now the query:

mysql> select distinct ScriptFilename from newstats;
+---------------------------------------------------+
| ScriptFilename                                    |
+---------------------------------------------------+
| /usr3/home/vanboers/public_html/OLDui/TOC.php3    |
| /usr3/home/vanboers/public_html/theacg/index.php3 |
| /usr3/www.dedserius.com/adopt/mainadopt.php3      |
| /usr3/www.dedserius.com/guestbook/index.php3      |

| /usr3/www.dedserius.com/halloween/index.php3      |
| /usr3/www.dedserius.com/hittrack/analyze.php3     |
| /usr3/www.dedserius.com/index.php3                |
| /usr3/www.dedserius.com/lyrics/blckball.php3      |
| /usr3/www.dedserius.com/lyrics/dreamer.php3       |
| /usr3/www.dedserius.com/lyrics/explain.php3       |
| /usr3/www.dedserius.com/lyrics/fearofsc.php3      |
| /usr3/www.dedserius.com/lyrics/jstdream.php3      |
| /usr3/www.dedserius.com/lyrics/livecart.php3      |
| /usr3/www.dedserius.com/lyrics/newyears.php3      |
| /usr3/www.dedserius.com/lyrics/ostrich.php3       |
| /usr3/www.dedserius.com/lyrics/pourno.php3        |
| /usr3/www.dedserius.com/lyrics/seemsill.php3      |
| /usr3/www.dedserius.com/lyrics/somethin.php3      |
| /usr3/www.dedserius.com/lyrics/violtblu.php3      |
| /usr3/www.dedserius.com/lyrics/walkaway.php3      |
| /usr3/www.dedserius.com/lyrics/wastedte.php3      |
| /usr3/www.dedserius.com/midi/php/blckball.php3    |
| /usr3/www.dedserius.com/midi/php/brickreg.php3    |
| /usr3/www.dedserius.com/midi/php/dreamer.php3     |
| /usr3/www.dedserius.com/midi/php/fearofsc.php3    |
| /usr3/www.dedserius.com/midi/php/jstdream.php3    |
| /usr3/www.dedserius.com/midi/php/lhermita.php3    |
| /usr3/www.dedserius.com/halloween/DeadLink.php3   |

| /usr3/www.dedserius.com/midi/php/livecart.php3    |
| /usr3/www.dedserius.com/midi/php/miditrack.php3   |
| /usr3/www.dedserius.com/midi/php/newyears.php3    |
| /usr3/www.dedserius.com/midi/php/ostrich.php3     |
| /usr3/www.dedserius.com/midi/php/pourwatr.php3    |
| /usr3/www.dedserius.com/midi/php/riffs.php3       |
| /usr3/www.dedserius.com/midi/php/seemsill.php3    |
| /usr3/www.dedserius.com/midi/php/somethin.php3    |
| /usr3/www.dedserius.com/midi/php/testmidi.php3    |
| /usr3/www.dedserius.com/midi/php/violtblu.php3    |
| /usr3/www.dedserius.com/midi/php/walkaway.php3    |
| /usr3/www.dedserius.com/midi/php/wastedte.php3    |
| /usr3/www.dedserius.com/misc/digs.php3            |
| /usr3/www.dedserius.com/misc/senate.php3          |
| /usr3/www.dedserius.com/missing.php3              |
| /usr3/www.dedserius.com/php/guestbook.php3        |
| /usr3/www.dedserius.com/ppt/index.php3            |
| /usr3/www.dedserius.com/resume/index.php3         |
| /usr3/www.dedserius.com/vinnie/index.php3         |
+---------------------------------------------------+
47 rows in set (7.55 sec)

And 7.55 secs seems pretty long when the table is:

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

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

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

Perhaps I need to recompile with some different flags.

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

Anyway, I'm stumped.

Regards,

Van

--
=========================================================================
Linux rocks!!!   www.dedserius.com
=========================================================================



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