Hi!
I exchanged some emails with Cal and he got his database running
today.
If you have repeating problems with creating the Innobase database,
check that you have deleted all Innobase data files and log files,
also a possible small archived log file, before trying the database
creation again. Forgotten log files which are out of sync can cause
problems.
A thing I have not mentioned in section 8.7 of the
manual is that a row in Innobase uses some 23 bytes + 1-2 bytes * number
of columns more space than it does in MyISAM. The primary index
does not take space, since also the data is stored in the index records,
but secondary indexes use more space than for MyISAM, because each index
record contains also the value of the primary key.
The 23 extra bytes are needed for multiversioning and row level locking,
and the 1-2 bytes per column contain a pointer to the column. If there
is less than 256 bytes of data in the row, the pointer is 1 byte, otherwise
2 bytes.
Also the space allocation in the Innobase tablespace is such that
a table may reserve more space than the size of the rows:
for tables of size > 32 database pages, Innobase allocates space
in chunks of 64 pages to reduce disk space fragmentation.
Finally, when you query the amount of free space in the tablespace
with SHOW TABLE STATUS as described in section 8.7, Innobase will
give you a value which is smaller than the actual physical space in
the tablespace. The reason for this is that some space is reserved
in individual pages for small tables, and also some space is reserved
for special situations like a rollback resulting from an error in an
SQL statement.
Also the rollback segment will reserve space. If you have
long-running consistent reads and lots of updates or deletes in the
database, the space reserved by the rollback segment can be considerable.
Inserts do not grow the rollback segment, except for the time
the transaction doing the insert is active (= not committed or rolled back),
but for updates and deletes we have to preserve old versions of the
rows as long as a consistent read might want to see them.
Regards,
Heikki
>Cal Evans writes:
>> Thanks for the reply but I'm not seeing any useful documentation.
>> Specifically, is there a comprehensive list of all the my.cnf settings along
>> with a detailed explanation of what they mean? Or is the one in the MySQL
>> manual complete?
>> Also, is there a tutorial or some sort of list of steps necessary to go
>> through to setup MySQL for running with INNOBASE tables? I thought I
>> understood but when I started loading data into my table, it started blowing
>> chunks.
>> Cal
> http://www.calevans.com > Hi!
>
>MySQL manual contains info on all Innobase startup variables available
>so far.
>Regards,Sinisa
---------------------------------------------------------------------
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
