[imp] MySQL performance tuning.

[Christopher Crowley]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello -

I am seeing high load on my IMP server. It also runs MySQL.  I am
running MySQL 3.23.47.


I apologize for the poor format of the tables in this email. I am
trying to included all the relevant data.




mysqladmin extended-status shows:

| Handler_delete           | 2037       |
| Handler_read_first       | 80         |
| Handler_read_key         | 2568339    |
| Handler_read_next        | 1954295    |
| Handler_read_prev        | 0          |
| Handler_read_rnd         | 0          |
| Handler_read_rnd_next    | 3952263599 |     <<<<<<<<<<<<<<< wow!
| Handler_update           | 293650     |




Am I missing an index?




mysql> show index from horde_prefs;
+-------------+------------+----------+--------------+-------------+--
- ---------+-------------+----------+--------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name |
| Collation | Cardinality | Sub_part | Packed | Comment | 
+-------------+------------+----------+--------------+-------------+--
- ---------+-------------+----------+--------+---------+
| horde_prefs |          0 | PRIMARY  |            1 | pref_uid    |
| A         |        NULL |     NULL | NULL   |         | horde_prefs
| |          0 | PRIMARY  |            2 | pref_scope  | A         | 
|       NULL |     NULL | NULL   |         | horde_prefs |          0
| | PRIMARY  |            3 | pref_name   | A         |       29349 |
|     NULL | NULL   |         | 
+-------------+------------+----------+--------------+-------------+--
- ---------+-------------+----------+--------+---------+
3 rows in set (0.00 sec)

mysql> show index from turba_objects;
+---------------+------------+----------+--------------+-------------+
- -----------+-------------+----------+--------+---------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name
| | Collation | Cardinality | Sub_part | Packed | Comment | 
+---------------+------------+----------+--------------+-------------+
- -----------+-------------+----------+--------+---------+
| turba_objects |          0 | PRIMARY  |            1 | object_id  
| | A         |       80311 |     NULL | NULL   |         | 
+---------------+------------+----------+--------------+-------------+
- -----------+-------------+----------+--------+---------+
1 row in set (0.00 sec)





mysqlshow --status -p horde 
Enter password: 


+--------------------------+--------+------------+-------+------------
- ----+-------------+-----------------+--------------+-----------+------
- ----------+---------------------+---------------------+---------------
- ------+----------------+---------+
| 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 | 
+--------------------------+--------+------------+-------+------------
- ----+-------------+-----------------+--------------+-----------+------
- ----------+---------------------+---------------------+---------------
- ------+----------------+---------+
| horde_prefs              | MyISAM | Dynamic    | 29346 | 167       
|     | 4903008     | 4294967295      | 1215488      | 0         |   
|             | 2002-03-08 10:26:11 | 2002-08-28 14:20:07 |          
|           | pack_keys=1    |         | kronolith_events_new     |
| MyISAM | Dynamic    | 9362  | 76             | 718876      |
| 4294967295      | 135168       | 0         |                |
| 2002-05-30 16:52:12 | 2002-08-28 14:16:46 |                     |  
|              |         | kronolith_events_new_seq | MyISAM | Fixed 
|     | 1     | 5              | 5           | 21474836479     | 2048
|         | 0         | 9828           | 2002-05-30 16:55:26 |
| 2002-08-28 14:11:52 |                     |                |       
|  | turba_objects            | MyISAM | Dynamic    | 80307 | 111    
|        | 8937140     | 4294967295      | 4404224      | 0         |
|                | 2002-03-08 10:12:24 | 2002-08-28 14:18:56 |
| 2002-03-08 10:13:36 | pack_keys=1    |         | 
+--------------------------+--------+------------+-------+------------
- ----+-------------+-----------------+--------------+-----------+------
- ----------+---------------------+---------------------+---------------
- ------+----------------+---------+



I looked in the mysql_create.sql script, and I see three tables that
aren't in my horde DB: horde_user, horde_categories,
horde_categories_categories.  How would the absence of these tables
affect the performance?

Thanks for reading through this very cluttered email.

Christopher Crowley
TIS Network Services
Tulane University
ccrowley@tulane.edu
504.314.2535
PGP Key ID: 0x7CF18FDB




-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

iQA/AwUBPW0ouc0q7cR88Y/bEQL78gCeKI4VdPygmWjSfF9lL+7YT1ZvTH0AoN6W
LOo9zfP9qQWBGz7u1ryEkyix
=Pzgi
-----END PGP SIGNATURE-----