Skip to main content

数据库分表的意义与数据库中单表的大小控制

转载来自:http://www.woqutech.com/?p=807

请问:MySQL库中单表的大小尽可能控制在多大?采取这种表大小限制的策略的原因是什么?一个MySQL服务器实例中,表的数量有限制吗?单个MySQL服务器实例中,表的大小的总和有限制吗?如果有,考虑的原因是什么?单台PC SERVER上建多个MySQL服务器实例,一般的实例数是多少?是基于什么样的考虑原因?
MySQL数据库分库后,我们的建议单表大小控制在10G以下。限制分拆以后的表的大小有几个好处:
1、表比较小的话,DDL操作更快。由于MySQL部分DDL操作需要锁表,所以表越小,锁表的时间就越短。
2、表越小,数据查询访问的速度越快。MySQL是B树结构,表越小,树的分层越少,IO也会比较少。
3、表越小,最终扩容到MySQL的实例数越多。将数据拆分得越散,数据分布越均匀,扩容的话,能够用更多的服务器来承担并发压力。
建议MySQL服务器的配置:2路6核cpu、192G内存,配有8块SSD或者PCIe Flash卡。这样一台服务器上一般是部署4-8个数据库实例,
这个要根据数据库硬件最大可承载的的CPU,IO(包括IO性能和IO容量),网络容量来评估。
比如双十一的时候,8个实例的flash卡MySQL数据库物理主机,就已经把网络的千兆网卡打满了。
所以需要进行充分的测试,检查在充分的压力下,数据库的能力会先遇到什么瓶颈。表的大小总和并没有限制,如果你是一个正常情况下访问量非常少的表,它的限制就可能在于单机MySQL磁盘或者flash卡的容量大小了。

Comments

Popular posts from this blog

Elasticsearch error when the field exceed limit 32kb

When we post data that the field exceed the limit, elasticsearch will reject the data which error: {"error":{"root_cause":[{"type":"remote_transport_exception","reason":"[cs19-2][10.200.20.39:9301][indices:data/write/index]"}],"type":"illegal_argument_exception","reason":"Document contains at least one immense term in field=\"field1\" (whose UTF8 encoding is longer than the max length 32766), all of which were skipped.  Please correct the analyzer to not produce such terms.  The prefix of the first immense term is You can handle this: 1. you can udpate the mapping part at any time PUT INDEXNAME/_mapping/TYPENAME { "properties" : { "logInfo" : { "type" : "string" , "analyzer" : "keyword" , "ignore_above" : 32766 } } } ignore_above means that we will only keep 32766 bytes 2...

Scrapy ERROR :ImportError: Error loading object 'scrapy.telnet.TelnetConsole': No module named conch

原文: https://stackoverflow.com/questions/17263509/error-while-using-scrapy-scrapy-telnet-telnetconsole-no-module-named-conch/17264705#17264705 On Ubuntu, you should avoid using  easy_install  wherever you can. Instead, you should be using  apt-get ,  aptitude , "Ubuntu Software Center", or another of the distribution-provided tools. For example, this single command is all you need to install scrapy - along with every one of its dependencies that is not already installed: $ sudo apt - get install python - scrapy easy_install  is not nearly as good at installing things as  apt-get . Chances are the reason you can't get it to work is that it didn't quite install things sensibly, particularly with respect to what was already installed on the system. Sadly, it also leaves no record of what it did, so uninstallation is difficult or impossible. You may now have a big mess on your system that prevents proper installations from working as well (or maybe not...

学习服务器配置之路~~

第一个常见的小问题:MySQL安装 os : fedora 20 mysql: mysql-server(5.5) 所有假设你的系统是没有经过特殊配置的。 1: yum install mysql-server 2: mysql 报错:socket连接不上 3: service mysqld start   注意这步是 mysqld 不是mysql 这样就解决。网上的方法好像有点麻烦。 第二个小问题:解压一些文件(.tar.gz)时报错 http://itsfoss.com/how-solve-stdin-gzip-format/ 上面介绍的很清楚,总之要先确认你下载的文件类型。 第三个小问题。配置tomcat服务器 主要问题是比如我的域名是 cqupt.me 而你tomcat服务器的项目在/webapps/{your projectname} 这时你很蛋疼的要 cqupt.me:8080/{your projectname}/index.html。 如果要cqupt.me就可以完成。这样配置: 都是在tomcat下/conf/server.xml 第一步端口。简单 不废话 第二部。 <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true" xmlValidation="false" xmlNamespaceAware="false"> </Host> 在标签中间插入: <Context path=""  docBase="xbwl"  debug="0" reloadable="true"/> docBase="xbwl" xbwl 即为指定的项目。即({your projectname}_ 完整如下: <Host name="localhost" appBase="webapps" ...