I have 1 table with 2.5 billion records(2.5 Cr.) and I am trying to get count of records based on indexed column. it takes 115 seconds to execute same.
我有1个表,有25亿条记录(2.5 Cr。),我正在尝试根据索引列获取记录数。执行相同需要115秒。
Query
Select count(1) from table1 where column=code
Table1: table with 2 billion (2 Cr.)
code gets resultset from DB with 1 to 10k rows,
Column is indexed.
Server Details
Intel Xeon 3.10ghz
8 gb RAM
MySQL server 5.1
MySQL Connector 5.0.6
My.cnf details
Default Engine =InnoDB
Innodn_buffer_pool=4G
Also take too much time for Update query please suggest how do i tune mysql.
还需要花费太多时间来更新查询请建议我如何调整mysql。
1 个解决方案
#1
1
8 Gbytes of ram for a 2 billion row table is not that much.
对于20亿行表而言,8 GB的ram并不是那么多。
The select query presumably just has to read the index, but the index has to be loaded into memory. This requires loading all 2 billion rows of the index into memory and then using it. 115 might seem a little on the long side, but not incredibly long.
选择查询可能只需读取索引,但索引必须加载到内存中。这需要将所有20亿行索引加载到内存中然后使用它。从长远来看,115似乎有点长,但不会太长。
You want to review your memory settings, to be sure you are using available memory. You might consider getting better hardware for this size problem. Alternatively, you might want to partition the table by code, if that is a common filtering clause.
您想要查看内存设置,以确保使用可用内存。您可能会考虑为此尺寸问题获得更好的硬件。或者,您可能希望按代码对表进行分区,如果这是一个常见的过滤子句。
* is probably not the right place for this question; a better place for such optimizations is https://dba.stackexchange.com/.
*可能不是这个问题的正确位置;这种优化的更好地方是https://dba.stackexchange.com/。
#1
1
8 Gbytes of ram for a 2 billion row table is not that much.
对于20亿行表而言,8 GB的ram并不是那么多。
The select query presumably just has to read the index, but the index has to be loaded into memory. This requires loading all 2 billion rows of the index into memory and then using it. 115 might seem a little on the long side, but not incredibly long.
选择查询可能只需读取索引,但索引必须加载到内存中。这需要将所有20亿行索引加载到内存中然后使用它。从长远来看,115似乎有点长,但不会太长。
You want to review your memory settings, to be sure you are using available memory. You might consider getting better hardware for this size problem. Alternatively, you might want to partition the table by code, if that is a common filtering clause.
您想要查看内存设置,以确保使用可用内存。您可能会考虑为此尺寸问题获得更好的硬件。或者,您可能希望按代码对表进行分区,如果这是一个常见的过滤子句。
* is probably not the right place for this question; a better place for such optimizations is https://dba.stackexchange.com/.
*可能不是这个问题的正确位置;这种优化的更好地方是https://dba.stackexchange.com/。