On 30 Sep, 09:11, gordonb.iy...@burditt.org (Gordon Burditt) wrote:
> >Hmmm... Still can't get this right.
>
> >I used a PHP script to run an alter command accross all the tables to
> >ensure that they are of type My_ISAM.
>
> >i.e. alter table `1` engine=MyISAM
>
> >Altering INSERT METHOD=LAST to INSERT_METHOD=LAST also makes no
> >difference.
>
> >When I issue the statement to create theMergeTable, it executes
> >without any errors. They only occur when I try and access the table.
>
> >mysql> describe `shares`;
> >ERROR 29 (HY000): File './securities/120.MYD' not found (Errcode: 24)
>
> Errcode 24 on my system is "too many open files". Assuming it means
> the same on yours, you're hitting an OS limit. Try to either getMySQLnot to cache so many open file descriptors (open tables), or
> raise the system limit.

OK,

I've just looked at this again, and tried to create a merge table out
of only five tables. Doesn't seem to matter whether it is 5 or 5000
underlying tables. It still gives the same error:

mysql> drop table shares;Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `shares` ( `date` date NOT NULL, `symbol` char(20)
default NULL, `open` decimal(10,2) default NULL, `close` decimal(10,2)
default NULL, `adjClose` decimal(10,2) NOT NULL, `high` decimal(10,2)
default NULL, `low` decimal(10,2) default NULL, `volume` decimal(10,2)
default NULL, `id` int(11) UNSIGNED NOT NULL auto_increment,
INDEX(id)) ENGINE=MRG_MyISAM UNION=(`1`, `2`, `3`, `4`, `5`)
INSERT_METHOD LAST;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from shares where symbol = "^ftse";ERROR 1168 (HY000):
Unable to open underlying table which is differently defined or of non-
MyISAM type or doesn't exist
mysql>


Regarding the point:
>> Try to either get MySQL not to cache so many open file descriptors (open tables), or raise the system limit.

I'm no expert, so I a little unsure what to tweak.

I upped some of the settings (as follows) but this dosen't seem to
make much difference:

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 256M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


As my machine only has 512MB RAM (possibly time to buy some more) I
can't set these too high.

Anything obvious here that I should tweak.

Many thanks again.

Rgds
Neil.