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 date>"2008-09-01" and > date<"2008-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 date>"2008-09-01" > and date<"2008-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 so bi bo in cs us sy id > wa > 1 1 36 374684 79952 261636 0 0 1524 0 480 100 1 3 50 > 46 > 0 1 36 372760 79952 262912 0 0 1300 0 488 74 1 2 50 > 47 > 0 2 36 370764 79956 264508 0 0 1540 16 559 258 1 3 49 > 48 > 0 1 36 368580 79956 265904 0 0 1468 0 1211 1681 7 11 36 > 46 > 0 2 36 367308 79964 266896 0 0 944 236 575 463 3 3 40 > 56 > 0 1 36 365076 79964 268552 0 0 1584 0 493 85 1 3 50 > 46 > 0 1 36 363320 79964 269852 0 0 1284 16 471 80 1 2 50 > 47 > 0 2 36 361112 79968 271420 0 0 1584 16 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 451164 0 0 56444 0 1140 977 0 6 58 > 35 > 1 0 36 12612 196792 450760 0 0 72704 0 1873 2273 0 10 48 > 41 > 0 1 36 9304 211072 439656 0 0 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 so bi bo in cs us sy id > wa > 4 0 48 14268 58756 724808 0 0 13068 28 594 1042 54 27 14 > 5 > 2 0 48 15596 58304 724104 0 0 12068 196 578 754 70 24 0 > 5 > 38 0 48 17564 55448 715604 0 0 6320 248 966 1731 58 39 0 > 2 > 8 0 48 22536 51856 716168 0 0 3796 0 933 3765 71 28 0 > 0 > 2 0 48 23808 51868 723084 0 0 6992 0 550 2959 74 21 0 > 5 > 2 0 48 14932 51880 732172 0 0 9080 200 525 409 64 20 0 > 16 > 2 0 48 13680 51576 734156 0 0 10724 32 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 in 3.03 seconds = 69.29 MB/sec > > (faster than old server...) > > bonnie++ in the old and new server: > OLD SERVER: > Writing with putc()...done > Writing intelligently...done > Rewriting...done > Reading with getc()...done > Reading intelligently...done > start 'em...done...done...done... > Create files in sequential order...done. > Stat files in sequential order...done. > Delete files in sequential order...done. > Create files in random order...done. > Stat files in random order...done. > Delete files in random order...done. > Version 1.03 ------Sequential Output------ --Sequential Input- > --Random- > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- > --Seeks-- > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec > %CP > presentation 2G 22350 71 45905 19 16827 4 18444 51 41778 4 145.8 > 0 > ------Sequential Create------ --------Random > Create-------- > -Create-- --Read--- -Delete-- -Create-- --Read--- > -Delete-- > files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec > %CP > 16 2549 87 +++++ +++ +++++ +++ 2076 64 +++++ +++ 8574 > 98 > > presentation,2G,22350,71,45905,19,16827,4,18444,51,41778,4,145.8,0,16,2549,87,++ > ++++,+++,+++++,+++,2076,64,+++++,+++,8574,98 > > > NEW SERVER: > Writing with putc()...done > Writing intelligently...done > Rewriting...done > Reading with getc()...done > Reading intelligently...done > start 'em...done...done...done... > Create files in sequential order...done. > Stat files in sequential order...done. > Delete files in sequential order...done. > Create files in random order...done. > Stat files in random order...done. > Delete files in random order...done. > Version 1.03 ------Sequential Output------ --Sequential Input- > --Random- > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- > --Seeks-- > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec > %CP > lexatelbackup 2G 24873 55 26393 9 13665 2 36893 72 40453 2 160.8 > 0 > ------Sequential Create------ --------Random > Create-------- > -Create-- --Read--- -Delete-- -Create-- --Read--- > -Delete-- > files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec > %CP > 16 27950 49 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ > +++ > > lexatelbackup,2G,24873,55,26393,9,13665,2,36893,72,40453,2,160.8,0,16,27950,49,+ > +++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++ > > (mmm, very strange, NEW SERVER doesn't have the information for > Create files/sec and Create %CP, etc. (??)) > > Does somebody has any idea why is slower than the other server? IO problem? > CPU > problem? > > In one hand looks like a IO problem but using bonnie and hdparm I don't see > anything _so bad_ (it takes 15 times more in one server than the other > server). > So, CPU is waiting for IO, and IO is not going to "maximum speed" (if I do > a > cat of any of the Mysql data files I get 70x IO in bi) > > The configuration file is the same in both servers. I even increased the > memory for indexes. The configuration file is mainly the Debian one with > some paths changed and nothing else, so the same than the other server. > > I'm open to give your any detail that you need about the configuration, > etc. > just ask :-) It looks interesting... but could be something stupid. I'm not > a > DBA... > > Thank you! > > -- > 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] > > -- Help build our city at http://free-dc.myminicity.com !
