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
=========================================================================
