List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:September 3 2010 7:15pm
Subject:Re: Performance problems on MySQL
View as plain text  
It seems that when your index is PRIMARY on InnoDB tables, it's magic and is
part of the data thereby it is not included in the index_length field.

I have never noticed this. I don't think adding a new index will make a
difference.

You could try moving your log files to a different disk array than where
your data is. If you have binary and query logging enabled, it's probably a
good idea.



  ------------------------------

*Johnny Withers*
jwithers@stripped
601.919.2275 x112
 [image: eCash Software Systems]


On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira <nullpt@stripped> wrote:

> Hi,
>
> When creating a table in MySQL with a PK it automatically creates an INDEX,
> correct?
>
> The Index_Length: 0 is rather strange..I've created a new INDEX on top of
> my PK column on my test system and Index_Length shows a big value different
> from 0. Do you think this might have any impact?
>
> mysql> show index from gwtraffic.clientinfo;
>
>
>
> +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table      | Non_unique | Key_name   | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
>
> +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | clientinfo |          0 | PRIMARY    |            1 | userid      |
> A         |      548216 |     NULL | NULL   |      | BTREE      |         |
> | clientinfo |          1 | userid_idx |            1 | userid      |
> A         |      548216 |     NULL | NULL   |      | BTREE      |         |
>
>
> +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
> 2 rows in set (0.01 sec)
>
> mysql> show table status LIKE 'clientinfo';
>
>
>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+
> | Name       | Engine | Version | Row_format | Rows   | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
> Create_time         | Update_time | Check_time | Collation         |
> Checksum | Create_options | Comment                 |
>
>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+
> | clientinfo | InnoDB |      10 | Compact    | 548216 |             62 |
> 34144256 |               0 |      5783552 |         0 |           NULL |
> 2010-09-03 17:38:16 | NULL        | NULL       | latin1_swedish_ci |
> NULL |                | InnoDB free: 1214464 kB |
>
>
>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+
> 1 row in set (0.00 sec)
>
> I'm trying to stress my test DB but can't measure any different results
> with or without the second INDEX.
>
> Regarding the disks.. the DB is updated 20+ times every second. Writing the
> log, checkpoint to disk, etc.. can cause that much load?
>
> BR
> AJ
>
>
>

Thread
Performance problems on MySQLAlexandre Vieira2 Sep
  • Re: Performance problems on MySQLJangita2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
      • Re: Performance problems on MySQLJangita3 Sep
  • RE: Performance problems on MySQLTravis Ard2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
Re: Performance problems on MySQLAlexandre Vieira3 Sep
  • Re: Performance problems on MySQLJohnny Withers3 Sep
    • Re: Performance problems on MySQLAlexandre Vieira3 Sep
      • Re: Performance problems on MySQLJohnny Withers3 Sep
        • Re: Performance problems on MySQLAlexandre Vieira3 Sep
          • Re: Performance problems on MySQLJohnny Withers3 Sep
            • Re: Performance problems on MySQLAlexandre Vieira3 Sep
              • Re: Performance problems on MySQLJohnny Withers3 Sep
                • Re: Performance problems on MySQLAlexandre Vieira3 Sep
                  • Re: Performance problems on MySQLJohnny Withers3 Sep
                    • Re: Performance problems on MySQLMySQL)5 Sep
  • Re: Performance problems on MySQLmos6 Sep
    • Re: Performance problems on MySQLAlexandre Vieira6 Sep
      • Re: Performance problems on MySQLAlexandre Vieira6 Sep