What is my goal?
我的目标是什么？
To measure actual query time, so that optimisation can be done if the time consumed is not satisfactory.
要测量实际查询时间，以便在消耗的时间不令人满意时进行优化。
What have I done so far?
到目前为止我做了什么？
We have been using MySQL and in there we used to measure by using SQL_NO_CACHE.While this does not make MySQL to use the cache as mentioned here it somewhat better than using the cache.
我们一直在使用MySQL，在那里我们通过使用SQL_NO_CACHE来测量。虽然这并没有使MySQL使用这里提到的缓存，但它比使用缓存要好一些。
The actual question is not that MySQL time is actually correct, in fact it is not since it does not disable the OS's cache, but on how to measure time for a query taking the cache out of the picture in Postgres.
实际的问题不是MySQL的时间实际上是正确的，实际上并不是因为它不会禁用操作系统的缓存，而是关于如何测量从Postgres中将缓存移出图片的查询的时间。
From the same SO answer above, it is evident that there is no equivalent for Postgres and the cache in Postgres works very differently.
从上面的相同SO答案中可以看出，Postgres没有相同的功能，Postgres中的缓存工作方式也大不相同。
So if we take a query and run it several times, it may take some time in the beginning, but further calls mostly would come from the cache(from using explain (analyze,buffers).
因此，如果我们进行多次查询并运行它，可能需要一些时间才开始，但是进一步的调用主要来自缓存（来自使用explain（analyze，buffers））。
With the above facts established,how I can measure query time.
建立上述事实后，我如何衡量查询时间。
Problem 1 : We cannot straightaway say that a query is slow for the first time since lot of warmup is required
问题1：由于需要大量的预热，我们不能直接说第一次查询很慢
Problem 2 : If we run the query say for 5 times and take the average, the results are not accurate since it uses the cache
问题2：如果我们运行查询说5次并取平均值，则结果不准确，因为它使用缓存
Questions
问题
- Taking problem 1,2 into consideration, my goal is to ascertain whether a query needs performance tuning, not actual performance/load testing in which case the buffer cache would come into the picture
- 考虑到问题1,2，我的目标是确定查询是否需要性能调优，而不是实际性能/负载测试，在这种情况下缓冲区缓存会进入图片
- From my understanding the cache contains pages in 8KB blocks, and it is just getting off the cache as in a quick read from memory. Is my understanding correct here?
- 根据我的理解，缓存包含8KB块中的页面，它就像从内存中快速读取一样从缓存中获取。我的理解在这里是否正确？
1 个解决方案
#1
0
You can set lowest possible value for cache to reduce cache effects
您可以为缓存设置最低可能值以减少缓存效果
You also can stop pg an run echo 3 /proc/sys/vm/drop_caches to remove os cache
你也可以停止pg运行echo 3 / proc / sys / vm / drop_caches来删除os缓存
But there is no need to real cache Your test environment should be nearest possible conditions to production and in prod u definitely have cache! It's better to compare different ways of getting a query or different ways of designing your schema and choose the best
但是没有必要真正缓存你的测试环境应该是最接近生产的条件，并且产品肯定有缓存！最好比较获取查询的不同方式或设计模式的不同方法，并选择最佳方案
#1
0
You can set lowest possible value for cache to reduce cache effects
您可以为缓存设置最低可能值以减少缓存效果
You also can stop pg an run echo 3 /proc/sys/vm/drop_caches to remove os cache
你也可以停止pg运行echo 3 / proc / sys / vm / drop_caches来删除os缓存
But there is no need to real cache Your test environment should be nearest possible conditions to production and in prod u definitely have cache! It's better to compare different ways of getting a query or different ways of designing your schema and choose the best
但是没有必要真正缓存你的测试环境应该是最接近生产的条件，并且产品肯定有缓存！最好比较获取查询的不同方式或设计模式的不同方法，并选择最佳方案