Re: MySQL InnoDB Performance vs Postgres
Hi Peter,
Here is the table schema from SHOW CREATE TABLE:
Table: friend
Create Table: CREATE TABLE `friend` (
`id` int(11) NOT NULL default '0',
`userid` int(11) NOT NULL default '0',
`friendid` int(11) NOT NULL default '0',
`status` int(11) NOT NULL default '0',
`catcode` varchar(255) default NULL,
`timeadded` datetime NOT NULL default '0000-00-00 00:00:00',
KEY `friend_userid_idx` (`userid`),
KEY `friend_friendid_idx2` (`friendid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.10 sec)
In addition, here are the table statistics from SHOW TABLE STATUS:
Name: friend
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 209899049
Avg_row_length: 63
Data_length: 13243514880
Max_data_length: NULL
Index_length: 14300479488
Data_free: 0
Auto_increment: NULL
Create_time: 2005-07-20 17:47:28
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 26908672 kB
Finally, here's what SHOW INNODB STATUS returns (I've removed the long list of queries at the top of the output and only left the last one):
---TRANSACTION 0 52104431, ACTIVE 7 sec, process no 10194, OS thread id 1106901344 fetching rows, thread declared inside InnoDB 136
mysql tables in use 2, locked 0
MySQL thread id 492, query id 41789 10.100.10.185 mysql Sending data
(select (userid + friendid) - ? as friendId from FRIEND where (FRIEND.USERID = ?) and FRIEND.STATUS = 1) UNION (select (userid + friendid) - ? as friendId from FRIEND where (FRIEND.FRIENDID = ?) and FRIEND.STATUS = 1) order by friendId
Trx read view will not see trx with id >= 0 52104432, sees < 0 52104358
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
812630 OS file reads, 2131950 OS file writes, 282529 OS fsyncs
8 pending preads, 0 pending pwrites
347.41 reads/s, 16384 avg bytes/read, 0.75 writes/s, 0.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 67373, seg size 67375, is empty
Ibuf for space 0: size 1, free list len 67373, seg size 67375,
19508975 inserts, 19508975 merged recs, 518301 merges
Hash table size 17700857, used cells 974993, node heap has 1481 buffer(s)
0.25 hash searches/s, 655.09 non-hash searches/s
---
LOG
---
Log sequence number 19 4265627899
Log flushed up to 19 4265627899
Last checkpoint at 19 4265627899
0 pending log writes, 0 pending chkp writes
115700 log i/o's done, 0.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9292018315; in additional pool allocated 12973312
Buffer pool size 524288
Free buffers 0
Database pages 522807
Modified db pages 0
Pending reads 8
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1701066, created 1588143, written 6835445
349.91 reads/s, 0.00 creates/s, 0.25 writes/s
Buffer pool hit rate 899 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 92 queries in queue
Main thread process no. 10194, id 1088420192, state: sleeping
Number of rows inserted 209898854, updated 0, deleted 0, read 210227030
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 476.13 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.06 sec)
I'll try to find out more about this cluster ordering on the web. Do you see any problems with my setup that jump out at you?
Thanks,
Ram