Re: mysql big table select speed
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 EstanyGPG 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]
Re: mysql big table select speed
does it have the same network speed as your old server. 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 EstanyGPG 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]
Re: mysql big table select speed
is /tmpdir parameter on both machines using the default value 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 EstanyGPG 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 EstanyGPG id: 0x17756391 http://pinux.info
Re: mysql big table select speed
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 EstanyGPG 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 EstanyGPG id: 0x17756391 http://pinux.info -- Carles Pina i EstanyGPG 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]
Re: mysql big table select speed
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 EstanyGPG 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 EstanyGPG 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]
Re: mysql big table select speed
On the new machine its on a different partition than the database. Also did u try to analyze the table and run the query 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 EstanyGPG 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 EstanyGPG id: 0x17756391 http://pinux.info -- Carles Pina i EstanyGPG id: 0x17756391 http://pinux.info
Re: mysql big table select speed
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 EstanyGPG 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 EstanyGPG id: 0x17756391 http://pinux.info -- Carles Pina i EstanyGPG id: 0x17756391 http://pinux.info -- Carles Pina i EstanyGPG 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]
Re: mysql big table select speed
Just a wild guess but, did you perhaps change the filesystem to a journalling filsystem when moving to the different server? 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.. Phil On Wed, Sep 24, 2008 at 6:16 PM, Carles Pina i Estany [EMAIL PROTECTED]wrote: Hello, I have a database with a big table: 350 milion of registers. The table is a Isam table, very simple: mysql describe stadistics; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) unsigned | NO | PRI | NULL| auto_increment | | param_name | smallint(11) | NO | | | | | param_value | smallint(6) | YES | | NULL| | | date| datetime | NO | MUL | | | +-+--+--+-+-++ 4 rows in set (0.00 sec) mysql I had this database in one server and I moved to another server and now the queries are slower (from 12-14 seconds the query that I will show to 2 min. 50 seconds). Servers hardware are quite similar, and servers software installation are similar too (Debian, ext3). Mysql version: mysql select version(); +--+ | version()| +--+ | 5.0.32-Debian_7etch6-log | +--+ 1 row in set (0.00 sec) While I'm doing this select: select count(*) from stadistics where date2008-09-01 and date2008-09-05 and param_name=124 and param_value=0; (very simple) In the explain select there isn't any surprise: mysql explain select count(*) from stadistics where date2008-09-01 and date2008-09-02 and param_name=124 and param_value=0; ++-++---+---++-+--+-+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | y+-++---+---++-+--+-+-+ | 1 | SIMPLE | stadistics | range | date_index| date_index | 8 | NULL | 1561412 | Using where | ++-++---+---++-+--+-+-+ 1 row in set (0.00 sec) Well, maybe somebody doesn't like the rows value (1561412) but it's what we have :-) Checking vmstat 1 in the new server doing the query is: procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 1 1 36 374684 79952 26163600 1524 0 480 100 1 3 50 46 0 1 36 372760 79952 26291200 1300 0 488 74 1 2 50 47 0 2 36 370764 79956 26450800 154016 559 258 1 3 49 48 0 1 36 368580 79956 26590400 1468 0 1211 1681 7 11 36 46 0 2 36 367308 79964 26689600 944 236 575 463 3 3 40 56 0 1 36 365076 79964 26855200 1584 0 493 85 1 3 50 46 0 1 36 363320 79964 26985200 128416 471 80 1 2 50 47 0 2 36 361112 79968 27142000 158416 530 221 2 2 44 53 This is very confusing for me! The CPU is in waiting state for IO 50% of the time. But looking in io bi is very low. For this hard disk I can reach this IO bi values (doing an hdparm, for example): 1 1 36 73124 136968 45116400 56444 0 1140 977 0 6 58 35 1 0 36 12612 196792 45076000 72704 0 1873 2273 0 10 48 41 0 1 36 9304 211072 43965600 71552 248 1481 1609 0 11 43 45 Same query in the same database but in the old server is: r b swpd free buff cache si sobibo in cs us sy id wa 4 0 48 14268 58756 72480800 1306828 594 1042 54 27 14 5 2 0 48 15596 58304 72410400 12068 196 578 754 70 24 0 5 38 0 48 17564 55448 71560400 6320 248 966 1731 58 39 0 2 8 0 48 22536 51856 71616800 3796 0 933 3765 71 28 0 0 2 0 48 23808 51868 72308400 6992 0 550 2959 74 21 0 5 2 0 48 14932 51880 73217200 9080 200 525 409 64 20 0 16 2 0 48 13680 51576 73415600 1072432 1263 1577 70 27 0 3 Here we have a better execution time, bi is higher, wa is lower. Also, sy is higher... Both systems has the database in a ext3 partition. In the new server I stopped the services and blocked writes to that table to avoid problems from outside. Hdparm results are: hdparm -tT /dev/sda /dev/sda: Timing cached reads: 2262 MB in 2.00 seconds = 1131.52 MB/sec Timing buffered disk reads: 210 MB
