3

My box is pegging out. I'm trying to setup a MySQL box running for:

  • 3000 connections (3 workers for 1000 boxes) (box maxes out at 700 connections, already)
  • Heavy Writes

Current Setup:

The config is: my-innodb-heavy-4G.cnf. Specific modifications are:

  • max_connections = 65000
  • innodb_buffer_pool_size = 5G

Otherwise everything is default. What recommendations do you guys have for a different MySQL setup?

Considerations are:

  1. MySQL Cluster
  2. Master/Slave replication (I don't know if there will be many gains here.)

We're already using the most powerful box AWS has available, so realistically it's looking like a distributed system is possibly the way we should go. Dedicated hardware might be a possibility but it's a very long shot.

What do you recommend/think how we should proceed? Is there a magic configuration I'm missing?

Thanks in advanced, Justin

Justin
  • 141
  • 1
  • 4

2 Answers2

2

MySQL Cluster very rarely apply in a web accessible setup. This product is mostly available for data warehousing in dedicated cluster environment.

MySQL replication (master/slave, dual master, etc.) will not help if you're biased toward write. For replication to happen, a write must be 'forwarded/executed' on all systems... this will easily lower your global performance. Note: replication could be useful if you have table contention (lock on whole table), but if you are using innodb, I'd be surprise that this happens often. Also, the performance cost of having a slave could be mitigated by the time saved in a crisis/recovery scenario - but this isn't the question asked.

You could look into the concept of sharding. Allied with MySQL-Proxy and a carefully crafted LUA script, you could automatically re-write your SQL queries to split write to a cluster of MySQL system (careful about failure rate of AWS instances).

While you say this is a long shot, dedicated hardware option should be carefuly studied. Most IAAS (such as AWS/EC2) environent are prepared for a very heavy bias toward read IO. On dedicated hardware, you could leverage SSD cache and/or storage tiering. You could also leverage dedicated SAN where I/O OPS capacity are tailored to your specific requirement.

CloudWeavers
  • 2,531
  • 1
  • 15
  • 17
2

Disclosure - I work as part of the MySQL Cluster product team

Just to correct the point above, MySQL Cluster is commonly used in web applications for scaling write operations - auto-sharding coupled with multi-master replication gives very high write throughput, ie 2.5m writes per second on a cluster of 8 commodity Intel servers: http://mysql.com/why-mysql/benchmarks/mysql-cluster/

On the other hand, data warehousing is really not a target workload

Would recommend taking a look at the MySQL Performance Guide (reg required) which discusses different sharding strategies: http://mysql.com/why-mysql/white-papers/mysql_wp_cluster_perfomance.php

user846654
  • 161
  • 2
  • Thanks for the reply. Can you speak towards handling the 3000 connections? The write payload itself is relatively small and even a Micro instance box could handle the write load (i.e. 3000 writes every 4 seconds). Also, as we scale to 3000 or 10000 connections, concurrency is a big consideration for us. Thanks! – Justin Dec 16 '11 at 20:42