OT: MySQL considerations (Was: Re: [rt-users] Performance Issues)
Rainer Duffner
rainer at ultra-secure.de
Fri Sep 10 07:26:43 EDT 2004
Am Fr, den 10.09.2004 schrieb Harald Wagener um 9:59:
> Am 09.09.2004 um 21:14 schrieb Dan Pritts:
>
> > On Thu, Sep 09, 2004 at 02:50:26PM -0400, Jesse Vincent wrote:
> >> Ok. That tells us that 1) you're running mysql in the default
> >> configuration, which is optimized for a pentium 133 with 64 megs of
> >> RAM.
> >> That's a bad plan.
> >
> > Nice. But is this really the problem here with a db with 100 tickets?
>
> The 'large' is about the system resources and database complexity, not
> necessarily the db size. MySQL does file based operations if the
> memory limits configured have been reached. This * will * slow things
> down.
> >
> >> and 2) you missed the note that said that InnoDB is a
> >> hard requirement. You _should_ see massively degraded performance if
> >> you're running without it. And also, database corruption.
> >
> > Whoops. I must have modified the my.cnf after installing RT. I
> > probably
> > did this to turn on skip-networking and copied the skip-innodb from
> > another server without remembering it was necessary.
> >
> > However, i've turned it on, and restarted mysql, and there's no
> > apparent effect. Things still take a long time, and
> > mason_handler.fcgi
> > processes still eat the CPU (visible via top) while I'm waiting for the
> > web requests to be fulfilled.
>
> You have to change the tables, too. They are in MyISAM format now. This
> is done in MySQL with "ALTER TABLE whatevertable TYPE=innodb;".
> Turning on innodb support in the engine does not automatically convert
> Your database.
OK, so I didn't waste a minute thinking about this (I'd have liked to
use postgresql (a real relational database...), but it doesn't work in a
FreeBSD-jail).
So, I took a look:
(sorry for that - I couldn't get show table status to only return some
columns - is that possible?)
mysql> show table status ;
+-------------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+-----------------------+
| Name | Type | Row_format | Rows | Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time
| Create_options | Comment |
+-------------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+-----------------------+
| ACL | InnoDB | Dynamic | 17 | 963
| 16384 | NULL | 16384 | 0 |
17 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Attachments | InnoDB | Dynamic | 41 | 1598
| 65536 | NULL | 32768 | 0 |
46 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Attributes | InnoDB | Dynamic | 1 | 16384
| 16384 | NULL | 32768 | 0
| 1 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| CachedGroupMembers | InnoDB | Fixed | 117 | 140
| 16384 | NULL | 16384 | 0 |
131 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| CustomFieldValues | InnoDB | Dynamic | 1 | 16384
| 16384 | NULL | 16384 | 0
| 1 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| CustomFields | InnoDB | Dynamic | 1 | 16384
| 16384 | NULL | 16384 | 0
| 1 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| GroupMembers | InnoDB | Fixed | 39 | 420
| 16384 | NULL | 16384 | 0 |
46 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Groups | InnoDB | Dynamic | 50 | 327
| 16384 | NULL | 32768 | 0 |
59 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Links | InnoDB | Dynamic | 1 | 16384
| 16384 | NULL | 65536 | 0
| 1 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| Principals | InnoDB | Dynamic | 59 | 277
| 16384 | NULL | 16384 | 0 |
59 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Queues | InnoDB | Dynamic | 4 | 4096
| 16384 | NULL | 32768 | 0
| 4 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| ScripActions | InnoDB | Dynamic | 18 | 910
| 16384 | NULL | 0 | 0 |
18 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| ScripConditions | InnoDB | Dynamic | 11 | 1489
| 16384 | NULL | 0 | 0 |
11 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Scrips | InnoDB | Dynamic | 15 | 1092
| 16384 | NULL | 0 | 0 |
15 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Templates | InnoDB | Dynamic | 13 | 1260
| 16384 | NULL | 0 | 0 |
13 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| TicketCustomFieldValues | InnoDB | Dynamic | 1 | 16384
| 16384 | NULL | 16384 | 0
| 1 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| Tickets | InnoDB | Dynamic | 6 | 2730
| 16384 | NULL | 49152 | 0
| 6 | NULL | NULL |
NULL | | InnoDB free: 11264 kB |
| Transactions | InnoDB | Dynamic | 58 | 282
| 16384 | NULL | 32768 | 0 |
58 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| Users | InnoDB | Dynamic | 10 | 1638
| 16384 | NULL | 32768 | 0 |
50 | NULL | NULL | NULL
| | InnoDB free: 11264 kB |
| sessions | MyISAM | Dynamic | 24 | 1112
| 26700 | 4294967295 | 2048 | 0 |
NULL | 2004-07-20 14:09:57 | 2004-09-06 11:44:58 | NULL
| | |
+-------------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+-----------------------+
20 rows in set (0.04 sec)
Everything innodb, except sessions - is that normal ?
Does it matter ?
This is rt3.2.1 with mysql-4.20
Another thing: I'm wondering, where the hell one should add the
information about mysql - it's currently distributed all over the wiki.
Or does it matter ? Should users use the search-function anyway ?
Rainer
--
===================================================
~ Rainer Duffner - rainer at ultra-secure.de ~
~ Freising - Munich - Germany ~
~ Unix - Linux - BSD - OpenSource - Security ~
~ http://www.ultra-secure.de/~rainer/pubkey.pgp ~
===================================================
More information about the rt-users
mailing list