EPrints Technical Mailing List Archive

Message: #07707

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] New system, crashing periodically b/c MySQL Locking

Hi Fran,
One thing to note - that I never finished getting to the bottom of - if your version of EPrints has been migrated from an earlier version (not sure how early - 3.2 - maybe early 3.3), then you may have some [dataset]__index tables using MyISAM, but without a Primary Key (PK).
When converting to InnoDB, if there is no PK, it will create an internal one in the background - which you won't be able to use.
To see whether this is the case, try the following:

mysql> SHOW CREATE TABLE eprint__index \G
*************************** 1. row ***************************
       Table: eprint__index
Create Table: CREATE TABLE `eprint__index` (
  `fieldword` varchar(255) DEFAULT NULL,
  `pos` int(11) DEFAULT NULL,
  `ids` text,
  KEY `pos` (`pos`),
  KEY `fieldword` (`fieldword`,`pos`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

For 'old' [dataset]__index tables there are two keys, but no PK.
 
On new installs:
mysql> SHOW CREATE TABLE eprint__index \G
*************************** 1. row ***************************
       Table: eprint__index
Create Table: CREATE TABLE `eprint__index` (
  `fieldword` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pos` int(11) NOT NULL,
  `ids` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`fieldword`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


If you have tables without PKs, try running this:
mysql> SELECT fieldword, pos, COUNT(*) c FROM eprint__index GROUP BY fieldword, pos HAVING c > 1;

If that results in an empty set, you *might* be in a good position to define the PK before updating the engine for that table to InnoDB:
mysql> ALTER TABLE eprint__index ADD PRIMARY KEY(fieldword, pos);

This isn't something I've done on a live server - so I'm not sure if there are any implications.

There was a brief mention of this on the list a few years ago:
http://threader.ecs.soton.ac.uk/lists/eprints_tech/thread-21830.html

Cheers,
John



-----Original Message-----
From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of Newman D.R. via Eprints-tech
Sent: 15 February 2019 13:43
To: eprints-tech@ecs.soton.ac.uk; Fran Callaghan <fran.callaghan@dcu.ie>
Subject: Re: [EP-tech] New system, crashing periodically b/c MySQL Locking

Hi Fran,

Yes, that is all you should need to do.  However, if the table you need
to convert is large (lots of rows) like the access of eprint__rindex
table this can take some time.  Some repositories I have converted have
taken as much as 6 hours when there is over 100 million rows in the
access table.

Whilst you convert the table, it will be locked so the general advice
is to take your repository offline whilst you do this, (especially if
it is the access table, as this is will backup INSERT queries every
time some requests a document or abstract page, whilst the table is
being converted).  When I have done this in the past I have just put in
place a splash screen that all requests are temporarily redirected to,
saying the repository will be back online by a certain time in the
future.

Although the MyISAM table will not be destroyed until the InnoDB is
completely built, it is probably advisable to backup any database
tables you intend to convert, just in case.  Also you need to make sure
you have plenty of spare disk space for wherever the database tables
are written to disk (typically /var/lib/mysql/DB_NAME/, e.g.
/var/lib/mysql/dorcas/).  To be safe, you probably need to have as much
free disk space as is currently being used by the database.  This is
because briefly you will need to store both versions of the table and
afterwards the InnoDB tables will take up more space than the MyISAM
ones.

Also, I would make sure the config option "innodb_file_per_table = 1"
is set.  Recent versions of MySQL have this enabled by default but you
can check with:

SHOW VARIABLES LIKE 'innodb_file_per_table';

The value should be set to ON.  If it is, this make sure each table has
its own file on disk.  There are various advantages to doing this [1].

Regarding the optimizer_search_depth, most commonly this is needed when
using simple search if you have a lot of different fields to be
searched over and you have a lot of terms in the search box.  The way
EPrints converts this into an SQL query against its database index is
not very optimal.  Nowadays people commonly use bespoke indexing tools
like Lucene (or Xapian which has been used with EPrints) to make search
more efficient.

Regards

David Newman
[1] https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Finnodb-multiple-tablespaces&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=RaE47lw6I7ze7ilmqf2gAVk4U%2Fje8qd9Kz6bKuSL954%3D&amp;reserved=0
.html
On Fri, 2019-02-15 at 13:00 +0000, Fran Callaghan via Eprints-tech
wrote:
> Hi John/All,
>
> One more question. If I find MyISAM tables in my DB is it a simple
> matter of ALTER TABLE isam_table_name ENGINE = innodb ?
>
> Fran Callaghan
>
>
> On Thu, 14 Feb 2019 at 15:47, Fran Callaghan via Eprints-tech <eprint
> s-tech@ecs.soton.ac.uk> wrote:
> > Hi John,
> >
> > Thanks for the very prompt feedback. I don't have a line in
> > mysql.pm so I'll try setting that (to 3 or 4 as suggested).
> > Interestingly the ideal solution is to "produce better SQL" but
> > isn't it only eprints producing the SQL?
> >
> > As for MyISAM I thought it was deprecated (or at least supplanted
> > by InnoDB) the system was only built 6 months ago. Can anybody tell
> > me how I can determine if it's using MyISAM?
> >
> > Thanks again,
> > Fran
> >
> >
> > On Thu, 14 Feb 2019 at 15:28, John Salter <J.Salter@leeds.ac.uk>
> > wrote:
> > > Hi Fran,
> > > A couple of things to look at:
> > > In ~/perl_lib/EPrints/Database/mysql.pm, do you have a line that
> > > references 'optimizer_search_depth'?
> > > If not, this might be useful: https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Feprints%2Feprints%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=CljMwG6Wf7AmpAaY7yqobbJyy%2FTU6dGunRNan%2BH1cNM%3D&amp;reserved=0
> > > issues/120
> > >
> > >
> > > If you already have that, what about the storage engines the
> > > tables are using?
> > > MyISAM uses table-level locking, but InnoDB use row-level
> > > locking.
> > > I did some investigations a while ago, and found that some
> > > queries that looked like they shouldn't lock a table were doing
> > > so for some reason (I cannot recall the exact details - but I
> > > don't think it's an 'eprints' thing - I could recreate the
> > > scenario in the DB directly).
> > >
> > > Any of that help?
> > > Cheers,
> > > John
> > >
> > >
> > > From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-b
> > > ounces@ecs.soton.ac.uk] On Behalf Of Fran Callaghan via Eprints-
> > > tech
> > > Sent: 14 February 2019 14:50
> > > To: eprints-tech@ecs.soton.ac.uk
> > > Subject: [EP-tech] New system, crashing periodically b/c MySQL
> > > Locking
> > >
> > > Hi Everybody,
> > >
> > > We have a (reasonably) new install that has been grinding to a
> > > halt every week or two. When I look at 'top' on the command line
> > > (Red Hat) mysqld is hogging 100% CPU. When I use mysqladmin
> > > processlist I can see a number of sessions queuing behind this...
> > >
> > > | 104558 | doras | localhost | doras | Query   | 1587 |
> > > statistics                   | INSERT INTO `cache17312`(`pos`,
> > > `eprintid`) SELECT @i:=@i+1, `eprintid` FROM (SELECT
> > > `eprint`.`eprin | 0.000    |
> > >
> > > The other processes (queued) are all waiting for table level lock
> > > and look like this...
> > >
> > >
> > >
> > > | 105134 | doras | localhost | doras | Query   | 487  | Waiting
> > > for table level lock | UPDATE `eprint` SET
> > > `edit_lock_user`='1037', `edit_lock_since`='1550139932',
> > > `edit_lock_until`='1550 | 0.000    |
> > >
> > > | 105135 | doras | localhost | doras | Query   | 486  | Waiting
> > > for table level lock | SELECT
> > > `eprintid`,`rev_number`,`eprint_status`,`userid`,`importid`,`sour
> > > ce`,`dir`,`datestamp_year`,` | 0.000
> > >
> > >
> > >
> > > Eventually the max http processes is reached and the system
> > > crashes. The only remedy is to restart httpd and mariadb.
> > >
> > >
> > >
> > > Any ideas what this insert into cache is and why it's locking the
> > > the system?
> > >
> > > Thanks for any guidance
> > >
> > > Fran Callaghan
> > > Dublin City University
> > >
> > > Séanadh Ríomhphoist/Email Disclaimer
> > > Tá an ríomhphost seo agus aon chomhad a sheoltar leis faoi rún
> > > agus is lena úsáid ag an seolaí agus sin amháin é. Is féidir
> > > tuilleadh a léamh anseo.
> > > This e-mail and any files transmitted with it are confidential
> > > and are intended solely for use by the addressee. Read more here.
> > >
> > >
> > >
> > >
> > >
> > >
> > Séanadh Ríomhphoist/Email Disclaimer
> > Tá an ríomhphost seo agus aon chomhad a sheoltar leis faoi rún agus
> > is lena úsáid ag an seolaí agus sin amháin é. Is féidir tuilleadh a
> > léamh anseo.
> > This e-mail and any files transmitted with it are confidential and
> > are intended solely for use by the addressee. Read more here.
> >
> >
> >
> >
> >
> > *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprint
> > s-tech
> > *** Archive: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=ltc3DDTBMsT7Cs6I4Yr8LxaDlgMNwJUvePIX3NLaTg8%3D&amp;reserved=0
> > *** EPrints community wiki: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=vEgITtF8N0cynBkX55NAXN4BEnw6gx5eeyJNuvTXBlU%3D&amp;reserved=0
> > *** EPrints developers Forum: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=tcSr%2BtU3TT1dHUN4tUWFgjg6MJ4P5K2f7FQ2kP%2BSswU%3D&amp;reserved=0
> >
> Séanadh Ríomhphoist/Email Disclaimer
> Tá an ríomhphost seo agus aon chomhad a sheoltar leis faoi rún agus
> is lena úsáid ag an seolaí agus sin amháin é. Is féidir tuilleadh a
> léamh anseo.
> This e-mail and any files transmitted with it are confidential and
> are intended solely for use by the addressee. Read more here.
>
>
>
>
>
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-
> tech
> *** Archive: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=ltc3DDTBMsT7Cs6I4Yr8LxaDlgMNwJUvePIX3NLaTg8%3D&amp;reserved=0
> *** EPrints community wiki: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=vEgITtF8N0cynBkX55NAXN4BEnw6gx5eeyJNuvTXBlU%3D&amp;reserved=0
> *** EPrints developers Forum: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=tcSr%2BtU3TT1dHUN4tUWFgjg6MJ4P5K2f7FQ2kP%2BSswU%3D&amp;reserved=0

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=ltc3DDTBMsT7Cs6I4Yr8LxaDlgMNwJUvePIX3NLaTg8%3D&amp;reserved=0
*** EPrints community wiki: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=vEgITtF8N0cynBkX55NAXN4BEnw6gx5eeyJNuvTXBlU%3D&amp;reserved=0
*** EPrints developers Forum: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6d6b12b3d3b34caa575108d6935da9a7%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=tcSr%2BtU3TT1dHUN4tUWFgjg6MJ4P5K2f7FQ2kP%2BSswU%3D&amp;reserved=0