Re: Why does this query take so long?
Hi René, everybody! René Fournier wrote: [[...]] However, even if the Index can't fit in memory (4GB of RAM, lots free), just reading it from disk should allow sub-millisecond response, no? No chance! Of course, performance of disk drives varies, but AFAIK typical values are in the range of 7 - 10 ms per random access. Assuming the (very unlikely) optimum case of one index access and one data access, this would put you into the range of 15 - 20 ms just for fetching the stuff from disk, not including any CPU time to traverse the data structures etc. Just do some math: A disk with 7,200 rpm has 120 revolutions per second, so it needs a bit more than 8 milliseconds per revolution. Random access means you have to wait (on average) for half a revolution (4 ms) until the desired block passes the disk head, and before that the head needs to be positioned at the proper cylinder (the drive's data sheet might give that time). I guess that even with SSD you will not reach sub-millisecond response times if the data is not in RAM. Strange thing is that I've used my laptop for benchmarking for the past five years and it's always produced results fairly typical or at least consistent in relation to our servers. This new thing is... new. IMO, the most influential factor in single-user database benchmarks are - disk performance - RAM size for caches, cache replacement - history, cache preloading Their relative importance will vary, depending especially on data size. As long as your data size is small enough that RAM differences between server and laptop don't matter too much, performance on the laptop may be a good prediction of that on the server. With multi-user benchmarks, CPU performance, number of cores etc becomes another important factor, again the relative weights will vary. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
Martijn, thanks for your excellent mail: Martijn Tonies wrote: [[...]] A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. To make it more explicit: The term null value is no proper expression in relational theory. Values can be compared to each other, and they are equal or not, some of them even are ordered. Null is no value but a state, signalling value is unknown. And of two unknowns you can't even tell whether they are equal or not, so the result of comparing anything (be it a value or unknown) to unknown is again unknown. That's why logic in SQL is three-valued: true, false, and unknown. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help keep the Internet free
Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
Jerry Schwartz wrote: From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Monday, December 28, 2009 9:25 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Jerry Schwartz wrote: -Original Message- From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Thursday, December 17, 2009 7:25 AM To: mysql@lists.mysql.com Subject: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Dear all, Would you mind to give me the suggestion ? I want to use 32bit php and mysql server on 64bit Windows 2003 Server... So, is it possible ( good for work also ) ? [JS] I'm using that combination on 64-bit Vista. The last time I checked, there was no 64-bit Windows build of PHP. Besides, the database engine doesn't (shouldn't) care who it's talking to. Dear Jerry, In our exiting Server, it is running with 4.4.x php and 4.1.x MySQL in Windows 2000 OS... Now, we are planning to upgrade the Windows OS ( of course include Hardware )... BUT I don't know which new version of php and MySQL is compatiable with the source code ( old ) of php and MySQL... So, would you mind to share your experience with us(me) ? [JS] Unfortunately, I� not sure I can help you. Our databases were fairly small (under 2GB total), so I simply did a mysqldump from the old system (Linux) and imported it into the new one (Vista). PHP is going to be more of a problem. There are some compatibility issues between 4.x and 5.x, and more between 4.x and 6.x. We were already running 5.x, so I didn� have a lot of trouble with that. You can probably get things running, but I strongly suggest that you read up on the newer versions of PHP. There are some changes between 5.x and 5.y that you need to pay attention to. The biggest issue will probably be the deprecation of short tags. There is a setting in php.ini that you can change to allow them. Dear Jerry, Sorry, did you suggest to use 5.x / 5.y ( NO 6.x ) ? BTW, would you mind to tell me which newer version of php and mysql are you running now ? Due to I want to download a version of them and do the test under win2003... Then, I want to tell(reply) you the result(stable)! Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help keep the Internet free
I would encourage everyone on this list to at least read the two articles Fox in the henhouse (http://helpmysql.org/en/theissue/foxinthehenhouse) and GPL is not the answer (http://helpmysql.org/en/theissue/gplisnottheanswer) You may or may not agree with those, but at least then you'll have made an educated decision whether to sign the petition or not. (Disclaimer: I'm a former employee of MySQL AB/Inc, but left before MySQL was bought up by Sun). / Carsten Michael Widenius skrev: Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help keep the Internet free
I am from Indonesia, have been use The Great MySQL for 10 years in development and production environments. Count me in. Willy Sent from my Sony Ericsson XPERIA X1. -Original Message- From: Michael Widenius mo...@askmonty.org Sent: 29 December 2009 19:59 To: mysql@lists.mysql.com Subject: Help keep the Internet free Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:htt [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help keep the Internet free 6D7-36A
I would encourage everyone on this list to at least read the two articles Fox in the henhouse (http://helpmysql.org/en/theissue/foxinthehenhouse) and GPL is not the answer (http://helpmysql.org/en/theissue/gplisnottheanswer) You may or may not agree with those, but at least then you'll have made an educated decision whether to sign the petition or not. (Disclaimer: I'm a former employee of MySQL AB/Inc, but left before MySQL was bought up by Sun). / Carsten Michael Widenius skrev: Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
Dear Jerry, Sorry, did you suggest to use 5.x / 5.y ( NO 6.x ) ? [JS] No, I am not saying that. The system I inherited was running 5.x, and I have not had the time nor urgent need to move upwards. There are some incompatibilities that I would have to deal with. For example, although short tags are deprecated in 5.x they are not allowed in 6.x; and the construct ?= $something ? is not allowed. I would have to go through all of our source code with a fine-tooth comb to fix these. We do run an international shop, with heavy use of UTF-8, so the internationalization features of PHP 6.x would be very useful. If you have the time to fix up your PHP code, then I would suggest that you go to 6.x. BTW, would you mind to tell me which newer version of php and mysql are you running now ? Due to I want to download a version of them and do the test under win2003... Then, I want to tell(reply) you the result(stable)! [JS] PHP 5.2.10, Apache 2.2.14 (Win32), MySQL 5.1.36-community Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
Jerry Schwartz wrote: Dear Jerry, Sorry, did you suggest to use 5.x / 5.y ( NO 6.x ) ? [JS] No, I am not saying that. The system I inherited was running 5.x, and I have not had the time nor urgent need to move upwards. There are some incompatibilities that I would have to deal with. For example, although short tags are deprecated in 5.x they are not allowed in 6.x; and the construct ?= $something ? is not allowed. I would have to go through all of our source code with a fine-tooth comb to fix these. We do run an international shop, with heavy use of UTF-8, so the internationalization features of PHP 6.x would be very useful. If you have the time to fix up your PHP code, then I would suggest that you go to 6.x. BTW, would you mind to tell me which newer version of php and mysql are you running now ? Due to I want to download a version of them and do the test under win2003... Then, I want to tell(reply) you the result(stable)! [JS] PHP 5.2.10, Apache 2.2.14 (Win32), MySQL 5.1.36-community Dear Jerry, Do you know which version of newer version of php which support the short tags ( allowed ) ? Or would you mind to tell me how to fix the problem of php source code and connect with mysql db ? Thanks ! Edward.
Re: Help keep the Internet free
Monty, Good looking site. I just signed the petition and hope others will too. Go get'em Monty! Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Weeks
ML, trying to write some SQL that will give me records for the CURRENT WEEK. Example, starting on a Sunday and going through Saturday. This week it would be Dec 27 - Jan 2. For the week of any date @d: ... WHERE order_date BETWEEN AddDate(@d, -DayOfWeek(@d)+1) AND AddDate(@d, 7-DayOfWeek(@d)) ... PB - ML wrote: Hi All, trying to write some SQL that will give me records for the CURRENT WEEK. Example, starting on a Sunday and going through Saturday. This week it would be Dec 27 - Jan 2. I am doing this so I can write a query that will show orders that are placed during the current week. Here is what I have, but this is showing from today for the next seven days. SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7) AND DATEDIFF(NOW(),orders.order_date) 7; Would anyone have any advice? -Jason No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.431 / Virus Database: 270.14.123/2592 - Release Date: 12/29/09 07:47:00
RE: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Tuesday, December 29, 2009 10:35 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Jerry Schwartz wrote: Dear Jerry, Sorry, did you suggest to use 5.x / 5.y ( NO 6.x ) ? [JS] No, I am not saying that. The system I inherited was running 5.x, and I have not had the time nor urgent need to move upwards. There are some incompatibilities that I would have to deal with. For example, although short tags are deprecated in 5.x they are not allowed in 6.x; and the construct ?= $something ? is not allowed. I would have to go through all of our source code with a fine-tooth comb to fix these. We do run an international shop, with heavy use of UTF-8, so the internationalization features of PHP 6.x would be very useful. If you have the time to fix up your PHP code, then I would suggest that you go to 6.x. BTW, would you mind to tell me which newer version of php and mysql are you running now ? Due to I want to download a version of them and do the test under win2003... Then, I want to tell(reply) you the result(stable)! [JS] PHP 5.2.10, Apache 2.2.14 (Win32), MySQL 5.1.36-community Dear Jerry, Do you know which version of newer version of php which support the short tags ( allowed ) ? Or would you mind to tell me how to fix the problem of php source code and connect with mysql db ? [JS] I believe that all 5.x versions of PHP support short tags, but there is a setting in php.ini that you have to set. As for your second question, I don’t know what you mean. With PHP 5.2.10, I’ve used the mysql, mysqli, and PDO interfaces in both procedural and object-oriented forms (just to stretch my mind). I think that anything in 4.x would be using the mysql interface, although I’m not positive. In any event, it probably is upward-compatible. You’ll want a decent text editor that can search for strings in multiple files, preferably one that can use regular expressions. You really need to check the PHP documentation. Thanks ! Edward.
Re: Weeks
If all you want is the current week then the query is simple: SELECT * FROM orders where WEEK(orders.order_date) = WEEK(NOW()) The default is thje day starts on Sunday so the second value is not needed. WEEK(NOW(),7) is equivalent to WEEK(NOW(),0) - the valid values are 0 - 6. As for performance, I tested the query against a table I have with 199,826 rows - it returned the data in 0.016 seconds (selecting distinct week(date)) and selecting * returned 3,816 rows in 0.827 seconds. One concern will be when the data spans years - in that case you will need to also check for year: SELECT * FROM orders where WEEK(orders.date) = WEEK(NOW()) and YEAR(orders.order_date) = YEAR(NOW()) - Original Message - From: ML mailingli...@mailnewsrss.com To: mysql@lists.mysql.com Sent: Monday, December 28, 2009 5:14 PM Subject: Weeks Hi All, trying to write some SQL that will give me records for the CURRENT WEEK. Example, starting on a Sunday and going through Saturday. This week it would be Dec 27 - Jan 2. I am doing this so I can write a query that will show orders that are placed during the current week. Here is what I have, but this is showing from today for the next seven days. SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7) AND DATEDIFF(NOW(),orders.order_date) 7; Would anyone have any advice? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Plugin
On Sun, Dec 27, 2009 at 10:40 AM, Kaushal Shriyan kaushalshri...@gmail.com wrote: On Wed, Dec 23, 2009 at 5:38 PM, Kaushal Shriyan kaushalshri...@gmail.com wrote: Hi I am running collectd (http://www.collectd.org/wiki/index.php/Plugin:MySQL) version 4.9.0 on Ubuntu Linux 8.04 (Hardy) Server. Plugin mysql Database SMSPAY Host localhost User nagios Password Database smspay Socket /var/run/mysqld/mysqld1.sock # MasterStats true /Database # Database SMSIN Host localhost User nagios Password Database smsin Socket /var/run/mysql/mysqld2.sock # SlaveStats true # SlaveNotifications true /Database /Plugin # mysql -u nagios -h localhost -S /var/run/mysqld/mysqld1.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | SMSPAY | | mysql | ++ 3 rows in set (0.00 sec) mysql quit Bye # mysql -u nagios -h localhost -S /var/run/mysqld/mysqld2.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | SMSIN | | mysql | ++ 3 rows in set (0.00 sec) mysql I get the below issue in the collectd.log [2009-12-21 03:12:22] mysql_real_connect failed: Access denied for user 'root'@'localhost' (using password: NO) [2009-12-21 03:12:22] read-function of plugin `mysql' failed. Will suspend it for 10 seconds. I am not able to figure out why i get r...@localhost and not nag...@localhost in the collectd.log file even after specifying it in the collectd.conf file. Please suggest/guide. Thanks, Kaushal Hi Again, Any updates to my earlier post to this Mailing List ? Thanks, Kaushal Hi Again, I have not got any reply for my query. Checking in again on this Mailing List. Thanks, Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Aborted_connects is incresing repidly
Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. What are reasons and how do i decrease the same? We are using connect() method in PHP code and have tried below command mysqladmin flush-hosts but still the value is same. Thanks Jeetendra Ranjan
RE: Aborted_connects is incresing repidly
Jetendra, Can u attach your my.cnf files and also your variable status (show variable status). This is required because we need to tune some params. Thanks, Dilipkumar -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, December 30, 2009 11:43 AM To: mysql@lists.mysql.com Subject: Aborted_connects is incresing repidly Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. What are reasons and how do i decrease the same? We are using connect() method in PHP code and have tried below command mysqladmin flush-hosts but still the value is same. Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
