Python+MySQL的executemany()在EC2上执行极慢求助

性能差异的可能原因 #

1. 数据库配置差异：本地WAMP的MySQL通常默认配置偏向开发场景，已做基础性能优化（如innodb_buffer_pool_size、日志刷盘策略等）；而EC2上的MySQL默认配置保守，未针对高写入场景调优，比如缓冲池过小导致频繁磁盘IO，或innodb_flush_log_at_trx_commit设为1强制每事务刷盘，大幅拖慢写入速度。
2. 硬件与存储瓶颈：本地多使用SSD硬盘，IO性能强劲；EC2若采用低配实例（如t2.micro）或gp2 EBS卷（IOPS不足时会触发性能限制），磁盘写入速度远低于本地，且CPU、内存资源受限会放大执行开销。
3. 连接与存储过程开销：代码中每个线程都新建数据库连接，频繁的连接创建销毁在EC2上会产生额外开销；同时调用存储过程本身有解析、执行的额外成本，批量调用时效率远低于原生INSERT语句，EC2的资源瓶颈会进一步放大这一问题。
4. 线程调度不合理：10线程的并发数若超过EC2实例的CPU核心数，会导致大量上下文切换，降低执行效率。

优化解决方案 #

1. 调整EC2上的MySQL配置 #

• 增大innodb_buffer_pool_size至实例内存的50%-70%（如4G内存实例设为2G），减少磁盘IO。
• 若业务允许放宽一致性要求，将innodb_flush_log_at_trx_commit设为2、sync_binlog设为100，降低日志刷盘频率（注意：此配置存在小概率数据丢失风险，需结合业务场景选择）。
• 调整max_connections至合理值，设置wait_timeout避免闲置连接占用资源。
• 关闭不必要的功能（如已废弃的query_cache），减少资源消耗。

2. 优化Python代码与数据库交互 #

• 使用连接池复用连接：避免每个线程新建连接，改用连接池减少连接开销：

  import mysql.connector.pooling
  
  DB_POOL = mysql.connector.pooling.MySQLConnectionPool(
      pool_name="mypool",
      pool_size=10,
      host=DB_HOST,
      user=DB_USERNAME,
      password=DB_PASSWORD,
      database=DB_NAME
  )
  
  def connect_to_database():
      return DB_POOL.get_connection()
  

• 替换存储过程为批量INSERT语句：直接使用INSERT...ON DUPLICATE KEY UPDATE的原生批量写法，规避存储过程的调用开销：

  def threading(symbol):
      data1 = [(), ()..] # 300条数据
      # 替换为实际表字段
      cols = "col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15"
      update_clause = ", ".join([f"{col.strip()}=VALUES({col.strip()})" for col in cols.split(",")])
      stmt = f"INSERT INTO your_table ({cols}) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE {update_clause}"
      conn = connect_to_database()
      cursor = conn.cursor()
      cursor.executemany(stmt, data1)
      conn.commit()
      conn.close()
  

• 调整线程池大小：根据EC2实例CPU核心数设置，比如2核实例将max_workers设为4-6，避免过多线程导致上下文切换。

3. 优化EC2实例与存储 #

• 升级EC2实例类型：换成CPU/IO优化型实例（如t3.large、c5.large），或选用带本地NVMe存储的实例（如i3系列）提升写入性能。
• 更换EBS卷类型：将gp2替换为gp3并配置足够的IOPS和吞吐量，或使用Provisioned IOPS SSD（io2/io2 Block Express）满足高写入需求。

4. 其他细节优化 #

• 增大单批次数据量：将每个线程处理的数据从300条提升至1000条左右，减少事务提交次数。
• 确保关闭自动提交：保持conn.autocommit=False（默认值），手动批量提交降低事务开销。
• 检查存储过程逻辑：若必须使用存储过程，清理内部冗余逻辑，确保相关字段已建立合理索引，避免不必要的计算或查询。

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