[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[EP-tech] Partitioning access table (INNODB)



CAUTION: This e-mail originated outside the University of Southampton.
Hola Juan Carlos,
?pudiste resolver este problema?
Yo tengo mi tabla access con 9.3GB y un poco m?s de 37 millones de registros y ya se est? volviendo un problema para los respaldos.
saludos!

On Tue, Mar 12, 2019 at 6:10 AM Juan C. Herraiz Regidor via Eprints-tech <eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>> wrote:
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





[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://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.ucm.es%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547343623%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=WJxMJxG7iN2DSSzj11VhqBwraj7ciZJqYto2ZvRsdCY%3D&amp;reserved=0<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.ucm.es%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547343623%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=WJxMJxG7iN2DSSzj11VhqBwraj7ciZJqYto2ZvRsdCY%3D&amp;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).
[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).

[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.

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547353578%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=HeT%2B%2FHROrEinQ5htzot41JD8s8oWehUqmX%2FxSGFeJ%2FQ%3D&amp;reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547353578%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=fNJpqms75WcfisFHWlsWYq9lGToJikfG6eJaZiWW6jE%3D&amp;reserved=0
*** EPrints developers Forum: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547353578%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=Op02Ppr4Qwg7fa50qpqXanmEexoJgFNwMYVDT8zgNZQ%3D&amp;reserved=0


--
Dagoberto Salas
dago.salas at gmail.com<mailto:dago.salas at gmail.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20210328/56420f1f/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/20210328/56420f1f/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/20210328/56420f1f/attachment-0001.png