List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 11 2001 8:02pm
Subject:Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)
View as plain text  
In the last episode (May 11), Heikki Tuuri said:
> Dan Nelson wrote:
> >  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.

The myisam version of ANALYZE does scan the entire table; I have a
table with a 4-field PRIMARY and after an ANALYZE TABLE, I get the
following:

mysql> show keys from ipsum;
+-------+------------+----------+-----+-------------+-------------+
| Table | Non_unique | Key_name | Seq | Column_name | Cardinality |
+-------+------------+----------+-----+-------------+-------------+
| ipsum |          0 | PRIMARY  |   1 | date        |        1979 |
| ipsum |          0 | PRIMARY  |   2 | ourip       |       65321 |
| ipsum |          0 | PRIMARY  |   3 | port        |      130643 |
| ipsum |          0 | PRIMARY  |   4 | proto       |      130643 |
+-------+------------+----------+-----+-------------+-------------+
4 rows in set (0.00 sec)

(there are 130643 records total).  If I do the same on an InnoDB table,
I get:

mysql> show keys from ipsum1;
+--------+------------+----------+-----+-------------+-------------+
| Table  | Non_unique | Key_name | Seq | Column_name | Cardinality |
+--------+------------+----------+-----+-------------+-------------+
| ipsum1 |          0 | PRIMARY  |   1 | date        |        NULL |
| ipsum1 |          0 | PRIMARY  |   2 | ourip       |        NULL |
| ipsum1 |          0 | PRIMARY  |   3 | port        |        NULL |
| ipsum1 |          0 | PRIMARY  |   4 | proto       |      223145 |
+--------+------------+----------+-----+-------------+-------------+
4 rows in set (0.01 sec)

So stats on key parts aren't calculated, and the stats on the index as
a whole are off by 2x.  I don't know if this affects how well the
upper-level SQL optimizer selects indexes.

Interestingly enough, a "select count(distinct date)" takes 5 seconds
with MyISAM but only 2 seconds with InnoDB, so exact key counts may not
matter if the DB engine is twice as fast :)

> >Suggested improvements would be the addition of 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.

During my tests I've found that a 100MB tablespace file gets filled up
even if I'm doing work on a 30MB table, and I can't add another table
without dropping and reloading the first.  It'd be nice if there was a
way to do this wihtout taking tables offline.  The current workaround
is just to allocate much more tablespace than you really need.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Eric J. Schwertfeger10 May
  • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Jeremy Zawodny10 May
    • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Eric J. Schwertfeger10 May
      • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Dan Nelson10 May
        • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Eric J. Schwertfeger11 May
          • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Dan Nelson11 May
Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Heikki Tuuri11 May
  • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Dan Nelson11 May
Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Heikki Tuuri13 May
  • Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)Tim Bunce14 May