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