› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 3506 天前的主题，其中的信息可能已经有所发展或是发生改变。
现在数据库层想做这样的方案，cluster+sharding，cluster方面资料挺多的，也基本上实现了，但是sharding方面好复杂，一种方案是业务层改变，另一种方案是加一层透明proxy，找了一下好像目前没有好用的proxy。业务层方面改又很费劲，面临以下问题：
- Do I need to shard all of my tables or just the big tables?
- How do I ensure my data is evenly distributed between shards?
- How does sharding affect referential integrity constraints?
- How do I use auto increment values and ensure unique values across all shards?
- How do I perform joins between my sharded tables and non-sharded tables?
- How do I run aggregrate queries that need data from multiple shards?
- What if I need to add more shards later on or change the sharding strategy?
- How do I perform the initial sharding of my existing data?
- What about joins between shards and transactions involving multiple shards?
- How do I ensure data is going to the correct shard?
- How do I implement HA in a sharded environment?
- How does sharding affect my backup/recovery procedures?
请问大家都是怎么sharding的？最好是生产环境验证过的，谢谢
 |
1
Actrace 2015-05-23 16:16:45 +08:00
分发的话,Haproxy不就够了吗..
|
 |
2
zaishanfeng OP @Actrace sharding 比如单表一亿条数据 分成十个子表 每个一千条
|
 |
3
siteshen 2015-05-25 06:10:41 +08:00
1. 用户产生的可能“超载”都需要 sharding
2. sharding时指定个权重即可，刚开始3个sharding时，1:1:1，时隔半年增加3个sharding,1:1:1:5:5:5 即可（具体比例根据业务发展调整） 3. 外键都得删掉，人工检查维护，当然完整性不能得到DB层保证 4. ((timestamp -CONSTANTS) << 23) + (sharding_id << 10) + (auto_increment & (2<<10))（支持2^13个sharding） 5. 避免join 6. 每个sharding里aggregrate，代码里合并 7. 一致性hash 8. return sharding0 if id < 10^9 9. WTF? 10. 代码保证啊 11. WTF? 12. 和去掉外键一样，没法保证 没在生产环境验证过，不谢。 参考文献： [1] http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram [2] http://media.postgresql.org/sfpug/instagram_sfpug.pdf |
Select Language