Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
Hi, IM>I'm not either sure if they're used during normal operation, but that way you IM>could get a file number like (x / 3) + [a few files] where x is the number of IM>files with MyISAM. Also, InnoDB should support .frm:less tables someday in IM>2003 (at least www.innodb.com says so). But if you bump into problems IM>with InnoDB, are you sure your database design is right? I mean, the number of IM>tables is a very rare problem. IM>Best sql,query (forgot this the first time :-) regards, IM>Iikka I don't know well InnoDB and i need to spend some times to learn InnoDB mechanism and new variables before i can use it ... Why create several tables ? In fact, for the moment i have a big MyISAM table with around 5 Millions of rows. The app that's using this table connect to a mysql server form a LAN. And performance isn't as best as i want. I have a search query that's sloo. So i try lots of thing to change the SQL of this query, trying fullsearch ... but do not see good results ... I look at my table structure and i found that i have a field like a category in fact. And i only read, update and delete row on one category at a time. Like a sub tables ... So i thinking to split the table by this category field. I'll have more tables, but smaller ones and lock table will be better ... Thanks David - 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
Re: Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
On Thu, Sep 26, 2002 at 11:52:52AM -0500, Pete Harlan wrote: > On Wed, Sep 25, 2002 at 08:49:43PM +0300, Iikka Meril?inen wrote: > > Hello, > > > > If the number of files is your concern, have you considered using InnoDB? It > > spans tables across any number of data files you want. The performance is > > great, too. > > The .frm files are still there, though, one per file. Maybe they're > not used during the operation of the db...? I don't know about that. Don't delete them. MySQL needs them, though InnoDB itself does not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 51 days, processed 1,092,197,732 queries (243/sec. avg) - 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
Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
Hi, I'm not either sure if they're used during normal operation, but that way you could get a file number like (x / 3) + [a few files] where x is the number of files with MyISAM. Also, InnoDB should support .frm:less tables someday in 2003 (at least www.innodb.com says so). But if you bump into problems with InnoDB, are you sure your database design is right? I mean, the number of tables is a very rare problem. Best sql,query (forgot this the first time :-) regards, Iikka On Thu, 26 Sep 2002, Pete Harlan wrote: > On Wed, Sep 25, 2002 at 08:49:43PM +0300, Iikka Meril?inen wrote: > > Hello, > > > > If the number of files is your concern, have you considered using InnoDB? It > > spans tables across any number of data files you want. The performance is > > great, too. > > The .frm files are still there, though, one per file. Maybe they're > not used during the operation of the db...? I don't know about that. > > --Pete > ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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
Re: Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
On Wed, Sep 25, 2002 at 08:49:43PM +0300, Iikka Meril?inen wrote: > Hello, > > If the number of files is your concern, have you considered using InnoDB? It > spans tables across any number of data files you want. The performance is > great, too. The .frm files are still there, though, one per file. Maybe they're not used during the operation of the db...? I don't know about that. --Pete sql,query - 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
Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
msg Mittwoch, 25. September 2002 21:07 by Pete Harlan: > > If not, i know that ext3 can have ten of thousands files in a directory. > > But commande like 'ls' will become slower and slower ... > > Is this also slowing mysql ? > > I believe it would have to. There is a patch somewhere (I don't know > if it's maintained) for adding indexed directories to ext2/ext3 to last patch is for htree on ext2, but you really should bring up that problem on the ext3-mailing-list at [EMAIL PROTECTED] the developers are always listening... i.t -- . ___ | | Irmund Thum | | - 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
Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
> If not, i know that ext3 can have ten of thousands files in a directory.
> But commande like 'ls' will become slower and slower ...
> Is this also slowing mysql ?
I believe it would have to. There is a patch somewhere (I don't know
if it's maintained) for adding indexed directories to ext2/ext3 to
help this problem, or you could use a different filesystem, such as
ReiserFS that indexes directories out of the box. In an environment
where directory accesses far outnumber directory modifications, you
should see a good improvement if you have 20K files in the directory.
('ls' slows down for reasons besides reading the directory; it usually
sorts its output, and it's often set up to guess file type for each of
the files. Turn those off and it usually goes quite quickly.)
--Pete
-
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
Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
Hello, If the number of files is your concern, have you considered using InnoDB? It spans tables across any number of data files you want. The performance is great, too. Best regards, Iikka On Wed, 25 Sep 2002, David Bordas wrote: > Hi list, > > I've just a little question for the end. > I planned to have around 10K tables under a DB and this number surelly grow > up to 20K. > I know that a database is a directory and a table is 3 files. > I just want to know is mysql have a limit in the number of table per > database. > > If not, i know that ext3 can have ten of thousands files in a directory. > But commande like 'ls' will become slower and slower ... > Is this also slowing mysql ? > > Thanks > > > - > 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 > ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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
Re: Max NB of MyISAM tables / DB ( Ext3 & linux )
In the last episode (Sep 25), David Bordas said: > I've just a little question for the end. I planned to have around 10K > tables under a DB and this number surelly grow up to 20K. I know that > a database is a directory and a table is 3 files. I just want to know > is mysql have a limit in the number of table per database. > > If not, i know that ext3 can have ten of thousands files in a > directory. But commande like 'ls' will become slower and slower ... > Is this also slowing mysql ? "show tables" will be slow, for the same reason ls is slow: it has to fetch the names of all 20k files. Direct access via mysql should be fine. -- Dan Nelson [EMAIL PROTECTED] - 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
