Re: SPARC to x64 Transition
Aaron, I've currently got a project to migrate a LARGE (3.5TB) MySQL data set from a Sun SPARC machine to a Sun x86 machine, both running Solaris 10 (though obviously one is x86 and the other is SPARC). Is it possible to simply copy the data files from one host to the other or is a full mysqldump/import necessary to preserve data integrity? If a file copy doesn't work, why specificially would it fail? Thanks, -Aaron you can simply copy the files, whether InnoDB or MyISAM. As far as I know, all modern processors use the same floating point format. And all integer and other data structures are platform independent in MyISAM and InnoDB. Best regards, Heikki Innobase/Oracle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and RAW Device and autoextend question
Mariella, Mariella Petrini wrote: Hi All, I have been using MySQL 5.1.x with InnoDB and Raw Devices. [mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw ... [mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw Is there any way to specifiy the autoextend option (...::autoextend) when using Innodb with raw devices for the Shared Tablespace ? sorry, no. We think of the partition as a chunk of storage. It does not extend. Thanks in advance for your help, Mariella Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup stratergy
Juan, InnoDB Hot Backup is non-free. A 1-year license costs 390 euros + VAT, and a perpetual license 990 euros + VAT. http://www.innodb.com/order.php The Perl script innobackup can be used to make consistent backups of MyISAM tables also, but those backup require the locking of MyISAM tables, and are not in that sense 'hot' or 'online'. InnoDB Hot Backup works with all MySQL versions up to 5.1. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL ... Hi Juan, Thanks a lot for the quick reply. Any idea how much it would cost for ibbackup for innodb. Will mysql be providing this with any of their new release. regards anandkl On 2/23/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Foreign Key
Mungbeans, I do not understand how you get error 152 from the ALTER. ./include/my_base.h:355:#define HA_ERR_ROW_IS_REFERENCED 152 Please print SHOW INNODB STATUS\G after you get that error. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . From: Mungbeans Date: January 2 2007 12:56am Subject: Deleting Foreign Key Get Plain Text I have a foreign key in a table which I need to drop. I have successfully dropped other foreign keys from this table using phpMyAdmin. However when I use these commands I get similar error messages: ALTER TABLE `mytable` DROP FOREIGN KEY `keyname` #1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11' (errno: 152) ALTER TABLE `mytable` DROP INDEX `keyname` #1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable' (errno: 150) Any clues as to what I need to do to get rid of this constraint? I want to delete the entire table structure and replace it with a different one and this is the only thing stopping me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bizarre InnoDB Error Message ( trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED)
Jason,
I am Cc:ing the MySQL General mailing list, so that others who bump into
this bug can find this discussion.
Jason J. W. Williams wrote:
Mr. Tuuri,
We have a high degree of UPDATE/INSERT concurrency along with high
SELECTs. It causes a deadlock about once every 24 hours. In this case
a deadlock was associated with this event.
ha_innodb.cc in 5.0:
int
convert_error_code_to_mysql(
/**/
/* out: MySQL error code */
int error, /* in: InnoDB error code */
THD*thd)/* in: user thread handle or NULL */
{
if (error == DB_SUCCESS) {
return(0);
} else if (error == (int) DB_DUPLICATE_KEY) {
return(HA_ERR_FOUND_DUPP_KEY);
} else if (error == (int) DB_RECORD_NOT_FOUND) {
return(HA_ERR_NO_ACTIVE_RECORD);
} else if (error == (int) DB_ERROR) {
return(-1); /* unspecified error */
} else if (error == (int) DB_DEADLOCK) {
/* Since we rolled back the whole transaction, we must
tell it also to MySQL so that MySQL knows to empty the
cached binlog for this transaction */
if (thd) {
ha_rollback(thd);
}
return(HA_ERR_LOCK_DEADLOCK);
...
/*
Frees a possible InnoDB trx object associated with the current THD. */
static
int
innobase_close_connection(
/*==*/
/* out: 0 or error number */
THD*thd)/* in: handle to the MySQL thread of the user
whose resources should be free'd */
{
trx_t* trx;
trx = (trx_t*)thd-ha_data[innobase_hton.slot];
ut_a(trx);
if (trx-active_trans == 0
trx-conc_state != TRX_NOT_STARTED) {
sql_print_error(trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED);
}
if (trx-conc_state != TRX_NOT_STARTED
global_system_variables.log_warnings)
sql_print_warning(MySQL is closing a connection that has an
active
InnoDB transaction. %lu row modifications
will
roll back.,
(ulong)trx-undo_no.low);
innobase_rollback_trx(trx);
trx_free_for_mysql(trx);
return(0);
}
Hmm... I need to check that the auto-increment code in ha_innodb.cc sets
trx-active_trans correctly. I guess you have an auto-inc column in your
table?
The deadlock output from
SHOW INNODB STATUS was so long, that it was truncated the SHOW
INNODB STATUS information somewhere in the middle of the deadlocked
rows output. The current transactions setting was completely missing
due to the truncation. I don't have access to the my.cnf from where I
am now, but I will send it on Monday once I get access. Lastly, there
were no errors printed to the .err log prior to the errors I sent.
Thank you so much for writing back. I do truly appreciate it! It is
very relieving to know it is not dangerous.
Best Regards,
Jason
Regards,
Heikki
On 12/30/06, Heikki Tuuri [EMAIL PROTECTED] wrote:
Jason,
Jason J. W. Williams wrote:
Hello Mr. Tuuri,
I'm sorry to bother you directly about this. I have had very little
luck finding anything on this in the forums or on Google and was
hoping you could help me understand a strange error message I received
from InnoDB (5.0.27). Any help would be very much appreciated. Thank
you in advance!
Best Regards,
Jason
---ERROR MESSAGE---
061228 19:02:55 [ERROR] trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED
061228 19:02:55 [Warning] MySQL is closing a connection that has an
active InnoDB transaction. 0 row modifications will roll back.
the error itself does not sound dangerous.
But do you have an idea how you got this?
What is your my.cnf like?
Are there any other warnings or errors printed to the .err log prior to
this?
Best regards,
Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Leo, Leo Huang wrote: Heikki, Thanks for you help! I also read the comment in file of innodbase/lock/lock0lock.c in which you said Different transaction can have conflicting locks set on the gap at the same time.. I think that the innodb gap lock's behavior just like an IX lock's behavior. hmm... yes, we could think that when a row is inserted, the inserted needs an 'X-lock on the whole gap'! Then the 'IX-locks' on the gap stop the insertion. When a transaction want to insert a record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't it? Yes, but in the above analogy, an insert is really requesting an 'X lock on the whole gap'. The name LOCK_INSERT_INTENTION is then somewhat misleading. I have read some source code in innodbase/lock/lock0lock.c. But I can't get a clear view of innodb lock modes and lock ways? Can you give me more information? On tables, InnoDB has X, S, IX, IS, and AUTO-INC type locks. InnoDB has basically just X and S type locks on records and gaps. The complexity comes from this: lock0lock.h in 5.0: #define LOCK_ORDINARY 0 /* this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ #define LOCK_GAP512 /* this gap bit should be so high that it can be ORed to the other flags; when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ #define LOCK_REC_NOT_GAP 1024 /* this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ #define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited to a neighboring record */ Unfortunately, the only existing documentation of the details of gap locking is in the source code and comments in lock0lock.c. PS: hi, Eric, Our MySQL version is 4.1.18. Thx! Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Leo, 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason why we let 'conflicting' locks of different transactions on a gap is that this way there are less lock waits and less deadlocks. In Eric Bergen's example, there was a row with id 6, and there the locks were not gap locks. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . From: leo huang Date: December 12 2006 7:46am Subject: Why innodb can give the same X gap lock to two transactions? Get Plain Text Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb log sequence error - urgent
Ratheesh, if the database otherwise looks ok (no crashes, no corrupt tables), then the easiest way to fix the wrong log sequence number (lsn) is to artificially inflate the log sequence number. If your log sequence number is 4 GB too small, then inserting and deleting 4 GB worth of rows will lift it high enough so that the complaints about a too small lsn end. The risk in having inconsistent lsn's stamped into data pages is that if there is a database crash, then the log will not be applied to those pages. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . From: Ratheesh K J Date: December 11 2006 10:23am Subject: Re: Innodb log sequence error - urgent Get Plain Text Thanks, I have the previous ib_log* files on the app server. And every thing on the cnf file was perfect. Only the ib_log file's size was a mismatch. Whats the best work around? Can I copy the log files of the App server to the DB server and change the innodb_log_file_size to 256M and then restart the MySQL server. If I do so will I lose the updates to the database that happened today? So my actual problem is this: I have two sets of ib_logfile* files. To be particular there are a.. ib_logfile0, ib_logfile1, ib_logfile2 on the App server- each 257M (when i did a du -sh). In the my.cnf file of the App server innodb_log_file_size is set to 256M b.. ib_logfile0, ib_logfile1, ib_logfile2 on the DB server - each 5M. These log files were created freshly by the MySQL server as the log files from the App server was not copied to the DB server. In the my.cnf file of the DB server innodb_log_file_size is set to 5M by mistake. All the other settings were same as on the app server. The ibdata1 file is that of the App server. And I get the log sequence errors as shown in my previous post. But everything seems to be working fine. There have been no problems accessing the data. What I can I possibly do to get everything right. How can I correct the log sequence error? Should the log files of App server be in the DB server? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Crashed
David, please send the entire .err file to me, zipped. Please tell the exact MySQL version you are using. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php .. From: David Vickers Date: December 4 2006 3:57pm Subject: SQL Crashed Get Plain Text Crashed mysql, claiming problem at line 1594, on a file structure that doesn't exist. Most confusing, also showed several Also tried to access /jsp/java/sql/BatchUpdateException.class HTTP/1.1 404 337 again, something else that doesn't exist. Confused! This might help, but I don't understand it. Innobase: Assertion failure in thread 5064 in file C:\test\innobase\os\os0file.c line 1594 Innobase: we intentionally generate a memory trap. Innobase: Send a bug report to mysql@lists.mysql.com Innobase: Thread 3476 stopped in file C:\test\innobase\mem\mem0pool.c line 321 Innobase: Thread 1252 stopped in file C:\test\innobase\os\os0file.c line 1590 Innobase: Thread 3596 stopped in file C:\test\innobase\sync\sync0arr.c line 335 Innobase: Thread 4704 stopped in file C:\test\innobase\sync\sync0arr.c line 335 Innobase: Thread 5156 stopped in file C:\test\innobase\sync\sync0arr.c line 335 Innobase: Thread 1732 stopped in file C:\test\innobase\sync\sync0arr.c line 335 Innobase: Thread 5360 stopped in file C:\test\innobase\sync\sync0arr.c line 335 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
Curtis, the reason why innodb.com was unreachable for some time on Friday was that Oracle web administrators moved the DNS records to an Oracle domain server. The registrar of innodb.com is Tucows, and I believe the admins made some error which caused Tucows to set renewyourname.net as the domain server, which made innodb.com to show as a Google search page. I am sorry for the inconvenience this caused for InnoDB users. Concerning the MySQL AB - Innobase Oy OEM contract, it was renewed in spring 2006. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . My guess is that its intentional. Oracle is who they are and MySQL is eating their lunch. I look for them to kill the product to try to drive MySQL out of business or make life difficult for them; hence the reason they're working on a new storage engine of their own. They tried to buy MySQL, but when they couldn't they bought up the two pieces of software that gave MySQL ACID transactions (innodb and Berkely (sp?)). They will renegotiate the contract, but make it very expensive for MySQL to license. You don't honestly think Oracle is going to be honest about this do you? They have absolutely no interest in helping MySQL survive. Sounds pretty fishy to me no matter what they're saying publicly. Curtis Bill MacAllister wrote: --On Friday, November 10, 2006 08:46:50 AM -0500 Curtis Maurand [EMAIL PROTECTED] wrote: http://www.oracle.com/innodb/index.html Please quit telling us that Oracle purchased Innodb. That is ancient news. The innodb.com web site had innodb content on it in the past, Heikki signature makes me think that it still should, and it doesn't now. That is the issue that I raised. I am guessing, given Reimer's insight, that the domain name registration has expired and someone needs to renew it. I hope they plan to do that because I found the site useful. Bill Riemer Palstra wrote: On Thu, Nov 09, 2006 at 09:26:52AM -0800, Bill MacAllister wrote: What happened to the Innodb web pages? What comes up for be is a search page with a bunch of related links on it. I wanted to pull down a copy of ibbackup documentation and it isn't there anymore. Strange indeed, I get the search pages that Tucows/OpenSRS put up when they park a domain as soon as a customer lets their domain name expire... -- Riemer Palstra Amsterdam, The Netherlands [EMAIL PROTECTED] http://www.palstra.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=1 +--- | Bill MacAllister, Senior Programmer | 10030 Foothills Blvd | Roseville, CA 95747 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
Bill, we are moving the DNS of innodb.com from Capnova to Oracle Corp. I can now view http://www.innodb.com through my ISP, Elisa. Does anyone still have problems accessing http://www.innodb.com? If you cannot see some web page, you can resort to Google's cache to view it. I hope that we will not get more disruption of service this weekend. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php What happened to the Innodb web pages? What comes up for be is a search page with a bunch of related links on it. I wanted to pull down a copy of ibbackup documentation and it isn't there anymore. Bill +--- | Bill MacAllister, Senior Programmer | 10030 Foothills Blvd | Roseville, CA 95747 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB + FULLTEXT
Tim, we hired Osku in August 2005 to work on the fulltext project. Osku has done also lots of other work during the past 15 months. His fulltext project is progressing slowly but steadily. I cannot give any promises when/if Osku's work will be ready. A factor that also affects this is that MySQL AB is overhauling the fulltext system so that it would better support fulltext implementations inside different engines. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php So, based on this article, they were looking for a coder in April of this past year. Did anyone start working on it? Does anyone know the status of the project? Tim Gustafson FalconSoft, Inc [EMAIL PROTECTED] http://falconsoft.com/ (831) 425-4522 (831) 621-6299 (Fax) - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: FalconSoft, Inc [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, November 08, 2006 11:25 PM Subject: Re: InnoDB + FULLTEXT Hi, Till MySQL 5.0 there was no support for FULLTEXT by InnoDB. More info on: www.innodb.com/innodbtalkUC2005.pdf Thanks ViSolve DB Team. - Original Message - From: FalconSoft, Inc [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 09, 2006 6:28 AM Subject: InnoDB + FULLTEXT Does anyone know if/when InnoDB will support FULLTEXT indexes? I have a project that I'm working on now that really needs support for both. Thanks! Tim Gustafson FalconSoft, Inc [EMAIL PROTECTED] http://falconsoft.com/ (831) 425-4522 (831) 621-6299 (Fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key MATCH constraint
Giorgio, InnoDB only implements MATCH SIMPLE. MySQL/InnoDB ignores the MATCH clause that you specify in the foreign key constraint definition. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt A referential constraint is satisfied if one of the following con- ditions is true, depending on the match option specified in the referential constraint definition: - If no match type was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table. - If MATCH FULL was specified then, for each row R1 of the refer- encing table, either the value of every referencing column in R1 shall be a null value, or the value of every referencing column in R1 shall not be null and there shall be some row R2 of the referenced table such that the value of each referencing col- umn in R1 is equal to the value of the corresponding referenced column in R2. 42 Database Language SQL X3H2-92-154/DBL CBR-002 4.10 Integrity constraints - If MATCH PARTIAL was specified then, for each row R1 of the referencing table, there shall be some row R2 of the refer- enced table such that the value of each referencing column in R1 is either null or is equal to the value of the corresponding referenced column in R2. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php ... Hi, I was looking at the CREATE TABLE syntax and I saw this reference definition for InnoDB tables: reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION Well, I pretty undestood all but I can't understand 2 things about MATCH option: 1. Which of three options (SIMPLE, FULL, PARTIAL) are implemented in the current production version of MySQL? Reading around, I think SIMPLE and FULL are supported. What about PARTIAL? Is it supported? 2. About MATCH PARTIAL. I could not find a good explanation on how it works. I found here http://forge.mysql.com/wiki/ForeignKeySupport the explanations on MATCH SIMPLE and MATCH FULL: --QUOTED- Handling of the the MATCH clause -- The HLS specifies that support for MATCH SIMPLE only is required. Since it is very easy to support MATCH FULL if we already have support for MATCH SIMPLE, MATCH FULL should perhaps be added to the list of requirements as an optional feature. These types of the MATCH clause differ only in case of checks that are done during insertion into a referencing table. So they can be implemented by a simple check which is to be done at the beggining of the trigger which is responsible for processing of a foreign key check on insertion: * if the match type is SIMPLE and one or more foreign key columns in the row being inserted is NULL, then regard the constraint as satisified. Otherwise continue with checking. * if the match type for the foreign key constraint is FULL and all foreign key columns in the row being inserted are NULL, then treat the constraint as satisified. If at least one of columns in the foreign key is NULL, and at least one of them is not NULL, treat the foreign key constraint as failed. Otherwise (if all columns in the foreign key are not NULL), continue with checking. Handling of foreign key constraints with MATCH PARTIAL is more complex and support for it is not required in the HLS. END QUOTED- But there's no explanation about MATCH PARTIAL. So, what about MATCH PARTIAL. Is it supported and how does it works? Thanks for any infos. Giorgio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Dominik, what does SHOW TABLE STATUS show for other tables? Are you using innodb_file_per_table? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php I recently deleted about 7.000.000 rows from a table, there are about 4.000.000 left. So I want to know how much space is free in table space now and execute: mysql show table status like table\G *** 1. row *** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 4354196 Avg_row_length: 210 Data_length: 917536768 Max_data_length: 0 Index_length: 2294349824 Data_free: 0 Auto_increment: 35040856 Create_time: 2006-10-12 10:29:36 Update_time: NULL Check_time: NULL Collation: latin1_german1_ci Checksum: NULL Create_options: Comment: InnoDB free: 6144 kB; InnoDB free: 1762304 kB 1 row in set (0,26 sec) Why does it show two values for InnoDB free? Which one is correct? I use MySQL 5.0.21 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Dominik, I have now filed: http://bugs.mysql.com/bug.php?id=23211 about this. Is there any pattern that could explain why the double print is only in those 3 tables? What values does it print for the tables where the printout is wrong, and what values does it print for ok tables? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php Heikki Tuuri wrote: FYI: http://lists.mysql.com/mysql/202574 what does SHOW TABLE STATUS show for other tables? It shows 2 values for about 3 of 260 tables. So most tables are okay. It does not seem to depend on table size, as the other tables only have a few hundred rows. Are you using innodb_file_per_table? Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Has InnoDb licensing changed to accommodate Oracle?
Mike, Oracle Corp. and MySQL AB renewed the InnoDB OEM contract in spring 2006. The licensing of InnoDB is the same as before and it is distributed in the official MySQL distros. Best regards, Heikki Tuuri CEO of Innobase Oy VP of Oracle Corporation Has Oracle placed any restrictions on using InnoDb and MySQL now that the original MySQL AB license has expired with Heikki?? What is the name of the new MySQL transaction engine and is anyone using it? Is it any good? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong in this Innodb status log?
Nico, please post more output. Maybe the SELECT from H.albero is just entering InnoDB, and therefore the associated transaction has not yet started. The output shows lots of file reads. There should be running queries visible in other printouts. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php Hi, after having noticed occasional load spikes I created an Innodb monitor; follows an excerpt of the output where I can read that certain transactions don't start and that accessing a table (H.albero) with a very low amount of records (185) seems to take a lot of time. What's wrong with these logs? why those transactions don't start? Thanks, Nico = 060529 16:27:40 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 44025032, signal count 43473647 Mutex spin waits 794955636, rounds 1522695948, OS waits 9162350 RW-shared spins 66265323, OS waits 32297148; RW-excl spins 1644124, OS waits 379573 TRANSACTIONS Trx id counter 0 678088914 Purge done for trx's n:o 0 678083128 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 678088912, not started, process no 30735, OS thread id 1210591600 MySQL thread id 6460882, query id 173503655 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678088911, not started, process no 30735, OS thread id 1207380336 MySQL thread id 6460881, query id 173503653 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678088913, not started, process no 30735, OS thread id 1193732464 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 6460880, query id 173503656 192.168.65.107 h3r4user Sorting result SELECT H.albero.ordinamento, H.albero.REF, H.albero.titolo, H.albero.categoria, H.albero.tabella, H.albero.url, ---TRANSACTION 0 678087842, not started, process no 30735, OS thread id 1175267696 MySQL thread id 6460844, query id 173502395 192.168.65.107 r1minse4 ---TRANSACTION 0 678087713, not started, process no 30735, OS thread id 1164028272 MySQL thread id 6460837, query id 173502245 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678082967, not started, process no 30735, OS thread id 1204169072 MySQL thread id 6460594, query id 173496341 192.168.65.107 h3r4user ---TRANSACTION 0 678081965, not started, process no 30735, OS thread id 1179281776 MySQL thread id 6460592, query id 173495169 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678081879, not started, process no 30735, OS thread id 1150380400 MySQL thread id 6460570, query id 173495043 192.168.65.108 cntdb3 ---TRANSACTION 0 678057066, not started, process no 30735, OS thread id 1192126832 MySQL thread id 6459537, query id 173464972 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678051381, not started, process no 30735, OS thread id 1195137392 MySQL thread id 6459290, query id 173458071 192.168.65.108 cntdb3 ---TRANSACTION 0 678045374, not started, process no 30735, OS thread id 1182894448 MySQL thread id 6459003, query id 173450765 192.168.65.105 prgdb_1 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 99506233 OS file reads, 1810896 OS file writes, 808231 OS fsyncs 266.30 reads/s, 33214 avg bytes/read, 1.75 writes/s, 1.12 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 5, seg size 7, 195942 inserts, 195942 merged recs, 7446 merges Hash table size 34679, used cells 6972, node heap has 13 buffer(s) 4850.07 hash searches/s, 1614.59 non-hash searches/s --- LOG --- Log sequence number 4 1251143669 Log flushed up to 4 1251143659 Last checkpoint at 4 1251140386 0 pending log writes, 0 pending chkp writes 1203908 log i/o's done, 1.12 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 364710794; in additional pool allocated 25165824 Buffer pool size 1024 Free buffers 0 Database pages 1011 Modified db pages 11 Pending reads 0 Pending writes: LRU 0, flush list
Re: innodb database crash
Vitaliy, - Original Message - From: Vitaliy Okulov [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, May 26, 2006 5:28 PM Subject: innodb database crash Hi all. I have some InooDB mysql crush logs, can somebody explain what they mean? Quote: 060525 18:09:43 InnoDB: Error: trying to declare trx to enter InnoDB, but InnoDB: it already is declared. TRANSACTION 0 550042370, ACTIVE 0 sec, process no 27054, OS thread id 163851, thread declared inside InnoDB 0 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320 this is http://bugs.mysql.com/bug.php?id=20090 The reason is not yet known. Quote: 060525 18:09:31InnoDB: Assertion failure in thread 786465 in file fsp0fsp.c line 2963 InnoDB: Failing assertion: descr InnoDB: We intentionally generate a memory trap. That is database corruption. Please post more of the .err log, particularly the first errors before it shows corruption. What operating system and hardware are you using? Can this errors occure if i change default value of 2 parameters to: innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 It will not help in corruption. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB corruption and crash on Windows 2000
Andrew, a possible reason for the corruption is that you have enabled write caching in the disk controller or in the disk, but those caches are not battery-backed. Then a hard reboot may destroy the contents, and the database becomes corrupt. What kind of hardware are you using? Do you have manuals that would describe the write caching policy? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Andrew Brockert [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, May 21, 2006 4:59 AM Subject: InnoDB corruption and crash on Windows 2000 --=_Part_127305_14374727.1148176365800 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I am running MySQL with hMailServer on Windows 2000 Pro. I was forced to hard reboot the machine this morning, and this is now the result of running mysqld-nt with --console: 060520 21:38:23 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 4648603 InnoDB: Doing recovery: scanned up to log sequence number 0 4648603 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1 row operations to undo InnoDB: Trx id counter is 0 478464 InnoDB: Starting rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 406541 InnoDB: Rollback of uncommitted transactions completed 060520 21:38:23 InnoDB: Flushing modified pages from the buffer pool... 060520 21:38:23 InnoDB: Started N:\hMailServer\MySQL\Bin\mysqld-nt.exe: ready for connections. Version: '4.0.17-nt' socket: '' port: 3307 060520 21:38:24 InnoDB: Error: page 209 log sequence number 0 4672346 InnoDB: is in the future! Current system log sequence number 0 4648603. InnoDB: Your database may be corrupt. 060520 21:38:24 InnoDB: Assertion failure in thread 2328 in file C:\4017\build\mysql-4.0.17\innobase\fsp\fsp0fsp.c line 3034 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com InnoDB: Thread 2732 stopped in file C:\4017\build\mysql- 4.0.17\innobase\sync\sync0arr.c line 126 --=_Part_127305_14374727.1148176365800-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Memory Problem causing mysql to crash
Dobromir, you are running a 32-bit operating system. Then the size of the mysqld process is limited to 2 GB, or at most to 4 GB. The amount of total RAM 8 GB does not help here, since 2^32 = 4 G. You should reduce the key_buffer_size or innodb_buffer_pool_size in my.cnf. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, May 05, 2006 10:50 PM Subject: Re: InnoDB Memory Problem causing mysql to crash Well, according to my calculations: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (I used the default binlog_cache_size value of 32K plus your settings) MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, unless of course your 8G of machine is running something other than MySQL. Is it? Because the fact that it could not allocate memory means that something was trying to use memory that didn't exist Did MySQL dump a core file? Did you follow this advice? You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap= for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html Did you read the man page? 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. Also, did you try to look at your slow query logs to see if there was some kind of query hogging memory? What about backups running at the same time? I'll note that you maxxed out your connections, which shouldn't cause a crash, but might indicate that your server tuning is not up-to-date with your actual usage. Are your data and logfiles are on a diffferent partitions? We had problems with one machine where the data and logfiles were on the same partition, and it would crash -- we moved to a machine that was the same except for the different OS partitions, and it didn't crash! We figure the disk seeking just killed the OS so it segfaulted the mysql process. -Sheeri On 5/4/06, Dobromir Velev [EMAIL PROTECTED] wrote: Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the l= ast month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELs= mp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=3D2000M innodb_additional_mem_pool_size=3D20M innodb_log_file_size=3D150M innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D0 innodb_lock_wait_timeout=3D50 key_buffer_size=3D1000M read_buffer_size=3D500K read_rnd_buffer_size=3D1200K sort_buffer_size=3D1M thread_cache=3D256 thread_concurrency=3D8 thread_stack=3D126976 myisam_sort_buffer_size=3D64M max_connections=3D600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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 buil= t, or misconfigured. This error can also be caused by malfunctioning hardwar= e. We will try our best to scrape up some info that will hopefully help diag= nose the problem, but since we have already crashed, something is definitely w= rong and this may fail. key_buffer_size=3D1048576000 read_buffer_size=3D507904 max_used_connections=3D600 max_connections=3D600 threads_connected=3D473 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = =3D 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap= for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=3D(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no
Re: How could I know which transaction or thread hold the lock
Sheeri, Gu Lei, SHOW PROCESSLIST only knows about MySQL table locks. To list row locks, you need to use innodb_lock_monitor: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, April 07, 2006 10:23 PM Subject: Re: How could I know which transaction or thread hold the lock While the query is still running, type mysql show processlist or mysql show full processlist find the query(ies) with the State column having a value of Locked -Sheeri On 4/7/06, $B8EMk(B [EMAIL PROTECTED] wrote: Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei -- 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: ~how to add a new innodb data file~
Mohammed, http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending. The correct way to get the size of a file is the 'ls -l' command. I think the 'du' command measures the physical size required for the file on the disk, and it can differ from the size of the file as seen by mysqld. 463470592 / (1024 * 1024) = 442. InnoDB measures the file size in MB, where MB is defined as 1024 * 1024 bytes. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Mohammed Abdul Azeem [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 30, 2006 10:03 AM Subject: ~how to add a new innodb data file~ Hi, Iam running a mysql server 5.0.15 over Redhat linux es4. My disk space has exhausted. so i need to add a new ibdata file to my /etc/my.cnf configuration. I followed the following procedure to do so. 1. I checked the ibdata1 file size. when i do a du -sh ibdata1 , i get the size to be 443M when i do a ls -ltr ibdata1 , i get the size to be 463470592 bytes 2. I edited my /etc/my.cnf to add the following: innodb_data_file_path = /mysql-system/mysql/data/ibdata1:443M;/mysql- system2/ibdata2:50M:autoextend i got the following error: 060330 01:48:42 mysqld started InnoDB: Error: data file /mysql-system/mysql/data/ibdata1 is of a different size InnoDB: 28288 pages (rounded down to MB) InnoDB: than specified in the .cnf file 28416 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 060330 1:48:42 [Note] /mysql-system/mysql/bin/mysqld: ready for connections. Version: '5.0.15-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 3. Then i tried adding the value from 'ls -ltr ibdata1' which is 463470592 bytes. I rounded the same to 464M . but still got the same error. Can anyone help me out on how to go about the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.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 errors on startup
Marten, Marten Lehmann wrote: Hello Heikki, can you email the complete .err log from the server to [EMAIL PROTECTED] the complete log is about 50 mb, since a lot of errors occur. I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! We had a crash of the filesystem on one server so we moved all databases to a new server by copying the files. I didn't copy the ib-files because I didn't know that they are indispensable since I created all innodb-tables with innodb_file_per_table. When I restored some databases ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. that didn't work correct, I simply removed the files from the databases-directory, which also wasn't a good idea as I know now, at least regarding innodb-table (most tables are thankfully in myisam-format). This server is hosting a few hundrets of databases (we are doing shared hosting), so it's not so easy to recover the ib-files and keep all databases running. Are you using some exotic file system? No, ext3. The output looks like ib_logfiles and data files from different servers would be mixed. Thats not exactly the way it happened, but it may have the same result. Since the dictionary only has problems with some certain databases, we simply created a new databases and restored the db-dump into it. So the databases are mostly working, but the errors on startup appear anyhow. Regards Marten Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Marten, - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 29, 2006 7:52 PM Subject: Re: innodb errors on startup Well, ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. but how can I repair my existing ib-files so that the error on startup doesn't appear any more? try: DROP TABLE db16041.intradv_cms_websites; if you do not have the .frm file for that table, use the trick explained at: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Regards Marten Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Marten, I replied today to your earlier email with the message pasted below. Regards, Heikki Marten, can you email the complete .err log from the server to [EMAIL PROTECTED] I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! Did you move around or delete ib_logfiles or ibdata files or .ibd files, or edit my.cnf while mysqld was running? Are you using some exotic file system? The output looks like ib_logfiles and data files from different servers would be mixed. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 28, 2006 7:49 PM Subject: innodb errors on startup Hello, I'm getting this in my errorlog: 060328 18:43:45 mysqld ended 060328 18:43:46 mysqld started 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_websites.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_wsepgmerchant.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Started; log sequence number 0 18831221 /vrmd/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution How do I get rid of these messages / the errors? The page for troubleshooting didn't help. Regards Marten -- 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: problems/feature request ideas
Sheeri, - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 28, 2006 9:29 PM Subject: problems/feature request ideas 2 weeks ago we had a server crashing, and while I was checking it out (before, during and after crashes) I noticed that after a crash, the server was slow. Threads would run for 200 seconds or more, and yet when they finished, nothing was written to the slow query log. Why would that be? Slow query logging was on all the time, and other slow queries were written after that (anything greater than 4 seconds would be) Also, do other folks find that a deadlock log would be useful? InnoDB obviously stores it in RAM because SHOW ENGINE INNODB STATUS will show you the last deadlock information. But I feel like a deadlock log would be useful, to see how many deadlocks we get in a certain period of time (but not an averageI'm sure there are peak times, etc). SHOW DEADLOCKS is in our TODO. It would definitely be useful for users. Any ideas/comments? -Sheeri Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnDB disabbled on 5.1.7
Gordon,
what does mysqld write to the .err log?
InnoDB should be included in all 5.1 binaries.
Best regards,
Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php
- Original Message -
From: Gordon [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, March 24, 2006 9:48 PM
Subject: InnDB disabbled on 5.1.7
We are running 2.6.15-gentoo Linux and downloaded the max binaries for
5.1.7. With the following my.cnf I thought we should have InnoDB. All of =
the
InnoDB files got created but show variables like 'have%'; displays
have_innodb DISABLED. Exactly the same my.cnf {except the skip bdb is =
not
commented out} has InnoDB enabled.
Any ideas on what we have to do to enable InnoDB.
my.cnf
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the --help option.
=A0
# The following options will be passed to all MySQL clients
[client]
#password=A0=A0=A0=A0=A0=A0 =3D your_password
port=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =3D 3306
socket=A0=A0=A0=A0=A0=A0=A0=A0=A0 =3D /tmp/mysql.sock
=A0
# Here follows entries for some specific programs
=A0
# The MySQL server
[mysqld]
port=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =3D 3306
socket=A0=A0=A0=A0=A0=A0=A0=A0=A0 =3D /tmp/mysql.sock
max_connections =3D 100
key_buffer =3D 256M
max_allowed_packet =3D 1M
table_cache =3D 256
sort_buffer_size =3D 256K
read_buffer_size =3D 256K
read_rnd_buffer_size =3D 256K
thread_cache_size =3D 8
query_cache_size=3D 2M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency =3D 4
=A0
=A0
=A0
# Replication Master Server (default)
# binary logging is required for replication
log-bin=3Dmysql-bin
=A0
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id=A0=A0=A0=A0=A0=A0 =3D 1
=A0
=A0
=A0
# Point the following paths to different dedicated disks
#tmpdir=A0=A0=A0=A0=A0=A0=A0=A0 =3D /tmp/
#log-update=A0=A0=A0=A0 =3D /path-to-dedicated-directory/hostname
=A0
skip-bdb
=A0
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir =3D /usr/local/var/
innodb_data_file_path =3D =
ibd1:2000M;ibd2:2000M;ibd3:2000;ibd4:10M:autoextend
#innodb_log_group_home_dir =3D /usr/local/var/
#innodb_log_arch_dir =3D /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size =3D 512M
innodb_additional_mem_pool_size =3D 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size =3D 128M
innodb_log_buffer_size =3D 8M
innodb_flush_log_at_trx_commit =3D 0
innodb_lock_wait_timeout =3D 50
=A0
[mysqldump]
quick
max_allowed_packet =3D 16M
=A0
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
=A0
[myisamchk]
key_buffer =3D 128M
sort_buffer_size =3D 128M
read_buffer =3D 2M
write_buffer =3D 2M
=A0
[mysqlhotcopy]
interactive-timeout
mysql select version();
++
| version() |
++
| 5.1.7-beta-max-log |
++
1 row in set (1.73 sec)
Linux zeus 2.6.15-gentoo-r1 #10 SMP PREEMPT Tue Mar 7 15:36:28 MST 2006 =
i686
Intel(R) Xeon(TM) CPU 3.80GHz GenuineIntel GNU/Linux
mysql show variables like 'have%';
++--+
| Variable_name | Value|
++--+
| have_archive | YES |
| have_bdb | DISABLED |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_example_engine| NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb| DISABLED |
| have_ndbcluster| DISABLED |
| have_openssl | NO |
| have_partitioning | YES |
| have_query_cache | YES |
| have_row_based_replication | YES |
| have_rtree_keys| YES |
| have_symlink | YES |
++--+
17 rows in set (0.10 sec)
mysql show variables like 'inno%';
+-+--=
---
+
| Variable_name | Value
|
+-+--=
---
+
| innodb_additional_mem_pool_size | 20971520
|
| innodb_autoextend_increment | 8
|
|
Re: ERROR 2013 (HY000): Lost connection to MySQL server during query
Marten, can you email the complete .err log from the server to [EMAIL PROTECTED] I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! Did you move around or delete ib_logfiles or ibdata files or .ibd files, or edit my.cnf while mysqld was running? Are you using some exotic file system? The output looks like ib_logfiles and data files from different servers would be mixed. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 27, 2006 3:46 PM Subject: ERROR 2013 (HY000): Lost connection to MySQL server during query Hello, again, I'm having problems with InnoDB tables. A certain table cannot be dropped. If I'm issueing the drop table statement, the connection is lost and I get the following in the logfile: 060327 14:38:11 InnoDB: error: space object of table db15670/mw_pagelinks, InnoDB: space id 12 did not exist in memory. Retrying an open. InnoDB: Error: trying to add tablespace 12 of name './db15670/mw_pagelinks.ibd' InnoDB: to the tablespace memory cache, but tablespace InnoDB: 12 of name './db15720/admin.ibd' already exists in the tablespace InnoDB: memory cache! 060327 14:38:11 InnoDB: Error: page 3 log sequence number 0 153218641 InnoDB: is in the future! Current system log sequence number 0 14322402. InnoDB: Your database may be corrupt. 060327 14:38:11InnoDB: Assertion failure in thread 196621 in file fsp0fsp.c line 3202 InnoDB: Failing assertion: xdes_get_bit(descr, XDES_FREE_BIT, buf_frame_get_page_no(header) % FSP_EXTENT_SIZE, mtr) == FALSE InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 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=402653184 read_buffer_size=2093056 max_used_connections=12 max_connections=1000 threads_connected=5 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 290904 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8a000c18 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=0xbe1fbe08, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80a43b4 0x82bf71c 0x820a5fd 0x81b64f5 0x8169f26 0x81a4bc7 0x81a4224 0x81a36c2 0x817f332 0x817e57d 0x819376b 0x8125411 0x811ba68 0x812abe0 0x812a500 0x80b76b2 0x80bbb72 0x80b54bd 0x80b5102 0x80b48f9 0x82bb001 0x82ed89a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/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 0x8bb38e0 = drop table mw_pagelinks thd-thread_id=220 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. Number of processes running now: 0 060327 14:38:12 mysqld restarted 060327 14:38:12 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. The whole mysql-server crashed just because something is wrong in one innodb table. The force-recovery-hint didn't help at all, because it just made it possible do drop the table, but nothing was fixed and I couldn't create the table back then. Why does this happen? Regards Marten -- 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: MySQL malloc error on Solaris
Ubaidul, ok, there is nothing in my.cnf that can explain why memory runs out. What kind of query are you running when the memory runs out? Does 'top' show that the mysqld process size grows uncontrollably? If you are using the C client interface, do you use 'mysql_store_result()' or 'mysql_use_result()'? For huge resultsets, one of them uses a huge amount of memory in the server. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Ubaidul Khan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 23, 2006 11:06 PM Subject: RE: MySQL malloc error on Solaris Following is the configuraton of mysqld: - Excerpt from my.cnf - # The MySQL server [mysqld] user= mysql port= 4406 socket = /tmp/mysql.sock #socket = /tmp/mysql_4.0.13.sock set-variable= max_connections=150 skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M local-infile = 0 # Clients authenticate to server must do so by the IP only skip-name-resolve # Display only the databases the authenticated user has privileges to safe-show-database # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication #log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended log-bin = /usr/local/mysql/var/myupdate-bin.log # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /usr/local/mysql/var/myupdate.log # Logs connections and queries to file. Use for troubleshooting, disable afterward s #log= /usr/local/mysql/var/myquery.log # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /opt/mysql_4.0.13/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /opt/mysql_4.0.13/var/ #innodb_log_arch_dir =
Re: Problem restarting server
Mike, - Original Message - From: Mike Blezien [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 22, 2006 3:42 AM Subject: Problem restarting server Hello, we had to reboot our server and now we can't get MySQL started, in the error log it states: == 060321 19:34:13 mysqld started 060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 060321 19:34:13 [ERROR] Can't init databases 060321 19:34:13 [ERROR] Aborting 060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete 060321 19:34:13 mysqld ended = How can this problem be fixed so we can restart MySQL server, kind of in a bind here now... mysqld is probably reading a different my.cnf file now than it did when you started mysqld last time. Have you edited my.cnf or replaced it while mysqld was running? Or could mysqld fail to find a my.cnf file altogether? You should determine where your my.cnf is, and where your ibdata files and ib_logfiles are, and specify in my.cnf appropriately: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html innodb_data_file_path innodb_log_group_home_dir innodb_log_files_in_group innodb_log_file_size TIA, Mike Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock - further information
Rithish,
- Original Message -
From: Rithish Saralaya [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, March 20, 2006 10:02 AM
Subject: deadlock - further information
--=_NextPart_000_0059_01C64C23.16088020
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Hello.
We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.
We get a mysql error recorded as - Deadlock found when trying to get
lock;
try restarting transaction when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}
I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to replicate
the scenario, as this error is not logged always. Is there a particular
case
when this happens?
I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
SELECT ... FROM is a consistent read, reading a snapshot of the database
and setting no locks ... So why is a lock being taken in the first
place?
I am at a loss. Can someone shed light on this, or point me to some
resource?
in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
make InnoDB to use a consistent read in the SELECT tables in CREATE ...
SELECT. Read the caveats about the my.cnf option, though.
This is explained at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
I noticed that this behavior is broken in 5.0. I filed the bug report
http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
this up.
Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.
Regards,
Rithish.
Best regards,
Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL malloc error on Solaris
Ubaidul, - Original Message - From: Ubaidul Khan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 20, 2006 8:31 PM Subject: MySQL malloc error on Solaris Hello, We are running MySQL 4.0.13 on Solaris 8 UltrSPARC with 2048 MB of RAM. This machine has plenty of swap space and has worked fine for over a year now. Out of the blue, it stopped working yesterday and after looking through the error logs, following is what I found: - Error Message - key_buffer_size=16777216 read_buffer_size=131072 Fatal signal 11 while backtracing 060319 16:19:46 mysqld restarted Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line InnoDB: Fatal error: cannot allocate 48 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 3879876 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 060319 16:19:47 mysqld ended - End of Error Message - Would increasing shared memory max, make a difference? what is your my.cnf like? InnoDB has only allocated 4 MB of memory, so InnoDB can hardly be the culprit. Do you run a query that uses huge amounts of memory? Thanks Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errors 1005 and 1025 - but not foreign keys
David, - Original Message - From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 11:31 PM Subject: Errors 1005 and 1025 - but not foreign keys I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. what is the MySQL version? Please post the error messages verbatim. If mysqld prints something to the .err log, please also post the printout. After the failing operation, run SHOW INNODB STATUS\G and post the latest foreign key error explanation in it if any. The issue may be an orphaned table in ibdata1 which does not have an .frm file: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Or you may have FOREIGN KEY constraints that you are not aware of. Deleting an .ibd file manually from the database directory never helps because the InnoDB internal data dictionary is in ibdata files. How can I get rid of this mess? Thanks. David Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL columns
Hi! - Original Message - From: Martijn Tonies [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 8:59 PM Subject: Re: NULL columns When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? In the cases that you really want to store NULL ;-) No, it's not the word null. Of course space is consumed. How much depends, see, for example: http://dev.mysql.com/doc/refman/5.0/en/static-format.html or http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html This is for MyISAM, InnoDB behaves differently. InnoDB's old table format in 4.1 and earlier kept a fixed-length column the same length even when the value NULL was stored. That, of course, wasted a lot of space. The advantage was less fragmentation in updates. InnoDB's new table format in 5.0 does not use any space to store a NULL. The column itself needs one bit to indicate whether the value is NULL or not. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Commit question
Rob, - Original Message - From: Rob Brooks [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, March 12, 2006 6:02 PM Subject: InnoDB Commit question --=_NextPart_000_0033_01C645BC.03223720 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into the myisam tables and the innodb table at the same time. We have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? which MySQL version you are using? Please describe in detail what MySQL statement(s) you use to insert into the MyISAM tables. Please post SHOW PROCESSLIST and SHOW INNODB STATUS\G readings when the commit is seemingly hung. I am not aware of any mechanism that should delay the processing of a commit in this case. Any help appreciated Rob Brooks The Brian Group LLC Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Indices
Robert, actually, InnoDB always internally adds the PRIMARY KEY to every secondary index record: http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html If a column has just four different values, then in most cases an index on that column does not help at all. And every index slows down inserts. That is why you normally do not create an index on such a column. But a query of the following type would get a speedup, assuming that the index tree completely fits in the buffer pool (main memory): SELECT COUNT(*) FROM t WHERE low_selectivity_column = 2; The speedup would be 4X compared to a table scan. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 09, 2006 8:41 PM Subject: RE: InnoDB Indices =20 ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. I guess I was thinking that if an index with otherwise low selectivity added a rightmost column that was completely unique that it would improve key distribution and therefore make deletes faster. But every database engine handles this stuff differently. R=20 -Original Message- From: David Turner [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 10:13 AM To: Robert DiFalco; mysql@lists.mysql.com Subject: Re: InnoDB Indices - Original Message From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 9, 2006 9:32:44 AM Subject: InnoDB Indices I have some questions regarding InnoDB indices. =20 Say I have a table with millions of records. On of the fields is a type field that has a possible value of 1,2,3, or 4. I sometimes query by the type field and may at other times order on it. =20 Do queries benefit from an index with this low of a selectivity? =20 ++ For the most part no. I come from Oracle where you can use histograms to help. So, someone feel free to correct me if I'm wrong. =20 If through the query browser I find that they do, should I increase the selectivity of the index by making it a compound index and adding my Primary Key to the TYPE index? =20 ++ If your primary key will be included in the where clause then definitely include it. =20 If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? =20 ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. =20 Dave =20 R. -- 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: InnoiDB Backups
Alan, - Original Message - From: Alan Fisher [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 09, 2006 8:38 PM Subject: InnoiDB Backups All, I would like to knew if anyone knows of a way to automate innoDB Hot Backups of several databases that can be set to occur at off hours. I think people create cron jobs to run at night. If I understood right, you have several separate MySQL installations in one computer. Maybe one cron job for each installation would work? InnoDB Hot Backup takes as the input the my.cnf file of the mysqld server, and a backup-my.cnf file that specifies where the data is copied. You must be very careful not to mix these .cnf files for different mysqld servers. And make sure there is enough free disk space for all the backups. Also, is it possible to run several backups at one time. I am using MySQL 4.1.x on a Solaris system. Yes, you can run several instances of ibbackup at the same time. Thanks, Alan Fisher Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem INNODB error 995
Osvaldo, Osvaldo Sommer wrote: Heikki: All servers have the auto upgrade on, so all the windows upgrade has been install. this is interesting. Error 995 might then be a hardware problem. We have a raid 5 on all servers. Could that give a random error? We can try a give a low format to the disks. Osvaldo Sommer Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Lunes, 06 de Marzo de 2006 11:50 p.m. To: mysql@lists.mysql.com Subject: Re: Problem INNODB error 995 Osvaldo, - Original Message - From: Osvaldo Sommer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 12:58 AM Subject: Problem INNODB error 995 --Boundary_(ID_PMYElD1sU13Il0ENO4J+aw) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit We have 4 windows 2003 server with mysql 4.0.12 install in each one. They have been working for about 1.5 years with no problem. Two weeks ago, in one server we lost the mysql service, and when we look the .err file it report a 995 error. We can star the service again and it works. In the two weeks the service has stop in the same server about 10 times, but none in the other server. We have the same applications working in each server. What can we do to stop the error? We even thought that we have a problem with windows, so we reinstall it, but the problem continue. Please help http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/ system_error_codes__500-999_.asp ERROR_OPERATION_ABORTED 995 The I/O operation has been aborted because of either a thread exit or an application request a few InnoDB users have reported this error number in the past 2 years. I have suspected that it is some bug in Windows or its device drivers, since InnoDB does not request aborts of I/O requests, nor do InnoDB's threads exit in the middle of an I/O operation. Did you upgrade the OS in that server before you started getting this error? Is the server identical to the other servers where mysqld works ok? The error might actually be a hardware problem. I have noticed that a hardware fault can produce strange error numbers in Linux. The same might hold for Windows. Osvaldo Sommer Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem INNODB error 995
Osvaldo, - Original Message - From: Osvaldo Sommer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 12:58 AM Subject: Problem INNODB error 995 --Boundary_(ID_PMYElD1sU13Il0ENO4J+aw) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit We have 4 windows 2003 server with mysql 4.0.12 install in each one. They have been working for about 1.5 years with no problem. Two weeks ago, in one server we lost the mysql service, and when we look the .err file it report a 995 error. We can star the service again and it works. In the two weeks the service has stop in the same server about 10 times, but none in the other server. We have the same applications working in each server. What can we do to stop the error? We even thought that we have a problem with windows, so we reinstall it, but the problem continue. Please help http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes__500-999_.asp ERROR_OPERATION_ABORTED 995 The I/O operation has been aborted because of either a thread exit or an application request a few InnoDB users have reported this error number in the past 2 years. I have suspected that it is some bug in Windows or its device drivers, since InnoDB does not request aborts of I/O requests, nor do InnoDB's threads exit in the middle of an I/O operation. Did you upgrade the OS in that server before you started getting this error? Is the server identical to the other servers where mysqld works ok? The error might actually be a hardware problem. I have noticed that a hardware fault can produce strange error numbers in Linux. The same might hold for Windows. Osvaldo Sommer Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
Daevid, - Original Message - From: Daevid Vincent [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 5:54 AM Subject: RE: Boolean searches on InnoDB tables? Osku is working on FULLTEXT for InnoDB. So, despite what the documentation says: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html Which states: They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. You're saying that InnoDB tables cannot use a BOOLEAN search at all, no way, no how? hmm... I have not heard that a MATCH ... AGAINST query in MySQL could work without a FULLTEXT index. If you try the query on a MyISAM table that does NOT have a FULLTEXT index, does it work there? At the moment I only have a small db while I'm building the product, so slow is going to be relative at this point (ie. fast), and I would expect it to converge when you get FULLTEXT working in InnoDB. I would like to get the code in there though for now. Do you have an estimate timeframe till this is implemented? Weeks? Months? Version? Sorry, no. You could look at Sphinx, for exapmple. What is Sphinx? I did a quick google search but found many references to pyramids and other projects (popular name). Do you have a URL for this? http://www.shodan.ru/projects/sphinx/ Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
Daevid,
- Original Message -
From: Daevid Vincent [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, March 04, 2006 9:54 AM
Subject: Boolean searches on InnoDB tables?
I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
They can work even without a FULLTEXT index, although a search executed
in
this fashion would be quite slow.
But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Full-text indexes can be used only with MyISAM tables
When I try a query on an InnoDB table:
SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);
I get:
Error Code : 1214
The used table type doesn't support FULLTEXT indexes
So, what is the deal? Am I missing something?
And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?
Osku is working on FULLTEXT for InnoDB.
In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.
You could look at Sphinx, for exapmple.
Best regards,
Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing Isolation in MySQL database
Vinay, - Original Message - From: Vinay [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 02, 2006 12:20 AM Subject: Enforcing Isolation in MySQL database --=_NextPart_000_002C_01C63D51.B51315D0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello , I suppose the InnoDB tables in Mysql have REPEATABLE-READ as = the default isolation. Is the concurrency enforced automatically by the = database or is there anything=20 the application programmer has to do to make sure the data consistent. How does the REPEATABLE-READ resolve the following scenario. User1 opens a application and reads a row on the screen. User2 opens the same application and read the same row on the screen. User2 updates a field (and commits ,as the autocommit is on), User1 still is reading the non-updated row , and updates the same field = updated by the User2 to different value. How does it affect the database when normal select , update queries are = used. you should use SELECT ... FOR UPDATE in this case. A normal plain SELECT in InnoDB does not lock the rows it reads. What should the application programmer to enforce database concurrency. Thank you, Vinay Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why do these transactions show table locks?
Robert, - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 26, 2006 8:27 PM Subject: RE: Why do these transactions show table locks? It might be important to note that I have a delete trigger on the ELEMS table, also, this INSERT call is being made from a stored procedure. The stored procedure only has one line, this INSERT statement. Could this have anything to do with bug# 16229? http://bugs.mysql.com/bug.php?id=3D16229=20 yes, I think this is: http://bugs.mysql.com/bug.php?id=16229 which is fixed in 5.0.19. I just tested that an ordinary INSERT in 5.0.18 does not use full explicit table locks in InnoDB. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 9:33 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Why do these transactions show table locks? My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- 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: error 1016 : cant open ibd file even though it exists
Rithish, - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 27, 2006 7:06 AM Subject: RE: error 1016 : cant open ibd file even though it exists Heikki. I found the ibdata1 and ib_log files located in only '/var/lib/mysql' which is defined as the mysql home folder. The last time my.cnf was editied was on the 9th of Dec, and the database was created somewhere in the month of January. So the database would have been created with the same configurations. Thankfully, I have a dump of the original database. Looks like I have to restore all of the 35 GB of data again. then this remains a mystery. InnoDB never deletes ibdata files or ib_logfiles by itself. Regards, Rithish. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 4:05 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, ok, then the most probable explanation is that someone had edited my.cnf earlier, when mysqld was running. When mysqld was restarted, it read the new my.cnf, and got confused. Please ask your sysadmins to scan the file system of the computer if they can find another ibdata1 file or ib_logfiles. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 25, 2006 12:14 PM Subject: RE: error 1016 : cant open ibd file even though it exists --=_NextPart_000_001D_01C63A22.BB0C91A0 Content-Type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 7bit Hello David. There was supposed to be a power outage in our office that day. So the server was shut down. Finally when the power was back, the machine was plugged on. That's all. No file system change. Nothing. Regards, Rithish. -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 3:32 PM To: Rithish Saralaya Cc: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Hi Rithish, After reading Heikkis points, I am inclined to agree. Did your sysadmins change a filesystem during the maintenance? Did they forget to restore a directory if they changed disks? What was the maintenance that was performed? Your InnoDB files disappeared at some point because the server would not have recreated them otherwise. I am sure it was a graceful shutdown, but something has changed. These things just don't happen by themselves. Regards Rithish Saralaya wrote: The mysql server was shut down for maintenance. However it was a graceful shutdown and restart. That's all. No files were touched or removed. How could this have happened, I fail to see. Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 3:55 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number
Re: error 1016 : cant open ibd file even though it exists
Rithish, ok, then the most probable explanation is that someone had edited my.cnf earlier, when mysqld was running. When mysqld was restarted, it read the new my.cnf, and got confused. Please ask your sysadmins to scan the file system of the computer if they can find another ibdata1 file or ib_logfiles. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 25, 2006 12:14 PM Subject: RE: error 1016 : cant open ibd file even though it exists --=_NextPart_000_001D_01C63A22.BB0C91A0 Content-Type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 7bit Hello David. There was supposed to be a power outage in our office that day. So the server was shut down. Finally when the power was back, the machine was plugged on. That's all. No file system change. Nothing. Regards, Rithish. -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 3:32 PM To: Rithish Saralaya Cc: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Hi Rithish, After reading Heikkis points, I am inclined to agree. Did your sysadmins change a filesystem during the maintenance? Did they forget to restore a directory if they changed disks? What was the maintenance that was performed? Your InnoDB files disappeared at some point because the server would not have recreated them otherwise. I am sure it was a graceful shutdown, but something has changed. These things just don't happen by themselves. Regards Rithish Saralaya wrote: The mysql server was shut down for maintenance. However it was a graceful shutdown and restart. That's all. No files were touched or removed. How could this have happened, I fail to see. Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 3:55 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB
Re: error 1016 : cant open ibd file even though it exists
Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 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 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY
Re: error 1016 : cant open ibd file even though it exists
Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dropping a database to reclaim space
Luke, if you do not have ANY valuable InnoDB tables in the installation, you can simply delete the ibdata files and ib_logfiles. Be very careful if you have several MySQL instances in the same computer. As Sheeri wrote, MySQL's system tables 'user.MYD' etc. are MyISAM tables in the 'mysql' database directory. They are not InnoDB type. In the future, you may want to put the option: innodb_file_per_table to your my.cnf. Then InnoDB tables are stored in .ibd files in individual database directories, and dropping those tables does free the disk space to the operating system. The ibdata files never shrink. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php Hi. I have a database that is fairly large and I'm doing some testing with 2 different mysql packages, one 32-bit, one 64bit, for comparison's sake. Now I've finished with one of the dbs and I want to reclaim the disk space. I've tried deleting a few (large) tables from the database, thinking this would free up space, however the mysql/var/ibdata1 file doesn't change in size. How can I get rid of a table or even complete database to reclaim disk space? I don't really want to delete the ibdata1 file, because that will also delete the system database right? I've already tried drop database and that doesn't show reduced disk space either. I'm doing this on a solaris 9 box. Any input appreciated. Thanks. Kind regards. -- Luke Thread dropping a database to reclaim space - Luke Vanderfluit, February 22 2006 3:48am Re: dropping a database to reclaim space - sheeri kritzer, February 22 2006 6:32pm RE: dropping a database to reclaim space - Robert DiFalco, February 22 2006 5:14pm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock wait timeout exceeded during concurrent inserts on an InnoDB table....
Robert, please post SHOW INNODB STATUS\G during such lock wait, so that we see what lock it is waiting for. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 20, 2006 8:00 PM Subject: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Concurrent inserts (there also may be concurrent reads going on) are intermittently causing: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction=20 I noticed that adding innodb_table_locks=3D0 in my.ini fixes the = problem. Looking through the manual however, this should only be a problem when I am using LOCK_TABLE, but there is no where in my code that I use this. Do some queries implicitly add LOCK_TABLE? Is there a way to disable innodb_table_locks when I create my database or in some JDBC property so I don't require users to modify their my.ini files? FWIW, I'm guessing this is a bug; i.e. hat innodb_table_locks controls=20 more than just the locking behavior of an explicit LOCK TABLE. R. -- 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: Lock wait timeout exceeded during concurrent inserts on an InnoDB table....
Robert, maybe it was waiting on the AUTO-INC lock of the table? InnoDB must lock the auto-inc counter, otherwise the MySQL replication would not work. That is a limitation imposed by the MySQL architecture, not by InnoDB. InnoDB itself never needs table locks. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 20, 2006 11:20 PM Subject: RE: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Ok, I will do that during my next test run. But in the meantime, when I did it previously, it was oddly enough waiting for a table lock on the table that I was inserting into. R.=20 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, February 20, 2006 1:18 PM To: mysql@lists.mysql.com Subject: Re: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Robert, please post SHOW INNODB STATUS\G during such lock wait, so that we see what lock it is waiting for. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 20, 2006 8:00 PM Subject: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Concurrent inserts (there also may be concurrent reads going on) are=20 intermittently causing: java.sql.SQLException: Lock wait timeout exceeded; try restarting=20 transaction=3D20 I noticed that adding innodb_table_locks=3D3D0 in my.ini fixes the =3D = problem. Looking through the manual however, this should only be a problem when I am using LOCK_TABLE, but there is no where in my code that I use this. Do some queries implicitly add LOCK_TABLE? Is there a way to disable=20 innodb_table_locks when I create my database or in some JDBC property=20 so I don't require users to modify their my.ini files? FWIW, I'm guessing this is a bug; i.e. hat innodb_table_locks=20 controls=3D20 more than just the locking behavior of an explicit LOCK TABLE. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] =20 -- 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: MySql InnoDB
Hi! - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, February 14, 2006 5:50 PM Subject: MySql InnoDB Hi, I'v installed MySql on my machine and created a new tables. when i open some table to alter it,i see in the COMMENT textbox: InnoDB free: 3072 kB what doe's it mean? it's mean that i only have 3072kb free for a given table or what? If you are not using innodb_file_per_table, then the value 3072 kB means that in ibdata files you have that amount of space free for adding more data to your tables. To be precise, there are three 1 MB 'extents' available for extending your tables. In addition, there may be individual 16 kB 'fragment pages' available, but they are not listed in the printout, for simplicity. If you are using innodb_file_per_table, then the value 3072 kB means that you have that much free space in the .ibd file of that table, for extending the table with those 1 MB extents. Thank's a lot. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client is starting to sue, Help me on this bug http://bugs.mysql.com/bug.php?id=15868
Ady, I replied to the bug report. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Dan Trainor [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, February 15, 2006 9:08 PM Subject: Re: Client is starting to sue, Help me on this bug http://bugs.mysql.com/bug.php?id=15868 Ady Wicaksono wrote: Dear Mr. Heikki and Team, Currently we hit by this bug http://bugs.mysql.com/bug.php?id=15868 It got worse on MySQL5 rather than MySQL4, since i failed to downgrade MySQL. Please give me idea to minimize the impact My client is very dissapointed with this issue Thank you very much Hi - I'm sorry, maybe I don't correctly understand the extent of the issue at hand. Is it just the test that is failing? Is it possible that the test is wrong, and you are just seeing erroneous results? Thanks -dant -- 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 per-table-space backup and move
Brandon, sorry, you cannot move .ibd files between installations. The ability to do so is in the TODO, but I cannot promise any release date. Currently, the only way to move individual InnoDB tables between installations is the dump + import method. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: HOTorNOT Tech [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 11, 2006 3:10 AM Subject: InnoDB per-table-space backup and move Hi, We'd like to be able to move certain innodb databases between machines (some, but not all). This would help greatly with resyncing slaves and for fast backups. I know that we can create table-spaces on a per-table basis as described here. http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html It also says that we cannot freely move tables between database directories like in MyISAM, but we can restore backups of .ibd files. Is there any way to move the .ibd files from one machine to another? Is there dependency on the shared tablespace and the corresponding .ibd file? i.e. can we copy a .ibd file to another machine and import tablespace on it? Any input would be appreciated. thanks! Brandon -- 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 locking
Patrick, it should work. You have only shown a fragment of the application code. Maybe there is a bug somewhere else. If you write a very simple test program to test this, do you still get the duplicate values? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Patrick Duda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 10, 2006 9:01 PM Subject: Re: InnoDB and locking At 12:54 PM 2/10/2006, Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Patrick Duda wrote: I guess I don't understand this locking stuff. I have a InnoDB table that has one thing in it, a counter. All I want to do is have multiple instances of the code read this counter and increment it. I want to make sure that each one is unique. Here is what I am doing in java: c.setAutoCommit(false); ... rs = statement.executeQuery(select request_id from requestid_innodb for update); ... String updateQuery = update requestid_innodb set request_id=; updateQuery = updateQuery + nextRequestId; tempStatement = c.createStatement(); tempStatement.executeUpdate(updateQuery); ... c.commit(); c.setAutoCommit(true); If I have multiple instances of this code running I end up with duplicate keys. I thought this was suppose to lock the table so that would not happen. What am I not doing right? What am I not understanding about locking? Thanks Patrick Patrick, Are you sure the table is using the InnoDB storage engine? What does the output of SHOW CREATE TABLE for the table in question say? -Mark ysql show create table requestid_innodb; +--+-+ | Table| Create Table | +--+-+ | requestid_innodb | CREATE TABLE `requestid_innodb` ( `request_id` int(11) NOT NULL default '0', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--+-+ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help configuring INNODB (Customer is ready to sue)
Shaun, the my.cnf looks ok. You might be able to raise the InnoDB buffer pool size to 3G, but beware swapping. SHOW INNODB STATUS looks ok, though it would be more informative if it were taken during a typical workload. Free buffers 0 Having free buffers 0 is very normal. Buffers are replaced using the Least Recently Used strategy. What problems does the customer encounter? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Shaun Adams [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 10, 2006 5:58 AM Subject: Need help configuring INNODB (Customer is ready to sue) --=_NextPart_000_00F7_01C62DCC.4BFFEE40 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I have a customer who has been in production for a few weeks now having converted from MyISM to INNODB. We have been experiencing a few problems with our application. With that said, could you take a look at the info below from my show INNODB status and let me know if you see any problems based off of the my.cnf configuration. Should I be worried about the free buffers being at 0 Also, if you have any suggestions for modifying my.cnf, please let me know. Server is a Dell 1850, Dual P4 Processor with HT, RHEL4 64-bit, 4GB Memory. The only thing we have running on the server is MySQL v 4.0.26 (upgrading is not an option right now). The web is on another server. Our programmers are working on examining the code and queries. 060209 21:52:39 INNODB MONITOR OUTPUT Per second averages calculated from the last 58 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 32909, signal count 32897 Mutex spin waits 22210, rounds 96667, OS waits 3131 RW-shared spins 59775, OS waits 29579; RW-excl spins 122, OS waits 80 TRANSACTIONS Trx id counter 0 12549 Purge done for trx's n:o 0 12265 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, process no 7200, OS thread id 1147570528 MySQL thread id 40, query id 32699 localhost root show INNODB status 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 10041 OS file reads, 98838 OS file writes, 28068 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 65 inserts, 65 merged recs, 63 merges Hash table size 4980539, used cells 3352510, node heap has 8217 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2288366733 Log flushed up to 1 2288366733 Last checkpoint at 1 2288366733 0 pending log writes, 0 pending chkp writes 11189 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 2761264906; in additional pool allocated 6054656 Buffer pool size 153600 Free buffers 0 Database pages 145383 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 49486, created 458759, written 822494 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 7200, id 1147169120, state: waiting for server activity Number of rows inserted 19473761, updated 1063, deleted 0, read 6117388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s MY. CNF # MySQL Server Instance Configuration File [mysqld] port=3306 socket=/tmp/mysql.sock basedir=/usr/local/mysql datadir=/var/lib/mysql default-character-set=latin1 lower_case_table_names=1 server-id=101 #log-slow-queries #long_query_time=1 #log max_allowed_packet=128M skip-bdb max_connections=100 thread_concurrency=10 open_files_limit=512 table_cache=512 thread_cache=20 query_cache_size=64M # replication #log-bin=/var/lib/mysql/binlog #server-id=90 #sync_binlog=1 # thread buffers read_buffer_size=128K read_rnd_buffer_size=256K
Re: Surviving MySQL crash
Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Foo Ji-Haw [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 10, 2006 7:05 AM Subject: Surviving MySQL crash Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- 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: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
Jan, if you make the InnoDB buffer pool big enough to hold all your data, or at least all the 'hot data', and set ib_logfiles large as recommended at http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html, then InnoDB performance should be quite close to MEMORY/HEAP performance for small SQL queries. If all the data is in the buffer pool, then InnoDB is essentially a 'main-memory' database. It even uses automatically built hash indexes. This assumes that you do not bump into extensive deadlock issues. Deadlocks can occur even with single row UPDATEs if you update indexed columns. Setting innodb_locks_unsafe_for_binlog will reduce deadlocks, but read the caveats about it. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 31, 2006 1:09 PM Subject: Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster? Hi, I am currently experiencing trouble getting my new mysql 5-servers running as slaves on my old 4.1.13-master. Looks like I'll have to dump the whole 30GB-database and import it on the new servers :( At this moment I do no see any oppurtunity to do this before the weekend since the longest time I can block any of our production systems is only 2-3 hours between midnight and 2am :( I am still curious if Innodb could handle the load of my updates on the heavy-traffic-tables since its disk-bound and does transactions. What I would probably need is an in-memory-table without any kind of locking - at least not table-locks! But there is no such engine in mysql. When a cluster can handle that (although it has the transaction-overhead) it would probably be perfect for since it even adds high availability in a very easy way... Jan Jan Kirchhoff schrieb: sheeri kritzer schrieb: No problem: Firstly, how are you measuring your updates on a single table? I took a few binary logs, grepped out for things that changed the table, counting the lines (using wc) and then dividing by the # of seconds the binary logs covered. The average for one table was 108 updates per second. I'm very intrigued as to how you came up with 2-300 updates per second for one table. . . did you do it that way? If not, how did you do it? (We are a VERY heavily trafficked site, having 18,000 people online and active, and that accounts for the 108 updates per second. So if you have more traffic than that. . .wow!) Thanks for your hardware/database information. I will look at that close tomorrow since I want to go home for today - it's already 9 pm over here... I need beer ;) We are not running a webservice here (actually we do, too, but thats on other systems). This is part of our database with data of major stock exchanges worldwide that we deliver realtime data for. Currently that are around 900,000 quotes, during trading hours they change all the time... We have much more updates than selects on the main database. Our Application that receives the datastream writes blocks (INSERT ... ON DUPLICATE KEY UPDATE...) with all records that changed since the last write. It gives me debug output like [timestamp] Wrote 19427 rows in 6 queries every 30 seconds - and that are numbers that I can rely on. Jan -- 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: Insert performance
Hi! All this was already mentioned in the discussion thread, but I summarize it. To make the InnoDB inserts to run as fast as possible: 1) Tune the buffer pool size and ib_logfile size as recommended at: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html 2) Set: innodb_flush_log_at_trx_commit=2 for the time of the import. Alternatively, you can wrap the inserts of, say, 1000 rows inside BEGIN ... COMMIT, so that the log does not get flushed to disk after each individual insert. 3) SET FOREIGN_KEY_CHECKS = 0; do the import ; SET FOREIGN_KEY_CHECKS = 1; But make sure your data does not break foreign key constraints! Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 31, 2006 8:01 PM Subject: Re: Insert performance --=_alternative 0062A2DC85257107_= Content-Type: text/plain; charset=US-ASCII Imran Chaudhry [EMAIL PROTECTED] wrote on 01/31/2006 12:44:17 PM: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Just thought I'd add a little to the good advice already given to you. I find an easy way to derive the extended insert syntax is to perform a mysqldump of a small table. From 4.1 onwards this wrapped the table dump in the extended insert syntax. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services The only drawback to doing that for really large tables is you have to provide mysqldump with the --max_allowed_packet value for the server you want to send the data to. If you don't, mysqldump will make a single HUMONGOUS extended insert statement that will be refused by the destination server. I found this out the hard way a long time ago. For example: If on server A your max_allowed_packet value is 4193280... serverAshow variables like 'max%'; +++ | Variable_name | Value | +++ | max_allowed_packet | 4193280| | max_binlog_cache_size | 4294967295 | | max_binlog_size| 1073741824 | | max_connect_errors | 10 | | max_connections| 100| | max_delayed_threads| 20 | | max_error_count| 64 | | max_heap_table_size| 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length| 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | +++ 17 rows in set (0.00 sec) ...and the data you want to load comes from server B then you have to tell mysqldump to use the value 4193280 or it will make just one large extended INSERT statement per table, regardless of table size \mysql\bin mysqldump -u login -p -h serverB --max_allowed_packet=4193280 sourcedatabasename Of course there are other options you need to worry about too (like quoting and compressing) but you get the picture. You can read about them in the fine manual or check the --help option of mysqldump. mysqldump --help Shawn Green Database Administrator Unimin Corporation - Spruce Pine --=_alternative 0062A2DC85257107_=-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Patrick, people have created files at least up to 500 GB using InnoDB's auto-extend feature. What does: ulimit -a say about the 'file size' of the user running mysqld? Have you put some disk space quotas on the directories of the MySQL datadir? Please correct me if I am wrong, but I think one can restrict how much disk space a directory can use in Linux. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Patrick Herber [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, January 15, 2006 4:16 PM Subject: RE: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18 Thanks a lot for your answer! However, when I used the option innodb_file_per_table I saw that the = temp file (#sql...) was created in my DB directory and on this partition I = still have plenty of space (more than 200GB). Do you think I CAN'T use this option for such a big table and I have to = use innodb_data_file_path? Thanks a lot and regards, Patrick -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED] Sent: Sunday, 15 January 2006 15:09 To: Patrick Herber Cc: mysql@lists.mysql.com Subject: Re: ERROR 1114 (HY000): The table is full converting=20 a big table from MyISAM to InnoDB on 5.0.18 =20 Hi, =20 I think you should change the tmpdir variable value to a=20 directory which have enough room to create your temp big table (by default,=20 it points to /tmp dir). =20 Regards, Jocelyn =20 Patrick Herber a =E9crit : Hello! I have a database with a big table (Data File 45 GB, Index=20 File 30 GB).=20 Since I have some performance troubles with table-locking in a=20 multi-user environment (when one of them performs a complex=20 query all=20 the other have to wait up to 1 minute, which is not very=20 nice...), I=20 would like to convert this (and other tables) into InnoDB engine. =20 I first tried using the innodb_file_per_table option but=20 when running=20 the statement =20 ALTER TABLE invoice ENGINE=3DINNODB; =20 ERROR 1114 (HY000): The table '#sql...' is full =20 (this about one our after the start of the command, when=20 the size of=20 the file was bigger than ca. 70GB (I don't know exactly the size)) =20 I tried then without the innodb_file_per_table option, setting my=20 innodb_data_file_path as follows: =20 =20 = innodb_data_file_path=3Dibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:5 00M;ib=20 =20 data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata1 0:500M :autoextend =20 Also in this case I got the same error message. =20 What should I do in order to convert this table? =20 Should I set in the innodb_data_file_path for example 50=20 Files, each=20 big 4GB ? =20 Thanks a lot for your help. =20 Best regards, Patrick =20 PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server. =20 =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http://lists.mysql.com/[EMAIL PROTECTED] =20 -- 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: Mysql Server crashes saying Page directory corruption
Ravi, please open a bug report at http://bugs.mysql.com and attach the COMPLETE UNEDITED .err log to that bug report. Looks like InnoDB accesses a page filled with zeros. This might be a hardware fault or an OS bug, as well as an InnoDB bug. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Ravi Prasad LR [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 11, 2006 2:13 PM Subject: Mysql Server crashes saying Page directory corruption Hi all, Server version :4.0.26 OS: FeeBSD-4.10 The mysql server(slave) crashes with the following messages in its error log, and gets restarted, after which rollbacks and starts replicating from master Page directory corruption: supremum not pointed to 051226 10:02:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex .(many 0's) ;InnoDB: End of page dump 051226 10:02:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer 0xc9c1bff8 InnoDB: buf pool start is at 0x52a4000, number of pages 70400 051226 10:02:58InnoDB: Assertion failure in thread 20491 in file ../../innobase/page/../include/buf0buf.ic line 286 InnoDB: We intentionally generate a memory trap InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; it is a slave server replicating from a master. There are no reads happening while server crashed. This has happened couple of times. we tried restoring mysql with a backup of master. But it goes through fine for few days(2-3 days) , after which the same error reiterated. what does this error mean? Please help me in figuring out this error. Thanks , Ravi -- 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: allocate space for innodb innodb_file_per_table
George, - Original Message - From: George Law [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, January 08, 2006 10:10 PM Subject: allocate space for innodb innodb_file_per_table Hi All, I am running mysql 5.0.18 with a innodb table of 9 GB (plus several others) on suse linux 9.3 with 4 GB ram. when doing a show table status command, this table shows Data_Free:0 I assume this is because it is the file per table setting, where the tables fall outside of the main ibdata files. Data_free = 0 in this case means that the whole .ibd file is currently allocated for that table. If you delete many rows from the table, you may see Data_free bigger than 0. The ibdata files are all preallocated when mysql starts up, is there anyway to preallocate the file per table files as well? No. Is there a specific innodb list? There is an InnoDB forum: http://forums.mysql.com/list.php?22 Thanks to Gleb's answer and the hint on the bulk load setting in my.cnf - that seems to have helped some what. I notice in the load data command there is a CONCURRENT option - but as far as I can tell, that only does MyISAM tables? You also suggested disablign the keys. Disabling keys has no effect on InnoDB type tables. I think I tried that, but with a table with 7 mil rows, that takes longer than the load data :-\ TIA. George Law Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Lock issue on insert
John, this SQL statement: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; sets locks on all the records it scans in TV_LOG_ALL. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. The default size of the buffer pool is just 8 MB. Fix: modify my.cnf and increase innodb_buffer_pool_size. But do not make it bigger than about 70 % of your computer's RAM. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Danny Stolle [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 02, 2006 9:31 PM Subject: Re: Table Lock issue on insert Hi John, I have looked around a bit and you might be interested in this part: InnoDB: Do not intentionally crash mysqld if the buffer pool is exhausted by the lock table; return error 1206 instead ... check this link: http://dev.mysql.com/doc/refman/4.1/en/news-4-1-8.html it is quite interesting and i am trying to get this error on my mysql databases by changing the bufferpool and inserting a lot of data; Best regards, Danny Brittingham, John wrote: They are InnoDB and max_write_lock_count=4294967295. The same thing happens when I create a copy of the table. -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Saturday, December 31, 2005 2:59 AM To: Brittingham, John; mysql@lists.mysql.com Subject: Re: Table Lock issue on insert Hi John, What kind of engine are you using on your table? MyIsam or InnoDB or are you using merged tables? If you query your system variables what is your max_write_lock_count? If you create a copy of the table: mysqlcreate table cp1 like USERS_PER_HOUR; and you try the insert again; is the error gone? Danny Brittingham, John wrote: I am having trouble with table lock. The query is as follows: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; I keep getting this error: #1206 - The total number of locks exceeds the lock table size How do I fix this? -- 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: removing ibdata1 if some/all tables are not InnoDB?
Carl, InnoDB does purge deleted rows from the ibdata files. Certain PostgreSQL advocates have been spreading a claim that InnoDB would not do that, but the claim is false. If your ibdata file keeps growing indefinitely, please check with SHOW INNODB STATUS that you do commit all your transactions. If a transaction stays open for months, then the purge cannot remove deleted rows. If you convert ALL your tables from InnoDB to MyISAM, then you can remove the ibdata files and ib_logfiles. If you put skip-innodb to my.cnf, then those files will not be created again. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 03, 2006 6:55 AM Subject: Re: removing ibdata1 if some/all tables are not InnoDB? HI Carl, The ibdata file growth can be stopped by removing the autoextend keyword in the my.cnf file. In your my.cnf file the entry might be innodb_data_file_path = ibdata1:256M:autoextend If you want to stop the growth of that file and add another file then this is what you want to do. 1. Stop the mysql server 2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size) 3. edit the my.cnf file and replace innodb_data_file_path = ibdata1:256M:autoextend with innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend 4. Start the server. This will stop that file from growing and a new file will be added that can pushed on to a different disk and symlinked into the ibdata directory. Data growth is a problem in all table types. Even if you migrate to MyISAM you need space. See whether there is log_bin turned on the server. If so there might be lots of bin log files that you can do a cleanup on. Bin logs occupy a great deal of space. Thanx Alex, MySQL DBA Yahoo! On Tue, 03 Jan 2006 08:28:24 +0530, Carl Brewer [EMAIL PROTECTED] wrote: Hello, I'm stuck with a rapidly decreasing amount of available disk space and a requirement to keep a lid on the size of our databases. We're using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of transactions keeping short term track of webserver sessions, which we don't need to keep logs of for very long. I have a number of databases, almost all of which are using MyISAM or HEAP, and one database using InnoDB. As such (or at least, as I understand it) we have a ibdata1 file that will grow forever and AFAIK there's no way to stop it growing forever for as long as we have that InnoDB database. Am I correct? I'm no MySQL guru, my parsing of TFM and googling around and finding bug and feature requests for ibdata1 purging suggests that this is the case. If so, if I drop the InnoDB database, stop mysqld, delete (UNIX filesystem) the imdata1 file, restart mysqld and import a (modified to be MyISAM) dumped copy of the InnoDB database, will that work without damaging anything and then not leave me with another infinatly growing imdata1 file? Am I correct in assuming that InnoDB databases are meant for sites where disk space is not ever likely to be an issue, and MyISAM is a more suitable database engine for our much tighter disk space situation? I may have missed a section of the doco that discusses why one would choose an engine over another? Thanks for any advice, Carl -- 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: CPU 100% + crashes ...
Dilipan,
indeed, there was a race in the printing of thd-query in 4.0.18. But the
problem was probably fixed in May 2004.
Best regards,
Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php
5.0.16, ha_innodb.cc:
...
if ((s = thd-query)) {
/* 3100 is chosen because currently 3000 is the maximum
max_query_len we ever give this. */
charbuf[3100];
uintlen;
/* If buf is too small, we dynamically allocate storage
in this. */
char* dyn_str = NULL;
/* Points to buf or dyn_str. */
char* str = buf;
if (max_query_len == 0)
{
/* ADDITIONAL SAFETY: the default is to print at
most 300 chars to reduce the probability of a
seg fault if there is a race in
thd-query_length in MySQL; after May 14, 2004
probably no race any more, but better be
safe */
max_query_len = 300;
}
len = min(thd-query_length, max_query_len);
if (len (sizeof(buf) - 1))
{
dyn_str = my_malloc(len + 1, MYF(0));
str = dyn_str;
}
/* Use strmake to reduce the timeframe for a race,
compared to fwrite() */
len = (uint) (strmake(str, s, len) - str);
putc('\n', f);
fwrite(str, 1, len, f);
if (dyn_str)
{
my_free(dyn_str, MYF(0));
}
...
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 16, 2005 3:46 PM
Subject: Re: CPU 100% + crashes ...
Dilipan,
thank you.
I think there indeed is a slight unprotected access in:
0x8158a17 innobase_mysql_print_thd + 471
We will investigate if it has been fixed in 4.1.xx.
Again, SHOW INNODB STATUS\G shows a very light load inside InnoDB. You
would get more informative output if you would wait 20 seconds before
printing each SHOW INNODB STATUS\G. Now it says:
Per second averages calculated from the last 0 seconds
But the high load hardly can be inside InnoDB. Please post SHOW
PROCESSLIST during typical high load.
Best regards,
Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php
Dilipan Sebastiampillai wrote:
can you please post the complete .err log that also contains
information about the crashes. If there are stack traces, please
resolve them.
-
.err
-
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=8388608
read_buffer_size=520192
max_used_connections=148
max_connections=1000
threads_connected=21
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 1540184 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x6f405da0
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=0x1978f88, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80f7893
0x6b4d96
0x82ab930
0x8158a17
0x82302c1
0x819f373
0x815d4e2
0x8105657
0x810868d
0x8103321
0x8102eb8
0x810280d
0x6afe51
0x51e06a
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 0xa633050 = SHOW INNODB STATUS
thd-thread_id=6289685
The manual page
Re: CPU 100% + crashes ...
Dilipan, thank you. I think there indeed is a slight unprotected access in: 0x8158a17 innobase_mysql_print_thd + 471 We will investigate if it has been fixed in 4.1.xx. Again, SHOW INNODB STATUS\G shows a very light load inside InnoDB. You would get more informative output if you would wait 20 seconds before printing each SHOW INNODB STATUS\G. Now it says: Per second averages calculated from the last 0 seconds But the high load hardly can be inside InnoDB. Please post SHOW PROCESSLIST during typical high load. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php Dilipan Sebastiampillai wrote: can you please post the complete .err log that also contains information about the crashes. If there are stack traces, please resolve them. - .err - 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=8388608 read_buffer_size=520192 max_used_connections=148 max_connections=1000 threads_connected=21 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1540184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x6f405da0 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=0x1978f88, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80f7893 0x6b4d96 0x82ab930 0x8158a17 0x82302c1 0x819f373 0x815d4e2 0x8105657 0x810868d 0x8103321 0x8102eb8 0x810280d 0x6afe51 0x51e06a 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 0xa633050 = SHOW INNODB STATUS thd-thread_id=6289685 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. 051214 19:09:08 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 1 2404815205 InnoDB: Doing recovery: scanned up to log sequence number 1 2410057728 InnoDB: Doing recovery: scanned up to log sequence number 1 2415300608 InnoDB: Doing recovery: scanned up to log sequence number 1 2420543488 InnoDB: Doing recovery: scanned up to log sequence number 1 2425786368 InnoDB: Doing recovery: scanned up to log sequence number 1 2431029248 InnoDB: Doing recovery: scanned up to log sequence number 1 2436272128 InnoDB: Doing recovery: scanned up to log sequence number 1 2441515008 InnoDB: Doing recovery: scanned up to log sequence number 1 2446757888 InnoDB: Doing recovery: scanned up to log sequence number 1 2452000768 InnoDB: Doing recovery: scanned up to log sequence number 1 2457243648 InnoDB: Doing recovery: scanned up to log sequence number 1 2462486528 InnoDB: Doing recovery: scanned up to log sequence number 1 2463763843 051214 19:09:10 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 051214 19:09:17 InnoDB: Flushing modified pages from the buffer pool... 051214 19:09:18 InnoDB: Started /var/lib/mysql/MySQL4.0.18/libexec/mysqld: ready for connections. Version: '4.0.18-log' socket: '/var/lib/mysql/MySQL4.0.18/var/mysql4.0.18.sock' port: 3306 051215 8:40:46 /var/lib/mysql/MySQL4.0.18/libexec/mysqld: Normal shutdown 051215 8:40:47 InnoDB: Starting shutdown... 051215 8:40:50 InnoDB: Shutdown completed 051215 8:40:50
Re: CPU 100% + crashes ...
Dilipan, can you please post the complete .err log that also contains information about the crashes. If there are stack traces, please resolve them. The workload inside InnoDB does not look that big if what you have posted is a typical SHOW INNODB STATUS\G. Is that the case? What does 'top' say? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Dilipan Sebastiampillai [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 15, 2005 2:04 PM Subject: CPU 100% + crashes ... Hi all, We have a MySQL server used for a queueing system. It worked fine so far but now we are experiencing some load problems couple with crashes. The load ( around 5-7 ) is due, according to vmstat, to processes waiting for the CPU. And it makes sense because the CPU is used 100% most of the time. Nearly all tables are Innodb ( 3 tables of 1kb are MyIsam ). Around 500 machines open a connection, make a query and disconnect. How can we tune MySQL so that it stops crashing and stays at a reasonnable load? Your help would be greatly appreciated. Dilipan FOLLOWING: system info my.cnf show status show innodb status - SYSTEM INFO - OS : Red Hat Enterprise Linux ES release 3 RAM : 4 Gig Hard disk : 15k rpm CPU : 2 x Intel Xeon 3.60GHz CPU with 1M cache (Hyperthreading Enabled) server version: 4.0.18-log ( I know it's old but we can't update for the moment ) database size : 1.9Gig - MY.CNF - [mysqld] skip-locking skip-grant-table # If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookup with #skip-name-resolve log-error log-slow-queries set-variable= join_buffer_size=256K set-variable= tmp_table_size=32M set-variable= max_tmp_tables=500 set-variable= long_query_time=2 set-variable= max_allowed_packet=1M set-variable= table_cache=2048 set-variable= sort_buffer=1M set-variable= read_buffer_size=512K set-variable= read_rnd_buffer_size=512K set-variable= myisam_sort_buffer_size=64M set-variable= back_log=256 set-variable= thread_cache_size=500 set-variable= max_connections=1000 set-variable= key_buffer_size=8M set-variable= max_connect_errors=5000 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 server-id = 1 set-variable= query_cache_size=20M innodb_data_home_dir = /var/lib/mysql/MySQL4.0.18/var/ innodb_data_file_path=ibdata1:1000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/MySQL4.0.18/var/ innodb_log_arch_dir = /var/lib/mysql/MySQL4.0.18/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high set-variable = innodb_buffer_pool_size=1924M # previous was 1024M set-variable = innodb_additional_mem_pool_size=30M # previous was 20M # Set .._log_file_size to 25 % of buffer pool size set-variable = innodb_log_file_size=256M set-variable = innodb_log_buffer_size=8M set-variable = innodb_lock_wait_timeout=300 #=5min innodb_flush_log_at_trx_commit=2 set-variable=innodb_thread_concurrency=8 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] err-log=/var/lib/mysql/MySQL4.0.18/var/safe_mysqld_mysqld4.0.18.log pid-file=/var/run/mysqld4.0.18.pid - SHOW STATUS - This MySQL server has been running for 0 days, 2 hours, 47 minutes and 55 seconds. It started up on Dec 15, 2005 at 08:53 AM. mysql show status ;
Re: innobase_query_caching_of_table_permitted error?
Mike, thank you for the bug report. I have now opened: http://bugs.mysql.com/bug.php?id=15758 Please add your comments there when you are able to determine the query that is causing the error print. Maybe enabling the MySQL General Query log would help? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Mike Debnam [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, December 13, 2005 10:58 PM Subject: innobase_query_caching_of_table_permitted error? --=_Part_381_21528807.1134507510720 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I just upgraded MySQL from 4.1.14 to 5.0.16 and I'm getting the following error over and over again in my server log. 051213 15:55:21051213 15:55:21 [ERROR] The calling thread is holding the adaptive search, latch though calling innobase_query_caching_of_table_permitted. I haven't been able to narrow it down to a single query yet, I was hoping someone had already seen this. I'm running FreeBSD 5.4. Thanks. Mike --=_Part_381_21528807.1134507510720-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize: 14 hours and still running!
Nathan, you can use SHOW INNODB STATUS\G to monitor how many rows per second it is inserting to the new, reorganized table. If the workload is disk-bound, it may be as low as 100 rows per second. Then inserting 20 million rows will take 2 days. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Nathan Gross [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 08, 2005 8:58 PM Subject: Optimize: 14 hours and still running! On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- 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: rollback after crash on OS X
Jaime, please post the COMPLETE UNEDITED .err log. The log sequence number would mean that your InnoDB tablespace has not been used after it was created: 051130 19:01:26 InnoDB: Started; log sequence number 0 43634 Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Jaime Magiera [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, December 02, 2005 8:07 PM Subject: rollback after crash on OS X Hello, Something weird happened on Wednesday: My Mac OSX Server unexpectedly restarted itself around 7PM. Everything seemed to be functional after that. However, I just noticed that basically a week worth of changes to a MySQL database have disappeared. Gone. Vanished. Objects that were created within a week leading to the crash have completely disappeared. Objects that were changed during that time show no sign of those changes. I could maybe understand if data from one session somehow was lost, but these changes were done over multiple sessions over a period of a week. The mysql log shows no shutdown info. The last error entry before restart is from the 21st. It definitely was an unclean shutdown. However, there are no startup errors. - 051130 19:01:15 mysqld started 051130 19:01:15 [Warning] Setting lower_case_table_names=2 because file system for /var/mysql/ is case insensitive 051130 19:01:26 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.13a' socket: '/var/mysql/mysql.sock' port: 3306 Source distribution - I not familiar enough with MySQL to even know where to begin looking for what could have happened. Any thoughts/suggestions? thanks, Jaime -- 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: MySQL with InnoDB on a readonly filesystem.
Hi! InnoDB does not work on a read-only file system. It needs to write to data files and ib_logfiles. For example, the transaction id advances also with SELECT queries, and we need to write it to the files. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: superfly [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 23, 2005 7:32 PM Subject: Re: MySQL with InnoDB on a readonly filesystem. Ralph, Not sure why your trying to open a innodb file on a read-only filesystem. Personally I'd place the innod onto a read-write file syste, and grant the users connect and select priveleges only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding SET FOREIGN_KEY_CHECKS=0;
Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20 command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - =20 =20 =20 I don't know which user group to use and wonder whether you may answer a question for me? =20 =20 =20 When you perform: =20 mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql =20 =20 =20 Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once the database install is complete? Under what circumstances one can get into trouble if the FK check is not set back to 1 at the end of the transaction? Is there a white paper I can read on the subject since I cannot find anything? =20 =20 =20 Is there a way to check whether the FK check is turned ON/OF? =20 =20 =20 Thanks you MUCH in advance and GOOD LUCK in your new Oracle adventure! =20 =20 =20 Noga Woronoff =20 Interactive Constructs, Inc. =20 200 Boston Ave. Suite #1800 =20 Medford, MA 02155 =20 =20 -- 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: Regarding SET FOREIGN_KEY_CHECKS=0;
Noga, yes, the setting SET FOREIGN_KEY_CHECKS=0 is per session. If it is somehow 'inherited' to another session, that is a serious bug. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php Noga Woronoff wrote: Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Regards - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 4:21 AM To: mysql@lists.mysql.com Cc: Noga Woronoff Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20 command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - =20 =20 =20 I don't know which user group to use and wonder whether you may answer a question for me? =20 =20 =20 When you perform: =20 mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql =20 =20 =20 Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once
Re: Background tasks performed by MySQL?
Hi! Also look at SHOW INNODB STATUS\G during the slow phase. What does it say about the 'Main thread ... state'? What does it say about transactions? Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 15, 2005 5:08 PM Subject: Re: Background tasks performed by MySQL? I would expect this to finally be something on the client end, rather than the server end... is there a search index that gets rebuilt periodically? Maybe some reports that get generated against the data? The last example that comes to my mind is if you use a client that caches data, does the cache get dumped or the client restarted at some point? These are the sort of things that we found led to the type of behavior you are talking about. Let me give clearer examples... Our databases typically do Discussion Boards... usually very large scale discussion boards (think eBay or HBO scales). The discussion board server (in this case the database client) keeps it's own search index, but need to update it on a regular basis to keep it current. If that period is too infrequent or the queries poorly optimized, they can generate a lot of load on the database, and you get the type of results you are seeing. Or if the discussion board tries to analyze the stats for the last day (or week or month etc) to provide information for reports... in our example a million page views a day means a million stats records a day, and any analysis can be quite the load generator. Same thing with our cache on our discussion board... if our discussion board has been up for some time it has all the messages most frequently used already in local cache, it doesn't do a query to recover each message in this situation... an instance of the discussion board going live into production with no data in the cache can mean a huge database hit for a few minutes while the caches in the discussion board get populated. These are just examples from our life, but I'm pretty sure when al is said and done that the cause will be some process that your client is generating to do something periodic, rather than the MySQL Server running some sort of process, which we've never seen. Take a look at the process list when it is in one of these cycles (from the mysql command line client type show processlist;). it should give you a pretty good idea of what's doing what at the time and will give you some idea on where to look. Best Regards, Bruce On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote: Hi. We have a MySQLd with both MyISAM and InnoDB tables that at uneven intervals stops responding correctly to connections. At all times, about one connection per minut fails, regardless of which database and/or user and/or remote host is connecting. The same connection parameters (and same queries) work correctly 99.9% of the time, and it is entirely random which connections time out and when. We can live with that problem, which does not seem to have any explanation. But some times, MySQLd starts taking all the CPU it can get, and gets extremely sluggish for a few minutes. At these times, several connections every second are rejected because of timeouts. These rejections we can't live with. To attempt solving the problem, I've started thinking that there might be some form of periodical cleanup that MySQLd or InnoDB performs automatically, and that we could force it to perform at night when the expected load is lower. Is there any such background cleanup performed? It could be periodical, when a certain number of queries/updates/inserts have been run, or when some query cache or similar gets full? If these problems or descriptions somehow ring a bell, I would welcome any insight I could get from the list. Thanks in advance, /Viktor... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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 assertion error
Aru, Gleb is right. Best to upgrade to 4.0 or 4.1. In those versions InnoDB will print the operating system error number and better diagnostics of the error. Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 10, 2005 12:42 AM Subject: Re: InnoDb assertion error Hello. I don't see any attempts from InnoDB to recover the data, so it is crashing before the recovering. To save your data, if you're unable to get tables with mysqldump, this might be helpful: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html MySQL 3.2.3 installed in our server suddenly started giving problem after restarting the server m/c. This version '3.2.3' looks too old for me. I strongly recommend you to upgrade to the latest release. Arunoday Chatterjee wrote: MySQL 3.2.3 installed in our server suddenly started giving problem after restarting the server m/c. /etc/init.d/mysqld status sys, mysqld dead but subsys locked Checked the log file and found that there is some Assertion failure for InnoDB, can you pls. provide a possible solution for this. Some pointers may be invalid and cause the dump to abort... thd-query at 0x3ac6fc20 is invalid pointer thd-thread_id=0 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 0 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 051109 00:41:00 mysqld ended 051109 09:38:56 mysqld started 051109 9:39:00 InnoDB: Assertion failure in thread 8192 in file os0file.c line 1044 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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 agaist 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 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=250 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 552190 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 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... Bogus stack limit or frame pointer, fp=0xbfffd488, stack_bottom=0x3bdbc910, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x3ac6fc20 is invalid pointer thd-thread_id=0 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 0 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 051109 09:39:08 mysqld ended 051109 10:06:15 mysqld started 051109 10:06:15 InnoDB: Assertion failure in thread 8192 in file os0file.c line 1044 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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 agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up som051109 10:06:15 mysqld ended 051109 10:08:18 mysqld started 051109 10:08:18 InnoDB: Assertion failure in thread 8192 in file os0file.c line 1044 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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 agaist 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 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=250 threads_connected=0
Re: Table_locks_immediate and Innodb Selects
Lee, Gleb is right. Conceptually, MySQL 'locks' every table that it uses in a SELECT query. The functions are ::store_lock() and ::external_lock(). But in the case of InnoDB, those table locks are very weak, they do not block anything. Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php . List: mysql Subject:Re: Table_locks_immediate and Innodb Selects From: Gleb Paharenko gleb.paharenko () ensita ! net Date: 2005-11-08 10:10:35 Message-ID: bl6643xdki.ln2 () blend ! ensita ! net [Download message RAW] Hello. In my opinion, it is just for some statistics. The variable locks_immediate (which corresponds to Table_locks_immediate) is incremented very often in mysys/thr_lock.c in this way: statistic_increment(locks_immediate,THR_LOCK_lock); lee wrote: Why would a simple select against an innodb db result in a bump of the \ table_locks_immediate variable? I've been debugging a different problem and I \ noticed this behavior. I don't believe it is a problem but can't explain the \ behavor and it seems odd. Regards, Lee mysql create table test (a char(1)) engine=innodb; Query OK, 0 rows affected (0.14 sec) mysql flush status; Query OK, 0 rows affected (0.07 sec) mysql show status like 'table_lock%'; +---+---+ Variable_name | Value | +---+---+ Table_locks_immediate | 0 | Table_locks_waited| 0 | +---+---+ 2 rows in set (0.07 sec) mysql select count(*) from test; +--+ count(*) | +--+ 0 | +--+ 1 row in set (0.07 sec) mysql show status like 'table_lock%'; +---+---+ Variable_name | Value | +---+---+ Table_locks_immediate | 1 | Table_locks_waited| 0 | +---+---+ 2 rows in set (0.07 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error compiling 5.0.15
Ed, when I type 'make', it prints the text make all-recursive on the next line. I did not type that keyword 'all-recursive'. I guess it means that gmake will recurse through certain directories and run make in each of them, but I do not know. Can some gmake expert please explain us what that 'all-recursive' means? Regards, Heikki Oracle/Innobase . List: mysql Subject:Re: Error compiling 5.0.15 From: Ed Kasky ed () esson ! net Date: 2005-11-07 17:23:46 Message-ID: 6.0.0.22.2.20051107092240.01c41cd8 () mail ! esson ! net [Download message RAW] I knew after I sent that first email to you that I had better check my gcc install. When I first starting updating some packages using src instead of rpm's, I neglected to remove the rpm first. My install of GCC was one of them ;-( I had a g++ of 3.2 and a 2.9 left over from the rpm. Once I got that straightened out, it compiled just fine. One other question. I noticed the following when you run make: [EMAIL PROTECTED]:~/mysql-5.0.15/sql$ make make all-recursive Should I be using the all-recursive? what does it do?? Thanks! Ed At 08:02 AM Monday, 11/7/2005, you wrote -= Ed, please post all this email traffic to mysql@lists.mysql.com In my office computer, both gcc and g++ give 3.3.5 as the version. [EMAIL PROTECTED]:~/mysql-5.0.15/sql$ gcc --version gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) Copyright (C) 2003 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. [EMAIL PROTECTED]:~/mysql-5.0.15/sql$ g++ --version g++ (GCC) 3.3.5 (Debian 1:3.3.5-13) Copyright (C) 2003 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. The linking phase in my computer looks like below. Regards, Heikki [EMAIL PROTECTED]:~/mysql-5.0.15/sql$ rm mysqld [EMAIL PROTECTED]:~/mysql-5.0.15/sql$ make make all-recursive make[1]: Entering directory `/home/heikki/mysql-5.0.15/sql' Making all in share make[2]: Entering directory `/home/heikki/mysql-5.0.15/sql/share' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/home/heikki/mysql-5.0.15/sql/share' make[2]: Entering directory `/home/heikki/mysql-5.0.15/sql' /bin/sh ../libtool --preserve-dup-deps --mode=link g++ -O3 -DDBUG_OFF -O3 -g -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysqld sql_lex.o sql_handler.o item.o item_sum.o item_buff.o item_func.o item_cmpfunc.o item_strfunc.o item_timefunc.o thr_malloc.o item_create.o item_subselect.o item_row.o item_geofunc.o field.o strfunc.o key.o sql_class.o sql_list.o net_serv.o protocol.o sql_state.o lock.o my_lock.o sql_string.o sql_manager.o sql_map.o mysqld.o password.o hash_filo.o hostname.o set_var.o sql_parse.o sql_yacc.o sql_base.o table.o sql_select.o sql_insert.o sql_prepare.o sql_error.o sql_update.o sql_delete.o uniques.o sql_do.o procedure.o item_uniq.o sql_test.o log.o log_event.o init.o derror.o sql_acl.o unireg.o des_key_file.o discover.o time.o opt_range.o opt_sum.o records.o filesort.o handler.o ha_heap.o ha_myisam.o ha_myisammrg.o ha_berkeley.o ha_innodb.o ha_ndbcluster.o sql_db.o sql_table.o sql_rename.o sql_crypt.o sql_load.o mf_iocache.o field_conv.o sql_show.o sql_udf.o sql_analyse.o sql_cache.o slave.o sql_repl.o sql_union.o sql_derived.o client.o sql_client.o mini_client_errors.o pack.o stacktrace.o repl_failsafe.o sql_olap.o sql_view.o gstream.o spatial.o sql_help.o sql_cursor.o tztime.o my_time.o my_decimal.o sp_head.o sp_pcontext.o sp_rcontext.o -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs
Mike, the opinions below are my personal opinions. They do not reflect the official standpoint of Oracle Corporation. - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, November 07, 2005 12:51 AM Subject: Re: Heikki: What will become of InnoDb once MySQL license runs At 08:33 PM 11/4/2005, you wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. Sure but if people have commercial applications that use InnoDb, then what? Is there a surprise tax waiting for them next year? When we in December 2002 negotiated the current MySQL AB - Innobase Oy OEM contract with MySQL AB's CEO Mårten Mickos, Mårten wanted a clause that makes all the details of the OEM contract confidential. Therefore, I have not been able to disclose the details of the current OEM agreement. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html From the website: InnoDB's contractual relationship with MySQL comes up for renewal next year. Oracle fully expects to negotiate an extension of that relationship. Terms of the transaction were not disclosed. It seems to me Oracle now has MySQL AB by the short and curlies. bg Negotiating a fair contract renewal could be painful under these conditions, don't you think? I believe in a situation like this it is possible to negotiate a fair contract renewal. In December 2002 the situation was different, and we were able to negotiate a new OEM contract. If Oracle is that much in favor of continuing the InnoDb contract with MySQL, why didn't they pre-announce it saying the terms and conditions would be the same as before. Or are they going to change the contract so they collect $500 or even $1000 extra for every commercial application that is distributed with InnoDb? But companies usually do not pre-announce the bids they are going to make. MySQL AB has not pre-announced MySQL's commercial non-GPL license prices in 2006. A problem is that an OEM contract is between two companies. One company cannot pre-announce what the other company might decide to do. If this happens, what alternative will MySQL be offering their users who need transactions and RI? If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. And did he say at what cost to the MySQL developers? It never struck me that Larry Ellison was a humanitarian who wanted a competitor to succeed. (Did Larry hit his head?vbg) Did Oracle give you any reason as to why they wanted to buy InnoDb? Are they going to be replacing Oracle's row locking with InnoDb? If they're not going to be using InnoDb, why buy it? This is looking more like a preemptive strike against MySQL. In which case, why would they honor the next contract? Future plans of Oracle and Innobase Oy are confidential. Like Jochem van Dieten said earlier in this thread, Oracle is a public company, and disclosure of future plans must go through a channel approved by the SEC. Mike Regards, Heikki Oracle/Innobase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error compiling 5.0.15
Ed, what does gcc --version say? I am able to compile with gcc-3.3.1. in ut0dbg.h we have: #if defined(__GNUC__) (__GNUC__ 2) # define UT_DBG_FAIL(EXPR) UNIV_UNLIKELY(!((ulint)(EXPR))) #else extern ulintut_dbg_zero; /* This is used to eliminate compiler warnings */ # define UT_DBG_FAIL(EXPR) !((ulint)(EXPR) + ut_dbg_zero) #endif and in ut0dbg.c: #if defined(__GNUC__) (__GNUC__ 2) #else /* This is used to eliminate compiler warnings */ ulint ut_dbg_zero = 0; #endif Looks like your compiler does define __GNUC__ 2 when you compile ut0dbg.c, but it does NOT define it when you compile ha_innodb.cc. This could be because ha_innodb.cc is C++ code. Another explanation would be that for some reason make fails to link ./innobase/libut.a in. If you look at the gcc output in compilation, do you see it being linked in? Regards, Heikki Oracle/Innobase - Original Message - From: Ed Kasky [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, November 06, 2005 10:08 PM Subject: Error compiling 5.0.15 I am experiencing problems when trying to compile 5.0.15 on RH 7.3. I run configure --prefix /usr/local/mysql with no apparent errors. when I run make I get the following error: ha_innodb.o: In function `check_trx_exists(THD *)': ha_innodb.o(.text+0x30a): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_query_caching_of_table_permitted(THD *, char *, unsigned int, unsigned long long *)': ha_innodb.o(.text+0x3ab): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x3b6): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_init(void)': ha_innodb.o(.text+0x69c): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x72e): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x98c): more undefined references to `ut_dbg_zero' follow collect2: ld returned 1 exit status make[4]: *** [mysqld] Error 1 make[4]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-5.0.15' make: *** [all] Error 2 Any ideas are greatly appreciated. Also, I know I can use the binary but I have been instaling from src for quite a while and would like to continue if my current OS can handle it. Thanks in advance Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (614 of 1009): It is bad luck to be superstitious. -- Andrew W. Mathis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5.0 DBD::Mysql, INSERT working but SELECT not showing INSERTED
Harry,
you have to commit your transaction. Otherwise it is rolled back when the
connection ends.
Regards,
Heikki
Oracle/Innobase
- Original Message -
From: Harry Hoffman [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 02, 2005 10:48 PM
Subject: mysql5.0 DBD::Mysql, INSERT working but SELECT not showing INSERTED
Hi All,
I've been trying to figure out what's going on with this for a little
bit now.
I've just upgraded to MySQL-5 on a system running RHAS-3. Everything
seemed to go smoothly for the upgrade, but I'm noticing something I'm
calling a bug (although I could be doing something wrong, as I Am Not A
DBA).
I have a bit of perl code that inserts iptables logs into a database.
The code seems to insert the row into the database but when I do a
SELECT * from fw_logs; I don't see the newly inserted data.
If I do the same insert from the mysql cli client and then do a SELECT I
see that the fw_id has auto incremented by two places (one presumably
being the attempt to insert via the perl code). But I only see the row
that was inserted via the mysql cli client.
At first I thought that perhaps the rpms of the client needed to get
updated, so I removed all of the mysql-* rpms, perl-DBI rpms, and
perl-DBD-Mysql rpms and installed, the rpms from mysql.com as well as
downloading and installing the latest versions of DBI and DBD::Mysql
from cpan.org. I am still having the same exact problem.
I'd be happy to provide any other info if that would help.
Thanks,
Harry
Here is the table.
CREATE TABLE fw_logs (
fw_id bigint(50) NOT NULL auto_increment,
sid int unsigned,
date datetime NOT NULL default '-00-00 00:00:00',
iface varchar(12) NOT NULL default '',
srcaddr varchar(64) NOT NULL default '',
srcport int(5) NOT NULL default '0',
dstaddr varchar(64) NOT NULL default '',
dstport int(5) NOT NULL default '0',
proto varchar(50) NOT NULL default '',
PRIMARY KEY (fw_id),
FOREIGN KEY (sid) REFERENCES sensor(sid) on delete cascade,
INDEX (date),
INDEX (srcaddr),
INDEX (srcport),
INDEX (dstaddr),
INDEX (dstport)
) TYPE INNODB;
Here is the table with the foreign key:
CREATE TABLE sensor (
sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
hostnameTEXT,
interface TEXT,
filter TEXT,
detail TINYINT,
encodingTINYINT,
last_cidINT UNSIGNED NOT NULL,
PRIMARY KEY (sid)
) TYPE INNODB;
Here is the query log:
/usr/sbin/mysqld, Version: 5.0.15-standard-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id CommandArgument
051102 15:14:51 1 Connect [EMAIL PROTECTED] on palantir
1 Query set autocommit=0
1 Prepare [1]
1 Execute [1] INSERT INTO
fw_logs(sid,date,iface,srcaddr,srcport,dstaddr,dstport,proto)
VALUES('1','2005-10-27
20:17:32','eth0','1xx.xx2.xx3.123','1734','1xx.xx2.xx3.38','139','TCP')
1 Quit
--
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: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
All, a fast COUNT(*) is in the TODO of InnoDB. But it is relatively difficult to implement without reducing INSERT performance. Regards, Heikki Oracle/Innobase - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 02, 2005 10:30 AM Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB? Shankar Unni wrote: [EMAIL PROTECTED] wrote: If I understand the InnoDB engine correctly, I don't see how they could speed it up unless they start tracking how many records belong to each active version within a database. But one thing you can do to speed it up somewhat is to do a COUNT(PK_column) (rather than COUNT(*)) if you have a column that is a primary key for the table - that's the same thing semantically, and involves searching an index rather than the data records themselves, which should involve less I/O. If I understand http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html correctly, the index of the primary key is stored as the clustered index together with the data. To me this means that there is no difference between counting the primary key entries and counting the data entries. Regards, Jigal. -- 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: Re:Re: Re: How Can I upgrade TPC-C performance test result for mysql
George, is the utilization of both CPUs only 10 %? How big it is in the test with MS SQL Server? The workload is very much disk-bound if CPU usage is only 10 %. Regards, Heikki Oracle/Innobase - Original Message - From: yanghaifeng [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 02, 2005 3:28 AM Subject: Re:Re: Re: How Can I upgrade TPC-C performance test result for mysql Heikki, We thanks vary much for your response. The CPU usage is avg 10% during the test. We think if you need we will give you our test source code. Best regards, george -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How Can I upgrade TPC-C performance test result for mysql
George, there seem to be two bottlenecks: 1) InnoDB has contention on the buffer pool mutex; 2) the workload is also disk-read-bound. For 1), we might have an improvement available in the future. We must let the threads leave the 'wait array' in sync0arr.c without reserving the wait array mutex. Also, we need to study if we can reduce the code that is executed when we have the buffer pool mutex reserved. To reduce 2), you can try increasing innodb_buffer_pool_size further. But be careful that Windows will not start swapping. What does the Task Manager say about CPU usage during the test? Regards, Heikki Oracle/Innobase ... Dear Heikki, we change the system's environment. now we use RAID0 disk to store the InnoDB data file,and other disk to store log file. follow is system's information: CPU: 4X2.8GHz RAM: 1G Disk: 4X36G the SHOW INNIDB STATUS\G result is: = 051021 10:51:59 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1416712, signal count 577079 --Thread 2364 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 for 0.00 seconds the semaphore: S-lock on RW-latch at 01471B80 created in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 139 a writer (thread id 2400) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 Last time write locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 wait has ended --Thread 2408 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1286 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2400 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 for 0.00 seconds the semaphore: X-lock on RW-latch at 01471B80 created in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 139 a writer (thread id 2400) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 Last time write locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 --Thread 2332 has waited at ../innobase/include\log0log.ic line 315 for 0.00 seconds the semaphore: Mutex at 014740D0 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c line 744, lock var 1 waiters flag 1 --Thread 2420 has waited at ../innobase/include\log0log.ic line 315 for 0.00 seconds the semaphore: Mutex at 014740D0 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c line 744, lock var 1 waiters flag 1 --Thread 2404 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2380 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2348 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2424 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1088 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 380 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1088 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2392 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0
Re: weird innodb foreign key feature
Ady,
NO ACTION actually means the same as RESTRICT. That is, the foreign key
constraint is still enforced. The name 'NO ACTION' is from the SQL standard.
I agree that the name is confusing, but I cannot help it, because it is in
the standard.
Regards,
Heikki
Oracle/Innobase
- Original Message -
From: Ady Wicaksono [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL default '',
`keycode_isactive` enum('Y','N') NOT NULL default 'N',
`keycode_tarif` int(11) NOT NULL default '2000',
PRIMARY KEY (`keycode_id`),
UNIQUE KEY `keycode_val` (`keycode_val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and my second table
CREATE TABLE `t_push_member_unsub` (
`push_member_id` int(11) NOT NULL auto_increment,
`push_keycode` varchar(15) NOT NULL default '',
`push_msisdn` varchar(16) NOT NULL default '',
`push_subscribe_at` datetime NOT NULL default '-00-00 00:00:00',
`push_unsubscribe_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`push_pending` enum('TRUE','FALSE') default 'FALSE',
`push_operator` varchar(30) NOT NULL default '',
`push_unsubscribe_reason` text NOT NULL,
PRIMARY KEY (`push_member_id`),
KEY `t_push_member_unsub_ibfk_1` (`push_keycode`),
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
when i delete rows on t_keycode EVEN if there's foreign key
(push_keycode) that refer to this row i hope
in t_push_member_unsub will do nothing, let it happened.
DELETE from t_keycode WHERE keycode_val='TEST'
Foreign key constraint fails for table `t_push_member_unsub`:
,
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
Trying to delete in parent table, in index `keycode_val` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8090; asc ;;
But in child table `t_push_member_unsub`, in index
`t_push_member_unsub_ibfk_1`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8000cb50; ascP;;
Any information?
Thx
--
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: ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139)
Sunil, in InnoDB, the maximum indexed column length is 767 bytes. Osku is improving the error message: http://bugs.mysql.com/bug.php?id=13315 Regards, Heikki Oracle/Innobase - Original Message - From: Sunil Vishwas [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 25, 2005 5:33 AM Subject: ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139) --_=_NextPart_001_01C5D90C.62DB5CF5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable (I am using mysql-5.0.15-win32) Why I am getting this error and is there any way I can fix it, or is it a bug? Between I don't get this error if I change the field size to 767 or below: =20 drop table Address; CREATE TABLE `Address`=20 ( `RecId` CHAR(32) NOT NULL,=20 `WebAddress` VARCHAR(1000),=20 CONSTRAINT PKAddress PRIMARY KEY(RecId) ); =20 CREATE INDEX AddressWebAddressWebAddress ON Address (WebAddress); =20 --_=_NextPart_001_01C5D90C.62DB5CF5-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Can I upgrade TPC-C performance test result for mysql
George, can you please post a few typical SHOW INNODB STATUS\G outputs during the stress test. Best regards, Heikki Oracle/Innobase - Original Message - From: yang george [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 24, 2005 1:50 PM Subject: How Can I upgrade TPC-C performance test result for mysql dear sir: we develop a TPC-C test progrom,this program use mysql ODBC3.51 to connect mysql server, and use microsoft sqlserver odbc to connect sqlserver2000. we suppose you are know well the TPC-C. we use InnoDB as mysql's store engine. follow is the os and hardware information: =09os : Windows advance server 2000 =09CPU: 2X3.0Ghz =09memory:2G =09disk: only one 40G IDE disk table struct: =09for one warehouse,we stat all table list: =09table name=09| record numbers |=09type record size(bytes)=09| type tab= le size(K bytes) =09 Warehouse=09 | 1=09 |89=09 | 0.0= 89 District=09 | 10=09 |95=09 | 0.95= 0 Customer=09 | 30k=09|655=09 | 1965= 0 History=09| 30k=09|46=09 | 1380 Order=09 | 30k=09|24=09 | 720 New-Order=09 | 9k=09 |8=09| 72 Order-Line=09| 300k=09|54=09 | 1620= 0 Stock=09 | 100k=09|306=09 | 30= 600 Item=09 | 100k=09|82=09 | 8200 =09SQL sentence: =09create table warehouse ( w_id int not null, w_name varchar(10) null, w_street_1 varchar(20) null, w_street_2 varchar(20) null, w_city varchar(20) null, w_state char(2) null, w_zip char(9) null, w_tax float null, w_ytd float null, primary key(w_id) ); =09create table district ( d_id int not null, d_w_id int not null, d_name varchar(10) null, d_street_1 varchar(20) null, d_street_2 varchar(20) null, d_city varchar(20) null, d_state char(2) null, d_zip char(9) null, d_tax float null, d_ytd float null, d_next_o_id int null, primary key(d_w_id, d_id),foreign key(d_w_id)references warehouse(w_id) ); =09create table customer ( c_id int not null, c_d_id int not null, c_w_id int not null, c_first varchar(16) null, c_middle char(2) null, c_last varchar(16) null, c_street_1 varchar(20) null, c_street_2 varchar(20) null, c_city varchar(20) null, c_state char(2) null, c_zip char(9) null, c_phone char(16) null, c_since timestamp null, c_credit char(2) null, c_credit_lim float null, c_discount float null, c_balance float null, c_ytd_payment float null, c_payment_cnt int null, c_delivery_cnt int null, c_data varchar(500) null, primary key(c_w_id, c_d_id, c_id),foreign key(c_w_id,c_d_id) references district(d_w_id,d_id) ); =09create table history ( h_c_id int null, h_c_d_id int null, h_c_w_id int null, h_d_id int null, h_w_id int null, h_date timestamp null, h_amount float null, h_data varchar(24) null,foreign key(h_c_w_id,h_c_d_id,h_c_id) references customer(c_w_id,c_d_id,c_id),foreign key(h_w_id,h_d_id)references district(d_w_id,d_id) ); =09create table orders ( o_id int not null, o_d_id int not null, o_w_id int not null, o_c_id int null, o_entry_d timestamp null, o_carrier_id int null, o_ol_cnt int null, o_all_local int null, primary key(o_w_id, o_d_id, o_id),foreign key(o_w_id,o_d_id,o_c_id)references customer(c_w_id,c_d_id,c_id) ); =09create table new_order ( no_o_id int not null, no_d_id int not null, no_w_id int not null, primary key(no_w_id, no_d_id, no_o_id),foreign key(no_w_id,no_d_id,no_o_id)references orders(o_w_id,o_d_id,o_id) ); =09create table item ( i_id int not null, i_im_id int null, i_name varchar(24) null, i_price float null, i_data varchar(50) null, primary key(i_id) ); =09create table stock ( s_i_id int not null, s_w_id int not null, s_quantity int null, s_dist_01 varchar(24) null, s_dist_02 varchar(24) null, s_dist_03 varchar(24) null, s_dist_04 varchar(24) null, s_dist_05 varchar(24) null, s_dist_06 varchar(24) null, s_dist_07 varchar(24) null, s_dist_08 varchar(24) null, s_dist_09 varchar(24) null, s_dist_10 varchar(24) null, s_ytd int null, s_order_cnt int null, s_remote_cnt int null, s_data varchar(50) null, primary key(s_w_id, s_i_id),foreign key(s_w_id)references warehouse(w_id),foreign key(s_i_id)references item(i_id) ); =09create table order_line ( ol_o_id int not null, ol_d_id int not null, ol_w_id int not null, ol_number int not null, ol_i_id int null, ol_supply_w_id int null, ol_delivery_d timestamp null, ol_quantity int null, ol_amount float null, ol_dist_info char(24) null, primary key(ol_w_id, ol_d_id, ol_o_id, ol_number),foreign key(ol_w_id,ol_d_id,ol_o_id) references orders(o_w_id,o_d_id,o_id),foreign key(ol_supply_w_id,ol_i_id)references stock(s_w_id,s_i_id) ); =09 =09index: =09create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id); =09create index i_customer on customer (c_w_id, c_d_id, c_last, c_first, c_=
Re: Input on Materialized Views
Andrew, how about using triggers to recompute materialized views? A challenge is to write an automatic program that can compute the required triggers based on the view definition. The materialized view would be a normal table. Triggers would update it. Regards, Heikki Oracle/Innobase - Original Message - From: Andrew Roth [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, October 19, 2005 6:06 PM Subject: Re: Input on Materialized Views Thanks for the clarification and comments. I should reiterate that for our project, we do *not* need to actually implement materialized views, but only suggest refactoring and/or new components required to implement them. I think implementing it would too time consuming for a group of three students in one term, but we can definitely post our findings here. We may also have some interesting MySQL source architecture visualizations to show. To make things a bit simpler, we've decided not to consider the query optimizer or scheduling updates. Instead, we'll focus on the storage engine and adding the keyword. My understanding of materialized views for the purposes of our assignment is that it's a view that caches the data when queried. If the data changes, the cache will have to be recomputed (or at least invalidated). -Andrew On 10/19/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Just like Jigal, I also had to lookup the term materialized view. For t= he sake of others on the list trying to follow along: a materialized view is basically a self-updating snapshot of a table (or tables) usually contain= ing some sort of intermediate statistical computations involving GROUP BY. For instance, if you have the raw log of visitors to your web site stored= in your database and you frequently ran queries that created summarations by day, you could save yourself a lot of processing time by periodically precomputing a daily table from your raw logs showing various statistic= s broken down for each date. Well, a materialized view would be one way to implement that daily table. -- 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: reinstall mysql show innodb error
Shuming, http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html you have to specify the right size for: innodb_log_file_size=14M in your my.cnf or my.ini. Please also check from: C:\Program Files\MySQL\MySQL Server 4.1\data how many ib_logfiles you have. If you have more than 2, specify also: innodb_log_files_in_group=... Regards, Heikki Oracle/Innobase - Original Message - From: wang shuming [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 20, 2005 3:05 PM Subject: reinstall mysql show innodb error --=_Part_890_17924509.1129809525031 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, Our mysql 4.1 database server on win2000 was uninstalled by mistake, but reinstalled , caused the follow errors: InnoDB: Error: log file .\ib_logfile0 is of different size 0 14680064 byte= s InnoDB: than specified in the .cnf file 0 10485760 bytes! 051020 19:30:42 [ERROR] Can't init databases 051020 19:30:42 [ERROR] Aborting Best regard! Shuming Wang Xtech Company limited Room 17G,17/F,Plaza 2, Tiancheng Building,No 96 Longkouxi Road, Tianhe Guangzhou,Guangdong,China http://www.xtech.com.cn http://www.87595959.com Email: [EMAIL PROTECTED] QQ:370783763 , MSN: [EMAIL PROTECTED] Tel:86-20-87595959---2001,2002 Fax:86-20-87576779 --=_Part_890_17924509.1129809525031-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non-linear degradation in bulk loads?
Jon, using a 4 GB buffer pool is safe in a 64-bit computer, and allocating 50 % - 80 % of memory to the buffer pool is recommended in an InnoDB-only server. Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; mysql@lists.mysql.com mailto:mysql@lists.mysql.com Lähetetty: Tuesday, October 18, 2005 4:39 AM Aihe: RE: Non-linear degradation in bulk loads? Side question: If I use a 64-bit MySQL build on a 64-bit kernel, is it safe and sane to allocate say, 6GB to the InnoDB buffer pool? On an 8GB box, 64-bit software stack, what is the optimum memory allocation for a pure-InnoDB (* - MyISAM used only for grant tables) mysql server running as the sole application on the machine? -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, October 17, 2005 10:55 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, I am not 100 % sure that the problem we saw was in a 64-bit Linux. It might have been 32-bit. Anyway, since CentOS is a clone of RHEL, this might be the same file cache phenomenon. I do not know if one can force the file cache to stay smaller than 4 GB. You can try running some dummy programs that occupy a few GB of memory. Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; mysql@lists.mysql.com mailto:mysql@lists.mysql.com Lähetetty: Monday, October 17, 2005 8:49 PM Aihe: RE: Non-linear degradation in bulk loads? Sorry to spam the group, but I just noticed that I asserted we were on a 2.4 kernel -- we're on 2.6.9-11 as part of CentOS 4.1... -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 2:23 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, your hardware/OS combination is quite new and unusual. It might indeed be an OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth of the normal. You would not expect that kind of behavior from a 64-bit OS. When you see the slowdown, what does 'top' say about the OS file cache size? The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Regards, Heikki - Original Message - From: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 14, 2005 10:39 PM Subject: RE: Non-linear degradation in bulk loads? I've tried tweaking the structure of the schema to have, for example, a = PRIMARY KEY index on email, no other indexes, and then insert in sorted = order -- made no improvement whatsoever. Another clue that leads me to = believe that this may be an OS issue: Starting a large cp on the same = box (from a local filesystem other than the one the InnoDB data pool was = on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from = ~15-20 connections at any given instant to 750 (our max_connections = setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? =20 Jon, =20 hmm... maybe one of the indexes inevitably is in a random order. =20 Please post a typical =20 SHOW INNODB STATUS\G =20 when the inserts happen slowly. =20 What is your my.cnf like? =20 Regards, =20 Heikki Innobase/Oracle =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED];=20 mailto:[EMAIL PROTECTED];=20 mysql@lists.mysql.com mailto:mysql@lists.mysql.com L=E4hetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? =20 =20 Two solutions: 1) sort the rows to be inserted on the key 'email
Re: Non-linear degradation in bulk loads?
Jon, I do not know. Why not install a 64-bit Linux in your computer? Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; mysql@lists.mysql.com Lähetetty: Monday, October 17, 2005 8:46 PM Aihe: RE: Non-linear degradation in bulk loads? Actually, I believe we're running 32-bit, with bigmem... Does similar behavior occur in such a scenario? -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 2:23 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, your hardware/OS combination is quite new and unusual. It might indeed be an OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth of the normal. You would not expect that kind of behavior from a 64-bit OS. When you see the slowdown, what does 'top' say about the OS file cache size? The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Regards, Heikki - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 14, 2005 10:39 PM Subject: RE: Non-linear degradation in bulk loads? I've tried tweaking the structure of the schema to have, for example, a = PRIMARY KEY index on email, no other indexes, and then insert in sorted = order -- made no improvement whatsoever. Another clue that leads me to = believe that this may be an OS issue: Starting a large cp on the same = box (from a local filesystem other than the one the InnoDB data pool was = on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from = ~15-20 connections at any given instant to 750 (our max_connections = setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? =20 Jon, =20 hmm... maybe one of the indexes inevitably is in a random order. =20 Please post a typical =20 SHOW INNODB STATUS\G =20 when the inserts happen slowly. =20 What is your my.cnf like? =20 Regards, =20 Heikki Innobase/Oracle =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED];=20 mysql@lists.mysql.com L=E4hetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? =20 =20 Two solutions: 1) sort the rows to be inserted on the key 'email'=20 before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting=20 from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily=20 turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=3D0; For big tables, this saves a lot of disk I/O because InnoDB can use=20 its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! =20 After sending my mail, I discovered SET UNIQUE_CHECKS=3D0, and=20 subsequent to that it also occurred to me to try putting the=20 data in in sorted order. Unfortunately, doing=20 UNIQUE_CHECKS=3D0 did not work, and even the combination of=20 both did not work. First chunk (3.4m rows) was ~1.5 minutes,=20 second was ~5 minutes... =20 At this point I'm inclined to believe that there is something=20 very wrong with the disk subsystem because of this and other=20 problems (doing a large cp from the datapool filesystem to=20 another filesystem brought the database to a near-halt, among=20 other things). =20 As a stop-gap solution, I created the table with no indexes,=20 and loaded all the data (loaded in linear time), and plan on=20 doing a CREATE UNIQUE INDEX on the table. Will this happen=20 in linear time, or near-linear time? =20 *sigh* =20 -JF=20 =20 =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http://lists.mysql.com/[EMAIL PROTECTED] =20 =20 -- 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
Re: Non-linear degradation in bulk loads?
Jon, I am not 100 % sure that the problem we saw was in a 64-bit Linux. It might have been 32-bit. Anyway, since CentOS is a clone of RHEL, this might be the same file cache phenomenon. I do not know if one can force the file cache to stay smaller than 4 GB. You can try running some dummy programs that occupy a few GB of memory. Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; mysql@lists.mysql.com Lähetetty: Monday, October 17, 2005 8:49 PM Aihe: RE: Non-linear degradation in bulk loads? Sorry to spam the group, but I just noticed that I asserted we were on a 2.4 kernel -- we're on 2.6.9-11 as part of CentOS 4.1... -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 2:23 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, your hardware/OS combination is quite new and unusual. It might indeed be an OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth of the normal. You would not expect that kind of behavior from a 64-bit OS. When you see the slowdown, what does 'top' say about the OS file cache size? The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Regards, Heikki - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 14, 2005 10:39 PM Subject: RE: Non-linear degradation in bulk loads? I've tried tweaking the structure of the schema to have, for example, a = PRIMARY KEY index on email, no other indexes, and then insert in sorted = order -- made no improvement whatsoever. Another clue that leads me to = believe that this may be an OS issue: Starting a large cp on the same = box (from a local filesystem other than the one the InnoDB data pool was = on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from = ~15-20 connections at any given instant to 750 (our max_connections = setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? =20 Jon, =20 hmm... maybe one of the indexes inevitably is in a random order. =20 Please post a typical =20 SHOW INNODB STATUS\G =20 when the inserts happen slowly. =20 What is your my.cnf like? =20 Regards, =20 Heikki Innobase/Oracle =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED];=20 mysql@lists.mysql.com L=E4hetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? =20 =20 Two solutions: 1) sort the rows to be inserted on the key 'email'=20 before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting=20 from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily=20 turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=3D0; For big tables, this saves a lot of disk I/O because InnoDB can use=20 its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! =20 After sending my mail, I discovered SET UNIQUE_CHECKS=3D0, and=20 subsequent to that it also occurred to me to try putting the=20 data in in sorted order. Unfortunately, doing=20 UNIQUE_CHECKS=3D0 did not work, and even the combination of=20 both did not work. First chunk (3.4m rows) was ~1.5 minutes,=20 second was ~5 minutes... =20 At this point I'm inclined to believe that there is something=20 very wrong with the disk subsystem because of this and other=20 problems (doing a large cp from the datapool filesystem to=20 another filesystem brought the database to a near-halt, among=20 other things). =20 As a stop-gap solution, I created the table with no indexes,=20 and loaded all the data (loaded in linear time), and plan on=20 doing a CREATE UNIQUE INDEX on the table. Will this happen=20 in linear time, or near-linear time? =20 *sigh* =20 -JF=20 =20 =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http
Re: Non-linear degradation in bulk loads?
Jon, your hardware/OS combination is quite new and unusual. It might indeed be an OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth of the normal. You would not expect that kind of behavior from a 64-bit OS. When you see the slowdown, what does 'top' say about the OS file cache size? The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Regards, Heikki - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 14, 2005 10:39 PM Subject: RE: Non-linear degradation in bulk loads? I've tried tweaking the structure of the schema to have, for example, a = PRIMARY KEY index on email, no other indexes, and then insert in sorted = order -- made no improvement whatsoever. Another clue that leads me to = believe that this may be an OS issue: Starting a large cp on the same = box (from a local filesystem other than the one the InnoDB data pool was = on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from = ~15-20 connections at any given instant to 750 (our max_connections = setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? =20 Jon, =20 hmm... maybe one of the indexes inevitably is in a random order. =20 Please post a typical =20 SHOW INNODB STATUS\G =20 when the inserts happen slowly. =20 What is your my.cnf like? =20 Regards, =20 Heikki Innobase/Oracle =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED];=20 mysql@lists.mysql.com L=E4hetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? =20 =20 Two solutions: 1) sort the rows to be inserted on the key 'email'=20 before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting=20 from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily=20 turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=3D0; For big tables, this saves a lot of disk I/O because InnoDB can use=20 its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! =20 After sending my mail, I discovered SET UNIQUE_CHECKS=3D0, and=20 subsequent to that it also occurred to me to try putting the=20 data in in sorted order. Unfortunately, doing=20 UNIQUE_CHECKS=3D0 did not work, and even the combination of=20 both did not work. First chunk (3.4m rows) was ~1.5 minutes,=20 second was ~5 minutes... =20 At this point I'm inclined to believe that there is something=20 very wrong with the disk subsystem because of this and other=20 problems (doing a large cp from the datapool filesystem to=20 another filesystem brought the database to a near-halt, among=20 other things). =20 As a stop-gap solution, I created the table with no indexes,=20 and loaded all the data (loaded in linear time), and plan on=20 doing a CREATE UNIQUE INDEX on the table. Will this happen=20 in linear time, or near-linear time? =20 *sigh* =20 -JF=20 =20 =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http://lists.mysql.com/[EMAIL PROTECTED] =20 =20 -- 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 open files issue
Jonathan, http://dev.mysql.com/doc/refman/5.0/en/innodb-start.html innodb_open_files This option is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default is 300. The file descriptors used for .ibd files are for InnoDB only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache. That parameter is completely independent of the MySQL table cache. Regards, Heikki Innobase/Oracle - Original Message - From: Jonathan Stockley [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 13, 2005 10:49 PM Subject: Innodb open files issue --_=_NextPart_001_01C5D02F.2BE09467 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi, I have mysql 4.1.14 on solaris 9 and have just switched to using the innodb_file_per_table option. Previously I was using MyISAM tables and created about 700 tables. The rlim_fd_cur and rlim_fd_max kernel paramteres are set to 256. =20 I'm getting an error from innodb that it is running out of file descriptors. I did not get this when using MyISAM tables. =20 Does the innodb not use the table cache when innodb_file_per_table is set? Where is this documented? =20 thanks, Jo --_=_NextPart_001_01C5D02F.2BE09467-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non-linear degradation in bulk loads?
Jon, hmm... maybe one of the indexes inevitably is in a random order. Please post a typical SHOW INNODB STATUS\G when the inserts happen slowly. What is your my.cnf like? Regards, Heikki Innobase/Oracle - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; mysql@lists.mysql.com Lähetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? Two solutions: 1) sort the rows to be inserted on the key 'email' before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! After sending my mail, I discovered SET UNIQUE_CHECKS=0, and subsequent to that it also occurred to me to try putting the data in in sorted order. Unfortunately, doing UNIQUE_CHECKS=0 did not work, and even the combination of both did not work. First chunk (3.4m rows) was ~1.5 minutes, second was ~5 minutes... At this point I'm inclined to believe that there is something very wrong with the disk subsystem because of this and other problems (doing a large cp from the datapool filesystem to another filesystem brought the database to a near-halt, among other things). As a stop-gap solution, I created the table with no indexes, and loaded all the data (loaded in linear time), and plan on doing a CREATE UNIQUE INDEX on the table. Will this happen in linear time, or near-linear time? *sigh* -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non-linear degradation in bulk loads?
Jon, my guess is that the inserts to the UNIQUE secondary index cause the workload to be seriously disk-bound. Two solutions: 1) sort the rows to be inserted on the key 'email' before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! Note that now you can get support on this MySQL mailing list from a Vice President of Oracle. I hope that the level of support improves. Best regards, Heikki Vice President, server technology Oracle/Innobase Oy ... Everyone, We're trying to do some bulk data loads on several different tables (on several different machines, using several different techniques) and seeing dramatically worse-than-linear performance. We've tried the bulk-INSERT syntax, and the LOAD DATA INFILE syntax. We've done ALTER TABLE ... DISABLE KEYS, SET FOREIGN_KEY_CHECKS=0 (where appropriate), and so forth. The one that is the most immediate concern is a table of the form: CREATE TABLE `test` ( `email` varchar(255) NOT NULL default '', `when_happened` datetime NOT NULL default '-00-00 00:00:00', UNIQUE KEY `email` (`email`), KEY `when_happened` (`when_happened`) ) TYPE=InnoDB; I'm loading data using LOAD DATA INFILE with chunks containing 3.4m rows each (~135MB files). The first chunk was very quick (about 1.5 minutes), but the tenth chunk has taken 22.6 hours and is still going. (It's been getting progessively slower with each chunk...) The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Something *has* to be wrong here, but we're not sure what we've missed. We've restored larger data sets from a mysqldump in the past in dramatically less time on far inferior hardware. (A superset of this same data to a schema which is also a superset, PLUS a bunch of other rather large tables -- all in ~8 hours on a 3Ware RAID array on a dual Xeon w/ 4GB of RAM) We're inclined to believe that this is a configuration problem, as opposed to a driver or hardware problem given the non-linear nature of the performance degradation. This implies we're doing something truly stupid with our loads. What could cause this kind of strangeness? -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about innodb, ibdata1
Fredrik, - Original Message - From: Fredrik Carlsson [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 10, 2005 9:57 PM Subject: Question about innodb, ibdata1 Hi, I'm using InnoDB with tablespaces for almost all tables. The last few week the file ibdata1 has started to grow, should it really do this when using tablespaces? i did an alter on one of the bigger tables some time if you are using innodb_file_per_table in my.cnf, then the tables are stored in .ibd files. If the table that you ALTER is not stored in the ibdata1 file, then the ibdata1 file should not grow in the ALTER. Note that InnoDB stores undo logs to ibdata files. If you forget a transaction dangling then purge cannot remove the undo log files. That would cause ibdata1 to grow constantly. Use: SHOW INNODB STATUS\G to determine if you have dangling transactions. ago to alter the size of a varchar collumn, could this has something to do with it? I'm using MySQL 4.1.11 // Fredrik Carlsson Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Deva, hmm... this is mysterious. This might also be an OS bug. If you have the .err log from other crashes, please send it to [EMAIL PROTECTED] Best regards, Heikki Tuuri Vice President, server technology Oracle Corp. - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 07, 2005 10:38 PM Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout) Heikki Tuuri wrote: Deva, it is probably this insert operation that is stuck, and is holding an S-latch of an index tree in emails_history_30_30: ---TRANSACTION 0 1856588555, ACTIVE 858 sec, process no 1792, OS thread id 2445845440 inserting, thread declared inside InnoDB 318 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 684 MySQL thread id 42, query id 6575 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( The insert operation below wants to get an X-latch on the tree. Apparently, it wants to make a B-tree page split. ---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread id 2450451904 inserting, thread declared inside InnoDB 160 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 341 MySQL thread id 148, query id 22800 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( The question is why the first insert is does no progress. undo log entries 684 If you look further into the .err log, do you see the first insert operation progressing at all, or does the number of undo log entries stay at 684? This particular entry was part of the last output before the server came down. If I look back in the log, I see that this transaction first appears when it had been active for 314 sec, and at that time, it had 684 undo log entries (just the same number it had at 858 sec). Looking at the error log, none of the active threads had any increase in undo log entries during the 9 minutes between the first innodb monitor output and the server crashing. After mysql restarted, innodb rolled back each of those transactions, undoing precisely as many rows as appeared in the first innodb monitor output for each transaction ID. The output below shows that InnoDB is probably doing a very active insert buffer merge. -- BUFFER POOL AND MEMORY -- Total memory allocated 1303861436; in additional pool allocated 12728448 Buffer pool size 65536 Free buffers 0 Database pages 65532 Modified db pages 14 Pending reads 22 Pending writes: LRU 0, flush list 0, single page 0 Pages read 91019, created 152, written 113129 91.49 reads/s, 0.00 creates/s, 135.55 writes/s Buffer pool hit rate 944 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 288 queries in queue Main thread process no. 1792, id 2434898496, state: doing insert buffer The question is why several inserts are stuck and do not progress. They are not waiting for any semaphore. Do you have enough RAM in your computer? 4GB RAM in the computer, dual Xeon 2.8GHz processors. I posted the full my.cnf and server details in my first post on this topic, but here again are the innodb specific settings: innodb_log_buffer_size = 16M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_additional_mem_pool_size = 128M innodb_buffer_pool_size = 1G innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 30 Is anything wrong with this? The server had been running fine for months with these settings Regards, Heikki Thanks for all your time! Devananda vdv -- 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: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Deva, it is probably this insert operation that is stuck, and is holding an S-latch of an index tree in emails_history_30_30: ---TRANSACTION 0 1856588555, ACTIVE 858 sec, process no 1792, OS thread id 2445845440 inserting, thread declared inside InnoDB 318 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 684 MySQL thread id 42, query id 6575 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( The insert operation below wants to get an X-latch on the tree. Apparently, it wants to make a B-tree page split. ---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread id 2450451904 inserting, thread declared inside InnoDB 160 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 341 MySQL thread id 148, query id 22800 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( The question is why the first insert is does no progress. undo log entries 684 If you look further into the .err log, do you see the first insert operation progressing at all, or does the number of undo log entries stay at 684? The output below shows that InnoDB is probably doing a very active insert buffer merge. -- BUFFER POOL AND MEMORY -- Total memory allocated 1303861436; in additional pool allocated 12728448 Buffer pool size 65536 Free buffers 0 Database pages 65532 Modified db pages 14 Pending reads 22 Pending writes: LRU 0, flush list 0, single page 0 Pages read 91019, created 152, written 113129 91.49 reads/s, 0.00 creates/s, 135.55 writes/s Buffer pool hit rate 944 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 288 queries in queue Main thread process no. 1792, id 2434898496, state: doing insert buffer The question is why several inserts are stuck and do not progress. They are not waiting for any semaphore. Do you have enough RAM in your computer? Regards, Heikki - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 06, 2005 11:02 PM Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout) Heikki Tuuri wrote: Deva, please post those transactions which have been active more than 800 seconds. The thread holding the RW-latch at 0x448286bc should be among those. Regards, Heikki The thread holding that RW-latch is the first of these. I've removed any sensitive information but left as much of the query as possible. ---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread id 2450451904 inserting, thread declared inside InnoDB 160 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 341 MySQL thread id 148, query id 22800 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856601294, ACTIVE 802 sec, process no 1792, OS thread id 2445059520 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 167, query id 22813 192.168.1.52 webserver statistics SELECT * FROM companies WHERE company_id = Trx read view will not see trx with id = 0 1856601295, sees 0 1856587424 ---TRANSACTION 0 1856601293, ACTIVE 802 sec, process no 1792, OS thread id 2450255424 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 163, query id 22802 192.168.1.52 webserver statistics SELECT * FROM emails_9_19 WHERE Trx read view will not see trx with id = 0 1856601294, sees 0 1856587424 ---TRANSACTION 0 1856601280, ACTIVE 802 sec, process no 1792, OS thread id 2451766592 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 170, query id 22788 192.168.1.37 webserver Sending data SELECT * FROM mta_servers WHERE ip IN( Trx read view will not see trx with id = 0 1856601281, sees 0 1856587424 ---TRANSACTION 0 1856601279, ACTIVE 802 sec, process no 1792, OS thread id 2446827840 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 169, query id 22786 192.168.1.37 webserver Sending data SELECT * FROM mta_servers WHERE ip IN( Trx read view will not see trx with id = 0 1856601280, sees 0 1856587424 ---TRANSACTION 0 1856601274, ACTIVE 803 sec, process no 1792, OS thread id 2449862464 waiting in InnoDB queue mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 1002 MySQL thread id 127, query id 22756 192.168.1.231 webserver update INSERT INTO emails_history_30_6(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856601269, ACTIVE 803 sec, process no 1792, OS thread id 2450058944 waiting in InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 162, query id 22751 192.168.1.232 webserver Sending data SELECT e.email_id, e.email_address, e.status, e.first_name, e.last_name, e.gender, e.dob, e.state, e.zipcode, e.country
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Deva, please post those transactions which have been active more than 800 seconds. The thread holding the RW-latch at 0x448286bc should be among those. Regards, Heikki - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 06, 2005 10:14 AM Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout) Heikki Tuuri wrote: Deva, please post the complete output except the transaction data. Regards, Heikki Please let me know if there is any additional data I can provide to help resolve this. = 051005 7:18:17 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 31277, signal count 29361 --Thread 2450451904 has waited at btr0cur.c line 340 for 800.00 seconds the semaphore: X-lock on RW-latch at 0x448286bc created in file dict0dict.c line 3593 a writer (thread id 2450451904) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 347 Last time write locked in file btr0cur.c line 340 Mutex spin waits 294649, rounds 709548, OS waits 19013 RW-shared spins 10614, OS waits 5357; RW-excl spins 14659, OS waits 6152 TRANSACTIONS Trx id counter 0 1856601574 Purge done for trx's n:o 0 1856601271 undo n:o 0 0 History list length 9 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: section cut 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: complete io for buf page (read thread) ev set I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 11, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 56937 OS file reads, 79359 OS file writes, 13851 OS fsyncs 49.00 reads/s, 30490 avg bytes/read, 96.68 writes/s, 18.50 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 30091, free list len 19083, seg size 49175, is not empty Ibuf for space 0: size 30091, free list len 19083, seg size 49175, 24395 inserts, 318393 merged recs, 60206 merges Hash table size 4425293, used cells 3168, node heap has 4 buffer(s) 0.00 hash searches/s, 281.36 non-hash searches/s --- LOG --- Log sequence number 115 1818376139 Log flushed up to 115 1818375712 Last checkpoint at 115 1818375695 0 pending log writes, 0 pending chkp writes 7992 log i/o's done, 10.69 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1303861436; in additional pool allocated 12728448 Buffer pool size 65536 Free buffers 0 Database pages 65532 Modified db pages 14 Pending reads 22 Pending writes: LRU 0, flush list 0, single page 0 Pages read 91019, created 152, written 113129 91.49 reads/s, 0.00 creates/s, 135.55 writes/s Buffer pool hit rate 944 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 288 queries in queue Main thread process no. 1792, id 2434898496, state: doing insert buffer merge Number of rows inserted 26399, updated 7272, deleted 0, read 851771 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
