Hi,
my question is about the size of the InnoDB datafiles, and I list the
questions in the end of this mail.
I am running MySQL-Max version 4.0.1 on Win2000 Prof.
For test purpose I have set up a small InnoDB database allocating
200M for each file. (innodb_data_file_path = ibdata1:200M;ibdata2:200M)
I _thougt_ this would be more than enough for this purpose.
Then I do inserts in a table like this:
#
# Table structure for table 't_data'
#
DROP TABLE /*!32200 IF EXISTS*/ t_data;
CREATE TABLE /*!32300 IF NOT EXISTS*/ t_data (
intDataPK int(10) unsigned NOT NULL auto_increment,
intParameterFK int(10) unsigned NOT NULL ,
tmsTimestamp timestamp(14) ,
intDataTypeFK int(10) unsigned NOT NULL ,
strData char(255) NOT NULL ,
intUserFK int(10) unsigned NOT NULL ,
boolReference char(1) NOT NULL DEFAULT '0' ,
boolApproved char(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (intDataPK) ,
INDEX parameter_index (intParameterFK) ,
INDEX datatype_index (intDataTypeFK) ,
INDEX user_index (intUserFK) ,
FOREIGN KEY (intUserFK) REFERENCES t_user(intUserPK) ,
FOREIGN KEY (intDataTypeFK) REFERENCES t_datatype(intDataTypePK) ,
FOREIGN KEY (intParameterFK) REFERENCES t_parameter(intParameterPK)
) TYPE=InnoDB;
#
To test the speed of queries I do a 'select count(*) from t_data'.
Now I start inserting data using a php script (bulk-inserts of 20.000 rows)
As long as the row count of the table is below 100.000 the query runs in
less than a second.
When I reach 120.000 rows suddenly the query runs for 6 seconds !! And from
here
it is getting even worse. Every new 20.000 rows increase query-time with
appr. 2 seconds !
At 500.000 rows query-time equals 1min10sec.
I thought this was strange, but then I discovered that my datafiles
hit 'full' at app. 550.000 rows.
>From 'show table status' I get this info about the table:
Name t_data
Type InnoDB
Row_format Fixed
Rows 517626
Avg_row_length 658 (349 removing foreign keys)
Data_length 340639744
Max_data_length NULL
Index_length 29933568
Data_free 0
Auto_increment 681535
Create_time NULL
Update_time NULL
Check_time NULL
Create_options
Comment InnoDB free: 3072 kB;
(intUserFK) REFER dev/t_user(intUserPK);
(intDataTypeFK) REFER dev/t_datatype(intDataTypePK);
(intParameterFK) REFER dev/t_parame
I have tried different datatypes and with/without foreing keys but I always
end up
with a huge datafile and hit the limit (200M) at appr. 550.000 rows (give or
take a few)
I know it is a tight setup I am using, but I still have some questions:
1. What happened when my queries suddenly began using seconds to run instead
af msec ?
2. Is my setup faulty or is this 'mass-consumption' af datafile-space normal
?
3. When a char(255) is defined how much storage is required to store 'test'
? 4 or 255 bytes ?
4. And an extra: It seems to mee, that table type 'fixed' and 'dynamic' is
used rather
randomly by mySQL when setting up the database. I specified nothing an
got a mix !!
Any explanation to this - and does it make a difference ??
Any connections between 3 and 4 ??? Yes ??
The manual says that ROW_FORMAT only applies to MyISAM tables. Not InnoDB
tables - but
it seems to me that it does make a great difference on InnoDB (size of
datafiles) !!
Thank you for any answers at all :-)
/Martin
---------------------------------------------------------------------
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
