Re: Why does the limit use the early row lookup.
On 4/22/2012 11:18 PM, Zhangzhigang wrote: Why does not the mysql developer team to do this optimization? --- 12年4月20日，周五, Reindl Haraldh.rei...@thelounge.net 写道： ... because the mysql optimizer until now is really bad in many situations - order by rand() makes a temporary table wil ALL data as example even with limit select * from table order by rand() limit 10; reads and writes the whole table to disk have fun with large tables :-) When the Optimizer is told to sort a result set in the order determined by a random value created only at the time of the query, what better technique could they use than to materialize the table, sort the data, then return the results? If you can think of a better way of sorting random numbers, please tell us. MySQL has and still does accept solutions from the community. I do admit that at times in our past we have been very slow about processing those submissions but recently we have made great improvements in how we handle those. Also, we have made significant strides in improving our Optimizer in 5.5 and more improvements will be coming in 5.6. Please check out our newer versions to see if we have solved or improved any particular scalability problems you may be having. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: problems with INNODB tables
Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036
RE: Why does the limit use the early row lookup.
InnoDB or MyISAM? PRIMARY KEY (id) is a separate index in MyISAM, so scanning 110 rows is faster than for InnoDB, where the PK is clustered with the data. That is, MyISAM scans a narrow, 2-column, index (id + pointer); InnoDB scans wide rows (all columns). There is no way to avoid scanning 110 rows of something (data or index). If you are doing Pagination via OFFSET and LIMIT -- Don't. Instead, remember where you left off. (More details upon request.) You can trick MySQL into doing late row lookup via a self join: SELECT b.* FROM tbl a JOIN ( SELECT id FROM tbl ORDER BY id LIMIT 100, 10) b ON a.id = b.id Meanwhile, see if it is already a feature request at bugs.mysql.com . If not, add it. Probably the optimization needs heuristics to decide which way to go. The choice of early vs late may depend on all of these: * OFFSET * LIMIT * Number of rows in the table * Width of the table versus width of the key involved. -Original Message- From: 张志刚 [mailto:zhig...@leju.sina.com.cn] Sent: Thursday, April 19, 2012 7:30 PM To: mysql@lists.mysql.com Subject: Why does the limit use the early row lookup. Dear all: I encounted a question that the limit is not use index to lookup row when I issue a sql. Theoretically, the lock is used when the sql update table data and update table indexes, It ensures updating data and updating indexes are synchronous. Why does the limit use early row lookup but not late row lookup? For example : Create table test (id int primary key, name char(20)); select * from test order by id limit 100, 10. The above sql is very slow when one fetch column is not in the indexes and the offset is more than million. The above sql count off the 110 rows and return the top 10 rows. But the sql: select id from test order by id limit 100, 10 is very fast，it skips to count off the 100 rows by using indexes, and count off 10 rows only. I don’t know the reason. My point is that the limit can use late row lookup: lookup rows after checking indexes to optimize the select speed. But the mysql optimizer do it with the early row lookup: lookup all rows before checking indexes when the one fetch column is not in the indexes. Tell me why? Thanks Sincerely yours, Zhigang zhang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: problems with INNODB tables
Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Why does the limit use the early row lookup.
Shawn... ORDER BY RAND() LIMIT 10 Also assuming: Table 10 rows MEMORY is practical for tmp table in this case Here's a faster way: Keep an in-RAM priority queue, truncating it at 10 items. Simply insert rows into it as you walk through the unsorted table. The effort is nearly Order(1), and the memory is only slightly more than the cost of 10 rows. -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Monday, April 23, 2012 5:52 AM To: mysql@lists.mysql.com Subject: Re: Why does the limit use the early row lookup. On 4/22/2012 11:18 PM, Zhangzhigang wrote: Why does not the mysql developer team to do this optimization? --- 12年4月20日，周五, Reindl Haraldh.rei...@thelounge.net 写道： ... because the mysql optimizer until now is really bad in many situations - order by rand() makes a temporary table wil ALL data as example even with limit select * from table order by rand() limit 10; reads and writes the whole table to disk have fun with large tables :-) When the Optimizer is told to sort a result set in the order determined by a random value created only at the time of the query, what better technique could they use than to materialize the table, sort the data, then return the results? If you can think of a better way of sorting random numbers, please tell us. MySQL has and still does accept solutions from the community. I do admit that at times in our past we have been very slow about processing those submissions but recently we have made great improvements in how we handle those. Also, we have made significant strides in improving our Optimizer in 5.5 and more improvements will be coming in 5.6. Please check out our newer versions to see if we have solved or improved any particular scalability problems you may be having. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Why does the limit use the early row lookup.
If you are doing Pagination via OFFSET and LIMIT -- Don't. Instead, remember where you left off. (More details upon request.) Thanks for your answer. Can you tell us the better approach about pagination to prevent to scan all table rows? How to use left off? --- 12年4月24日，周二, Rick James rja...@yahoo-inc.com 写道： 发件人: Rick James rja...@yahoo-inc.com 主题: RE: Why does the limit use the early row lookup. 收件人: 张志刚 zhig...@leju.sina.com.cn, mysql@lists.mysql.com mysql@lists.mysql.com 日期: 2012年4月24日,周二,上午2:54 InnoDB or MyISAM? PRIMARY KEY (id) is a separate index in MyISAM, so scanning 110 rows is faster than for InnoDB, where the PK is clustered with the data. That is, MyISAM scans a narrow, 2-column, index (id + pointer); InnoDB scans wide rows (all columns). There is no way to avoid scanning 110 rows of something (data or index). If you are doing Pagination via OFFSET and LIMIT -- Don't. Instead, remember where you left off. (More details upon request.) You can trick MySQL into doing late row lookup via a self join: SELECT b.* FROM tbl a JOIN ( SELECT id FROM tbl ORDER BY id LIMIT 100, 10) b ON a.id = b.id Meanwhile, see if it is already a feature request at bugs.mysql.com . If not, add it. Probably the optimization needs heuristics to decide which way to go. The choice of early vs late may depend on all of these: * OFFSET * LIMIT * Number of rows in the table * Width of the table versus width of the key involved. -Original Message- From: 张志刚 [mailto:zhig...@leju.sina.com.cn] Sent: Thursday, April 19, 2012 7:30 PM To: mysql@lists.mysql.com Subject: Why does the limit use the early row lookup. Dear all: I encounted a question that the limit is not use index to lookup row when I issue a sql. Theoretically, the lock is used when the sql update table data and update table indexes, It ensures updating data and updating indexes are synchronous. Why does the limit use early row lookup but not late row lookup? For example : Create table test (id int primary key, name char(20)); select * from test order by id limit 100, 10. The above sql is very slow when one fetch column is not in the indexes and the offset is more than million. The above sql count off the 110 rows and return the top 10 rows. But the sql: select id from test order by id limit 100, 10 is very fast，it skips to count off the 100 rows by using indexes, and count off 10 rows only. I don’t know the reason. My point is that the limit can use late row lookup: lookup rows after checking indexes to optimize the select speed. But the mysql optimizer do it with the early row lookup: lookup all rows before checking indexes when the one fetch column is not in the indexes. Tell me why? Thanks Sincerely yours, Zhigang zhang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
