0

I am building a database, it requires each row to be about 60,000 bytes (very close to mysql max row size, basically lots of varchar(500) statements [necessary]). now, that is ok in myism as far as I know.

However, I am considering innodb due to its row locking behaviour (I get lots of updates to my table). I have only just come across some troubling information regarding innodb not liking large rows (something about 8000 bytes max or something).

So, given that my table will have rows of approx 60,000(!) bytes each, is this a problem for innodb? Is it a big problem? deal breaker? what would you suggest?

Table: ID (int, autoinc, primary key), varchar(500), varchar(500)...repeat...varchar(500) until end of table reached

David19801
  • 11,214
  • 25
  • 84
  • 127
  • Please explain the makeup of your rows. Blob and Text columns, for example, have slightly different rules for judging size. – Jeff Ferland Dec 28 '10 at 20:47
  • i've added an explanation of my rows. total is about 7 million rows. – David19801 Dec 28 '10 at 21:39
  • I would suggest not using MySQL. Is there any reason why you want to use MySQL for this instead of some NoSQL solution? – Wolph Dec 28 '10 at 22:48
  • why do you need 120 varchar(500) columns - perhaps you should be inserting rows instead !! – Jon Black Dec 28 '10 at 23:44
  • Why mysql? - the db will be on shared hosting and its all they have...why 120 varchars? because thats the fastest way! I thought about joins, but thats too slow, this should be quick, since the rows are indexed. speed is the most important thing, available size is huge, bandwidth too. I need it fast. V.fast. – David19801 Dec 29 '10 at 10:40

2 Answers2

0

not sure why you've chosen to use 120 varchar(500) columns but i think you'd be better off inserting rows - something like this:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null,
next_note_id smallint unsigned not null default 0
)
engine=innodb;

drop table if exists user_notes;
create table user_notes
(
user_id int unsigned not null,
note_id smallint unsigned not null,
note varchar(512) not null,
primary key (user_id, note_id) -- clustered composite PK
)
engine=innodb;

delimiter #

create trigger user_notes_before_ins_trig before insert on user_notes
for each row
begin
declare v_id int unsigned default 0;

  select next_note_id + 1 into v_id from users where user_id = new.user_id;
  set new.note_id = v_id;
  update users set next_note_id = v_id where user_id = new.user_id;
end#

delimiter ;

insert into users (username) values ('f00'),('bar');

insert into user_notes (user_id, note) values
(1,'user 1 note 1'), (1,'user 1 note 2'), (1,'user 1 note 3'),
(2,'user 2 note 1');

select * from users;
user_id username   next_note_id
======= ========   ============
1         f00        3
2         bar        1

select * from user_notes;
user_id note_id note
======= ======= ====
1         1       user 1 note 1
1         2       user 1 note 2
1         3       user 1 note 3
2         1       user 2 note 1

hope it helps :)

Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • Interesting, but with 7 million rows already, would this mean 700 million if 100 varchars each?> – David19801 Dec 29 '10 at 11:03
  • In my example if each user has 100 notes and there are 7 million users then you would have (7 million users * 100 notes) = 700 million rows of data. If you're worried about that, you might want to check my answer here http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 (just think of a forum as a user and a thread as a note - 500 million rows with a query runtime of 0.02 seconds under load) – Jon Black Dec 29 '10 at 11:51
0

InnoDB has the same 64K limit for row size. It also limits row sizes to half the page size, but that does not include varbinary, varchar, blob, or text columns. By default, InnoDB's page size is 16K, so that's where you end up with the 8K limit. But you should be fine for the conversion since your columns are varchar.

However, if you are not using the InnoDB Plugin, the row storage format used ("compact") may be inefficient, as the varchar content beyond 768 bytes is stored in supplemental 16K pages, and those 4 extra pages in your case (ceil(60/16)) may be stored anywhere in the InnoDB pool (not a problem if your dataset fits in the InnoDB buffer pool). I would use the InnoDB Plugin's Dynamic row format.

Be aware that the conversion of large tables takes a long time. Try converting a backup on your development machine first.

Mark Rose
  • 971
  • 9
  • 15
  • "the varchar content beyond 768 bytes is stored in supplemental 16K pages" This is very interesting. Each varchar will be latin1, which I believe stores as 1byte per letter. Hence, would a varchar(500) be ok? Is the 768 limit per varchar or for all varchars in the row added together? cheers – David19801 Dec 29 '10 at 10:29
  • The 768 byte in-row-storage limit is for all v/v/b/t columns together. So if you have more than 768 bytes in total of those, extra pages will be used by InnoDB to store the content beyond 768 bytes (using the old "compact" format). If using the new dynamic row format, all the v/v/b/t columns are stored in additional pages as it makes the b-tree indexes smaller in size, which speeds index operations up (less memory paging by the CPU). Since MySQL 5.0.3, the maximum length of an individual varchar is 65,535 bytes. http://dev.mysql.com/doc/refman/5.0/en/char.html – Mark Rose Dec 29 '10 at 17:45
  • How sure are you that its for all c/b/t columns **TOGETHER**? I would have thought that is for each, with 8000 being the absolut max of everything. 768 just seems way too low... – David19801 Dec 29 '10 at 20:57
  • Read the two links I posted in the answer; it's explained there. – Mark Rose Dec 30 '10 at 03:53