<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<div style="padding-bottom: 10px; padding-top: 5px;">
<div style="padding:12px; border:1px solid #8D3970; background-color:#F7F9FA; color:#8D3970; font-size:14px; line-height:22px; font-family: Calibri, Arial, Helvetica, sans-serif;">
<strong>CAUTION:</strong> This e-mail originated outside the University of Southampton.
</div>
</div>
<div>
<div dir="ltr">Hola Juan Carlos,
<div>¿pudiste resolver este problema?</div>
<div>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.</div>
<div>saludos!</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Tue, Mar 12, 2019 at 6:10 AM Juan C. Herraiz Regidor via Eprints-tech <<a href="mailto:eprints-tech@ecs.soton.ac.uk">eprints-tech@ecs.soton.ac.uk</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="ES">
<div class="gmail-m_8469264206729299442WordSection1">
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">Hello everybody,</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">our eprints repository has an access table with more than 133 million records and uploading.</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">We recently updated the IRStats module version 1.1 in our test repository, and the reindexing has been running for 9 days and counting.</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">We have also converted the access table to INNODB and compressed (it took 7 hours).</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">The MySQL slow queries log reports:</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="gmail-m_8469264206729299442MsoListParagraph" style="margin-left:35.4pt">
<span style="font-family:"Courier New""><span>-<span style="font:7pt "Times New Roman"">
</span></span></span><span style="font-family:"Courier New"">At first it took 2 seconds per query:<b><span style="color:red"> Query_time: 2.068357</span></b><span style="color:red">
</span></span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""> </span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">Time: 190304 0:13:52</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""># User@Host: eprintsdbo[eprintsdbo] @ [10.147.128.44] Id: 80</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">#
<b><span style="color:red">Query_time: 2.068357</span></b><span style="color:red">
</span>Lock_time: 0.000141 Rows_sent: 100000 Rows_examined: 700000</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">SET timestamp=1551654832;</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">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;</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""> </span></p>
<p class="gmail-m_8469264206729299442MsoListParagraph" style="margin-left:35.4pt">
<span style="font-family:"Courier New""><span>-<span style="font:7pt "Times New Roman"">
</span></span></span><span style="font-family:"Courier New"">Nine days later .., it took 660 seconds per query:
<b><span style="color:red">Query_time: 661.963604</span></b></span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""> </span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""># Time: 190312 12:33:07</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New""># User@Host: eprintsdbo[eprintsdbo] @ [10.147.128.44] Id: 1077</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">#
<b><span style="color:red">Query_time: 661.963604</span></b> Lock_time: 0.000180 Rows_sent: 99787 Rows_examined: 123899787</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">SET timestamp=1552390387;</span></p>
<p class="MsoNormal" style="margin-left:17.4pt"><span style="font-family:"Courier New"">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;</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New"">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.</span></p>
<p class="MsoNormal"><span lang="EN" style="font-size:10pt;font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Courier New"">Regards,</span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Courier New"">JC</span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-family:"Courier New""> </span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span><img width="202" height="52" style="width: 2.1041in; height: 0.5416in;" id="gmail-m_8469264206729299442Imagen_x0020_1" src="cid:ec501793d4b1aa1c_0.1" alt="cid:image005.jpg@01D39E6C.6586DC70"></span></p>
<p class="MsoNormal"><b><span style="font-size:6pt"> </span></b></p>
<p class="MsoNormal"><b><span style="font-size:10pt">Juan Carlos Herraiz Regidor</span></b><span></span></p>
<p class="MsoNormal"><span style="font-size:10pt">Gobierno TI</span><span></span></p>
<p class="MsoNormal"><i><span style="font-size:9pt;color:gray;letter-spacing:1pt">Servicios Informáticos · Gobierno TI. Avenida Complutense s/n. 28040 Madrid</span></i><span style="font-size:9pt;color:gray"></span></p>
<p class="MsoNormal"><span style="font-size:9pt;color:gray">Teléfono: +34 91 394 5130, Fax: +34 91 394 4773</span></p>
<p class="MsoNormal"><span style="font-size:10pt"><a href="https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.ucm.es%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547373492%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=4gU7WhCmX9OESzytbxUSaHWVpgNedIRSWEqzo7EkbJ8%3D&reserved=0" originalSrc="http://www.ucm.es/" shash="s3mipJdStAJTX6ZqfQXwhHr4MPulR/b/miTXXLvPhx19aA1n56b/8T6TBzBi0ufgXOUh1YBXbkA1xP4+1cmfIBkic6za7rt1e/Obyu3HRcnXCz5dOQRnNawOheasKc/S5mGiGlJqS2NWGjFjNM2MifPBLnfcEzon4hd5CbMfeY0=" target="_blank"><span style="color:rgb(5,99,193)">www.ucm.es</span></a></span><span></span></p>
<p class="MsoNormal"><span style="color:gray">___</span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray">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.</span><span style="color:gray"></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:8pt;color:gray"> </span><span style="color:gray"></span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray">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).</span><span style="color:gray"></span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray"><img border="0" width="18" height="20" style="width: 0.1875in; height: 0.2083in;" id="gmail-m_8469264206729299442Imagen_x0020_2" src="cid:ec501793d4b1aa1c_0.2" alt="cid:image006.png@01D39E6C.6586DC70">
Antes de imprimir este correo piense si es necesario: el medioambiente es cosa de todos.</span><span style="color:gray"></span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray"> </span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray">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.</span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray"> </span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray">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).</span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:gray"> </span></p>
<p class="MsoNormal"><span style="font-size:8pt;color:rgb(118,113,113)"><img border="0" width="18" height="20" style="width: 0.1875in; height: 0.2083in;" id="gmail-m_8469264206729299442Imagen_x0020_3" src="cid:ec501793d4b1aa1c_0.2" alt="cid:image006.png@01D39E6C.6586DC70"></span><span style="font-size:8pt;color:gray">
Before printing this mail please consider whether it is really necessary: the environment is a concern for us all.</span></p>
<p class="MsoNormal"> </p>
</div>
</div>
*** Options: <a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech" rel="noreferrer" target="_blank">
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech</a><br>
*** Archive: <a href="https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547383449%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=6U1U%2FLoAubPJ1qV4qvYwaBn7UFlYSSCRU4MLwD%2Fwt58%3D&reserved=0" originalSrc="http://www.eprints.org/tech.php/" shash="XfKd3+mCDjC2Pm5e9jS22dW8jwNMn+MWcIRaY2udQxrAgZ/OiuBpWnGPz0XsTkLMEvfmfSUco1UVx0ig0ATPJ5yHFcmEyRghE+o77N2AIKHY9chBWTbjgaHgtrKkB0BqB/aaBJs04dDkkAfRUjmnkKftBdW0cPqWQT2Uzh8DbKY=" rel="noreferrer" target="_blank">
http://www.eprints.org/tech.php/</a><br>
*** EPrints community wiki: <a href="https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547383449%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=4I9JWOx7yQdGaq70qW3f7yVY%2B9HAdFrvaB6%2BbYhCEXE%3D&reserved=0" originalSrc="http://wiki.eprints.org/" shash="l6fYgGZSN3HGG89Y/teqPtmp1CFr5EEgikxAGIU/98Sk2kCKerGxpaEMlBkP4ttB0cJiT7OwZrL4+bT0fZeEeX9dQQ45jCA2fTkpKFRTITC/Ew4PEbbpHOCk5rNH2KA6NlR0EWXOIgB/hsiuySQgxWhfF0I/1T8lZSEaa9w1Pio=" rel="noreferrer" target="_blank">
http://wiki.eprints.org/</a><br>
*** EPrints developers Forum: <a href="https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cd5d041ed9ea9494375f308d8f26349d8%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637525855547393408%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Scl9SL%2FuY4yXl9nLk4o5O4kfkZqTyXvwh7EWu2TaeS0%3D&reserved=0" originalSrc="http://forum.eprints.org/" shash="FiMHNVJn594F5Z+lF/CnUG6qKApCvn4HffQ5CxyMsFzxug2bYVmM7D7+gAj4ggfbZd8p6FaOHTLcBUwE2CpEmUxPwJhDcjy/Uf7+OEqz4sYh8ijodUXKiRrZDvwz2mOFYiJlYxRPskZ0cP3mtwd2dluQdn/qrcDrWgwD5TC5HKs=" rel="noreferrer" target="_blank">
http://forum.eprints.org/</a><br>
</blockquote>
</div>
<br clear="all">
<div><br>
</div>
-- <br>
<div dir="ltr" class="gmail_signature">Dagoberto Salas<br>
<a href="mailto:dago.salas@gmail.com" target="_blank">dago.salas@gmail.com</a></div>
</div>
</body>
</html>