[rt-users] Problem with my.cnf, InnoDB and permissions

[Mathew Snyder]

Huw Selley wrote:
> Hi Mathew,
> 
> On 24/10/07 1:13, "Mathew Snyder" <theillien at yahoo.com> wrote:
> 
>> We're having performance issues which I'm trying to nail down.  One thing
> 
> I would try find the bottleneck before changing anything ;)
> 
> What makes you believe the database is causing your performance issues?
> 

I'm really only guessing based on the length of time it takes to return tickets
sometimes.  We've found that if a ticket has multiple documents attached to it
or even just several transactions it takes a significantly long time to display.
 I've been thinking about turning on slow_query_logging but have been
sidetracked by other things since I've started looking at this problem.

>> which
>> I wonder might be the issue is the fact that the RT database is configured to
>> use InnoDB but the my.cnf file has all InnoDB-related options commented out.
> 
> That should have only mattered when you created the database (the table type
> is set then). If there was no Inno support iirc mysql will use the default
> storage engine (normally MyISAM unless you have changed it) when it creates
> the tables. You can check what your default storage engine is by running
> 'show engines'. One of them should fess up to being the default.
> 
> You can check the storage engine on a table by table basis by running 'show
> table status' (after selecting your rt database) e.g:
> 
> mysql> use rt;
> Database changed
> mysql> show table status\G
> *************************** 1. row ***************************
>            Name: ACL
>          Engine: InnoDB
>         Version: 10
>      Row_format: Compact
>            Rows: 217
>  Avg_row_length: 226
>     Data_length: 49152
> Max_data_length: 0
>    Index_length: 16384
>       Data_free: 0
>  Auto_increment: 565
>     Create_time: 2007-05-03 15:48:27
>     Update_time: NULL
>      Check_time: NULL
>       Collation: latin1_swedish_ci
>        Checksum: NULL
>  Create_options: 
>         Comment: InnoDB free: 6144 kB
> 

Did that.  That's why I started looking at the InnoDB options.

> It should then dump you the status of all tables. You probably just want to
> check the value for 'Engine:' I suspect that (provided Inno was disabled
> when you created the database) they would be MyIsam.
> 
> If your tables are MyIsam then that could be causing your problems. MyIsam
> performs a table lock when trying to write a row, if you have multiple
> writes on the same table they have to wait for the table lock to be released
> before they can do their work. This could make things go slowly.
> 
> Hope this helps
> Huw
> 
>