Re: MyISAM / Performance / Nb of table per DataBase
DB I found my problem and why Mysql lock my table for a select. In DB fact, mysql sometimes don't use the right index and so does a 'Table DB sort' that lock my table. I modify the query to add USE INDEX DB clause and now, all seems to work well ... I just need to test DB during several days to be sure that's i'm right .. JZ Did you first try an ANALYZE TABLE to update the optimizer stats? Yes ... I'm still having problem in fact, my search query is faster but also lock my table. But it's just a select :( I'm doing TCP connections to mysql and not localhost, perhaps the problem is here ... The network is ok and my app server is linking with the mysql server over a 100Mb LAN. The network have been tested and tested and all is well. I've given a try to fulltext index, not really better in query time processing but indeces have gone bigger drasticully ... Now, i should give a try to InnoDb. And i 'll also try to split my MyISAM table into smaller ones ... Thanks to all 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: MyISAM / Performance / Nb of table per DataBase
DBI still have performance problem with my big MyISAM table. DBIn fact, mysql keep locking my table, even for doing select :( PDB That's how MyISAM works -- it uses table locks for everything. PDB Given that fact, if it didn't lock the table for reading, that would PDB allow other clients to update the table at the same time -- leading PDB to disastrous results. DBI don't know why but it's very very annoying. DB1% of my query are search ones. DBTheses queries are slow, too slow. I found my problem and why Mysql lock my table for a select. In fact, mysql sometimes don't use the right index and so does a 'Table sort' that lock my table. I modify the query to add USE INDEX clause and now, all seems to work well ... I just need to test during several days to be sure that's i'm right .. 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: MyISAM / Performance / Nb of table per DataBase
On Tue, Sep 24, 2002 at 10:36:31AM +0200, David Bordas wrote: DBI still have performance problem with my big MyISAM table. DBIn fact, mysql keep locking my table, even for doing select :( PDB That's how MyISAM works -- it uses table locks for everything. PDB Given that fact, if it didn't lock the table for reading, that would PDB allow other clients to update the table at the same time -- leading PDB to disastrous results. DBI don't know why but it's very very annoying. DB1% of my query are search ones. DBTheses queries are slow, too slow. I found my problem and why Mysql lock my table for a select. In fact, mysql sometimes don't use the right index and so does a 'Table sort' that lock my table. I modify the query to add USE INDEX clause and now, all seems to work well ... I just need to test during several days to be sure that's i'm right .. Did you first try an ANALYZE TABLE to update the optimizer stats? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 49 days, processed 1,042,139,438 queries (242/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
MyISAM / Performance / Nb of table per DataBase
Hi list, I still have performance problem with my big MyISAM table. In fact, mysql keep locking my table, even for doing select :( I don't know why but it's very very annoying. 1% of my query are search ones. Theses queries are slow, too slow. So i'm thinking about splitting my table to lot of little tables. Can i have around 5000 Tables under the same database ? Or should i keep my big table and do not split ? Thanks David Config : Mysql 3.23.45 ( tar.gz binary ) Linux Red-hat 7.1 kernel 2.4.19 1Go ram - 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: MyISAM / Performance / Nb of table per DataBase
At 9:35 +0200 9/23/02, David Bordas wrote: Hi list, I still have performance problem with my big MyISAM table. In fact, mysql keep locking my table, even for doing select :( That's how MyISAM works -- it uses table locks for everything. Given that fact, if it didn't lock the table for reading, that would allow other clients to update the table at the same time -- leading to disastrous results. I don't know why but it's very very annoying. 1% of my query are search ones. Theses queries are slow, too slow. So i'm thinking about splitting my table to lot of little tables. Can i have around 5000 Tables under the same database ? Or should i keep my big table and do not split ? You might want to try converting the table to InnoDB instead. That's a lot easier to try that creating thousands of tables. Thanks David Config : Mysql 3.23.45 ( tar.gz binary ) Linux Red-hat 7.1 kernel 2.4.19 1Go ram - 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
