RE: InnoDB frightens me...
Sure seems that way to me. Same SQL engine no matter what table type you use. InnoDB didn't scare me. Pretty friendly to use. :>) --Walt -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 2:09 PM To: [EMAIL PROTECTED] Cc: Tobias Lind - Telia Internet; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: InnoDB frightens me... On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: > I sure would, thanks Monty and Tobias. In particular some sample > update/insert/delete db code for MyISAM vs. InnoDB, (especially in > the case where you're not adding transactions, just trying to take > advantage of row-locking.) Assuming something changes, of course. > > The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 896,354,110 queries (394/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: > I sure would, thanks Monty and Tobias. In particular some sample > update/insert/delete db code for MyISAM vs. InnoDB, (especially in > the case where you're not adding transactions, just trying to take > advantage of row-locking.) Assuming something changes, of course. > > The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 896,354,110 queries (394/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB frightens me...
I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. Steve Rapaport. ] -Original Message- ] From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] ] Sent: Tuesday, March 05, 2002 1:46 PM ] To: [EMAIL PROTECTED] ] Cc: [EMAIL PROTECTED] ] Subject: Re: InnoDB frightens me... ] ] ] Thanks - that would be great! ] I'm sure a lot of people will find this very helpful. ] ] Regards, ] Tobias ] ] ] - Original Message - ] From: "Michael Widenius" <[EMAIL PROTECTED]> ] To: "Tobias Lind - Telia Internet" <[EMAIL PROTECTED]> ] Cc: <[EMAIL PROTECTED]> ] Sent: Tuesday, March 05, 2002 12:36 PM ] Subject: Re: InnoDB frightens me... ] ] ] > ] > Hi! ] > ] > Tobias> Hi! ] > Tobias> I'm in the same position as Steve: Considering ] switching from MyISAM to InnoDB because of ] the ] > Tobias> row-level locking capabilities. My application has ] quite a lot of updates/inserts mixed ] with ] > Tobias> selects, and is starting to suffer from the ] table-locking policy... ] > Tobias> And like Steve, I'm also scared! :) ] > ] > ] > ] > Tobias> Maybe some of this confusion and fear could be avoided ] with a section in the InnoDB ] documentation ] > Tobias> that describes InnoDB from a "MyISAM-point-of-view" :) ] > ] > Tobias> Explaining how all common things, principles and ] routines with MyISAM works with InnoDB. ] I'm sure a ] > Tobias> lot of people have very good knowledge of MyISAM and ] comes from the same direction as I ] do... ] > ] > I have forwarded this to our docmentation team and we shall try to do ] > this in the near future. ] > ] > Regards, ] > Monty ] > ] > -- ] > For technical support contracts, goto https://order.mysql.com/ ] >__ ___ ___ __ ] > / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> ] > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO ] > /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ] ><___/ www.mysql.com ] ] ] - ] Before posting, please check: ]http://www.mysql.com/manual.php (the manual) ]http://lists.mysql.com/ (the list archive) ] ] To request this thread, e-mail <[EMAIL PROTECTED]> ] To unsubscribe, e-mail ] <[EMAIL PROTECTED]> ] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB frightens me...
You can use "logical lock" - set a STATUS int <>0 (probably the ID of user) to indicate someone started to edit it interactively to block other user to edit the same record. Before you write it back to disk, read it again in a buffer to keep all data possible changed by batch updates. Or separate the table to 2 tables, one for each kind of update. Also I use a kind of "transactions" before I know that exists, I don't subtract from "items" directrly but I put it in a field named "keeps". If user change his mind or something goes wrong, I only subtruct from "keeps" fields.Otherwise I subtract from "items" and "keeps". Of course I have create another temp file with the invoice. I use these methods several years now mainly with C-tree from Faircom, they work fine. But to tell you the true, I use Delphi with Interbase for this kind of application on the net. I don't know if it's possible with PHP. Can someone directs me to the avantages of Inno base comparing wtih default MySQL? Row locking is the bigest one as I can read here? Thanks Makis > -Original Message- > From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 05, 2002 2:46 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: InnoDB frightens me... > > > Thanks - that would be great! > I'm sure a lot of people will find this very helpful. > > Regards, > Tobias > > > - Original Message - > From: "Michael Widenius" <[EMAIL PROTECTED]> > To: "Tobias Lind - Telia Internet" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, March 05, 2002 12:36 PM > Subject: Re: InnoDB frightens me... > > > > > > Hi! > > > > Tobias> Hi! > > Tobias> I'm in the same position as Steve: Considering > switching from MyISAM to InnoDB because of > the > > Tobias> row-level locking capabilities. My application has > quite a lot of updates/inserts mixed > with > > Tobias> selects, and is starting to suffer from the > table-locking policy... > > Tobias> And like Steve, I'm also scared! :) > > > > > > > > Tobias> Maybe some of this confusion and fear could be > avoided with a section in the InnoDB > documentation > > Tobias> that describes InnoDB from a "MyISAM-point-of-view" :) > > > > Tobias> Explaining how all common things, principles and > routines with MyISAM works with InnoDB. > I'm sure a > > Tobias> lot of people have very good knowledge of MyISAM > and comes from the same direction as I > do... > > > > I have forwarded this to our docmentation team and we shall > try to do > > this in the near future. > > > > Regards, > > Monty > > > > -- > > For technical support contracts, goto https://order.mysql.com/ > >__ ___ ___ __ > > / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius > <[EMAIL PROTECTED]> > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO > > /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland > ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Thanks - that would be great! I'm sure a lot of people will find this very helpful. Regards, Tobias - Original Message - From: "Michael Widenius" <[EMAIL PROTECTED]> To: "Tobias Lind - Telia Internet" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, March 05, 2002 12:36 PM Subject: Re: InnoDB frightens me... > > Hi! > > Tobias> Hi! > Tobias> I'm in the same position as Steve: Considering switching from MyISAM to >InnoDB because of the > Tobias> row-level locking capabilities. My application has quite a lot of >updates/inserts mixed with > Tobias> selects, and is starting to suffer from the table-locking policy... > Tobias> And like Steve, I'm also scared! :) > > > > Tobias> Maybe some of this confusion and fear could be avoided with a section in the >InnoDB documentation > Tobias> that describes InnoDB from a "MyISAM-point-of-view" :) > > Tobias> Explaining how all common things, principles and routines with MyISAM works >with InnoDB. I'm sure a > Tobias> lot of people have very good knowledge of MyISAM and comes from the same >direction as I do... > > I have forwarded this to our docmentation team and we shall try to do > this in the near future. > > Regards, > Monty > > -- > For technical support contracts, goto https://order.mysql.com/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO > /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ><___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi! Tobias> Hi! Tobias> I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias> row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias> selects, and is starting to suffer from the table-locking policy... Tobias> And like Steve, I'm also scared! :) Tobias> Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias> that describes InnoDB from a "MyISAM-point-of-view" :) Tobias> Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias> lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: InnoDB frightens me...
As far my knowledge goes there is only 1 tablespace available atm. If you create new datafiles they will get appended to the one and only tablespace. IF there would be more than one tablespace you would need to specify one as default. You should also be able to "ALTER" a table to another tablespace. Regards, Christian -Ursprungliche Nachricht- Von: Rick Flower [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. Marz 2002 19:17 An: MySQL Mailing List Cc: Heikki Tuuri Betreff: AW: InnoDB frightens me... Christian Rabe writes: >Are there plans on creating more than on tablesspace plus making them >selectable ? >So that I can tell the DB: >- in which datafiles to store the tablespace X >- in which tablespace to store table Y This is one of the things I was wondering about.. If I have multiple InnoDB tablespaces, and I fire up the MySQL command-line client and issue a "Create Table... TYPE=InnoDB", how do I tell it I want the table created in InnoDB space #1 or #2...?? As far as I can tell, it would appear that MySQL/InnoDB get the deciding vote there.. Perhaps I'm just missing something.. -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
On Monday 04 March 2002 09:32 pm, you wrote: > On Mon, Mar 04, 2002 at 09:30:16AM -0500, Ken Menzel wrote: > > Hi Heikki, > > > > I don't know if this has been requested, but what about a tool to > > 'pre-create' dataspace? This tool would allow someone to create a > > new dataspace, then a quick restart (After adding the name of the > > space to 'my.cnf') and the new dataspace is available! Maybe just > > extract the pieces from MySQL code and make it a separate tool? > > > > I know I don't want to have a server shutdown while it creates 10GB > > of Dataspace! Of course autoextending is one answer. One could > > just create a small dataspace and let it autoextend. > > I'll second the request. I'm planning to test InnoDB with "large" > tablespaces soon (on the order of .5TB) and would love to be able to > create the data files off-line and add them as needed. > > Jeremy Ditto, I know this is one thing keeping us from going to Innodb full scale. Jayce^ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
On Mon, Mar 04, 2002 at 09:30:16AM -0500, Ken Menzel wrote: > > Hi Heikki, > > I don't know if this has been requested, but what about a tool to > 'pre-create' dataspace? This tool would allow someone to create a > new dataspace, then a quick restart (After adding the name of the > space to 'my.cnf') and the new dataspace is available! Maybe just > extract the pieces from MySQL code and make it a separate tool? > > I know I don't want to have a server shutdown while it creates 10GB > of Dataspace! Of course autoextending is one answer. One could > just create a small dataspace and let it autoextend. I'll second the request. I'm planning to test InnoDB with "large" tablespaces soon (on the order of .5TB) and would love to be able to create the data files off-line and add them as needed. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 860,216,505 queries (388/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: InnoDB frightens me...
Christian Rabe writes: >Are there plans on creating more than on tablesspace plus making them >selectable ? >So that I can tell the DB: >- in which datafiles to store the tablespace X >- in which tablespace to store table Y This is one of the things I was wondering about.. If I have multiple InnoDB tablespaces, and I fire up the MySQL command-line client and issue a "Create Table... TYPE=InnoDB", how do I tell it I want the table created in InnoDB space #1 or #2...?? As far as I can tell, it would appear that MySQL/InnoDB get the deciding vote there.. Perhaps I'm just missing something.. -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi Heikki, I don't know if this has been requested, but what about a tool to 'pre-create' dataspace? This tool would allow someone to create a new dataspace, then a quick restart (After adding the name of the space to 'my.cnf') and the new dataspace is available! Maybe just extract the pieces from MySQL code and make it a separate tool? I know I don't want to have a server shutdown while it creates 10GB of Dataspace! Of course autoextending is one answer. One could just create a small dataspace and let it autoextend. Thanks, Ken - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, March 03, 2002 6:46 AM Subject: Re: InnoDB frightens me... > Steve, > > I added an item to the TODO list at http://www.innodb.com/todo.html > > .. > May, 2002: > Make a data file auto-extendible. You can specify the last data file in > innodb_data_file_path like this: > > ibdata1:50Mautoextend > > It will create a data file whose initial size is 50 MB, and InnoDB will > automatically extend it in units of 10 MB when the data file becomes full. > .. > > Best regards, > > Heikki Tuuri > Innobase Oy > --- > Order technical MySQL/InnoDB support at https://order.mysql.com/ > Speed up adding of features to MySQL/InnoDB through support contracts > See http://www.innodb.com for the online manual and latest news on InnoDB > > > -Original Message- > From: Steve Rapaport <[EMAIL PROTECTED]> > Newsgroups: mailing.database.mysql > Date: Sunday, March 03, 2002 12:18 AM > Subject: InnoDB frightens me... > > > >I'm seriously considering switching to mysql-max so I can make my > >session handling table an Innodb type. Currently the mysql locking policy > >allows big traffic jams when several sessions are active simultaneously, > >and it's the only table that has frequent updates. I need row-locks! > > > >BUT, and it's a big but, > > > >I just read through the InnoDB manual pages in the mysql site, and > >it seems I can't have row-locking without a lot of programming > >and worse, admin overhead. And scary a-priori decisions. > > > >At first glance (correct me) I need to > >1. Check through all my programs handling this table to > >add AUTOCOMMIT or Commit/Rollback as appropriate. > > > >2. Decide with zero experience on a lot of maximum sizes which will not be > >adjustable in future, including dataspace. > > > >3. If I run up against one of those limits in future I am guaranteed > >a nightmare of table copying, deleting, restoring, and woe if I > >happen to get a "runaway rollback". I am also required to back > >up my database table and all its update logs in case of this situation, > >although my chances of restoring them successfully look dim. > > > >I am sufficiently frightened to just accept table-lock traffic jams > instead. > >Can anyone tell me how I can use row-locking without getting into > >this frightening world? > > > >Best, > >Steve > > > >- > >Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > >To request this thread, e-mail <[EMAIL PROTECTED]> > >To unsubscribe, e-mail > <[EMAIL PROTECTED]> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: InnoDB frightens me...
Hi :) Are there plans on creating more than on tablesspace plus making them selectable ? So that I can tell the DB: - in which datafiles to store the tablespace X - in which tablespace to store table Y With MYISAM we could simply move the data+index files to a separate disc, create symlinks and everthing was ok, with InnoDB all tables are on the same disc and there is no way to divide the i/o :( Sure, you can use raid. But you still have no say in how much i/o a given table may use. Regards -Ursprüngliche Nachricht- Von: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 3. März 2002 12:46 An: [EMAIL PROTECTED] Betreff: Re: InnoDB frightens me... Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... >I'm seriously considering switching to mysql-max so I can make my >session handling table an Innodb type. Currently the mysql locking policy >allows big traffic jams when several sessions are active simultaneously, >and it's the only table that has frequent updates. I need row-locks! > >BUT, and it's a big but, > >I just read through the InnoDB manual pages in the mysql site, and >it seems I can't have row-locking without a lot of programming >and worse, admin overhead. And scary a-priori decisions. > >At first glance (correct me) I need to >1. Check through all my programs handling this table to >add AUTOCOMMIT or Commit/Rollback as appropriate. > >2. Decide with zero experience on a lot of maximum sizes which will not be >adjustable in future, including dataspace. > >3. If I run up against one of those limits in future I am guaranteed >a nightmare of table copying, deleting, restoring, and woe if I >happen to get a "runaway rollback". I am also required to back >up my database table and all its update logs in case of this situation, >although my chances of restoring them successfully look dim. > >I am sufficiently frightened to just accept table-lock traffic jams instead. >Can anyone tell me how I can use row-locking without getting into >this frightening world? > >Best, >Steve > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi! I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the row-level locking capabilities. My application has quite a lot of updates/inserts mixed with selects, and is starting to suffer from the table-locking policy... And like Steve, I'm also scared! :) I've been working quite a lot with MyISAM-tables and I have a good feeling for how it works (backups, data-files, index-files, size of these files, etc) But with InnoDB, I'm - hmmm - confused - that's probably the best word to describe it! The "auto-extendible"-feature on the todo-list is a great thing! That's one of the big concerns I've had with InnoDB. Another concern is the actual file size - I understand that InnoDB-tables will use a lot more space than the same table in MyISAM... Question is HOW much more (the largest table I currently have is ~2 Gb datafile, 360 Mb indexfile) I'm also confused with how it all works with tablespaces, log-files etc. Is all this "common knowledge" with transactional databases? If I'm not interested in transactions, do I have to care about all that? And all these limits and parameters! To quote from the InnoDB-documentation: "Note that data files must be < 2G in some file systems! The combined size of data files must be >= 10 MB. The combined size of the log files must be < 4G. " Hmmm - does any of this put any limitation for my application? (it's a retorical question - I know it depends on my app, but you get the point: It's a lot of new things to learn and consider before switching) And what about backups? Now I'm using mysqldump every night to backup my database - will that be ok for InnoDB too? (I know that it _works_ but what about performance - will it take longer to make a dump with InnoDB-tables? I have to take the website offline while dumping so it's important) I have been looking forward to online-backups, and it is on the todo-list for MySQL 4.1 - will that feature work with InnoDB-tables as well? I noticed that hotbackups is also on the todo-list for InnoDB, but that it will not be free... So... As you can see I'm a bit confused, and I think a lot of people are! It seems to be a whole lot more to learn, more administration, tweaking, etc with InnoDB than with MyISAM. Maybe I'm wrong, but I get that feeling from reading the InnoDB-docs... Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation that describes InnoDB from a "MyISAM-point-of-view" :) Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a lot of people have very good knowledge of MyISAM and comes from the same direction as I do... Thanks for your time! :) /Tobias Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... >I'm seriously considering switching to mysql-max so I can make my >session handling table an Innodb type. Currently the mysql locking policy >allows big traffic jams when several sessions are active simultaneously, >and it's the only table that has frequent updates. I need row-locks! > >BUT, and it's a big but, > >I just read through the InnoDB manual pages in the mysql site, and >it seems I can't have row-locking without a lot of programming >and worse, admin overhead. And scary a-priori decisions. > >At first glance (correct me) I need to >1. Check through all my programs handling this table to >add AUTOCOMMIT or Commit/Rollback as appropriate. > >2. Decide with zero experience on a lot of maximum sizes which will not be >adjustable in future, including dataspace. > >3. If I run up against one of those limits in future I am guaranteed >a nightmare of table copying, deleting, restoring, and woe if I >happen to get a "runaway rollback". I am also required to back >up my database table and all its update logs in case of this situation, >although my chances of restoring them successfully look dim. > >I am sufficiently frightened to just accept table-lock traffic jams instead. >Can anyone tell m
Re: InnoDB frightens me...
Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... >I'm seriously considering switching to mysql-max so I can make my >session handling table an Innodb type. Currently the mysql locking policy >allows big traffic jams when several sessions are active simultaneously, >and it's the only table that has frequent updates. I need row-locks! > >BUT, and it's a big but, > >I just read through the InnoDB manual pages in the mysql site, and >it seems I can't have row-locking without a lot of programming >and worse, admin overhead. And scary a-priori decisions. > >At first glance (correct me) I need to >1. Check through all my programs handling this table to >add AUTOCOMMIT or Commit/Rollback as appropriate. > >2. Decide with zero experience on a lot of maximum sizes which will not be >adjustable in future, including dataspace. > >3. If I run up against one of those limits in future I am guaranteed >a nightmare of table copying, deleting, restoring, and woe if I >happen to get a "runaway rollback". I am also required to back >up my database table and all its update logs in case of this situation, >although my chances of restoring them successfully look dim. > >I am sufficiently frightened to just accept table-lock traffic jams instead. >Can anyone tell me how I can use row-locking without getting into >this frightening world? > >Best, >Steve > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi Jeremy, My follow-up for this answer from the other list was: Does replication hurt MyISAM performance in the same way? If I'm running a master that takes all the inserts and one or more slaves to take all the select queries, would it be better implemented in MyISAM or InnoDB? I don't need the transaction ability of InnoDB, but if concurrent replication affects MyISAM performance in the same way as concurrent insert/select ops, it makes me wonder which table type I should use on my high insert activity tables. My assumption was MyISAM, but I'm not so sure now... I may have posted the same question here, earlier. I'd appreciate any input. Thanks, --jeff - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Jeff Kilbride" <[EMAIL PROTECTED]> Cc: "MySQL" <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 7:11 PM Subject: Re: InnoDB frightens me... > On Sat, Mar 02, 2002 at 06:52:57PM -0800, Jeff Kilbride wrote: > > > I'm looking to make the move to InnoDB, too. All I've heard is positve. > > Here's a reply I got on another list: > > > > > > If you have a very busy read/write op database, MyISAM can't handle > > it. It's very efficient for when you have many more reads than > > writes, but once you start hitting a balance between the two and > > then get some load, MyISAM just locks up. It's just known. > > Table-level locks lead to lengthy delays. > > And from what I've seen, things start to get problematic with MyISAM > when the percentage of read-only queries drops below about 70% or so. > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 23 days, processed 765,311,906 queries (376/sec. avg) > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
On Sat, Mar 02, 2002 at 06:52:57PM -0800, Jeff Kilbride wrote: > I'm looking to make the move to InnoDB, too. All I've heard is positve. > Here's a reply I got on another list: > > > If you have a very busy read/write op database, MyISAM can't handle > it. It's very efficient for when you have many more reads than > writes, but once you start hitting a balance between the two and > then get some load, MyISAM just locks up. It's just known. > Table-level locks lead to lengthy delays. And from what I've seen, things start to get problematic with MyISAM when the percentage of read-only queries drops below about 70% or so. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 23 days, processed 765,311,906 queries (376/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
I'm looking to make the move to InnoDB, too. All I've heard is positve. Here's a reply I got on another list: If you have a very busy read/write op database, MyISAM can't handle it. It's very efficient for when you have many more reads than writes, but once you start hitting a balance between the two and then get some load, MyISAM just locks up. It's just known. Table-level locks lead to lengthy delays. Our experiences with InnoDB have been absolutely terrific. We've peaked at 800/inserts/sec on mediocre hardware into multiple complex tables (large indices, many indices, etc.). The only problems we've had is the foreign key vs. alter table issue. Alter table wipes out foreign keys, and a lot of the things MySQL does behind the scenes to make things work use alter table. As Jeremy said, you can always create more space later and you don't have to worry about Commit/Rollback or AUTOCOMMIT, unless you're using transactions. I'm assuming you're not, since you're moving from MyISAM. Thanks, --jeff - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Steve Rapaport" <[EMAIL PROTECTED]> Cc: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 6:19 PM Subject: Re: InnoDB frightens me... > On Sat, Mar 02, 2002 at 11:14:42PM +0100, Steve Rapaport wrote: > > > > I'm seriously considering switching to mysql-max so I can make my > > session handling table an Innodb type. Currently the mysql locking > > policy allows big traffic jams when several sessions are active > > simultaneously, and it's the only table that has frequent updates. > > I need row-locks! > > > > BUT, and it's a big but, > > > > I just read through the InnoDB manual pages in the mysql site, and > > it seems I can't have row-locking without a lot of programming and > > worse, admin overhead. And scary a-priori decisions. > > Hm? > > > At first glance (correct me) I need to > > 1. Check through all my programs handling this table to > > add AUTOCOMMIT or Commit/Rollback as appropriate. > > Only if you want transactions. > > > 2. Decide with zero experience on a lot of maximum sizes which will not be > > adjustable in future, including dataspace. > > You can always add more space later. > > > I am sufficiently frightened to just accept table-lock traffic jams > > instead. Can anyone tell me how I can use row-locking without > > getting into this frightening world? > > Try out BDB tables, which have page-level locking? > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 23 days, processed 763,640,350 queries (376/sec. avg) > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
On Sat, Mar 02, 2002 at 11:14:42PM +0100, Steve Rapaport wrote: > > I'm seriously considering switching to mysql-max so I can make my > session handling table an Innodb type. Currently the mysql locking > policy allows big traffic jams when several sessions are active > simultaneously, and it's the only table that has frequent updates. > I need row-locks! > > BUT, and it's a big but, > > I just read through the InnoDB manual pages in the mysql site, and > it seems I can't have row-locking without a lot of programming and > worse, admin overhead. And scary a-priori decisions. Hm? > At first glance (correct me) I need to > 1. Check through all my programs handling this table to > add AUTOCOMMIT or Commit/Rollback as appropriate. Only if you want transactions. > 2. Decide with zero experience on a lot of maximum sizes which will not be > adjustable in future, including dataspace. You can always add more space later. > I am sufficiently frightened to just accept table-lock traffic jams > instead. Can anyone tell me how I can use row-locking without > getting into this frightening world? Try out BDB tables, which have page-level locking? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 23 days, processed 763,640,350 queries (376/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB frightens me...
I'm seriously considering switching to mysql-max so I can make my session handling table an Innodb type. Currently the mysql locking policy allows big traffic jams when several sessions are active simultaneously, and it's the only table that has frequent updates. I need row-locks! BUT, and it's a big but, I just read through the InnoDB manual pages in the mysql site, and it seems I can't have row-locking without a lot of programming and worse, admin overhead. And scary a-priori decisions. At first glance (correct me) I need to 1. Check through all my programs handling this table to add AUTOCOMMIT or Commit/Rollback as appropriate. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. 3. If I run up against one of those limits in future I am guaranteed a nightmare of table copying, deleting, restoring, and woe if I happen to get a "runaway rollback". I am also required to back up my database table and all its update logs in case of this situation, although my chances of restoring them successfully look dim. I am sufficiently frightened to just accept table-lock traffic jams instead. Can anyone tell me how I can use row-locking without getting into this frightening world? Best, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
