[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[EP-tech] Partitioning access table (INNODB)
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