helbreathszw
说mysql不行的人，是自己没有适当的硬件环境和深入的研究
本来这是c,c++版，照说不应该谈论什么mysql这种话题
但是瞧了这个版的一个热贴说google的算法引发的数据库的探讨，我就有些话如鲠在喉，不吐不快也
首先，有几个人目前管理或是架构过千万级别用户的项目的数据库。
偶有幸有接触这么上述的环境，所以颇有一些心得体会。
mysql完全能够胜任千万，乃至亿级别的用户，很多人就误解其读写性能问题，其实，写压力大可以用分库（不同机器），分表，实在不行用主库用内存表，从库用myisam做备份
当然还有一些比如用memcache做实时性不是很强的信息缓冲写和读，这种手段能非常有效的减轻数据库压力。
有人说提到自己写的什么mysql测试脚本结果不佳，就说mysql写入，查询不行。
首先你的mysql调优过没，你的机器是什么配置的机器，你的系统是做了什么样的优化没，你的硬件是多少个盘组成的盘阵
你写的sql测试语句是多大的写入量，你的连接是否是本地还是通过网络socket进行写入的。
mysql的参数优化有100多个，不才研究了大概最少40-50个参数能让你数据库性能提高5-10倍。
系统的优化内核参数大概也够几百种了吧。
机器的配置内存16G的，8G的也是相差很大的。
综上所述，很多人认为mysql性能不行，我个人感觉是因为他们接触的机器太滥了，大部分是因为磁盘IO所导致，非mysql不行也。
而且也缺乏架构，集中于一个主库，其实分个10个，乃至100个主库，完全能扛住这个亿级的用户。而且绰绰有余，地球人口总数加上也不怕：）
顺便转个ebay用mysql的文章。不喜欢瞧英文的可以瞧这个兄弟的翻译的文章（此人翻译了很多不错的mysql大会的，和一些国外[url]http://highscalability.com/[/url]的网上文章，甚为佩服此君毅力）
[url]http://www.dbanotes.net/database/ebay_personalization_platform_mysql.html[/url]
Fun Facts About eBay
110 Million items for sale on the site
$59 Billion in gross merchandize value (GMV) per year
Approx $2,039 worth of goods traded on the site every second
276 Million registered users
2 Billion URL requests per day
6,000 application servers with 12,000 Java processes
40 Billion database requests per day
300 different databases (over 700 instances)
9 PB of data storage
13 million lines of source code
(In 2008 will surpass Windows NT 4.0 O/S 16 million lines)
Background
Further distinguish the eBay shopping experience
Provide a more relevant and even better user experience
Provide users with a more rich experience with greater continuity
Provide users with the best selection tailored to their interests/profile
Provide better user experience through real time personalization data feedback loop that is immediately available
Provide users with tailored alternatives
Further distinguish the eBay business value proposition
Advertising shown to more relevant buyers
More effective merchandizing and marketing of items
Increase conversion rates through better buyer experience and greater relevancy of items presented to the buyer
Background
eBay needed to expand its real time personalization capabilities
eBay needed to be able to associate more data with sessions
Both personalization and session data were constrained by technology
Cookies limitation
Client side cookie limit of 4KB data
Long term scalability issue of sending all cookie data, whether needed or not
High cost of traditional server side solutions using an OLTP database
eBay’s very large scale quickly multiplies costs in to a very large number
Throughput of OLTP’s decrease with high write ratio of approximately 50%
Large number of licenses/servers needed for throughput was cost prohibitive
High cost of other commercial alternatives at eBay’s very large scale
These constraints were limiting business decisions and had to be solved
General Vision
Every Application Server
Can Access Data
For Every URL Request
(All 2 Billion of them!)
Session Data
Personalization Data
General Requirements
Handle 4 Billion reads/writes per day
Support connections and requests from 12,000 Java processes
High throughput on low cost hardware
Scale both horizontally and vertically for 10x future growth
Scale without operational interruption
High availability and operational failure robustness
Low latency response times
Low licensing, support, and total cost of ownership costs
Enterprise class support agreement
Enterprise class management and monitoring tools
Driver for Java
Why MySQL Memory Engine?
MySQL Memory Engine had the best performance
Very impressive POC results for MySQL Memory Engine
Approx 2X more throughput than nearest competitor (Java driver)
eBay test case of 50/50 read/writes showed approx 13,000 TPS @ 50% CPU for a Sun 4100 running Solaris 10 x86 (2 CPU, Dual Core Opteron, 16GB RAM) for a network client
Handled 20,000 concurrent connections with less than 1% degradation in throughput than baseline case (eBay developed patch)
Production performance has been consistent with POC results
Why MySQL Memory Engine?
MySQL Enterprise had a very attractive cost structure
MySQL’s ability to offer enterprise class support
MySQL’s combined throughput and cost structure provided a low cost system for the scale of eBay
Power and flexibility of using SQL for different needs
A company with a significant track record
Why MySQL Memory Engine?
The power of open source
eBay has developed and contributed two enhancements to MySQL
Support for an event port based threading and connection handling model for scalable connection handling
Support for true variable size columns in MySQL Memory Engine
Option to be able to apply our talent and create the enhancements we need quickly
Receive the benefits of innovations of others via open source
Why MySQL Memory Engine?
The power of an open source company behind the product
Ability to collaborate with MySQL on enhancements to the product
Option to request enhancements from a company behind the product
Out of the box monitoring and administration tools
Eliminate tying up high end eBay talent in owning it ourselves
An enterprise class open source product
Enterprise class support offerings for use in critical systems
eBay Personalization System Overview
MySQL Memory Engine
Cache Tier
Application
Servers
Browser
Persistent
Database
Replication
eBay Personalization System Overview
MySQL Memory Engine
Cache Tier
Application
Servers
Persistent
Database
5 min Batched
Write Back
Read/Write
Cache Miss Read
eBay Personalization System Overview
Replication optional based on criticality of data loss for past 5 min
Trade-off between data criticality versus double the memory cost
Some personalization data may not be critical enough for the additional hardware cost
Single threaded MySQL replication is generally problematic
Once replication falls behind it stays behind with continued traffic
Replication can be achieved via dual writes from the application server performed transparently by the framework
Second write to replica can be asynchronous
Automatic redistribution of data when node failure or draining a node
eBay Personalization System Overview
Write back to persistent database performed by batch process
Evictions performed by batch process based on target free memory
Buffering space is set aside in case persistent database is unavailable
Special techniques used to minimize table lock duration during write back and eviction operations
Results
A business critical system running on MySQL Enterprise for one of the largest scale websites in the world
Highly scalable and low cost system that handles all of eBay’s personalization and session data needs
Ability to handle 4 billion requests per day of 50/50 read/write operations for approximately 40KB of data per user / session
Approx 25 Sun 4100’s running 100% of eBay’s personalization and session data service (2 CPU, Dual core Opteron, 16 GB RAM, Solaris 10 x86)
Results
Highly manageable system for entire operational life cycle
Leveraging MySQL Dashboard as a critical tool in providing insight into system performance, trending, and identifying issues
Adding new applications to ebay.com domain that previously would have been in a different domain because of cookie constraints
Creating several new business opportunities that would not have been possible without this new low cost personalization platform
Leveraging MySQL Memory Engine for other types of caching tiers that are enabling new business opportunities