数据库查询和插入速度取决于什么？ [英] What does database query and insert speed depend on?
问题描述
在我的工作中，我们有一个小的数据库（如200个表，也许总共有100万行左右）。
我一直期望
相反，我们的性能非常好，每秒的插入速度为几万次，连接建立后的查询时间为毫秒。
问题，这样我们每秒只能得到几百次插入和查询，即使最简单的插入也将永远存在。
我不确定这是否是标准方法行为/性能，或者我们做错了什么。例如，1500个查询暗示在单个键列上连接4个表大约需要10秒钟。使用简单的插入操作将3万个xml格式的数据加载到数据库中需要3分钟，而没有违反任何约束。
该数据库是SQL Server 2005，具有丰富的关系依赖性模型，意味着对数据的许多关系和分类，以及对分类代码和其他一些东西的全套检查约束。
那些时间对吗？如果没有，什么会影响性能？ （所有查询都在索引列上完成）
要进行粗略的比较： SQL Server的TPC-C基准记录大约为每分钟120万笔交易，就像这样最近4年左右的时间（上限为64个CPU操作系统）。这就是每秒〜16k交易的关键。这是在超高端计算机，64个CPU，充足的RAM，每个NUMA节点的关联客户端和服务器上的短剥离I / O系统上使用的（每个主轴仅使用大约1-2％）。请记住，这些是TPC-C事务，因此它们由几个操作组成（我认为平均每个操作有4-5次读取和1-2次写入）。
现在您应按实际部署规模缩减此顶级硬件的规模，并据此为总体 OLTP交易处理设定期望。
对于数据上传，当前世界记录大约是30分钟内达到1TB （如果仍然有效...）。如果在重要的硬件上正确完成，每秒几万次插入是相当雄心勃勃的，但是可以实现的。链接中的文章包含ETL高通量的提示和技巧（例如，使用多个上传流并将其关联到NUMA节点）。
对于您的情况，我会先建议您以及最重要的措施，因此您可以找出瓶颈，然后向特定问题提问，以解决特定的瓶颈问题。一个很好的起点是等待和队列白皮书。
At my work we have a small database (as in two hundred tables and maybe a total of a million of rows or so).
I've always expected it to be quite fast in the order of several ten of thousands insertion per second and with querys taking milliseconds once the connection is established.
Quite the contrary we are having some performance problems so that we only get a couple of hundred insertions per second and querys, even the simplest ones would take for ever.
I'm not enterly sure if that's the standar behavior/performance or we're doing something wrong. For example, 1500 querys which imply joining 4 tables on a single key column take around 10 seconds. It takes 3 minutes to load 300K of data in xml format into the database using simple inserts without violating any constraints.
The database is SQL Server 2005 and has a rich relational dependency model, meaning a lot of relations and categorizations over the data as well as a full set of check constraints for the categorization codes and several other things.
Are those times right? If not, what could be affecting performance? (All queries are done on indexed columns)
To have a rough comparison: the TPC-C benchmark record for SQL Server is at around 1.2 mil transactions per minute, and is been like this over last 4 years or so (caped by the 64 CPU OS limit). That is something in the balpark of ~16k transactions per second. This is on super high end machines, 64 CPUs, plenty of RAM, affinitized clients per NUMA node and a serverly short stripped I/O system (only about like 1-2% of each spindle is used). Bear in mind those are TPC-C transactions, so they consist of several operations (I think is 4-5 reads and 1-2 writes each in average).
Now you should scale down this top of the line hardware to your actual deployment and will get the ballpark where to set your expectations for overal OLTP transaction processing.
For data upload the current world record is about 1TB in 30 minutes (if is still current...). Several tens of thousands of inserts per second is quite ambitious, but achievable, when properly done on serious hardware. The article in the link contains tips and tricks for ETL high troughput (eg. use multiple upload streams and affinitize them to NUMA nodes).
For your situation I would advise first and foremost measure so you find out the bottlenecks and then ask specific questions how to solve specific botlenecks. A good starting point is the Waits and Queues whitepaper.
这篇关于数据库查询和插入速度取决于什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
