Thank you very much, this fixed it! I made the buffer pool as well as the log file size 256M for now. The MySQL/InnoDB manual suggests that the buffer pool size could be increased up to 80% of the physical memory size, and this s4erver has 2G. It is not clear to me how much of a gain in performance for large transactions I would get by further increasing the buffer pool size...
Thanks again, Sergei -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, July 15, 2004 2:39 AM To: [EMAIL PROTECTED] Subject: Re: Expensive InnoDB queries crash mysql daemon Sergei, ok, this explains it. Your buffer pool is too small. Look at the MySQL manual. All operations in InnoDB happen inside transactions. ALTER TABLE essentially does INSERT INTO newtable SELECT * FROM oldtable; and sets lots of shared row locks on oldtable. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Sergei Skarupo"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, July 15, 2004 9:37 AM Subject: RE: Expensive InnoDB queries crash mysql daemon > Thanks for your reply. > > I think on this machine MySQL was installed from an RPM distribution... = > All the settings are default... > > I seem to have an unisual setup as far as the logfiles are concerned. = > There are no localhost.log and localhost.err files on this machine, nor = > any .log or .err files in the data directory. > > There is mysql.log in the /var/log directory, and it does have a record = > of this crash... Around the time of the crash it got flooded with = > messages like this: > > 040714 14:39:02 InnoDB: WARNING: over 4 / 5 of the buffer pool is = > occupied by > InnoDB: lock heaps or the adaptive hash index! Check that your > InnoDB: transactions do not set too many row locks. > InnoDB: Your buffer pool size is 8 MB. Maybe you should make > InnoDB: the buffer pool bigger? > InnoDB: Starting the InnoDB Monitor to print diagnostics, including > InnoDB: lock heap and hash index sizes. > > > I'm pretty sure that at the moment, no queries were setting locks or = > using transactions explicitly. > Then, the error itself: > > > 040714 14:39:03 InnoDB: Assertion failure in thread 1104214832 in file = > buf0lru.c line 234 > InnoDB: Failing assertion: 0 > InnoDB: We intentionally generate a memory trap. > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this = > binary > or one of the libraries it was linked against is corrupt, improperly = > built, > or misconfigured. This error can also be caused by malfunctioning = > hardware. > We will try our best to scrape up some info that will hopefully help = > diagnose > the problem, but since we have already crashed, something is definitely = > wrong > and this may fail. > > key_buffer_size=3D8388600 > read_buffer_size=3D131072 > max_used_connections=3D0 > max_connections=3D100 > threads_connected=3D1 > It is possible that mysqld could use up to=20 > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = > =3D 225791 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > > > The memory is OK. > > Then it printed a stack trace, which was terminated because a sanity = > check failed... I was able to resolve the stack trace using the = > resolve_stack_dump utility, but that did not help... I'm not including = > it here... > > Finally, the query that was running: > > thd->query at 0x8827870 =3D alter table sensortest_rawdata > change column gate_current gate_current float default null, > change column source_voltage source_voltage float default null > thd->thread_id=3D1 > > > Should I try to change the buffer size? Should I report this as a bug? > > Thanks, > > Sergei > > > > -----Original Message----- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 14, 2004 10:49 PM > To: [EMAIL PROTECTED] > Subject: Re: Expensive InnoDB queries crash mysql daemon > > > Sergei, > > please run CHECK TABLE on the tables, and check if it prints something = > to > the .err log. > > Also, resolve the stack trace printed by the crashing mysqld, as = > explained > in the manual. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up = > MyISAM > tables > http://www.innodb.com/order.php > > Order MySQL technical support from https://order.mysql.com/ > > > ----- Original Message -----=20 > From: ""Sergei Skarupo"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Thursday, July 15, 2004 1:50 AM > Subject: Expensive InnoDB queries crash mysql daemon > > > > ------_=3D_NextPart_001_01C469F5.1B46DF30 > > Content-Type: text/plain; > > charset=3D"iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > Hello All, > > =3D20 > > I hope someone can shed some light on this problem... > > =3D20 > > This concerns large InnoDB tables (having on the order of millions of = > =3D > > rows). > > =3D20 > > When I run ALTER TABLE (for example, to change the default column = > value) =3D > > or UPDATE or DELETE queries that affect many rows, mysqld-max crashes = > =3D > > and apparently gets restarted by mysqld_safe. This also seems to hang = > =3D > > the web server that runs on the same machine and maintains connections = > =3D > > with the database. Of course, the query does not complete. > > =3D20 > > I'm using mysql version 4.0.16 on Red Hat 9 Linux, kernel 2.4.20. > > =3D20 > > Thanks in advance, > > =3D20 > > Sergei > > > > ------_=3D_NextPart_001_01C469F5.1B46DF30-- > > > --=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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
