From: Van Date: March 17 1999 6:32am Subject: Performance And, What am I Missing List-Archive: http://lists.mysql.com/mysql/384 Message-Id: <36EF4C88.33F46AE7@dedserius.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 =========================================================================