Hi listers
1. environment
root@myhost ~> uname -a
Linux myhost.mydom.tld 2.6.12-1.1390_FC4_cubbi4_swsusp2 #1 Sat Jul 9
12:34:47 CEST 2005 i686 i686 i386 GNU/Linux
root@myhost ~>
2. mysql
root@myhost ~> rpm -qa |grep -i mysql
MySQL-server-4.1.14-0
MySQL-shared-4.1.14-0
MySQL-devel-4.1.14-0
MySQL-client-4.1.14-0
root@myhost ~>
3. mysql tables
two tables:
localhost.addresses2> describe first_table;
+------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+------------------+------------------+------+-----+---------------------+----------------+
| name | text | YES | | NULL
| |
| title | text | YES | | NULL
| |
| phone | text | YES | | NULL
| |
| desc | text | YES | | NULL
| |
| comment | text | YES | | NULL
| |
| status | text | YES | | NULL
| |
| url | text | YES | | NULL
| |
| businesscategory | text | YES | | NULL
| |
| address | text | YES | | NULL
| |
| kanton | text | YES | | NULL
| |
| reply_1 | text | YES | | NULL
| |
| reply_2 | text | YES | | NULL
| |
| reply_3 | text | YES | | NULL
| |
| reply_date | datetime | YES | | NULL
| |
| ip_address | text | YES | | NULL
| |
| firm | text | YES | | NULL
| |
| served | datetime | YES | | NULL
| |
| addon | text | YES | | NULL
| |
| givenname | text | YES | | NULL
| |
| history | text | YES | | NULL
| |
| favorit | text | YES | | NULL
| |
| last_update | timestamp | YES | | 0000-00-00 00:00:00
| |
| task_link | int(11) | YES | | NULL
| |
| counter | int(10) unsigned | | PRI | NULL
| auto_increment |
+------------------+------------------+------+-----+---------------------+----------------+
24 rows in set (0.00 sec)
localhost.addresses2>
localhost.addresses2> describe second_table;
+------------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+------------------+------+-----+-------------------+----------------+
| name | text | YES | | NULL
| |
| title | text | YES | | NULL
| |
| phone | text | YES | | NULL
| |
| desc | text | YES | | NULL
| |
| comment | text | YES | | NULL
| |
| status | text | YES | | NULL
| |
| url | text | YES | | NULL
| |
| businesscategory | text | YES | | NULL
| |
| address | text | YES | | NULL
| |
| kanton | text | YES | | NULL
| |
| reply_1 | text | YES | | NULL
| |
| reply_2 | text | YES | | NULL
| |
| reply_3 | text | YES | | NULL
| |
| reply_date | datetime | YES | | NULL
| |
| firm | text | YES | | NULL
| |
| served | datetime | YES | | NULL
| |
| addon | text | YES | | NULL
| |
| givenname | text | YES | | NULL
| |
| ip_address | text | YES | | NULL
| |
| history | text | YES | | NULL
| |
| favorit | text | YES | | NULL
| |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP
| |
| task_link | int(11) | YES | | NULL
| |
| counter | int(10) unsigned | | PRI | NULL |
auto_increment |
+------------------+------------------+------+-----+-------------------+----------------+
24 rows in set (0.00 sec)
localhost.addresses2>
cvs@myhost ~/work/adr3/batch> php merge_dbs_join.php
... 270202 rows from table first_table
=====================================================================================
... 77334 rows from table second_table
cvs@myhost ~/work/adr3/batch
4. action
AFTER a 16 HOURS RUN of the following SQL from the above php program:
SELECT first_table.counter as c1, second_table.counter as c2 FROM
first_table INNER JOIN second_table ON first_table.desc REGEXP
second_table.desc
After the 16 hours run I have stopped the php program manually (CTRL-C),
but mysqld continued to consume all available processing time (in top:
id= 0).
when I did an strace on the mysqld PID:
root@myhost ~> strace -p 20810
Process 20810 attached - interrupt to quit
read(41, " face=\"Arial, Helvetica, sans-se"..., 131072) = 131072
read(41, "ute des prix, plus d\'effort pour"..., 131072) = 131072
read(41, "n?</font></p>\n <p align=\"left\">"..., 131072) = 131072
read(41, "ont size=\"3\" face=\"Arial, Helvet"..., 131072) = 131072
read(41, "r />8027 Mytown<br />\n\n</font></"..., 131072) = 131072
read(41, "\n \32\0# info@stripped\v\0"..., 131072) = 131072
this reads dropped in in a time-delay of about 10 sec.
to say that mysql after 16 hours run still was reading the tables...
5. required actions
correct the error in mysqld
suomi
--
----------------------------------------
Ayni AG
Sternenstrasse 24
P.O.Box 1521
CH-8027 Zurich
Switzerland, Europe
+41 44 280 22 44, Fax +41 44 280 22 49
E-mail: info@stripped
Web: http://www.ayni.com
