What sort of queries are you doing on this large table? I notice you only have
a couple of the fields indexed
jason
----- Original Message -----
From: "chchen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 6:35 PM
Subject: Speed of mysql
hi,all
i have a strange question.
I use FreeBSD4.2-RELEASE + mysql-3.23.32
Dual PIII667+ 768MB RAM
I have a table like this
CREATE TABLE ABC (
A int(10) unsigned DEFAULT '0' NOT NULL,
B int(10) unsigned DEFAULT '0' NOT NULL,
C0 bigint(20) unsigned DEFAULT '0' NOT NULL,
C1 bigint(20) unsigned DEFAULT '0' NOT NULL,
C2 bigint(20) unsigned DEFAULT '0' NOT NULL,
C3 bigint(20) unsigned DEFAULT '0' NOT NULL,
C4 bigint(20) unsigned DEFAULT '0' NOT NULL,
C5 bigint(20) unsigned DEFAULT '0' NOT NULL,
C6 bigint(20) unsigned DEFAULT '0' NOT NULL,
C7 bigint(20) unsigned DEFAULT '0' NOT NULL,
Total bigint(20) unsigned DEFAULT '0' NOT NULL,
A2 smallint(5) DEFAULT '0' NOT NULL,
B2 smallint(5) DEFAULT '0' NOT NULL,
PRIMARY KEY (A, B),
KEY B (B),
KEY B2 (B2)
);
and i write a C program to insert data into this table.
and my data have about 1M+ rows.
i find when it insert data in mysql, it seems at a high speed.
but after about 200K rows+, it slow down and down.
actually i don;t know if it slow down.
but use CPU usage of my C program and Mysqld
from the begin is 9x% , and then.....less and less after about 200K+.
fiannly it usually use only 1x or even less only x%'s cpu usage.
i tried to use memory disk to stored Table, it seems won't happen
what i say before. so i think it is the I/O problem.
so....i think maybe is my table too big to search the data.
so....i separate my table into 256 tables. to reduce the size of table
but... seems it doesn't useful to solve speed problem.
and when it slow down(CPU useage reduce), i check
all these 256 tables, no one is bigger than 100K rows.
so....anyone have good idea to solve this situation?
please don't ask me to put it all into the memory.
i tried before. but table is too big.
i guess about 500MB for the MYD and MYI
Regards
chChen
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
