Hello, On Sep/25/2008, Ananda Kumar wrote: > On the new machine its on a different partition than the database. > > Also did u try to analyze the table and run the query
I will do it (maybe on Saturday, as I guess that will take long time to do it). But I think that I did last weekend when I moved to the new server. Here: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html it says that: "MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query. " The index is correctly decided in the query that I reported. And this still doesn't explain why CPU is waiting for IO and IO is not fast :-) > > > On 9/25/08, Carles Pina i Estany <[EMAIL PROTECTED]> wrote: > > > > > > Hello, > > > > On Sep/25/2008, Ananda Kumar wrote: > > > is /tmpdir parameter on both machines using the default value > > > > Old machine: yes. > > > > New machine: I have tried two places (different partitions, same FS > > -ext3-, same hard disk). On the old machine it's in a different > > partition of the same hard disk than the database, same thing happends > > in the new server. > > > > Thanks, > > > > > > > > On 9/25/08, Carles Pina i Estany <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > Hello, > > > > > > > > On Sep/25/2008, Ananda Kumar wrote: > > > > > does it have the same network speed as your old server. > > > > > > > > yes, it has. But I'm running the query from localhost :-) (socket > > > > connection). Even, the query only returns one number and I don't have > > > > any federated tables, etc. > > > > > > > > > > > > > > On 9/25/08, Carles Pina i Estany <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > > > > > Hello, > > > > > > > > > > > > On Sep/24/2008, Phil wrote: > > > > > > > Just a wild guess but, did you perhaps change the filesystem to a > > > > > > > journalling filsystem when moving to the different server? > > > > > > > > > > > > mount reports the same (ext3) > > > > > > > > > > > > > I once accidently moved my database from an ext2 to an ext3 > > partition > > > > and > > > > > > it > > > > > > > took me a while to figure out the degradation of queries.. > > > > > > > > > > > > I have problems reading (selecting) and without any writing disk > > > > > > activity (I mean, no temporary tables, etc.). Journaling could > > affect > > > > > > writing but not reading, no? > > > > > > > > > > > > But I already thought about it :-) and tune2fs -l /dev/partition in > > > > both > > > > > > servers reports something different: > > > > > > > > > > > > New one: > > > > > > Filesystem features: has_journal resize_inode dir_index > > filetype > > > > > > needs_recovery sparse_super large_file > > > > > > > > > > > > Old one: > > > > > > Filesystem features: has_journal filetype needs_recovery > > > > > > sparse_super large_file > > > > > > > > > > > > I think that this "resize_inode" is not a problem. I don't know why > > > > > > appears "needs_recovery". Both filesystems are "Clean", etc. > > > > > > > > > > > > Thanks for your idea, do we have more ideas? :-) or anything else > > to > > > > > > check... > > > > > > > > > > > > -- > > > > > > Carles Pina i Estany GPG id: 0x17756391 > > > > > > http://pinux.info > > > > > > > > > > > > -- > > > > > > MySQL General Mailing List > > > > > > For list archives: http://lists.mysql.com/mysql > > > > > > To unsubscribe: > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > -- > > > > Carles Pina i Estany GPG id: 0x17756391 > > > > http://pinux.info > > > > > > -- > > Carles Pina i Estany GPG id: 0x17756391 > > http://pinux.info > > -- Carles Pina i Estany GPG id: 0x17756391 http://pinux.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]