Query time taken on disk
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
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
Re: Stored Procedure help
I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- (c) 850-449-1912 (f) 423-930-8646
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 h.rei...@thelounge.net 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: Stored Procedure help
The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- 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 stdra...@gmail.com 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
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: Stored Procedure help
Anders, I didn't see that at first, but now. I'd agree. Maybe I should read up on stored procedures. On Mon, July 14, 2014 16:25, Anders Karlsson wrote: The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Migration from SQL Server
Hello, I'm a web developer and I'm developing a web application on Rails. I'm gonna use MySQL at production (I'm using Amazon RDS with MySQL) for this app. But this app already has a version of it (developed at ASP.NET) at production using SQL Server. Now I need to migrate the data from SQL Server to MySQL. The databases are very similar: - Few columns change the name. - One table was split in two - Few columns added - Few columns changed the type. Do you suggest any tool the helps with this migration? Thank you, Felipe. -- Felipe Leal Coutinho Website http://www.felipelc.com/ | Linkedin http://www.linkedin.com/pub/felipe-coutinho/21/902/a22 | Facebook http://www.facebook.com/felipelcoutinho
Re: Migration from SQL Server
Workbench provides some migration features and supports SQL Server http://www.mysql.com/products/workbench/migrate/ On Mon, Jul 14, 2014 at 4:47 PM, Felipe Coutinho felipelcouti...@gmail.com wrote: Hello, I'm a web developer and I'm developing a web application on Rails. I'm gonna use MySQL at production (I'm using Amazon RDS with MySQL) for this app. But this app already has a version of it (developed at ASP.NET) at production using SQL Server. Now I need to migrate the data from SQL Server to MySQL. The databases are very similar: - Few columns change the name. - One table was split in two - Few columns added - Few columns changed the type. Do you suggest any tool the helps with this migration? Thank you, Felipe. -- Felipe Leal Coutinho Website http://www.felipelc.com/ | Linkedin http://www.linkedin.com/pub/felipe-coutinho/21/902/a22 | Facebook http://www.facebook.com/felipelcoutinho
