[EP-tech] Partitioning access table (INNODB)

Juan C. Herraiz Regidor jcherraiz at ucm.es
Tue Mar 12 12:09:10 GMT 2019


Hello everybody,



our eprints repository has an access table with more than 133 million
records and uploading.



We recently updated the IRStats module version 1.1 in our test repository,
and the reindexing has been running for 9 days and counting.



We have also converted the access table to INNODB and compressed (it took 7
hours).



The MySQL slow queries log reports:



-   At first it took 2 seconds per query:* Query_time: 2.068357*



Time: 190304  0:13:52

# User at Host: eprintsdbo[eprintsdbo] @  [10.147.128.44]  Id:    80

# *Query_time: 2.068357*  Lock_time: 0.000141 Rows_sent: 100000
Rows_examined: 700000

SET timestamp=1551654832;

SELECT
`accessid`,`datestamp_year`,`datestamp_month`,`datestamp_day`,`datestamp_hour`,`datestamp_minute`,`datestamp_second`,`requester_id`,`requester_user_agent`,`referring_entity_id`,`service_type_id`,`referent_id`,`referent_docid`
FROM `access` LIMIT 100000 OFFSET 600000;



-   Nine days later .., it took 660 seconds per query: *Query_time:
661.963604*



# Time: 190312 12:33:07

# User at Host: eprintsdbo[eprintsdbo] @  [10.147.128.44]  Id:  1077

# *Query_time: 661.963604*  Lock_time: 0.000180 Rows_sent: 99787
Rows_examined: 123899787

SET timestamp=1552390387;

SELECT
`accessid`,`datestamp_year`,`datestamp_month`,`datestamp_day`,`datestamp_hour`,`datestamp_minute`,`datestamp_second`,`requester_id`,`requester_user_agent`,`referring_entity_id`,`service_type_id`,`referent_id`,`referent_docid`
FROM `access` LIMIT 100000 OFFSET 123800000;



I was wondering if someone has partitioned the access table (for example
every 10 million records - access) and if this would improve the generation
of statistics.



Regards,

JC











[image: cid:image005.jpg at 01D39E6C.6586DC70]



*Juan Carlos Herraiz Regidor*

Gobierno TI

*Servicios Informáticos · Gobierno TI. Avenida Complutense s/n. 28040
Madrid*

Teléfono: +34 91 394 5130, Fax: +34 91 394 4773

https://emea01.safelinks.protection.outlook.com/?url=www.ucm.es&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C63ef9b3791e34ba12ec908d6a6e38adf%7C4a5378f929f44d3ebe89669d03ada9d8%7C0&sdata=LF2kHvaOLloQ5EZlLrThjo2Gf4IvNijmRe6I49kuUu0%3D&reserved=0

___

La información contenida en este correo es CONFIDENCIAL, de uso exclusivo
del destinatario/a arriba mencionado. Si ha recibido este mensaje por
error, notifíquelo inmediatamente por esta misma vía y proceda a su
eliminación, ya que ud. tiene totalmente prohibida cualquier utilización
del mismo, en virtud de la legislación vigente.



Los datos personales recogidos serán incorporados y tratados en el fichero
'Correoweb', bajo la titularidad del Vicerrectorado de Tecnologías de la
Información, y en él el interesado/a podrá ejercer los derechos de acceso,
rectificación, cancelación y oposición ante el mismo (artículo 5 de la Ley
Orgánica 15/1999, de 13 de diciembre, de Protección de Datos de Carácter
Personal).

[image: cid:image006.png at 01D39E6C.6586DC70] Antes de imprimir este correo
piense si es necesario: el medioambiente es cosa de todos.



This message is private and confidential and it is intended exclusively for
the addressee. If you receive this message by mistake, you should not
disseminate, distribute or copy this e-mail. Please inform the sender and
delete the message and attachments from your system, as it is completely
forbidden for you to use this information, according to the current
legislation. No confidentiality nor any privilege regarding the information
is waived or lost by any mistransmission or malfunction.



The personal data herein will be collected in the file "Correoweb", under
the ownership of the Vice-Rectorate for Information Technologies, in which
those interested may exercise their right to access, rectify, erasure or
right to object the contents (article 15-21 of Regulation (EU) 2016/679,
General Data Protection Regulation).



[image: cid:image006.png at 01D39E6C.6586DC70] Before printing this mail
please consider whether it is really necessary: the environment is a
concern for us all.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190312/d2db6966/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 3824 bytes
Desc: not available
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190312/d2db6966/attachment-0001.jpg 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 1088 bytes
Desc: not available
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190312/d2db6966/attachment-0001.png 


More information about the Eprints-tech mailing list