title: Loading Elasticsearch via Logstash on large dataset runs very slowly
body: <p>I have a large dataset in MySql (around 2.2 million rows) and my importing to Elasticsearch via Logstash works, but now is going <strong>incredibly slowly</strong>. </p>
  
  <p>On my local machine in vagrant instances with 4GB RAM, each, it went relatively quickly (took 3 days) compared to taking an estimate 80+ days for a server-to-server transfer.</p>
  
  <p>The query is quite complex (using a subquery, etc).</p>
  
  <p>I switched the mysql server from using the the /tmp directory to using the /data/tmp_mysqldirectory, but even then I was occasionally running out of temporary space. when I switched to the /data/tmp_mysql directory to hold the /tmp files instead of the /tmp directory.</p>
  
  <p>e.g: I was getting the error:</p>
  
  <p>message=>"Exception when executing JDBC query,
  exception Sequel::DatabaseError: Java::JavaSql::SQLException
  Error writing file '/data/tmp_mysql/MYHPf8X5' (Errcode: 28)</p>
  
  <p>I updated my query to have this limit (200):
  UPDATE p_results set computed_at="0000-00-00 00:00:00" WHERE computed_at IS NULL LIMIT 200;</p>
  
  <p>My configuration file looks like this: (notice that I'm using paging with a page size of 10000).</p>
  
  <pre><code>    input {
      jdbc {
          jdbc_connection_string =&gt; "jdbc:mysql://xxx.xxx.xxx.xxx:3306/xxx_production"
          jdbc_user =&gt; "xxx"
          jdbc_password =&gt; "xxx"
          jdbc_driver_library =&gt; "/usr/share/java/mysql.jar"
          jdbc_driver_class =&gt; "com.mysql.jdbc.Driver"
          statement_filepath =&gt; "./sql/req.sql"
          jdbc_paging_enabled =&gt; "true"
          jdbc_page_size =&gt; 10000
      }
  }
      output {
          elasticsearch {
              index =&gt; "xxx_resultats_preprod2"
              document_type =&gt; "resultats"
              hosts =&gt; ["localhost:9200"]
              codec =&gt; "plain"
              template =&gt; "./resultats_template.json"
              template_name =&gt; "xxx_resultats"
              template_overwrite =&gt; true
              document_id =&gt; "%{result_id}"
          }
      }
  </code></pre>
  
  <p>I've looked at some of the documentation <a href="https://www.elastic.co/guide/en/logstash/current/tuning-logstash.html" rel="nofollow noreferrer">here</a></p>
  
  <p>running <code>free -m</code> on my logstash/elasticsearch server, I see this:</p>
  
  <blockquote>
  <pre><code> total   used   free shared  buffers  cached
  </code></pre>
    
    <p>Mem:          3951       2507       1444          0        148        724</p>
    
    <p>-/+ buffers/cache:       1634       2316</p>
    
    <p>Swap:         4093        173       3920</p>
  </blockquote>
  
  <p>So total RAM= 4GB, and 2.5GB or <strong>63.4%</strong> of it is used. So Ram on the Elasticsearch server doesn't seem to be the issue.</p>
  
  <p>running <code>free -m</code> on my MySql server I see this:</p>
  
  <blockquote>
  <pre><code>         total       used       free     shared    buffers     cached
  </code></pre>
    
    <p>Mem:          3951       3836        115          0          2       1154</p>
    
    <p>-/+ cache:       2679       1271</p>
    
    <p>swap:       4093        813       3280</p>
  </blockquote>
  
  <p>So total Ram = 4GB and ~3,8GB or <strong>97%</strong> is used. This looks like a problem.</p>
  
  <p>My theories are that I'm occasionally swapping to disk and that is part of the reason why it's slow. Or maybe I'm using BOTH paging and a limit and that's slowing things down?</p>
  
  <p>The load average on the Mysql server is relatively low right now.</p>
  
  <pre><code>top
  </code></pre>
  
  <blockquote>
    <p>load average: 1,00, 1,00, 1,00</p>
  </blockquote>
  
  <p>under /data I see:</p>
  
  <pre><code>sudo du -h -d 1
  </code></pre>
  
  <blockquote>
    <p>13G   ./tmp_mysql </p>
    
    <p>4,5G  ./production </p>
  </blockquote>
  
  <p>using <code>df-h</code> I see:
  total used utilization%
      /dev/sdb1           32G    6,2G   24G  21% /data</p>
  
  <p>If someone can help me make my queries execute much faster I'd very much appreciate it!</p>
  
  <p>Edit:
  Thank you all for your helpful feedback. It turns out my logstash import had crashed (due to running out of /tmp space in Mysql for the subquery), and I assumed that I could just keep running the same import job. Well, I could run it, and it loaded into elastic, but very, very slowly. When I re-implemented the loading of the index entirely and started running it on a new index, the load time became pretty-much on par with what it was in the past. I estimate it will take 55 hours to load the data - which is a long time, but at least it's working reasonably now.</p>
  
  <p>I did an EXPLAIN on my Mysql subquery and found some indexing issues I could address/improve, too.</p>