innodb system variable
Dear All, I have the following two system variable set in my MySQL configuration file under mysqld section. But I am not fully understand what the two variable internally does. innodb_rollback_on_timeout=1 innodb_lock_wait_timeout=600 Any help will be appreciated. Thanks for Your Time Mohan L
performance debian lenny and vmware
Hi Carlos and all: Our architecture is a vm-ware virtual machine with debian lenny as S.O (64 bits), with 4 Gb of RAM . The virtual machine is a dedicated server with MySQL 5.0. (INDB) Attached the my.cnf file. I've searched information about this problem and to many persons the same have problem. My mysql server is too slow. And I'm follow this white paper http://www.vmware.com/files/pdf/Virtualization-for-MySQL-on-VMware.pdf. Thanks a lot 2011/3/28 Carlos Proal carlos.pr...@gmail.com Rafael Performance depends on several things, but none related with debian or vmware per se. So we need more information about you configuration (ram, buffers, etc) and you environment (concurrent users, transactions, etc). Maybe you have not tuned your mysql and it is slow because of that. Carlos Proal On 3/27/2011 1:14 PM, Rafael Valenzuela wrote: Hi all: I have installed mysql 5.0 on a debian lenny 64 bits, in vmware,but I see that there are problems with mysql performance the server is too very slow. any solution? or any idea? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=rav...@gmail.com -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/ -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: innodb system variable
Hi All innodb_rollback_on_timeout=1 Specifies when there is transaction open by session and not committed, If such session is inactive for long time, MySQL by default kicks out such session and transaction perform by session would be rollback innodb_lock_wait_timeout=600 Specify wait for lock on row up to provided seconds, if it is time out, it not try to process transaction further, but roll back will not happen, You need to start you transaction from point it was timeout, else need to be rollback explicitly and re run transaction or wait till session timeout and re run transaction --Anupam K From: Mohan L l.mohan...@gmail.com To: mysql@lists.mysql.com Sent: Tue, 29 March, 2011 12:54:12 PM Subject: innodb system variable Dear All, I have the following two system variable set in my MySQL configuration file under mysqld section. But I am not fully understand what the two variable internally does. innodb_rollback_on_timeout=1 innodb_lock_wait_timeout=600 Any help will be appreciated. Thanks for Your Time Mohan L
A common request
Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons: 1) Maybe MySQL has a good way to do this, and I just don't know about it 2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps. Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles? Ideally, I would want to write something like this: SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580) basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself. But my point is, this is very common for social networking apps. Every app wants to show the X created by your friends, or friends of yours (given some list from a social network) who have taken action X. Here is how I would do it if I had raw access to the MySQL index in memory: a) Sort the list of entries in the IN, in ascending order. b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually. The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order. Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated. Sincerely, Gregory Magarshak Qbix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A common request
How can I quickly find all the articles written by this user's friends, and not just random articles? Taking the simplest possible case, with table friends(userID,friendID) where each friendID refers to a userID in another row, the friends of userID u are ... select friendID from user where userID=u; so articles by those friends of u are ... select a.* from article a join ( select friendID from user where userID=u ) f on a.userID=f.friendID; PB - On 3/29/2011 12:50 PM, Gregory Magarshak wrote: Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons: 1) Maybe MySQL has a good way to do this, and I just don't know about it 2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps. Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles? Ideally, I would want to write something like this: SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580) basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself. But my point is, this is very common for social networking apps. Every app wants to show the X created by your friends, or friends of yours (given some list from a social network) who have taken action X. Here is how I would do it if I had raw access to the MySQL index in memory: a) Sort the list of entries in the IN, in ascending order. b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually. The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order. Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated. Sincerely, Gregory Magarshak Qbix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A common request
Yes, this would be fine. But often, the list of friends is obtained from a social network like facebook, and is not stored internally. Basically, I obtain the friend list in a request to facebook, and then see which of those users have created things. So would I have to create a temporary table and insert all those uids just to make a join? Why not optimize the IN ( ... ) to do the same type of thing? There is also a second problem: I want to use MySQL Cluster, because I expect to have many users. Would it be efficient to use JOIN between the friends table and the articles table? Both tables are partitioned by user_id as the primary key, so the join would have to hit many different nodes. I always tried to avoid joins because I am planning to horizontally partition my data. But if MySQL cluster can handle this join transparently and split it up based on the partition, then that's fine. Do you have any info on this? Greg On 3/29/11 2:10 PM, Peter Brawley wrote: How can I quickly find all the articles written by this user's friends, and not just random articles? Taking the simplest possible case, with table friends(userID,friendID) where each friendID refers to a userID in another row, the friends of userID u are ... select friendID from user where userID=u; so articles by those friends of u are ... select a.* from article a join ( select friendID from user where userID=u ) f on a.userID=f.friendID; PB - On 3/29/2011 12:50 PM, Gregory Magarshak wrote: Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons: 1) Maybe MySQL has a good way to do this, and I just don't know about it 2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps. Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles? Ideally, I would want to write something like this: SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580) basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself. But my point is, this is very common for social networking apps. Every app wants to show the X created by your friends, or friends of yours (given some list from a social network) who have taken action X. Here is how I would do it if I had raw access to the MySQL index in memory: a) Sort the list of entries in the IN, in ascending order. b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually. The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order. Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated. Sincerely, Gregory Magarshak Qbix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A common request
Why not optimize the IN ( ... ) to do the same type of thing? If the argument to IN() is a list of values, it'll be OK. If it's a SELECT, in 5.0 it will be slower than molasses (see The unbearable slowness of IN() at http://www.artfulsoftware.com/queries.php. I always tried to avoid joins because I am planning to horizontally partition my data. A severe unfortunate constraint. Can't help you there. PB - On 3/29/2011 1:27 PM, Gregory Magarshak wrote: Yes, this would be fine. But often, the list of friends is obtained from a social network like facebook, and is not stored internally. Basically, I obtain the friend list in a request to facebook, and then see which of those users have created things. So would I have to create a temporary table and insert all those uids just to make a join? Why not optimize the IN ( ... ) to do the same type of thing? There is also a second problem: I want to use MySQL Cluster, because I expect to have many users. Would it be efficient to use JOIN between the friends table and the articles table? Both tables are partitioned by user_id as the primary key, so the join would have to hit many different nodes. I always tried to avoid joins because I am planning to horizontally partition my data. But if MySQL cluster can handle this join transparently and split it up based on the partition, then that's fine. Do you have any info on this? Greg On 3/29/11 2:10 PM, Peter Brawley wrote: How can I quickly find all the articles written by this user's friends, and not just random articles? Taking the simplest possible case, with table friends(userID,friendID) where each friendID refers to a userID in another row, the friends of userID u are ... select friendID from user where userID=u; so articles by those friends of u are ... select a.* from article a join ( select friendID from user where userID=u ) f on a.userID=f.friendID; PB - On 3/29/2011 12:50 PM, Gregory Magarshak wrote: Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons: 1) Maybe MySQL has a good way to do this, and I just don't know about it 2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps. Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles? Ideally, I would want to write something like this: SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580) basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself. But my point is, this is very common for social networking apps. Every app wants to show the X created by your friends, or friends of yours (given some list from a social network) who have taken action X. Here is how I would do it if I had raw access to the MySQL index in memory: a) Sort the list of entries in the IN, in ascending order. b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually. The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order. Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated. Sincerely, Gregory Magarshak Qbix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A common request
Hi Gregory, Are you sure you'd like to do this using MySQL? What would happen if you start using sharding? Maybe you could consider using a stack (stored in a tool like Redis?). Whenever some user adds some item, you add primary key of the new item to the network updates stack of each friend of the user (and remove the last one). This way, your reads will be fast and you don't need complex joins over multiple shards. Just one of my first ideas which came up. Any thoughts? Best regards, Sander On 03/29/2011 07:50 PM, Gregory Magarshak wrote: Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons: 1) Maybe MySQL has a good way to do this, and I just don't know about it 2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps. Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles? Ideally, I would want to write something like this: SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580) basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself. But my point is, this is very common for social networking apps. Every app wants to show the X created by your friends, or friends of yours (given some list from a social network) who have taken action X. Here is how I would do it if I had raw access to the MySQL index in memory: a) Sort the list of entries in the IN, in ascending order. b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually. The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order. Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated. Sincerely, Gregory Magarshak Qbix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
getting procedure code via mysqldump
I would like to use mysqldump to get a copy of the code for a stored procedure in a format that is similar to the code I used to create it. The problem is that I'm blind and I have to listen to the code to debug it. I think I have a file containing the code that I used to create the stored procedure but I want to make absolutely sure. This is what I've tried: mysqldump --p --routines --no-create-info --no-data --no-create-db --skip-opt --skip-comments --compatible=ansi --result=routines.sql database My problem is that generates a file with a lot of lines I don't understand. for example: /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `TIMETABLE_SYNC`() That appears to be the line to create the stored procedure 'timetable_sync'. But what's with all the other stuff on that line? Can i get rid of it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: getting procedure code via mysqldump
Hi John, The lines you mention are comments , the comments in mysql sql files are enclosed between two delimiters. The first is the sequence /* and the second is the sequence */ , inside the comments you can have a marker constituted by a ! and a number that represents a mysql version. These markers are used to give instructions to specific mysql versions. When a mysql client reads a sql file executes all commands enclosed plus the commented parts that correspond to the server version. Usually you can delete those parts, since in any case they are comments, but you should not need to delete them. I hope I was enough clear, My Android mobile soft keyboard is good, but not so inviting for writing long messages! Claudio On Mar 30, 2011 1:10 AM, John G. Heim jh...@math.wisc.edu wrote: I would like to use mysqldump to get a copy of the code for a stored procedure in a format that is similar to the code I used to create it. The problem is that I'm blind and I have to listen to the code to debug it. I think I have a file containing the code that I used to create the stored procedure but I want to make absolutely sure. This is what I've tried: mysqldump --p --routines --no-create-info --no-data --no-create-db --skip-opt --skip-comments --compatible=ansi --result=routines.sql database My problem is that generates a file with a lot of lines I don't understand. for example: /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `TIMETABLE_SYNC`() That appears to be the line to create the stored procedure 'timetable_sync'. But what's with all the other stuff on that line? Can i get rid of it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: getting procedure code via mysqldump
On 3/29/2011 19:09, John G. Heim wrote: I would like to use mysqldump to get a copy of the code for a stored procedure in a format that is similar to the code I used to create it. The problem is that I'm blind and I have to listen to the code to debug it. I think I have a file containing the code that I used to create the stored procedure but I want to make absolutely sure. This is what I've tried: mysqldump --p --routines --no-create-info --no-data --no-create-db --skip-opt --skip-comments --compatible=ansi --result=routines.sql database My problem is that generates a file with a lot of lines I don't understand. for example: /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `TIMETABLE_SYNC`() That appears to be the line to create the stored procedure 'timetable_sync'. But what's with all the other stuff on that line? Can i get rid of it? As Claudio mentioned, those are version-sensitive comments. In order for a MySQL server to not ignore the comment, it must be a version equal to or greater than the value tagged in the comment. For example, stored procedures did not exist before version 5.0.3 . So all of the stored procedure will be enclosed with comments that look like /*!50003 */ We enhanced the security of the stored procedures themselves by adding the DEFINER= option to the definition. We did this in version 5.0.20. That is why that part of the stored procedure was dumped using the comment tags /*!50020 */ Unfortunately, I have no way at this time to separate the version-specific comments from the rest of the dump. Perhaps someone better than I at using grep, sed, or awk could produce a script to strip those comments and share with the list? Yours, -- 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?unsub=arch...@jab.org
