求助：MySQL 300万数据转Elasticsearch及count性能优化方案

Hey there, let’s break down how to get your 3M MySQL records into Elasticsearch efficiently, and make sure ES actually solves your slow count(*) problem long-term. You’ve already tried index tweaks in MySQL and hit walls with PHP plugins and Logstash—here’s what to do next:

1. Fix Your Logstash Configuration (It’s Probably Underperforming) #

If Logstash ran all night without finishing, it’s almost certainly a resource or config issue. Let’s tweak these key settings:

• Boost batch and fetch sizes: In your jdbc input block, increase how much data MySQL sends per fetch and how much Logstash pushes to ES in one go:

  jdbc {
    jdbc_connection_string => "jdbc:mysql://your-mysql-host:3306/your-db"
    jdbc_user => "your-user"
    jdbc_password => "your-pass"
    jdbc_driver_library => "/path/to/mysql-connector-java-8.0.30.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    # Use your optimized join query here (only select needed fields!)
    statement => "SELECT t1.id, t1.name, t2.metadata FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id"
    jdbc_fetch_size => 15000 # Default is usually 1000—bigger means fewer round-trips to MySQL
    batch_size => 8000 # Adjust based on your ES cluster's RAM/CPU (start here, test up)
    schedule => nil # Remove the schedule since this is a one-time import
  }
  

• Cut unnecessary processing: If you’re using filters (like mutate, grok) that don’t add value for this import, strip them out—they waste CPU cycles.
• Check Elasticsearch health: Make sure your ES cluster isn’t starved for resources. For a single node, set the JVM heap to ~50% of available RAM (max 32GB) and ensure disk I/O isn’t spiking (use curl http://your-es-host:9200/_cat/nodes?v to check).

2. Faster Alternatives to Logstash/PHP Plugins #

If Logstash still isn’t moving fast enough, try these purpose-built tools:

• MySQL dump + Elasticsearch Bulk API:
  1. Export your joined data to a CSV with SELECT ... INTO OUTFILE (faster than mysqldump for large datasets):

     SELECT t1.id, t1.name, t2.metadata 
     INTO OUTFILE '/tmp/exported_data.csv'
     FIELDS TERMINATED BY ',' ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
     FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id;
     

  2. Convert the CSV to ES’s bulk format (each document needs a {"index":{}} line followed by the JSON data). A quick Python script can handle this in minutes.
  3. Push the bulk file to ES with curl:

     curl -XPOST "http://your-es-host:9200/_bulk" -H "Content-Type: application/x-ndjson" --data-binary @bulk_data.ndjson
     

• DataX (Open-Source Data Sync Tool): Alibaba’s DataX is built for high-speed data transfers between databases and ES. You just configure a job file that defines the MySQL source (with join queries) and ES target—it’s way faster than custom PHP code or even Logstash for large batches.
• Chunked Import with Custom Script: Write a simple PHP/Python script that fetches data in chunks (using WHERE id > last_id LIMIT 10000 instead of OFFSET to avoid slow MySQL offset scans) and sends bulk requests to ES. This gives you full control over the process.

3. Optimize the MySQL Source Query #

Your join query might be slowing down the entire import before data even leaves MySQL:

• Index join columns: Ensure the columns used in your JOIN clauses (like t1.id and t2.table1_id) have indexes—this cuts down on table scans.
• Avoid SELECT *: Only fetch the fields you actually need in Elasticsearch. Less data to transfer means faster imports and smaller ES indexes.
• Chunk the query: Instead of fetching all 3M rows at once, split the import into chunks using a sorted column (like id):

  SELECT ... FROM table1 t1 JOIN table2 t2 ON ... WHERE t1.id > 0 LIMIT 10000;
  SELECT ... FROM table1 t1 JOIN table2 t2 ON ... WHERE t1.id > 10000 LIMIT 10000;
  

  Repeat this until no more rows are returned—this prevents MySQL from locking up or using too much memory.

4. Ensure Elasticsearch Solves Your Count(*) Problem #

Once your data is in ES, make sure you’re using it correctly to avoid new slowdowns:

• Use POST /your-index/_count instead of mimicking MySQL’s count(*)—ES is optimized for this endpoint.
• Avoid over-sharding your index: For 3M records, 1-3 shards is ideal. Too many shards add overhead to count queries.
• Enable doc values for fields you’ll aggregate/sort on: This speeds up count and aggregation operations (doc values are enabled by default for most field types in recent ES versions, but double-check).

内容的提问来源于stack exchange，提问作者truesource