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]
