Slow response with MRG tables.

[Mariella Di Giacomo]

Hello,



I would like to ask three questions related to MySQL and MERGE tables.

I have a program that reads from mysql server (4.0.5 for Solaris 2.8 on a V880 with 8 processors and 32 GB of memory), using Perl DBI MySQl driver, two tables FULLM (merge of 3 tables) and RECKM (merge of 15 tables), that are MERGED TABLES.

There is an update program that reads (it has been running for weeks) both tables doing a LEFT JOIN in the following matter, where the value
of "vkeys" changes for each query:

select substring(collection,1,3) as coll, count(*) from FULLM left join RECKM on intkeys=starkey where vkeys="
/cors/abcd00/2301-4598/1970/0/79_RODBARD-CARARD" group by coll;

I have the slow log option turned on and in general these queries take a long time (in most cases over a minute).
What I have noticed is that most of the time is spent in querying the RECKM merged table (even in the case where I do not have a join), no matter if I am querying through Perl or directly from mysql command line.

My questions are :
1) when a query is made to a MRG table, how is that query implemented by MySQL ?
2) Is there anything I could optimize in the query itself ? I cannot have ENUM Type fields as procedure analyse() suggests.
3) I have included the table types and description, the mysqld conf file.

Could someone help please ?

Thanks a lot in advance for your help,

Mariella
_____________________________________________________________

SELECT substring(collection, 1, 3) as coll, count(*) from FULLM LEFT JOIN RECKM on intkeys=starkey where vkeys=
"/cors/abcd00/0003-0147/106/949/283_NEI-GDBP" group by coll PROCEDURE ANALYSE() \G
*************************** 1. row ***************************
Field_name: coll
Min_value: Art
Max_value: Art
Min_length: 3
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 3.0000
Std: NULL
Optimal_fieldtype: ENUM('Art') NOT NULL
*************************** 2. row ***************************
Field_name: count(*)
Min_value: 4
Max_value: 3628
Min_length: 1
Max_length: 4
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 1250.3333
Std: 1681.9196
Optimal_fieldtype: ENUM('4','119','3628') NOT NULL
2 rows in set (0.78 sec)
_____________________________________________________________

mysql> describe FULLM;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| vkeys | varchar(100) | | PRI | | |
| intkeys | varchar(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe RECKM;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| vdkkey | varchar(100) | YES | MUL | NULL | |
| starkey | varchar(11) | YES | MUL | NULL | |
| collection | varchar(15) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


____________________________________________________________
show index FROM RECKM \G
*************************** 1. row ***************************
Table: RECKM
Non_unique: 1
Key_name: index_vdkkey
Seq_in_index: 1
Column_name: vdkkey
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type:
Comment:
*************************** 2. row ***************************
Table: RECKM
Non_unique: 1
Key_name: index_starkey
Seq_in_index: 1
Column_name: starkey
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type:
Comment:
2 rows in set (0.00 sec)
____________________________________________________________
show index FROM FULLM \G
*************************** 1. row ***************************
Table: FULLM
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: vkeys
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type:
Comment:
1 row in set (0.00 sec)
____________________________________________________________
show table STATUS like "FULLM" \G
*************************** 1. row ***************************
Name: FULLM
Type: MRG_MyISAM
Row_format: Dynamic
Rows: 17610691
Avg_row_length: 112
Data_length: 1348904896
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment:

____________________________________________________________


show table STATUS like "RECKEYS" \G
*************************** 1. row ***************************
Name: RECKEYS
Type: MRG_MyISAM
Row_format: Dynamic
Rows: 491596360
Avg_row_length: 127
Data_length: 40240090580
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment:
____________________________________________________________

This is the mysqld my.cnf file
________________________________________________
#
set-variable = max_connections=16384
set-variable = max_tmp_tables=512
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=0
set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
#
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=10M
#
set-variable = thread_cache_size=8
set-variable = thread_cache=512
set-variable = thread_concurrency=16
____________________________________________



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php