Hi!
>In the last episode (May 10), Eric J. Schwertfeger said:
>> Also, my initial testing shows that INNOBASE tables are even faster
>> than MyISAM, at least for insertions, though the same tupples in a
>> similar table seem to take up more room. Aside from the restrictions
InnoDB tables are faster than MyISAM also for SELECTs. You may look
at the benchmarks at www.innobase.fi. InnoDB calculates simple joins
over 10 times faster than PostgreSQL.
The indexes and data in InnoDB take more room than in MyISAM because
of multiversioning and lack of index compression. You can look at section
8.7.10.4 of MySQL online manual at www.mysql.com, or section 10.4
at the InnoDB online manual at www.innobase.fi. The manual at
www.innobase.fi is always the most up-to-date.
>> mentioned in 8.7.4 of the reference manual, are there any other
>> gotcha's that I should be looking out for?
You should look at the up-to-date online manual at www.mysql.com
or www.innobase.fi. For example:
- InnoDB tables should not be created as TEMPORARY before 3.23.38.
- In the MySQL replication 'load table from master' does not work yet for
InnoDB tables. A workaround is to alter the table to MyISAM in the master,
do then the load, and after that alter back to InnoDB in the master.
- You cannot create an index on a prefix of a column:
CREATE TABLE T (A CHAR(20), B INT, INDEX T_IND (A(5))) TYPE = InnoDB;
The above will not work. For a MyISAM table the above would create an index
where only the first 5 characters from column A are stored.
-INSERT DELAYED is not supported for InnoDB tables.
>Here are two more minuses:
>
> No ANALYZE TABLE - correct key and subkey cardinality counts work
> wonders for complex queries (probably easy to add).
InnoDB does the estimation of cardinalities required by the MySQL optimizer.
But the counts are not exact, only estimates based on a few dives into the
index tree. If analyze table would read the whole table,
then we could get exact figures.
> No OPTIMIZE TABLE - Any table that you constantly insert and delete
> on is going to fragment and have data blocks with holes. Since all
> Innobase data goes into a single global tablespace, there's currently
> no way to defrag the space as a while besides dropping and recreating
> everything.
Now I realize I should add to the manual a section on tablespace
management. InnoDB allocates file space to tables and indexes according
to the following algorithm:
- First 32 pages are allocated individually, though still trying to get them
physically contiguous.
- After that InnoDB will allocate whole contiguous 64-page chunks to
the table or index.
- You can defragment a table simply by dumping and dropping it, and then
recreating it.
- InnoDB contracts the indexes and data when rows are deleted from a table:
when the fillfactor of a database page drops below 25 %, InnoDB will try to
merge it to a neighboring page.
>Suggested improvements would be the addition of the COALESCE TABLESPACE
Do you mean reorganization and compaction of a tablespace? The way to
do it is to dump and reload all tables in the tablespace.
>and REBUILD INDEX commands,
This can be done by dropping the indexe(s) and adding them again.
But I agree that in some circumstances it would be nice to be able
to make an index physically contiguous by copying its pages in order
to another tablespace segment. This would help scans.
>and a way to say "this tablespace can only
>be used for this table/index/database" (rebuild index is easy to
>implement; table coalescing is harder).
The support for many tablespaces is built in, but there is
currently no user interface to it: we should add parameters to
my.cnf and to the CREATE TABLE syntax. Many tablespaces would help
balance i/o between different physical disks.
>> So far, MySQL is doing quite well at matching or exceding PostreSQL's
>> performance, though oddly enough, MySQL is taking more memory to do its
>> job. Not complaining, mind you.
>
>I have found that MySQL stays within the memory limits you give it in
>my.cnf; you might want to lower some of the buffer settings depending
>on your workload. http://www.mysql.com/doc/S/e/Server_parameters.html
>is a good starting point.
InnoDB does not normally allocate memory outside the chunks you
specify in my.cnf. If you have many tables, InnoDB may run out of
additional_mem_pool, and it will allocate memory from the OS:
in this case it prints a warning to the MySQL error log.
Thank you for the feedback! I will add a new section to the manual
today about tablespace management.
Best regards,
Heikki
>--
> Dan Nelson
> [EMAIL PROTECTED]
>
---------------------------------------------------------------------
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
