Re: Query time taken on disk
Hi Satendra, On 7/14/2014 5:48 AM, Satendra wrote: Hi there, I'm struggling to find the total time taken by a database query on the disk? As I understand when a database query start execution it takes some time inside the database engine & some time to seek the result from disk (if that is not in cache/buffer) Can anybody from the group please suggest any clue about the execution time on the disk? Many thanks Stdranwl As stated in other emails you can use the performance_schema. Mark Leith has provided a stable version of ps_helper now called the sys schema and can be obtained on github here, https://github.com/MarkLeith/mysql-sys . There is quite a bit of help and examples in the README.md. Also you check into the experimental portion of it called dba helper also on github, https://github.com/MarkLeith/dbahelper . I think you will be able to find what you are looking for utilizing these tools that make performance schema much easier to use and under stand. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query time taken on disk
Hi Satendra, On Jul 14, 2014, at 3:48 AM, Satendra wrote: > Hi there, I'm struggling to find the total time taken by a database query > on the disk? As I understand when a database query start execution it takes > some time inside the database engine & some time to seek the result from > disk (if that is not in cache/buffer) > > Can anybody from the group please suggest any clue about the execution time > on the disk? I have a performance_schema example demonstrating total IO wait time for a workload: http://www.tocker.ca/2014/02/18/todays-practical-use-case-for-performance-schema.html To prepare this data non-aggregated (per-query) is always a little bit difficult: - With select statements there is read ahead. - With write statements there is redo logging (which is grouped together with other statements). Maybe someone else on the list has better ideas on how to accommodate this? - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query time taken on disk
Satendra, Google "show profile" as it may give you all the information that you need. There is a lot more details in the performance_schema if you want to dig into it, but it can be quite difficult to get out. Here is one place to start if you want to pursue that angle: http://www.markleith.co.uk/2011/05/23/monitoring-mysql-io-latency-with-performance_schema/ keith On Mon, Jul 14, 2014 at 5:59 AM, Reindl Harald wrote: > > > Am 14.07.2014 12:48, schrieb Satendra: > > Hi there, I'm struggling to find the total time taken by a database query > > on the disk? As I understand when a database query start execution it > takes > > some time inside the database engine & some time to seek the result from > > disk (if that is not in cache/buffer) > > > > Can anybody from the group please suggest any clue about the execution > time > > on the disk? > > mysql can't know this in any useful way > > "disk" can be anyhting, real disk access, VFS and so cache > the application layer knows nothing about > > -- (c) 850-449-1912 (f) 423-930-8646
Re: Query time taken on disk
Am 14.07.2014 12:48, schrieb Satendra: > Hi there, I'm struggling to find the total time taken by a database query > on the disk? As I understand when a database query start execution it takes > some time inside the database engine & some time to seek the result from > disk (if that is not in cache/buffer) > > Can anybody from the group please suggest any clue about the execution time > on the disk? mysql can't know this in any useful way "disk" can be anyhting, real disk access, VFS and so cache the application layer knows nothing about signature.asc Description: OpenPGP digital signature
