Re: InnoDB Backups
Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, January 30, 2004 6:51 PM Subject: Re: InnoDB Backups By open file tool I mean software that works concurrently with a backup suite such as veritas that would backup any open files (such as MySQL binaries) that would normally be skipped. with Google I found something called an 'open file tool'. I do not know if it works properly with InnoDB or other relational databases. Best to test it very carefully. What do you mean by inconsistent? What does InnoDB Hot backup do differently than an open file agent? It checks the consistency of each InnoDB data page and archives the log as well. And it has been extensively tested. So the only two ways to do an online backup of InnoDB tables is InnoDB Hot backup or mysqldump? Also snapshot file systems, like the Veritas file system should work, though I have not tested them. Thanks, Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 4:22 AM Subject: Re: InnoDB Backups Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Backups
Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Backups
By open file tool I mean software that works concurrently with a backup suite such as veritas that would backup any open files (such as MySQL binaries) that would normally be skipped. What do you mean by inconsistent? What does InnoDB Hot backup do differently than an open file agent? So the only two ways to do an online backup of InnoDB tables is InnoDB Hot backup or mysqldump? Thanks, Mauro - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 4:22 AM Subject: Re: InnoDB Backups Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Backups
The Hot backup/dump tools use the mysql server to create a live backup while the server is running. On MyIsam tables, I think they are locked during the entire process.. innodb may be different. You can backup the DB files directly, but, the mysql server MUST be shut down to do so.. which is likely not what you want. Since lots of information may be sitting in the buffers when you copy the files, along with file-close checks and such, you would only get partial data backups, which would be far less effective to restore from. When the mysql server is shut down, those buffers would all be sent to disk. Any remaining FS/OS buffers would be honored by the FS call for copy. So, yes it works fine if the files are copied when the server is off. I am about to switch to innodb myself, and I am simply going to have to buy the Innodb hot backup tool to make full backups. however, do not forget that even those are out of date the moment the backup is done ;) Replication is your best friend, next to your Dog of course, -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 30, 2004, at 11:50 AM, Mauro Marcellino wrote: By open file tool I mean software that works concurrently with a backup suite such as veritas that would backup any open files (such as MySQL binaries) that would normally be skipped. What do you mean by inconsistent? What does InnoDB Hot backup do differently than an open file agent? So the only two ways to do an online backup of InnoDB tables is InnoDB Hot backup or mysqldump? Thanks, Mauro - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 4:22 AM Subject: Re: InnoDB Backups Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb firsttime startup error
Kev [EMAIL PROTECTED] wrote: I just ran mysql 4.0.15 on a Mac for the first time and got the following in my .err file: InnoDB: a new database to be created! 040128 7:40:24 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 20 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:45 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 20 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040128 7:41:08 InnoDB: Started 040128 7:41:08 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040128 7:41:08 Aborting 040128 7:41:08 InnoDB: Starting shutdown... 040128 7:41:11 InnoDB: Shutdown completed 040128 7:41:11 /Library/MySQL/libexec/mysqld: Shutdown Complete 040128 07:41:11 mysqld ended This error isn't related to the InnoDB. It means that you doesn't have privilege tables or MySQL doesn't have permission on the data dir. Check if privilege table files exist in the directory of the database mysql. If so, check permissions on the files and data dir. If files don't exist, you should run mysql_install_db script to install grant tables. How do I create the mysql.host file and where should it be located? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on delete, on update
Gitte und Ingolf [EMAIL PROTECTED] wrote: Hi out there, i.m using InnoDB with foreign key constraints and i.m looking for the default behavior of ON DELETE and ON UPDATE. Secondly, what does the ON UPDATE NO ACTION option means? Ist the way it is used in db2, that every child row must have some matching parent row. If this condition is not met,the Update fails and all changes are rolled back? Or is the Update done? Currently NO ACTION does nothing. In a future it will work the same as RESTRICT: if you update referenced column and there are any matching rows in the referencing table, the update will fail. What is the meaning of ON UPDATE CASCADE and ON UPDATE SET NULL?? If you specify ON UPDATE CASCADE, every time you update row in the parent table, InnoDB will automatically update corresponding foreign key column in all matching rows in the child table to the same value. ON UPDATE SET NULL means that every time you update row in the parent table, InnoDB will automatically set corresponding foreign key column in every matching rows of the child table to NULL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB file format changes 2004 - 2005; Re: InnoDB development
Chris, InnoDB file format changes: 4.1.0 - 4.1.1 introduced multiple tablespaces; 4.1.1 - 4.1.2 or .3allow multiple charsets in the same database installation (currently only the default charset is used in InnoDB); 5.0.0 - 5.0.x create all new InnoDB tables in a space-saving format; this will also slightly change the InnoDB log format, and users of InnoDB Hot Backup should upgrade ibbackup at the same time. I hope there will not be more InnoDB file format changes in 2004 - 2005. In the MySQL tradition, an upgrade is always painless with no data conversion. But a downgrade is tricky if the file format has changed. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html . List:MySQL General Discussion« Previous MessageNext Message » From:Chris NolanDate:January 27 2004 1:29am Subject:InnoDB development Hi all, I've been away from the list for a while and am wondering if the following question has been answered: Given that the extremely funky InnoDB is going to gain a new file format in the future, would it be a fair guess to say that any additions to InnoDB requiring file format changes would be made after the release of the new compressed format? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
In theory it is fastest to add indexes first, then disable it (ALTER TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys (ALTER TABLE x DISABLE KEYS). mirza Keith Thompson wrote: Hello all, I need to load a new InnoDB table with about 80 million rows. With MyISAM I have often found that it is faster to create the table without indexes, add all the data, then add the indexes. Is this true with InnoDB as well, or should I put my indexes in before loading? Similarly, I have another large table that is currently MyISAM that I'd like to move to InnoDB. What would be the fastest steps towards accomplishing this (as far as indexes, etc.)? Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mirza [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 22, 2004 11:04 AM Subject: Re: InnoDB loading: add keys before or after In theory it is fastest to add indexes first, then disable it (ALTER TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys (ALTER TABLE x DISABLE KEYS). mirza Keith Thompson wrote: Hello all, I need to load a new InnoDB table with about 80 million rows. With MyISAM I have often found that it is faster to create the table without indexes, add all the data, then add the indexes. Is this true with InnoDB as well, or should I put my indexes in before loading? Similarly, I have another large table that is currently MyISAM that I'd like to move to InnoDB. What would be the fastest steps towards accomplishing this (as far as indexes, etc.)? Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
Heikki, Thanks for your help. I have another very large table to convert to InnoDB from MyISAM. I also have the same table saved in a file suitable for 'load data'. Which would be faster: ALTER TABLE xxx TYPE=InnoDB; or CREATE TABLE newxxx ...; INSERT INTO newxxx select * from xxx; or CREATE TABLE newxxx ...; LOAD DATA INFILE ... Thanks -keith Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mirza [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 22, 2004 11:04 AM Subject: Re: InnoDB loading: add keys before or after In theory it is fastest to add indexes first, then disable it (ALTER TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys (ALTER TABLE x DISABLE KEYS). mirza Keith Thompson wrote: Hello all, I need to load a new InnoDB table with about 80 million rows. With MyISAM I have often found that it is faster to create the table without indexes, add all the data, then add the indexes. Is this true with InnoDB as well, or should I put my indexes in before loading? Similarly, I have another large table that is currently MyISAM that I'd like to move to InnoDB. What would be the fastest steps towards accomplishing this (as far as indexes, etc.)? Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
Keith, I would divide the work: 1) CREATE TABLE innodbtypetable ... 2) INSERT INTO innodbtypetable SELECT * from myisamtypetable WHERE primarykey = xyz AND primarykey zyx; etc. If you run out of disk space or get some other problem, the rollback will not be as huge as for a single transaction. Regards, Heikki - Original Message - From: Keith Thompson [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 4:46 PM Subject: Re: InnoDB loading: add keys before or after Heikki, Thanks for your help. I have another very large table to convert to InnoDB from MyISAM. I also have the same table saved in a file suitable for 'load data'. Which would be faster: ALTER TABLE xxx TYPE=InnoDB; or CREATE TABLE newxxx ...; INSERT INTO newxxx select * from xxx; or CREATE TABLE newxxx ...; LOAD DATA INFILE ... Thanks -keith Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mirza [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 22, 2004 11:04 AM Subject: Re: InnoDB loading: add keys before or after In theory it is fastest to add indexes first, then disable it (ALTER TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys (ALTER TABLE x DISABLE KEYS). mirza Keith Thompson wrote: Hello all, I need to load a new InnoDB table with about 80 million rows. With MyISAM I have often found that it is faster to create the table without indexes, add all the data, then add the indexes. Is this true with InnoDB as well, or should I put my indexes in before loading? Similarly, I have another large table that is currently MyISAM that I'd like to move to InnoDB. What would be the fastest steps towards accomplishing this (as far as indexes, etc.)? Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
Hi there, Patrick Fowler wrote: Do I have to compile from source with the innoDB flag in order to use the innoDB tables or just in stall the RPM? As I am using flawlessly working InnoDB tables on an RPM installation of MySQL, I can say: no need to compile from source for InnoDB use. Fred -- Machines certainly can solve problems, store information, correlate, and play games -- but not with pleasure. -- Leo Rosten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
Hello Heikki, Thank you for your reply and your explanation. It clarifies things and explains some of the unexpected behavior I've observed (such as my own inability to insert to the gap after locking it). In general, is there a place where I can find a good discussion on the various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping troubleshoot these kinds of problems. Thank you for your time, Alex Zeltser List: MySQL General Discussion Previous MessageNext Message From: Heikki Tuuri Date: January 21 2004 4:32am Subject: Re: InnoDB locking 'non-existence' of a row Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row Hi Joe, Thanks for your reply. Actually, in my experience (and according to the = docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row = for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert = the row from another transaction will block or fail (until the first one does a commit or a = rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction = 'lock' the non-existence of a row with a select, and another wait until the lock is released--also = with a select. I've considered doing what you propose with a separate lock table, and may still do just = that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi,=20 Selecting a non-existent row won't acquire any locks that prevents = inserts from happening. One way to accomplish what you want is to create a separate insert lock table = consisting of a table name and a lock counter. Add a row for each table that you want to have these = insert locks on, and before performing any inserts, either update the corresponding row in the = insert lock table or select it for an update.=20 joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, =20 Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 that works just like the default REPEATABLE READ level? I've tried by = example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 the results were the same. =20 Is there any way to make the second session block when both it and the = first one are 'locking' non-existence of a row? =20 Thanks in advance, =20 Alex =20 -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row =20 =20 Hi Alex! =20 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, =20 I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to = do this by using 'select ... for update', using the 'mysql' client=20 from two separate sessions as shown below: =20 Session 1: set AUTOCOMMIT=3D0; begin
Re: InnoDB locking 'non-existence' of a row
Alex, - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 10:28 PM Subject: RE: InnoDB locking 'non-existence' of a row Hello Heikki, Thank you for your reply and your explanation. It clarifies things and = explains some of the unexpected behavior I've observed (such as my own inability to insert to = the gap after locking it). In general, is there a place where I can find a good discussion on the = various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but = wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping = troubleshoot these kinds of problems. /mysql/innobase/lock/lock0lock.c contains a lengthy comment at the start of the file. And the full source code of the algorithms, too :). Papers by IBM researcher C. Mohan may also be of interest. Thank you for your time, Alex Zeltser Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
hi, Selecting a non-existent row won't acquire any locks that prevents inserts from happening. One way to accomplish what you want is to create a separate insert lock table consisting of a table name and a lock counter. Add a row for each table that you want to have these insert locks on, and before performing any inserts, either update the corresponding row in the insert lock table or select it for an update. joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
Hi Joe, Thanks for your reply. Actually, in my experience (and according to the docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert the row from another transaction will block or fail (until the first one does a commit or a rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction 'lock' the non-existence of a row with a select, and another wait until the lock is released--also with a select. I've considered doing what you propose with a separate lock table, and may still do just that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi, Selecting a non-existent row won't acquire any locks that prevents inserts from happening. One way to accomplish what you want is to create a separate insert lock table consisting of a table name and a lock counter. Add a row for each table that you want to have these insert locks on, and before performing any inserts, either update the corresponding row in the insert lock table or select it for an update. joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB key adding slowness
I forgot to mention too that this is the 64bit MySQL 4.0.17 running on Solaris9. Hi all, I decided I needed another index in an InnoDB table that has about 25 million rows (each 80 bytes long). As I've done dozens of times with MyISAM tables, I did a simple alter table: alter table WMH_CHK_a add key JoinTrans (TransID) This has been running for 14 hours and I have no idea how much longer it's going to take, but it's getting darned frustrating as it's preventing lots of other work. This is not a complicated table and it is running on a 4-CPU Sun server with a high-speed disk setup. The server has 4GB memory and I've got all of the InnoDB parameters set up at a moderate level (such as a 1GB buffer_pool_size), etc. It is otherwise not very busy on other activity, so it should have most of the system's resources available to it. Any ideas what is taking this so long? This would not have taken more than a couple hours with a similar MyISAM table on the same server (based on prior experience). Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB key adding slowness
Keith, please see my reply to Mikel in thread 'Too slow recovering mysqldump files'. Since you are running a 64-bit version, you can set innodb_buffer_pool_size as high as 3 GB in your hardware. You can monitor the progress of the big ALTER TABLE with SHOW INNODB STATUS\G Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Keith Thompson [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 3:50 AM Subject: Re: InnoDB key adding slowness I forgot to mention too that this is the 64bit MySQL 4.0.17 running on Solaris9. Hi all, I decided I needed another index in an InnoDB table that has about 25 million rows (each 80 bytes long). As I've done dozens of times with MyISAM tables, I did a simple alter table: alter table WMH_CHK_a add key JoinTrans (TransID) This has been running for 14 hours and I have no idea how much longer it's going to take, but it's getting darned frustrating as it's preventing lots of other work. This is not a complicated table and it is running on a 4-CPU Sun server with a high-speed disk setup. The server has 4GB memory and I've got all of the InnoDB parameters set up at a moderate level (such as a 1GB buffer_pool_size), etc. It is otherwise not very busy on other activity, so it should have most of the system's resources available to it. Any ideas what is taking this so long? This would not have taken more than a couple hours with a similar MyISAM table on the same server (based on prior experience). Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row Hi Joe, Thanks for your reply. Actually, in my experience (and according to the = docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row = for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert = the row from another transaction will block or fail (until the first one does a commit or a = rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction = 'lock' the non-existence of a row with a select, and another wait until the lock is released--also = with a select. I've considered doing what you propose with a separate lock table, and may still do just = that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi,=20 Selecting a non-existent row won't acquire any locks that prevents = inserts from happening. One way to accomplish what you want is to create a separate insert lock table = consisting of a table name and a lock counter. Add a row for each table that you want to have these = insert locks on, and before performing any inserts, either update the corresponding row in the = insert lock table or select it for an update.=20 joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, =20 Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 that works just like the default REPEATABLE READ level? I've tried by = example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 the results were the same. =20 Is there any way to make the second session block when both it and the = first one are 'locking' non-existence of a row? =20 Thanks in advance, =20 Alex =20 -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row =20 =20 Hi Alex! =20 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, =20 I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to = do this by using 'select ... for update', using the 'mysql' client=20 from two separate sessions as shown below: =20 Session 1: set AUTOCOMMIT=3D0; begin; select * from T where A =3D 'NOT_THERE' for update; =20 Session 2: set AUTOCOMMIT=3D0; begin; select * from T where A =3D 'NOT_THERE' for update; =20 What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. =20 Unfortunately, it didn't work that way. The 'select's in both=20 sessions returned right away, and it was only the subsequent=20 'insert's, 'update's and 'delete's that blocked. I can understand = the=20 rationale behind this behavior, but unfortunately it doesn't help me = with my problem. I'd like to be able to reliably check for = existence=20 of a record from two concurrent sessions and have the 'select' in = the=20 'second' session block
Re: InnoDB locking 'non-existence' of a row
Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb defragmentation question
Franky, you apparently have just the PRIMARY KEY defined on the table and no secondary indexes. I guess the fragmentation comes because you delete rows from in the middle with respect to the primary key ordering? Or do you also insert in random order? Anyway, you cannot do anything to prevent fragmentation. Below the average length of a row in 50 bytes. It cannot yet be terribly fragmented, because the minimum length for an InnoDB row is about 20 bytes. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html From: Franky Van Liedekerke ([EMAIL PROTECTED]) Subject: Re: innodb defragmentation question View: Complete Thread (4 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-31 00:33:47 PST the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of show table status and show innodb status for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | users_groups| InnoDB | Fixed | 0 | 0 | 16384 |NULL |0 | 0 |NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic| 7 | 2340 | 16384 |NULL |16384 | 0 | 8 | NULL| NULL| NULL || InnoDB free: 8192 kB And the show innodb status: = 031231 9:31:03 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 TRANSACTIONS Trx id counter 0 7556158 Purge done for trx's n:o 0 2341232 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root 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 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second -- -- BUFFER POOL AND MEMORY -- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s END OF INNODB MONITOR OUTPUT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb defragmentation question
the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of show table status and show innodb status for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | users_groups| InnoDB | Fixed | 0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic| 7 | 2340 | 16384 |NULL |16384 | 0 | 8 | NULL| NULL| NULL || InnoDB free: 8192 kB And the show innodb status: = 031231 9:31:03 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 TRANSACTIONS Trx id counter 0 7556158 Purge done for trx's n:o 0 2341232 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root 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 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second -- -- BUFFER POOL AND MEMORY -- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s END OF INNODB MONITOR OUTPUT F. Heikki Tuuri wrote: Franky, since MySQL performs ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; wait some 70 sec here DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata
Re: innodb defragmentation question
Franky, since MySQL performs ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; wait some 70 sec here DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata files, and can be used for undo logs of other transactions, but the space is not freed to the OS. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ... List:MySQL General Discussion« Previous MessageNext Message » From:Franky Van LiedekerkeDate:December 30 2003 3:00pm Subject:innodb defragmentation question since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: Hi all, if I try to defrag an InnoDB table (using alter table ... type=innodb;) I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB size against MyISAM size
On Mon, Dec 22, 2003 at 04:57:00PM +0200, Eli Hen wrote: Hello, My HDD is running low and I MyISAM tables are keep crashing... Are the MyISAM tables crashing *because* you're low on space? If so, you need more space, *not* a new table type. I think that converting to InnoDB will be more stable, but what about the data files sizes? convertion to InnoDB will need more or less disk space than MyISAM? More space. InnoDB has a larger per-row overhead than MyISAM tables do. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, Assertion failure in file log0log.c with 4.0.!7
j,
how you have set
innodb_thread_concurrency
and
innodb_log_file_size
in my.cnf?
I was able to repeat the assertion failure by setting the log file size to
only 8 MB, and setting concurrency to 500. I changed now InnoDB so that it
no longer asserts, but calls exit(1). It prints the following instructions
if log files are too small for the concurrency:
if (!success) {
fprintf(stderr,
InnoDB: Error: ib_logfiles are too small for innodb_thread_concurrency
%lu.\n
InnoDB: The combined size of ib_logfiles should be bigger than\n
InnoDB: 200 kB * innodb_thread_concurrency.\n
InnoDB: To get mysqld to start up, set innodb_thread_concurrency in
my.cnf\n
InnoDB: to a lower value, for example, to 8. After an ERROR-FREE
shutdown\n
InnoDB: of mysqld you can adjust the size of ib_logfiles, as explained
in\n
InnoDB: section 5 of http://www.innodb.com/ibman.php;,
(ulong)srv_thread_concurrency);
fprintf(stderr,
InnoDB: Cannot continue operation. Calling exit(1).\n);
exit(1);
}
Merry Christmas!
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: j.random.programmer [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, December 23, 2003 5:49 AM
Subject: BUG: Innodb, Assertion failure in file log0log.c with 4.0.!7
There is a MySQl/Innodb bug with Mysql-Max 4.0.17
on mac osx 10.3 panther. A google search showed
a similar problem with another platform (I think
it was windows).
I am running 4.0.14 without any problems. After
upgrading to 4.0.17, copying the data directory
from 4.0.14 to 4.0.17 and then starting mysqld
I get:
---
031222 22:05:51 mysqld started
InnoDB: Error: log file group too small for
innodb_thread_concurrency
031222 22:05:51 InnoDB: Assertion failure in thread
2684396012 in file log0log.c line 856
InnoDB: Failing assertion: log_calc_max_ages()
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to
[EMAIL PROTECTED]
mysqld got signal 10;
This could be because you hit a bug. It is also
possible that this binary
or one of the libraries it was linked against is
corrupt, improperly built,
or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will
hopefully help diagnose
the problem, but since we have already crashed,
something is definitely wrong
and this may fail.
-
Reverting back to 4.0.14 works fine and I
get no error messages.
Best regards,
--j
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB file data size against MyISAM files data sizes
In my experience, I see about a 2x increase in space required between MyISAM and InnoDB. I believe this may be documented btw, check the InnoDB section of the manual. I have been using InnoDB for a couple years now on databases up to 180GB. InnoDB has been very robust and I have only once come across a corruption after a crash of the DB or the operating system (linux). Richard On Mon, 2003-12-22 at 09:48, MySQL Support wrote: Hello, My HDD is running low and I MyISAM tables are keep crashing... I think that converting to InnoDB will be more stable, but what about the data files sizes? convertion to InnoDB will need more or less disk space than MyISAM? -thanks, Eli -- Richard F. Rebel [EMAIL PROTECTED] t. 212.239. signature.asc Description: This is a digitally signed message part
Re: innodb questions (backup and transactions)
Russ, you can also use mysqldump --single-transaction to back up InnoDB type tables. The advantage of InnoDB Hot Backup over that method is that InnoDB Hot Backup takes binary backups of the ibdata files. Restoring a binary backup is much faster than a table dump. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Russ [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 22, 2003 1:22 PM Subject: innodb questions (backup and transactions) Hey Guys, I have just a couple of questions regarding the innodb database type that I'd appreciate any advice with. I'm currently developing a custom shopping cart / backend and, due to the nature of such a system, would like to take advantage of transactions - so innodb seems the logical choice. However, I have a couple of reservations... I realise innodb does not have fulltext support built in - is the only way round this to have any data that needs to be fulltext-searched in a myISAM table? I've read that a ROLLBACK after a transaction will not rollback the myISAM tables - so I guess some app logic is required for these special cases. Is my thinking right on this issue? And secondly, I'm a bit worried about backing up innodb tables - more specifically a live backup. I've seen a product innodb sell specifically to do this but I can't really justify the licensing costs. Are there any other options for a live backup? The only others means of backup would be to shutdown the server and copy the data manually I assume? Any pointers to the above two queries greatly appreciated! Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB lock in share mode problems
Andrew,
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
[WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
I think the clause LOCK IN SHARE MODE has to be the very last.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
..
Search Result 25
From: Andrew Kennard ([EMAIL PROTECTED])
Subject: InnoDB lock in share mode problems
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.mysql
Date: 2003-12-15 06:42:12 PST
Hi all
I'm new tot PHP/mySQL but have many years programming experience.
We've just setup a new webspace account with Demon Internet in the UK and
it has PHP/mySQL services
I'm just testing the InnoDB an am having a problem with the following
statement
SELECT * FROM invtest LOCK IN SHARE MODE
Which gives the following error:-
Error
SQL-query :
SELECT *
FROM invtest
LOCK IN SHARE MODE LIMIT 0 , 30
MySQL said:
You have an error in your SQL syntax. Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'LIMIT 0,
30' at line 1
I can't seem to find anything about this limit clause/parameter in any of
the help files. Demon say 'Dunno mate we just provide the service !!!'
Any help with this would be most appreciated as I really need 'proper'
transactions for the project I'm about to start.
Thanks in advance
Andrew Kennard
PS the mySQL version they are running seems to be 4.0.9-gamma-log
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb multiple tablespaces benchmark
Carlos,
- Original Message -
From: Carlos Proal [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 12, 2003 4:09 AM
Subject: Innodb multiple tablespaces benchmark
Hi all, specially to Heikki.
Its really amazing that multiple tablespaces are available before 2004,
congratulations to Innodb Oy Inc.
Right now im migrating from 4.1.0 to 4.1.1 but im figuring out if there is
a
downgrade in performance in order to use multiple tablespaces, obviously
it
must be one because handling several files adds an overhead but it
something
to worry about ?
a customer has been running benchmarks where he compared multiple
tablespaces (each table in its own .ibd file) to the traditional single big
ibdata file. He said the performance was about the same.
I thougt that these tablespaces would be for each database and not for
each
table, this is possible ?, factible ?, useful ?, its scheduled ?.
It is not possible in 4.1.1. I will look at customer feedback and add a more
flexible tablespace concept if there is great demand. Most of the hard work
was already done in 4.1.1.
Thanx a lot and again some claps for innodb :)
Thank you :).
Carlos
Oops! Now I realize I have forgotten to document the following my.cnf option
in the manual:
{innodb_open_files, OPT_INNODB_OPEN_FILES,
How many files at the maximum InnoDB keeps open at the same time.,
(gptr*) innobase_open_files, (gptr*) innobase_open_files, 0,
GET_LONG, REQUIRED_ARG, 300L, 10L, ~0L, 0, 1L, 0},
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB as default table type
Harald Falkenberg [EMAIL PROTECTED] wrote: is it possible to use as a default INNODB instead of ISAM in a mysql server, so that every table, database (at least the complete server) is based on a INNODB tablespace? Is this a practical and good way to use mysql in that setup, if possible? Take a look at --default-table-type option of mysqld: http://www.mysql.com/doc/en/Command-line_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB as default table type
At 22:06 +0100 12/16/03, Harald Falkenberg wrote: Hallo, is it possible to use as a default INNODB instead of ISAM in a mysql server, so that every table, database (at least the complete server) is based on a INNODB tablespace? Is this a practical and good way to use mysql in that setup, if possible? No, at least not unless you disable the grant tables in the mysql database. Those tables must be MyISAM tables. You can set the default table type for new table using the --default-table-type, as Egor has already mentioned. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
InnoDB is extremely stable! I have a single InnoDB database that's currently holding about 20GB (with about 95% of that in a single table). All of this database is contained inside a single InnoDB tablespace file. In the last 12 months, the only command I've thrown at it by hand was ALTER TABLE messageblks TYPE = InnoDB, and only because I wanted to see how long it would take. Hope this helps! Regards, Chris Nicolas Ross wrote: Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
Hi, How are you doing backups??? Since it's replicated, I stop the slave and copy the entire database directory. Then, I restart the server and the slave will get all the queries from the master... Works very well! Eduardo - Original Message - From: Arnoldus Th.J. Koeleman [EMAIL PROTECTED] To: 'Eduardo D Piovesam' [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:38 PM Subject: RE: Innodb in production How are you doing backups??? -Original Message- From: Eduardo D Piovesam [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 10:31 AM To: Nicolas Ross; [EMAIL PROTECTED] Subject: Re: Innodb in production Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
On Mon, Dec 15, 2003 at 10:13:44AM -0500, Nicolas Ross wrote: Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. FYI, that restriction is gone in MySQL 4.1.x. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb transactions
Hi, I did some tests earlier where I inserted 100,000 rows into a table (table definition below). First, I did it without using transactions and it took 243 seconds approximately. Then, I did the same test using transactions, and it took 28 seconds. I am using MySQL v4. Here is the table definition: CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, num1 FLOAT(9,2) NOT NULL DEFAULT 0.0, num2 FLOAT(9,2) NOT NULL DEFAULT 0.0, ) TYPE=InnoDB; I guess my question is, how can this be? I was lead to believe that using transactions would slow things down but the opposite appears to be happening. Can anyone offer an explanation as to why it took so much longer to do the inserts when not using transactions ? Well, not using transactions might be an auto-started and committed transaction for each insert: 100.000 transactions instead of 1 (started by you). Either way, Heiko probably will comment as well :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB lock in share mode on union
Bill, I tested this on the latest 4.1.1 snapshot, and it worked ok: client 2 waited for client 1 to commit. Please test again. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html Client 1: mysql CREATE TABLE `table1` ( - `id` int(11) NOT NULL default '0', - PRIMARY KEY (`id`) - ) TYPE=InnoDB - - ; Query OK, 0 rows affected (0.10 sec) mysql CREATE TABLE `table2` ( - `id` int(11) NOT NULL default '0', - PRIMARY KEY (`id`) - ) TYPE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql set autocommit = 1; Query OK, 0 rows affected (0.00 sec) mysql insert into table2 values (2) - ; Query OK, 1 row affected (4.78 sec) mysql insert into table1 values (3); Query OK, 1 row affected (5.66 sec) mysql BEGIN; Query OK, 0 rows affected (0.00 sec) mysql (select id from table1 where id 1 lock in share mode) union (select - id from table2 where id 1); ++ | id | ++ | 3 | | 2 | ++ 2 rows in set (33.36 sec) mysql commit; Query OK, 0 rows affected (0.00 sec) mysql Client 2: mysql delete from table1 - where id=3; Query OK, 1 row affected (22.33 sec) mysql From: Bill Easton ([EMAIL PROTECTED]) Subject: InnoDB lock in share mode on union This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2003-11-10 07:05:16 PST How do I lock rows in a union query so that I know they won't change during the rest of my transaction? I want to do the following query, using LOCK IN SHARE MODE: (select id from table1 where id 1) union (select id from table2 where id 1); If I try: (select id from table1 where id 1 LOCK IN SHARE MODE) union (select id from table2 where id 1); it doesn't appear to do the locking. Another process can delete a record from table1 which was contained in the result. It appears that I see a consistent snapshot, and the record is gone after I commit. (See below.) I get the same result if I use FOR UPDATE. MySQL doesn't allow me to put LOCK IN SHARE MODE after the second select or after the whole union. I'm using MySQL 4.0.13-nt-log. -- Example follows In the following, table1 and table2 have the following structure: CREATE TABLE `table1` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB mysql begin; Query OK, 0 rows affected (0.00 sec) mysql (select id from table1 where id 1 LOCK IN SHARE MODE) union (select id from table2 where id 1); ++ | id | ++ | 3 | | 2 | ++ 2 rows in set (0.00 sec) *** another process does: delete from table1 where id=3; mysql (select id from table1 where id 1 lock in share mode) union (select id from table2 where id 1); ++ | id | ++ | 3 | | 2 | ++ 2 rows in set (0.01 sec) mysql commit; Query OK, 0 rows affected (0.00 sec) mysql (select id from table1 where id 1 lock in share mode) union (select id from table2 where id 1); ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB caused crash and left me a log entry...
Bruce,
I am not able to repeat the crash. I tested on Linux with 4.0.17.
You have the index
KEY `jiveForum_name_idx` (`name`(10)),
The bug is probably in the column prefix index. That feature was introduced
in 4.0.14. Did you create the table with a version 4.0.14? What kinds of
operations have you done with the table? Updates, deletes?
The failing assertion is the one below. InnoDB has determined that a
secondary index record is alphabetically equal to the new value (which is
understandable, because in your update the first 10 characters do not
change), but it turns out that the field length is NOT the same in the
updated value.
If I cannot repeat the crash, I will add diagnostic code to that place
anyway, so that we get more information of the bug. Also note that OS X is
not as well tested as Linux. There may be file corruption bugs in OS X.
Please send me a dump of the table for more testing, and also your my.cnf.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
/***
Builds an update vector from those fields which in a secondary index entry
differ from a record that has the equal ordering fields. NOTE: we compare
the fields as binary strings! */
upd_t*
row_upd_build_sec_rec_difference_binary(
/**/
/* out, own: update vector of differing
fields */
dict_index_t* index, /* in: index */
dtuple_t* entry, /* in: entry to insert */
rec_t* rec,/* in: secondary index record */
mem_heap_t* heap) /* in: memory heap from which allocated */
{
upd_field_t*upd_field;
dfield_t* dfield;
byte* data;
ulint len;
upd_t* update;
ulint n_diff;
ulint i;
/* This function is used only for a secondary index */
ut_ad(0 == (index-type DICT_CLUSTERED));
update = upd_create(dtuple_get_n_fields(entry), heap);
n_diff = 0;
for (i = 0; i dtuple_get_n_fields(entry); i++) {
data = rec_get_nth_field(rec, i, len);
dfield = dtuple_get_nth_field(entry, i);
ut_a(len == dfield_get_len(dfield));
- Original Message -
From: Bruce Dembecki [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, November 25, 2003 7:28 AM
Subject: InnoDB caused crash and left me a log entry...
InnoDB seems to have crashed on us, and put the errors below into the log
files... It took several crashes and some time but I was able to isolate
the
extremely simple query involved.
Server is OSX 10.3 running on a Dual 200MHZ G5 with 4Gigs ram. MySQL is
4.0.16.
The original queries to crash were a little longer in that they also
updated
the field description with a large block of text as well as the field
name. The following Queries repeatedly caused a crash when executed from
the mysql command line directly to mysqld with no other client connections
open:
update jiveForum set name='Technical Questions: SDK' where forumID=4;
update jiveForum set name=Technical Questions: SDK where forumID=4;
update jiveForum set name='Technical Questions - SDK' where forumID=4;
update jiveForum set name='Technical Questions' where forumID=4;
These queries did not cause the crash:
update jiveForum set name='Technical' where forumID=4;
update jiveForum set name='SDK Questions' where forumID=4;
I eventually moved the table out of InnoDB, made the changes, and moved it
back to InnoDB again without experiencing further problems. In order to
restore client services I did not try again to reproduce the problem,
instant Database server crash is not an attractive experience, especially
in
our production environment.
The table in question has 12 rows and looks like this:
CREATE TABLE `jiveForum` (
`forumID` bigint(20) NOT NULL default '0',
`name` varchar(255) binary NOT NULL default '',
`description` text,
`modDefaultThreadVal` bigint(20) NOT NULL default '0',
`modMinThreadVal` bigint(20) NOT NULL default '0',
`modDefaultMsgVal` bigint(20) NOT NULL default '0',
`modMinMsgVal` bigint(20) NOT NULL default '0',
`creationDate` varchar(15) NOT NULL default '',
`modifiedDate` varchar(15) NOT NULL default '',
`categoryID` bigint(20) NOT NULL default '1',
`categoryIndex` int(11) NOT NULL default '0',
PRIMARY KEY (`forumID`),
UNIQUE KEY `name` (`name`),
KEY `jiveForum_name_idx` (`name`(10)),
KEY `jiveForum_cat_idx` (`categoryID`),
KEY `jiveForum_catIndex_idx` (`categoryIndex`)
) TYPE=InnoDB
Below are the logs of the first two crashes and some
Re: InnoDB caused crash and left me a log entry...
Thanks Heikki, I'll send those along a little later today. I believe that
there is a second row in the table that has the name value Technical
Questions: API... If I am reading your comments correctly we are running
into a problem where the Key is limited to the first ten characters and
therefore despite the name having different values the first 10 characters
would be the same, thus causing the confusion with the key.
The table was designed pre 4.0.14, this particular one was likely deployed
originally under 4.0.13. Essentially we have about 40 deployments of
databases with identical structure for different clients, about half of them
for the same client just with instances for different countries and
purposes... So this is something we want to understand carefully.
As for operations to the table... This particular table doesn't see a lot of
changes. We run discussion boards, and this table holds the top level
listings for all the boards, or the list of Forums as we call it. Within
each forum there are multiple threads and messages, which have their own
tables, most of our table changes occur here. It's quite likely that once a
product is launched the Forums table doesn't get changed for months at a
time. That said I routinely (aka monthly) run a script which walks through
all our databases and tables and empties our InnoDB file space by
sequentially doing ALTER TABLE TYPE=myisam. Once completed the script
walks through again and turns them back into InnoDB. The intent here is just
to clean up the file space, rebuild the indexes etc... This procedure
typically increases our free space in the InnoDB file space and improves
performance significantly. So despite the fact the data stored in the table
hasn't changed since this database was launched, the table has been swapped
between InnoDB and MyISAM on a monthly basis.
Last night we were changing the data structure as the client was changing
the format of their discussion boards... It's a rare thing, but it happens.
Anyway I'll send you the table dump under separate cover later this morning.
Already your explanation makes sense given what I know about the other
record having a similar value.
Best Regards, Bruce
On 11/25/03 1:29 AM, Heikki Tuuri [EMAIL PROTECTED] wrote:
Bruce,
I am not able to repeat the crash. I tested on Linux with 4.0.17.
You have the index
KEY `jiveForum_name_idx` (`name`(10)),
The bug is probably in the column prefix index. That feature was introduced
in 4.0.14. Did you create the table with a version 4.0.14? What kinds of
operations have you done with the table? Updates, deletes?
The failing assertion is the one below. InnoDB has determined that a
secondary index record is alphabetically equal to the new value (which is
understandable, because in your update the first 10 characters do not
change), but it turns out that the field length is NOT the same in the
updated value.
If I cannot repeat the crash, I will add diagnostic code to that place
anyway, so that we get more information of the bug. Also note that OS X is
not as well tested as Linux. There may be file corruption bugs in OS X.
Please send me a dump of the table for more testing, and also your my.cnf.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
/***
Builds an update vector from those fields which in a secondary index entry
differ from a record that has the equal ordering fields. NOTE: we compare
the fields as binary strings! */
upd_t*
row_upd_build_sec_rec_difference_binary(
/**/
/* out, own: update vector of differing
fields */
dict_index_t* index, /* in: index */
dtuple_t* entry, /* in: entry to insert */
rec_t* rec,/* in: secondary index record */
mem_heap_t* heap) /* in: memory heap from which allocated */
{
upd_field_t*upd_field;
dfield_t* dfield;
byte* data;
ulint len;
upd_t* update;
ulint n_diff;
ulint i;
/* This function is used only for a secondary index */
ut_ad(0 == (index-type DICT_CLUSTERED));
update = upd_create(dtuple_get_n_fields(entry), heap);
n_diff = 0;
for (i = 0; i dtuple_get_n_fields(entry); i++) {
data = rec_get_nth_field(rec, i, len);
dfield = dtuple_get_nth_field(entry, i);
ut_a(len == dfield_get_len(dfield));
- Original Message -
From: Bruce Dembecki [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, November 25, 2003 7:28
Re: InnoDB on master, MyISAM on slaves?
On Fri, Nov 21, 2003 at 01:15:52PM -0700, Matt Sturtz wrote: Thanks for the quick reply-- Yes, this is a common strategy, actually. Any tricks to getting the tables converted on the master without the slaves knowing about it (IE how can I do 'alter table' on the master without it being executed on the slaves)? Sure: http://www.mysql.com/doc/en/SET_SQL_LOG_BIN.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 70 days, processed 2,743,752,365 queries (452/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on master, MyISAM on slaves?
At 11:36 -0700 11/21/03, Matt Sturtz wrote: Hello, We run a master-slave configuration and are considering migrating a few tables to InnoDB to get transaction capability... Question is, can we keep the tables as MyISAM on the slaves to maintain the high-speed accesses? I suppose this would require the SQL not being written to the bin-log until it's committed, and we would need to convert the tables on the master without the slaves knowing about it... Anybody doing anything like this? Or am I simply barking up the wrong tree? Yes, this is a common strategy, actually. Transactions are not written to the binary log on the master until they are committed, so that's already taken care of. (Rolled back transactions never appear in the binary log.) -Matt- -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on master, MyISAM on slaves?
Thanks for the quick reply-- Yes, this is a common strategy, actually. Any tricks to getting the tables converted on the master without the slaves knowing about it (IE how can I do 'alter table' on the master without it being executed on the slaves)? Transactions are not written to the binary log on the master until they are committed, so that's already taken care of. (Rolled back transactions never appear in the binary log.) -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Set-up
Mulugeta Maru [EMAIL PROTECTED] wrote: My background is Micorosoft Access and SQL server. The InnoDB table type gives me a much close option to move my databases. I found the database a little complex and would like to know if there is a better step-by-step explanation of setting up InnoDB in MySQL 4.1 on a Windows 2000 machine. By default in v4.1 MySQL creates auto-extending file in the data dir (ibdata1). You can also set InnoDB startup options manually. Check the following link: http://www.mysql.com/doc/en/InnoDB_start.html You can find examples of my.cnf file and list of InnoDB parametrs with descriptions there. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb-errors
Margrit,
fix the problem that leads to OS error number 17.
What does perror tell you about it?
Regards,
Frank.
Margrit Lottmann schrieb:
Following errors we've got by restarting mysqld
031114 10:26:51 mysqld started
InnoDB: Fatal error: cannot read from file. OS error number 17.
031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1329
--cut
How can we resolve our problems???
--
Mit freundlichen Gruessen
M.Lottmann
Otto - von - Guericke Universitaet __ __ _ _ __
Magdeburg / / / / / __ \__ // | / /
/ / / / / /_/ / / / __ / |/ /
Margrit Lottmann/ /_/ / / _, _/ / /___// /| /
Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/
Netze Kommunikation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. Frank Ullrich, DBA Netzwerkadministration
Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625
Hannover
E-Mail: [EMAIL PROTECTED]
Phone: +49 511 5352 587; FAX: +49 511 5352 538
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb-errors
Margrit,
your disk or file system is probably broken:
InnoDB: Fatal error: cannot read from file. OS error number 17.
17 EEXIST File exists
The error number does not make sense in a file read. Strange.
You cannot remove ib_logfiles from an InnoDB installation. They are as
important as ibdata files.
You can look at http://www.innodb.com/ibman.html#Forcing_recovery about
emergency recovery using a recent MySQL-4.0 version.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: Margrit Lottmann [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, November 14, 2003 3:27 PM
Subject: innodb-errors
Following errors we've got by restarting mysqld
031114 10:26:51 mysqld started
InnoDB: Fatal error: cannot read from file. OS error number 17.
031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file
os0file.c line 1329
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x83a9fb0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfffd058, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80df7ea
0x40035f05
0x82ac20c
0x82acc41
0x825be18
0x820e66c
0x816fb00
0x813d58a
0x8138000
0x80e05f3
0x42017589
0x80a0da1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at (nil) is invalid pointer
thd-thread_id=1667594851
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
031114 10:26:56 mysqld ended
We have done recovery by using ibdata1 file from current update (by
removing logfiles
...mysqld startet ok ...working for recovery ...but stopped if any select
request to an InnoDB table was executed.
Following trace-results we've got by using resove-tool...
0x80df7ea handle_segfault + 398
0x40035f05 _end + 935903133
0x81b5384 row_search_for_mysql + 5856
0x813f6e8 index_read__11ha_innobasePcPCcUi16ha_rkey_function + 644
0x8142c0f index_first__11ha_innobasePc + 35
0x8142d09 rnd_next__11ha_innobasePc + 41
0x8135704 rr_sequential__FP14st_read_record + 144
0x811311b join_init_read_record__FP13st_join_table + 75
0x810d346 sub_select__FP4JOINP13st_join_tableb + 78
0x810d101 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 393
0x81060bb
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP1
3select_result + 8683
0x81128cc handle_select__FP3THDP6st_lexP13select_result + 92
0x80ea8f2 mysql_execute_command__Fv + 978
0x80ee2bd mysql_parse__FP3THDPcUi + 349
0x80e9c68 dispatch_command__F19enum_server_commandP3THDPcUi + 1244
0x80ef85d do_command__FP3THD + 149
0x80e90e0 handle_one_connection + 584
0x40032faf _end + 935891015
0x420e790a _end + 970185122
How can we resolve our problems???
--
Mit freundlichen Gruessen
M.Lottmann
Otto - von - Guericke Universitaet __ __ _ _
__
Magdeburg / / / / / __ \__ // | /
/
/ / / / / /_/ / / / __ / |/
/
Margrit Lottmann/ /_/ / / _, _/ / /___// /| /
Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/
Netze Kommunikation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Re: Innodb: Can't init databases
Hi Hsiu-Hui, I havent followed the threat, excuse if this was mentioned before. Did you try to start the server with the --skip-innodb option yet ? -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ On Thursday 13 November 2003 17:38, Hsiu-Hui Tseng wrote: Hi, I removed idbdata and iblogs and recreated idbdata and iblogs. But I keeps getting Can't init databases. I still have a lot of disk space. Could any one please tell me what's wrong? Here are message from error log InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files because InnoDB: data files are corrupt, or new data files were InnoDB: created when the database was started previous InnoDB: time but the database was not shut down InnoDB: normally after that. 031112 23:44:05 Can't init databases 031112 23:44:05 Aborting Thank you for your help! Hsiu-Hui --- -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNODB flush holdup
Is there anything that can be done to increase the speed of a flush? Can it be run while other queries are running? The problem I'm having is that every time a flush is waiting to happen no new connections can be initiated and all my queries are on hold. If there is something long running before it everything comes to a halt for a good 30-40 seconds. I can't make these long queries any faster. What can I do? Thanks, Nihal -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:09 PM To: Nihal; [EMAIL PROTECTED] Subject: Re: INNODB flush holdup Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB flush holdup
On Thu, Nov 06, 2003 at 09:40:41AM -0700, Nihal wrote: Is there anything that can be done to increase the speed of a flush? Can it be run while other queries are running? Are you actually running the FLUSH TABLES command? If so, why? The problem I'm having is that every time a flush is waiting to happen no new connections can be initiated and all my queries are on hold. If there is something long running before it everything comes to a halt for a good 30-40 seconds. I can't make these long queries any faster. What can I do? Increase disk I/O capacity or add some caching on the disk controller. Jeremy -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:09 PM To: Nihal; [EMAIL PROTECTED] Subject: Re: INNODB flush holdup Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,004,112,227 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNODB flush holdup
I am running FLUSH TABLES manually. I am doing it each hour to generate a bin file, for incremental backups. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 10:05 AM To: Nihal Cc: [EMAIL PROTECTED] Subject: Re: INNODB flush holdup On Thu, Nov 06, 2003 at 09:40:41AM -0700, Nihal wrote: Is there anything that can be done to increase the speed of a flush? Can it be run while other queries are running? Are you actually running the FLUSH TABLES command? If so, why? The problem I'm having is that every time a flush is waiting to happen no new connections can be initiated and all my queries are on hold. If there is something long running before it everything comes to a halt for a good 30-40 seconds. I can't make these long queries any faster. What can I do? Increase disk I/O capacity or add some caching on the disk controller. Jeremy -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:09 PM To: Nihal; [EMAIL PROTECTED] Subject: Re: INNODB flush holdup Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,004,112,227 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
MySQL doesn't work. I tried to modified the line: innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata1:500M or innodb_data_file_path = ibdata1:500M:autoextend or innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend they all gave me the same error below. InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 031105 9:42:56 Can't init databases 031105 09:42:56 mysqld ended The last one really does the matter!! That's if I run out of the space on the current directory, I won't be able to put another file anywhere else!? Leo Nitin wrote: You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after
RE: InnoDB Questions
Hi, From my experience, once you have created a datafile it doesn't work when you try to make it larger. If you dont care about the data thats already in it then delete the current ibdata files and restart mysql. If you do then put the size back to how it was, dump the data, shutdown the server, change the cfg file and remove the ibdata file. At startup it should create the new larger file, then you can import the dumped data. Marvin. -Original Message- From: Leo Huang [mailto:[EMAIL PROTECTED] Sent: 05 November 2003 07:40 To: Nitin Cc: [EMAIL PROTECTED] Subject: Re: InnoDB Questions MySQL doesn't work. I tried to modified the line: innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata1:500M or innodb_data_file_path = ibdata1:500M:autoextend or innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend they all gave me the same error below. InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 031105 9:42:56 Can't init databases 031105 09:42:56 mysqld ended The last one really does the matter!! That's if I run out of the space on the current directory, I won't be able to put another file anywhere else!? Leo Nitin wrote: You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I
Re: InnoDB and raw tablespace
No, files can be bigger than 2GB. In OSX prior to Panther there is a 2GB per-process memory limit though. Then again, on anything other than the PowerMac G5 this doesn't matter because the G5 is the only Mac that can hold more than 2GB of RAM. - Gabriel On Tuesday, November 4, 2003, at 04:42 PM, Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
On Wed, Nov 05, 2003 at 12:08:29PM +1100, Chris Nolan wrote: To my knowledge, ext2 does have the [2GB filesize] limitation but ext3 does not. ext2 does not have this limitation. It was never a limitation of the filesystem, only kernel/glibc. On 64bit architectures ext2 has been handling large files for the past eight(?) years. On 32 bit architectures the kernel and libc have been handling large files on ext2 for at least two years. I hate to keep posting the same thing to this list, but I keep seeing the same misinformation that ext2 can't handle large files. It can. Cheers, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
If I recall correctly, the G5, the mighty PowerPC 970, is used by Apple just as Windows currently uses the mighty Hammer series from AMD - as a souped up 32-bit processor. Regards, Chris Gabriel Ricard wrote: No, files can be bigger than 2GB. In OSX prior to Panther there is a 2GB per-process memory limit though. Then again, on anything other than the PowerMac G5 this doesn't matter because the G5 is the only Mac that can hold more than 2GB of RAM. - Gabriel On Tuesday, November 4, 2003, at 04:42 PM, Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
How about we just all agree that SCO's OSes can't handle large files, and therefore should all be avoided in favour of completely superior OSes, like FreeBSD, OpenBSD, Linux, NetBSD and DOS 2.11 Regards, Chris Pete Harlan wrote: On Wed, Nov 05, 2003 at 12:08:29PM +1100, Chris Nolan wrote: To my knowledge, ext2 does have the [2GB filesize] limitation but ext3 does not. ext2 does not have this limitation. It was never a limitation of the filesystem, only kernel/glibc. On 64bit architectures ext2 has been handling large files for the past eight(?) years. On 32 bit architectures the kernel and libc have been handling large files on ext2 for at least two years. I hate to keep posting the same thing to this list, but I keep seeing the same misinformation that ext2 can't handle large files. It can. Cheers, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB flush holdup
Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: PGP 8.0.2 iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN h21IQZ8ozOUeELhvWSpznyTI =H/2E -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: PGP 8.0.2 iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN h21IQZ8ozOUeELhvWSpznyTI =H/2E
Re: InnoDB and raw tablespace
On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
On Tuesday, November 4, 2003, at 11:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt [EMAIL PROTECTED] writes: I'm considering using the raw tablespace from InnoDB for a project I'm working on. I noticed a couple of years ago that there were reports of tablespace corruption on Linux and these raw tablespaces. Have these problems been fixed? Yes. I'm using a raw disk for some months now, without any problems. However, I've heard that it doesn't give the performance improvement I'd expected. Try it yourself. I don't have specific numbers in front of me right now, but I tested the raw performance of InnoDB on a G5 running OSX 10.3 and it was actually worse than using regular files. I'll see if I can dig up the specific numbers. - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
On Tuesday, November 4, 2003, at 07:58 AM, Leo Huang wrote: In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? How exactly does this increase performance? Will InnoDB store some data in one data file and some in another (somewhat like RAID 1)? - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? No, OS X has a file size limit of 2 TB (prior to 10.2), 8 TB (10.2.x) or 16 TB (10.3). http://docs.info.apple.com/article.html?artnum=25557 --Ware Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
I don't know. I will get some time this week, shutdown MySQL, backup my binary files, have a go as what Nitin said and see what's going on there. Leo Gabriel Ricard wrote: On Tuesday, November 4, 2003, at 07:58 AM, Leo Huang wrote: In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? How exactly does this increase performance? Will InnoDB store some data in one data file and some in another (somewhat like RAID 1)? - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
On Tue, Nov 04, 2003 at 01:42:23AM -0600, Mark Lubratt wrote: I'm considering using the raw tablespace from InnoDB for a project I'm working on. I noticed a couple of years ago that there were reports of tablespace corruption on Linux and these raw tablespaces. Have these problems been fixed? I'm considering running it on a hardware RAID (stripes of mirrors, I forget if that's RAID 10, or RAID 01). Should I use FreeBSD instead of Linux? I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Any thoughts? I usually tell people to think twice about using raw disks for two main reasons: 1. Performance. I've not seen anybody report a significant performance boost doing this. 2. Transparency. It's nice to be able to use a wider variety of tools to examine, copy, back up, and otherwise tinker with data. By using a raw disk, you lose most of this. However, if the performance gain is really there, maybe it's more important than #2. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,631,314 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
The last one you're referring to - could it be the error log? The log files will only grow to a pre-determined limit. These log files are used to ensure that transactions maintain their durability. With Oracle, you'd want to be careful. Oracle gets very, very picky about the stuff underneath it when it's running. If it gets to a configuration limit, the results can be very, very depressing. Having to extend tablespaces by hand is a very common Oracle DBA task. Regards, Chris Leo Huang wrote: Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
To my knowledge, ext2 does have the limitation but ext3 does not. Additionally, ReiserFS, JFS and XFS all have disgustingly large file size limits. As a side note, apparently NetWare has major file size limitations (going on Gupta's SQLBase documentation) Regards, Chris Mark Lubratt wrote: No, I'm thinking about ext2 on Linux. Which I'm pretty sure has a 2GB limit. Ext3 has the same limitation. Both filesystems will support larger file sizes if the kernel is configured with Large Filesystem Support (LFS). The last time I heard, this is still not fully implemented (at least enough to trust to something like this...) I could certainly be wrong on the LFS status. If so, please let me know, I'm running RH9. Mark On Tuesday, November 4, 2003, at 03:42 PM, Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
not all the log files will work together, i mean, these files work in cyclic fashion, one fills up, second one gets used, it fills up, second one, and then back to first one. so it wont keep getting bigger, it will overwrite the previous data when it comes back to that file. in oracle, if you have data more than the size you're specifying, it'll through error and wont do nothing. Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:12 AM Subject: Re: InnoDB Questions Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
yea, he's right, it may be error log file Nitin - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:53 AM Subject: Re: InnoDB Questions The last one you're referring to - could it be the error log? The log files will only grow to a pre-determined limit. These log files are used to ensure that transactions maintain their durability. With Oracle, you'd want to be careful. Oracle gets very, very picky about the stuff underneath it when it's running. If it gets to a configuration limit, the results can be very, very depressing. Having to extend tablespaces by hand is a very common Oracle DBA task. Regards, Chris Leo Huang wrote: Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)
Gabriel, - Original Message - From: Gabriel Ricard [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 27, 2003 6:46 PM Subject: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1) On Monday, October 27, 2003, at 07:45 AM, Chris Nolan wrote: 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Just out of curiosity, has anyone been able to get InnoDB to use a raw partition in OSX? When I tried it, it complained about the file already existing. did you add the newraw keyword? http://www.innodb.com/ibman.html#Disk_io_and_raw_devices 12.1 Disk i/o and raw devices Starting from 3.23.41, you can also use a raw disk partition (a raw device) as a data file. When you create a new data file you have to put the keyword newraw immediately after the data file size in innodb_data_file_path. The partition must be equal to or larger than the size you specify. Note that 1M in InnoDB is 1024 x 1024 bytes, while in disk specifications 1 MB usually means 1000 000 bytes. innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw When you start the database again you MUST change the keyword to raw. Otherwise InnoDB will write over your partition! Starting from 3.23.44, as a safety measure InnoDB prevents a user from modifying data when any partition with newraw is specified. After you have added a new partition, shut down the database, edit my.cnf replacing newraw with raw, and restart. innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw By using a raw disk you can on Windows and on some Unixes perform non-buffered i/o. In Windows raw disk i/o, starting from 4.1.1, you can allocate a disk partition as a data file like this: innodb_data_home_dir= innodb_data_file_path=//./D::10Gnewraw - Gabriel Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB errors
Shane, it is not an InnoDB error message. InnoDB messages are always prefixed with InnoDB: The error means that there is an inconsistent row in the db.MYD system table of MySQL. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables - Original Message - From: Shane Allen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 24, 2003 9:22 PM Subject: InnoDB errors I've searched the archives and was unable to find anything that seemed pertinent. Earlier today I had to stop and start mysql. When I issued mysqladmin shutdown, however, the process did not die cleanly (reason unknown), and I ended up having to kill -9 mysqld and mysqld_safe. When the DB came back up, I now have the following in my log: 031024 11:06:04 mysqld started 031024 11:06:05 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 697473956 InnoDB: Doing recovery: scanned up to log sequence number 0 697473956 031024 11:06:06 InnoDB: Flushing modified pages from the buffer pool... 031024 11:06:06 InnoDB: Started 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped ... 031024 11:07:53 mysqld started 031024 11:07:54 InnoDB: Started 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped as you can see, on the second restart, I did not get warnings about having to recover the tables, but I still get the 'empty database name' errors. How can I determine what the cause of this error is an get it fixed? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. 2. Price When looking at commercial licensing, it costs more to buy a version that includes InnoDB. Hope this helps! Regards, Chris On Fri, 24 Oct 2003 01:50 am, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
In the last episode (Oct 24), Chris Nolan said: The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. MyISAM also lets you put indexes and tables onto separate disks for more performance, and supports a compressed read-only format. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
At 02:37 PM 10/23/2003, you wrote: I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis Travis, Correct. Innodb DOES NOT support full text indexes. Someday perhaps, but not now. :-( Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi Heikki, here is a snip from the original posting which you probably have overlooked: The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Mon, 20 Oct 2003, Heikki Tuuri wrote: Date: Mon, 20 Oct 2003 22:05:07 +0300 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: InnoDB or OS restriction? Alex, in FreeBSD user process memory space is often restricted to 512 MB. You have to reconfigure or recompile the FreeBSD kernel to increase that limit. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB: now also backs up your MyISAM tables - Original Message - From: alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 20, 2003 1:30 PM Subject: Re: InnoDB or OS restriction? Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Alex, because 512 MB is not an InnoDB or MySQL restriction, it must be an OS restriction :). I assume you have not allocated 1.5 GB of MySQL key_buffer. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables - Alkuperäinen viesti - Lähettäjä: Varshavchick Alexander [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Tuesday, October 21, 2003 2:55 PM Aihe: Re: InnoDB or OS restriction? Hi Heikki, here is a snip from the original posting which you probably have overlooked: The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Mon, 20 Oct 2003, Heikki Tuuri wrote: Date: Mon, 20 Oct 2003 22:05:07 +0300 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: InnoDB or OS restriction? Alex, in FreeBSD user process memory space is often restricted to 512 MB. You have to reconfigure or recompile the FreeBSD kernel to increase that limit. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB: now also backs up your MyISAM tables - Original Message - From: alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 20, 2003 1:30 PM Subject: Re: InnoDB or OS restriction? Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi Alex, What user is the mysql daemon running as? Are there any limits on that user or user class (/etc/login.conf). Just because you have raised the kernel limits does not mean they are raised for the user. Can you login as that user and type limit and get something like - %limit cputime unlimited filesizeunlimited datasize1581056 kbytes stacksize 524288 kbytes coredumpsizeunlimited memoryuse unlimited vmemoryuse unlimited descriptors 19000 memorylockedunlimited maxproc 5547 sbsize unlimited % Hope this helps, Ken - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 9:54 AM Subject: Re: InnoDB or OS restriction? Alex, because 512 MB is not an InnoDB or MySQL restriction, it must be an OS restriction :). I assume you have not allocated 1.5 GB of MySQL key_buffer. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables - Alkuperäinen viesti - Lähettäjä: Varshavchick Alexander [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Tuesday, October 21, 2003 2:55 PM Aihe: Re: InnoDB or OS restriction? Hi Heikki, here is a snip from the original posting which you probably have overlooked: The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Mon, 20 Oct 2003, Heikki Tuuri wrote: Date: Mon, 20 Oct 2003 22:05:07 +0300 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: InnoDB or OS restriction? Alex, in FreeBSD user process memory space is often restricted to 512 MB. You have to reconfigure or recompile the FreeBSD kernel to increase that limit. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB: now also backs up your MyISAM tables - Original Message - From: alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 20, 2003 1:30 PM Subject: Re: InnoDB or OS restriction? Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction? - sorry for duplicated posts
I'm very sorry for the duplicated posts, my mail softtware behaved wrong :( --- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Alex, in FreeBSD user process memory space is often restricted to 512 MB. You have to reconfigure or recompile the FreeBSD kernel to increase that limit. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB: now also backs up your MyISAM tables - Original Message - From: alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 20, 2003 1:30 PM Subject: Re: InnoDB or OS restriction? Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB vs MyISAM
On Thu, Oct 09, 2003 at 01:23:39PM -0400, Don Vu wrote: Hi guys, Do both MyISAM tables and INNODB tables support foreign keys in 4.0.15? No. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 25 days, processed 959,424,306 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB vs MyISAM
At 12:23 PM 10/9/2003, you wrote: Hi guys, Do both MyISAM tables and INNODB tables support foreign keys in 4.0.15? If so, are the main advantages of using INNODB tables the added features of transactions, cascading deletes, and it's other more robust features? Any thoughts on any disadvantages of INNODB to MyISAM? I know that INSERTS, UPDATES and DELETES will be slower with INNODB cuz of the transaction overhead, but do you think the SELECT speed is about the same? any feedback appreciated. thanks, Don Don, For my tables the select speed with InnoDb is about 10x slower than MyISAM. I didn't do any tuning but my feeling is if the table is readonly, stick with MyISAM. If you have a lot of updates to the table, then InnoDb is recommended. You can of course this with your own table structure to see how well it performs. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb won't recognize index when optimizing query
Hi Heath, MySQL cannot use the trans_team query because you're using !=, for which an index is never used (currently anyway). Do you think that trans_team is the best index that will find the least rows and produce the fastest result? If so, you can try using the following, which can be optimized, instead of != ... AND (transfer_logs.trans_team 'team oscar' OR transfer_logs.trans_team 'team oscar') I think that's the same as !=. :-) But MySQL will only use the index if it will find few enough rows ( ~30%) -- in other words, if more than ~2/3 of the trans_team values ARE 'team oscar'. Hope that helps. Matt - Original Message - From: heath boutwell Sent: Wednesday, October 08, 2003 3:15 PM Subject: Innodb won't recognize index when optimizing query The query optimizer will not recognize an index on an innodb table. tranfer_logs is an innodb table, auth_user is not. As demonstrated below, trans_team is not even recognized as a possible key when EXPLAIN SELECT is used. (my apologies for the extra wide message). I am not sure if this is an innodb issue or just an ignorant user(me) issue. mysql show keys from transfer_logs; +---+++--+-+ ---+-+--++ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | +---+++--+-+ ---+-+--++ | transfer_logs | 0 | PRIMARY|1 | trans_id | A | 3573681 | NULL | NULL | | transfer_logs | 1 | trans_time |1 | trans_time | A | 3573681 | NULL | NULL | | transfer_logs | 1 | user_id|1 | user_id | A | 11872 | NULL | NULL | | transfer_logs | 1 | event_id |1 | event_id | A | 108293 | NULL | NULL | | transfer_logs | 1 | trans_team |1 | trans_team | A | 1786840 | NULL | NULL | | transfer_logs | 1 | trans_type |1 | trans_type | A | 21 | NULL | NULL | | transfer_logs | 1 | trans_cat |1 | trans_cat | A | 21 | NULL | NULL | +---+++--+-+ ---+-+--++ mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees - FROM transfer_logs, auth_user - WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---+--+--+--+-- ---+---+--++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+--+--+--+-- ---+---+--++ | auth_user | ref | PRIMARY,user_ref | user_ref | 20 | const | 13 | where used | | transfer_logs | ref | trans_time,user_id,trans_cat | user_id | 32 | auth_user.user_id | 301 | where used | +---+--+--+--+-- ---+---+--++ As witnessed above, possible_keys doesn't even list trans_team as a possible index. I want to use the index on trans_team but when I try to force this via USE INDEX mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees -FROM transfer_logs USE INDEX(trans_team), auth_user -WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---++--+-+- +---+-++ | table | type | possible_keys| key | key_len | ref | rows| Extra | +---++--+-+- +---+-++ | transfer_logs | ALL| trans_time,user_id,trans_cat | NULL| NULL | NULL | 2036463 | where used | | auth_user | eq_ref | PRIMARY,user_ref | PRIMARY | 32 | transfer_logs.user_id | 1 | where used | +---++--+-+- +---+-++ 2 rows in set (0.00 sec) Any ideas on wny innodb won't recognize the index on trans_team? This query shouldn't take 1 minute+ but it does unless I
Re: InnoDB speed problems
Matthias, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or restart the mysqld server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, October 01, 2003 6:04 AM Subject: InnoDB speed problems Hi all, Because I want to use transactions in the future I have converted all tables of a copy of our production database server (1800+, 512 MB RAM, Linux) to InnoDB format. No problem until now. First, let me show you settings in my.cnf: key_buffer= 16M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency= 8 innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB? All these settings seem to be fine for me. With MyISAM I have used a key_buffer of 256M and sort_buffer_size of 4M which procuded very fast database accesses. mytop's output: MySQL on localhost (4.0.15-standard-log) up 0+23:14:39 [04:23:24] Queries: 5.7M qps: 72 Slow:34.0 Se/In/Up/De(%): 63/10/15/05 Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3% Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k But now everything is slow, I don't know why. Even without load each query takes a bit longer. Shouldn't it be vice versa? Then I did some load testing: CPU usage and system load raised by 100 percent. That's not normal for me, does InnoDB need more power, more momory? While testing MySQL was able to handle all the queries but, well, not as fast as I would like to have it in productive environment. I have also noticed that some more complex queries (select with 4 joins and 2 orders) last much too long. With MyISAM everything was 0.5s but now I sometimes have a strange one that is listed for several houndred seconds (?!) in the process list. That's not normal, isn't it? Something strange is going on here and I do not have a clue what I could be. Playing around with the settings and raising InnoDB's pool size to 80% of memory didn't change anything. So, I'm not familiar with this great InnoDB thing, maybe you have some ideas. :) Thanks in advance! Matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB speed problems
Heikki, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Does that speed up the thing? I should make some testing. Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or restart the mysqld server. Sure, I had several restarts while changing settings and testing again... Best regards Matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB / Linux
Hi, Its RedHat 7.3 with a 2.4 kernel. Yes the filesystem is ext3, but if your recommending a different one then I'm open to suggestions. This box is purely for mysql so anything that will benefit the database is best. As I said the disk size is not too much of a problem but its really the memory one, under a large amount of connections the 2Gb limit will be exceeded and I run the risk of crashing the mysql process, this is what I really want to get around. Best Regards, Marvin. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: 30 September 2003 00:07 To: [EMAIL PROTECTED] Subject: Re: InnoDB / Linux Marvin Wright said: Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright Depends on: your version of Linux, File system and processor. I believe that the basic 2 GB limit is gone in Linux 2.4 with ext2 or ext3 file system. You may have to turn on some flags or define an estimated table size to cause mysql to use large enough pointers, but I thought that InnoDB could use multiple extants each of which could be up to 2 GB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB / Linux
Marvin Wright said: Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright Depends on: your version of Linux, File system and processor. I believe that the basic 2 GB limit is gone in Linux 2.4 with ext2 or ext3 file system. You may have to turn on some flags or define an estimated table size to cause mysql to use large enough pointers, but I thought that InnoDB could use multiple extants each of which could be up to 2 GB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
# [EMAIL PROTECTED] / 2003-09-25 08:58:09 +0300: From: Roman Neuhauser [EMAIL PROTECTED] Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Having this, and other management features, in InnoDB would be a boon. InnoDB adds much complexity to MySQL, but the administrator doesn't have many tools to monitor its health. Many more parts that can break, but that are not visible to the administrator. What I've been missing most dearly from MySQL/InnoDB lately is documentation to the output of SHOW INNODB STATUS and friends. Section 9.1 of the InnoDB manual doesn't cut it. Also, don't be afraid to dive into finer details when describing the innards of InnoDB: I was trying to help myself before posting here, but found that section 12 just scratches the surface. All in all, thanks for InnoDB, and thanks for the support. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
Roman, - Alkuperäinen viesti - Lähettäjä: Roman Neuhauser [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 26, 2003 12:02 PM Aihe: Re: innodb: storage requirements # [EMAIL PROTECTED] / 2003-09-25 08:58:09 +0300: From: Roman Neuhauser [EMAIL PROTECTED] Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Having this, and other management features, in InnoDB would be a boon. InnoDB adds much complexity to MySQL, but the administrator doesn't have many tools to monitor its health. Many more parts that can break, but that are not visible to the administrator. What I've been missing most dearly from MySQL/InnoDB lately is documentation to the output of SHOW INNODB STATUS and friends. Section 9.1 of the InnoDB manual doesn't cut it. Also, don't be afraid to dive into finer details when describing the innards of InnoDB: I was trying to help myself before posting here, but found that section 12 just scratches the surface. All in all, thanks for InnoDB, and thanks for the support. it would be nice if academic people and others would publish more papers on the internals. The page http://www.innodb.com/books.html contains some links. The source code itself contains very long comments at the start of some .c files. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
Roman, - Original Message - From: Roman Neuhauser [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 11:46 PM Subject: Re: innodb: storage requirements # [EMAIL PROTECTED] / 2003-09-24 17:41:29 +0300: the row count in SHOW TABLE STATUS is only an estimate based on 8 dives into the index tree. ok. You had a typical symptom of a fragmented table: space usage much bigger than you would expect. that doesn't answer my questions :) notice the numbers: mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 225198 ^^ here, InnoDB thinks the table has 225198 rows. mysql ALTER TABLE editor_competence_product TYPE=InnoDB; Query OK, 187654 rows affected (47.34 sec) Records: 187654 Duplicates: 0 Warnings: 0 here, 187654 rows is reported. mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 187265 ^^ yet another number. what I don't understand is why the second and third number differ. ALTER TABLE reports an exact count. After the reorganization, also SHOW TABLE ... happens to report almost the right number. This is expected, because the reorganization packs pages full of records. If you insert rows ( 1000 bytes) sequentially in the order of the primary key, then actually SHOW TABLE STATUS is a good measure of fragmentation. If its estimate differs a lot from the real row count, that is a symptom of a f ragmented table. but this question is more important, and I'd really *love* to know an answer: Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb: CREATE INDEX
Yes, i did that. It's given me something like si 200/300 so 300/500 It's a lot, doing my system going down. But i think that the problem is that i'm reserving too much memory for mysql... Or could exists another reason? Thx Alexis Quoting Per Andreas Buer [EMAIL PROTECTED]: [EMAIL PROTECTED] writes: I'm creating indexes in a table with 16 million rows (it's a fact table), and it takes a lot of time (2/3/4 hours), because my system is always swapping in/out (i think). If you run vmstat 2 while the index is created you will see if swap is beeing used or not (see the si and so columns and man vmstat for futher information). -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
