Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » InnoDB data layout
InnoDB data layout [message #338] Wed, 08 November 2006 16:05 Go to next message
micboh is currently offline micboh
Messages: 2
Registered: November 2006
Junior Member
Say I have an innodb table with three columns:

fid (int),
date (datetime),
val (int).

The primary key for this table is fid,date and if I understand correctly, InnoDB will cluster on this key, so the data will be sequential on disk. In this case, fid and val are foreign keys to other tables, and date is stamped with now() on every insert. Additionally, most queries need to lookup by fid, so clustering on date,fid would not be ideal.

Can someone explain to me how room gets made on disk when an insert occurs on this table? Specifically, I'm wondering what happens when a page for a given fid,date is full. Does MySQL make room in the next page by moving data around so it can stay sequential on disk? Or is another page allocated at the end, and is the data not really seqential anymore until some later rebuild?

Thanks,
Mike
Report message to a moderator
Re: InnoDB data layout [message #354 is a reply to message #338 ] Sun, 12 November 2006 22:33 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
What about fid ? Are they inserted in random or sequential orfer ?
If order is random clustering by (date,fid) may be faster and you can add separate key on fid.

Regarding what happens if page becomes full with new insert - in this case page split happens and so you get two pages instead of one. One of them will be stored in the same place while other moved to different location - this is how fragmentation occures.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: InnoDB data layout [message #358 is a reply to message #354 ] Mon, 13 November 2006 18:11 Go to previous messageGo to next message
micboh is currently offline micboh
Messages: 2
Registered: November 2006
Junior Member
Hi Peter,

The rows are inserted in random fid order. We considered clustering on (date,fid), and adding a separate index for fid. However, since almost all lookups are done by fid, we wanted to optimize for reads to occur on the clustered index.

Overall this table will be much more read heavy than insert heavy (thankfully we will never need to update this table), so maybe this is the best we can get. Do you have any tips for dealing with innodb table fragmentation?

Thanks a ton for your insight.

-Mike
Report message to a moderator
Re: InnoDB data layout [message #359 is a reply to message #358 ] Mon, 13 November 2006 19:58 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
OK. This is good choice then.

Innodb table fragmentation will affect you only if you do large ranges on primary keys or full table scans. Rows do not fragment with Innodb and is it is still always clustered within one page.

OPTIMIZE TABLE can be used to defragment but it locks table

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:sort in client lang (php) instead of mysql
Next Topic:innodb_thread_concurrency / cpu usage
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Thu Jul 9 21:44:22 EDT 2009

Total time taken to generate the page: 0.01061 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.