Re: mysqldump: Error 2013: Lost connection to MySQL server
Hi, please increase your interactive_timeout variable to some big number and also try to log the erros if any thing by using the command: mysqldump --opt db_name db_name.sql -p 2bkp.err check if you get some thing in the bkp.err file. Regards, Chandru, www.mafiree.com On Mon, Jan 12, 2009 at 9:07 AM, Daniel Kasak d...@entropy.homelinux.orgwrote: Hi all. I have a 30GB innodb-only database in mysql-5.0.54. I have always done nightly backups with: mysqldump --opt db_name db_name.sql -p Recently this started failing with: Error 2013: Lost connection to MySQL server I have checked all tables for corruption - nothing found. Also as far as I can tell there are no issues with clients using the database. There have been no crashes since I did a full restore. So I assume we can rule out corruption. I have searched around for the error message, and found people discussing the max_allowed_packet option. I've tried increasing the server's max_allowed_packet to many different values. Currently it's at 128M, which is *way* over the default. I have also used the --max_allowed_packet option simultaneously with mysqldump. And lastly, I have been restarting the server after each my.cnf change. The data was inserted via the 'dbmail' application ( http://www.dbmail.org ), while the server was set up with the default max_allowed_packet size. DBMail breaks up message into chunks, and stores these chunks in individual records. I'm not sure what the default size of these chunks is, but I belive it's a reasonable value anyway. What next? I *must* get regular backups working again ... Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=chandru@gmail.com
Re: Locking database when 'creating sort index'
Hi David, I think try using show full processlist that shall tell the query that is running. I think the problem is with your query only. we need to fine tune the query. Please send the query and the explain plan for the same. share more stats on things that you notice during that time. Regards, Chandru forums.mafiree.com On Thu, Jan 8, 2009 at 12:37 AM, David Scott critt...@desktopcreatures.comwrote: Oh and we increased the key_buffer_size=1200M (30% of ram) no change. 2009/1/7 David Scott critt...@desktopcreatures.com 1) InnoDb2) 5.0.51 on Linux 3) No, a Select with a bunch of Joins, a Where, group and order 4) 37 seconds 5) Yes 6) Show Processlist does not show anything, just the user, what are you looking for? 2009/1/7 mos mo...@fastmail.fm At 11:20 AM 1/7/2009, you wrote: When we run a large query other queries start to back up when the large one gets to the 'creating sort index' phase, this lock seems to affect the whole server, all databases... does anyone know what may be causing this? Thanks in advance -- David Scott David, Can you provide us with more info? 1) Is this an InnoDb table or MyISAM? 2) What version of MySQL are you using? 3) Are you using Create Index or Alter Table? Can you give us the syntax you are using? 4) How long does it take? Can you give us the table structure # of indexes? 5) Are these queries that are backed up, referencing the table you are building the index on? 6) Can you provide us with a Show Process List? This should help the members of this list give you a better more informed answer. Offhand I suspect your key_buffer_size may be too low and MySQL is attempting to build the index on disk rather than in memory. If the index can be built in memory it will be 10x faster than building the index on disk. That is why adding as much ram as possible to your server will help. This is set in your my.cnf file: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=500M If you increase your key_buffer size from the default value to 30% of your memory, you should get indexes built faster. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=critt...@desktopcreatures.com
Re: On fighting with master-slave replication lag
Hi, I use Mycat to monitor more than 300 servers using a single config file. It can be used only for replication monitoring though Regards, Chandru www.mafiree.com On Tue, Dec 23, 2008 at 10:51 PM, Andy Shellam andy-li...@networkmail.euwrote: Hi I would suggest a Nagios monitoring system, useful for many different checks and with plugins to check also mysql replication. I'll second this. The standard check_mysql plugin included with Nagios allows you to monitor a MySQL slave and alert when the lag behind the master is larger than a given threshold (e.g. 600 seconds.) We had an issue last week where the slave's SQL thread died following a server failure - Nagios caught it and let us know immediately. www.nagios.org. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=chandru@gmail.com
Re: How many users access MySQL
Hi Shuly, you can use the method suggested by Shuly or you cook up your query logs if you need more accurate information. May be you can write a small script to do the job. Regards, Chandru forums.mafiree.com On Mon, Dec 22, 2008 at 7:34 AM, mos mo...@fastmail.fm wrote: At 02:32 PM 12/21/2008, you wrote: Hi, Is there any way to figure out how many, if any, people are accessing and using our MySQL server? I am aware of the SHOW PROCESSLIST command, but this one only shows how many processes are currently running. I would like to register this information, and be able to analyze it later. Thanks, Shuly, Shuly, Try show status where variable_name like %connections%. This will tell you the number of connections, not the number of users. I don't think you can find the number of connected users unless you build it into your application. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=chandru@gmail.com
Re: About combine ssl with mysql.
can you please share how you have solved that? Did u use mysql -u root -p --ssl when connecting? Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:15 PM, Moon's Father yueliangdao0...@gmail.comwrote: I have solved this problem. ^_^ On Fri, Dec 12, 2008 at 10:54 AM, Moon's Father yueliangdao0...@gmail.comwrote: Hi. I want to know how to connect mysqld with ssl encryption from windows or linux system. Any reply will be appreciated. Here is my status. mysql status -- /usr/local/mysql-ytt/bin/mysql Ver 14.14 Distrib 5.1.30, for pc-linux-gnu (i686) using EditLine wrapper Connection id: 4 Current database: Current user: r...@localhost SSL:Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.1.30-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql3309.sock Uptime: 1 hour 2 min 4 sec Threads: 2 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.4 -- mysql show variables like '%ssl%'; +---+---+ | Variable_name | Value | +---+---+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /home/david_yeung/ssl/openssl/cacert.pem | | ssl_capath| | | ssl_cert | /home/david_yeung/ssl/openssl/server-cert.pem | | ssl_cipher| | | ssl_key | /home/david_yeung/ssl/openssl/server-key.pem | +---+---+ 7 rows in set (0.00 sec) Here is my.cnf. [mysqld] # SSL options. ssl-ca=/home/david_yeung/ssl/openssl/cacert.pem ssl-cert=/home/david_yeung/ssl/openssl/server-cert.pem ssl-key=/home/david_yeung/ssl/openssl/server-key.pem [client] # SSL options. ssl-ca=/home/david_yeung/ssl/openssl/cacert.pem ssl-cert=/home/david_yeung/ssl/openssl/client-cert.pem ssl-key=/home/david_yeung/ssl/openssl/client-key.pem Here is my user. mysql show grants for root@'%'; +--+ | Grants for r...@% | +--+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE SSL | +--+ 1 row in set (0.00 sec) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MYSQL startup problem
Hi, I think you may have to get me the output of netstat -an (this only tells the ports that are all listening). sorry for the confusion. can you connect using mysql -u root -p and also using mysql -u root -p -h 127.0.0.1 one thing that i find here is that you have missed the -p option which prompts for password. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:39 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, Pls. Find response below: C:\wamp\mysql\binMysql -h localhost -u root ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -Original Message- From: chaim.rie...@gmail.com [mailto:chaim.rie...@gmail.com] Sent: 12 December 2008 13:13 To: BAJAJ POOJA; Chandru Cc: mysql@lists.mysql.com Subject: Re: MYSQL startup problem When you do the following Mysql -h localhost -u root. What do you get ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:43:45 To: chaim.rie...@gmail.com; Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, It doesn't allows me to telnet port 3306. If I ping that port I receive Request timed out. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -Original Message- From: chaim.rie...@gmail.com [mailto:chaim.rie...@gmail.com] Sent: 12 December 2008 13:02 To: BAJAJ POOJA; Chandru Cc: mysql@lists.mysql.com Subject: Re: MYSQL startup problem It says that is started mysql Can you telnet to port 3306 ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:27:02 To: Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, Pls find logs attached below in mail: 080605 15:28:08 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 9:32:18 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 9:32:18 InnoDB: Starting shutdown... 080606 9:32:21 InnoDB: Shutdown completed 080606 9:32:21 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 9:30:58 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 11:43:53 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 11:43:53 InnoDB: Starting shutdown... 080606 11:43:54 InnoDB: Shutdown completed 080606 11:43:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 11:43:55 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 9:29:04 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 10:14:40 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 10:14:40 InnoDB: Starting shutdown... 080609 10:14:41 InnoDB: Shutdown completed 080609 10:14:41 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 10:26:27 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 16:45:52 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 16:45:52 InnoDB: Starting shutdown... 080609 16:45:54 InnoDB: Shutdown completed 080609 16:45:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 17:01:50 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080610 11:05:35 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080610 11:05:35 InnoDB: Starting shutdown... 080610 11:05:36 InnoDB: Shutdown completed 080610 11:05:36 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080610 11:11:23 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080613 17:28:21 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080613 17:28:21 InnoDB: Starting shutdown... 080613 17:28:24 InnoDB: Shutdown completed 080613 17:28:24 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080616 11:28:22 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080701 11:10:31 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080701 11:10:31 InnoDB: Starting shutdown... 080701 11:10:33 InnoDB: Shutdown completed 080701 11:10:33 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080701 11:12:34 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections
Re: MYSQL startup problem
Hi, I find that mysql is running fine :) TCP * 0.0.0.0:3306 * 0.0.0.0:0 LISTENING . you are connecting wrongly please connect using mysql -u root -p -h 127.0.0.1 I find that you tried the same option both times :) Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:53 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, Pls. find response below: C:\wamp\mysql\binnetstat -an Active Connections Proto Local Address Foreign AddressState TCP0.0.0.0:25 0.0.0.0:0 LISTENING TCP0.0.0.0:80 0.0.0.0:0 LISTENING TCP0.0.0.0:1350.0.0.0:0 LISTENING TCP0.0.0.0:4450.0.0.0:0 LISTENING TCP0.0.0.0:2701 0.0.0.0:0 LISTENING TCP0.0.0.0:2702 0.0.0.0:0 LISTENING TCP0.0.0.0:3306 0.0.0.0:0 LISTENING TCP0.0.0.0:3389 0.0.0.0:0 LISTENING TCP0.0.0.0:8081 0.0.0.0:0 LISTENING TCP0.0.0.0:13468 0.0.0.0:0 LISTENING TCP127.0.0.1:1074 0.0.0.0:0 LISTENING TCP172.21.138.200:139 0.0.0.0:0 LISTENING TCP172.21.138.200:1074172.21.136.21:139 TIME_WAIT TCP172.21.138.200:3389172.21.138.11:2590 ESTABLISHED TCP172.21.138.200:4534172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4535172.21.136.21:1026 TIME_WAIT TCP172.21.138.200:4537172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4538172.21.136.21:389 TIME_WAIT TCP172.21.138.200:4575172.21.13.139:445 SYN_SENT TCP172.21.138.200:4576172.21.13.140:445 SYN_SENT TCP172.21.138.200:4577172.21.13.141:445 SYN_SENT TCP172.21.138.200:4578172.21.13.142:445 SYN_SENT TCP172.21.138.200:4579172.21.13.143:445 SYN_SENT TCP172.21.138.200:4580172.21.13.144:445 SYN_SENT TCP172.21.138.200:4581172.21.13.145:445 SYN_SENT TCP172.21.138.200:4582172.21.13.146:445 SYN_SENT TCP172.21.138.200:4583172.21.13.147:445 SYN_SENT TCP172.21.138.200:4584172.21.13.148:445 SYN_SENT UDP0.0.0.0:69 *:* UDP0.0.0.0:445*:* UDP0.0.0.0:500*:* UDP0.0.0.0:1025 *:* UDP0.0.0.0:1026 *:* UDP0.0.0.0:1221 *:* UDP0.0.0.0:4500 *:* UDP0.0.0.0:8081 *:* UDP0.0.0.0:8082 *:* UDP127.0.0.1:123 *:* UDP127.0.0.1:1900 *:* UDP127.0.0.1:4542 *:* UDP172.21.138.200:123 *:* UDP172.21.138.200:137 *:* UDP172.21.138.200:138 *:* UDP172.21.138.200:1900*:* C:\wamp\mysql\binmysql -u root -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) C:\wamp\mysql\binmysql -u root -p Enter password: ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -- *From:* Chandru [mailto:chandru@gmail.com] *Sent:* 12 December 2008 13:40 *To:* BAJAJ POOJA *Cc:* chaim.rie...@gmail.com; mysql@lists.mysql.com *Subject:* Re: MYSQL startup problem Hi, I think you may have to get me the output of netstat -an (this only tells the ports that are all listening). sorry for the confusion. can you connect using mysql -u root -p and also using mysql -u root -p -h 127.0.0.1 one thing that i find here is that you have missed the -p option which prompts for password. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:39 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, Pls. Find response below: C:\wamp\mysql\binMysql -h localhost -u root ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -Original Message- From: chaim.rie...@gmail.com [mailto:chaim.rie...@gmail.com] Sent: 12 December 2008 13:13 To: BAJAJ POOJA; Chandru Cc: mysql@lists.mysql.com Subject: Re: MYSQL startup problem When you do the following Mysql -h localhost -u root. What do you get ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:43:45 To: chaim.rie...@gmail.com; Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, It doesn't allows me to telnet port 3306. If I ping
Re: Compiled configure string
Hi, you can use mysqlbug to get the compiled options. Ususally mysqlbug generates a output that has the the one that you are looking for :) Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 3:19 AM, Madan Thapa madan.feedb...@gmail.comwrote: On Fri, Dec 12, 2008 at 12:05 AM, voipcore voipcore voipc...@gmail.com wrote: Hi All, first post here :) I was wondering how can I view the ./configure ... ... string with which a mysql server installation was compiled with. Is that possible ? Thanks for supporting, have a nice day. Mike To make sure that you don't loose hope, I can say it is possible, but I don't remember the way to find it out. If I find it, before someone updates, I will let you know.
Re: MYSQL startup problem
Hi, can you please look at the mysql error logs? if you are not able to figure out the reason, please paste the error log. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 11:52 AM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, I am using wamp server version 1.1. When I try to start wamp server, It starts apache only, and does not starts mysql service. I tried to start mysql service independently, even that is not possible. Pls. let me know if any solution available. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078
Re: MYSQL startup problem
Hi, By default you can find them in the datadirectory ( the place where you have all your databases stored) Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 12:42 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi Chandru, Can you pls. help where can i search for logs. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -- *From:* Chandru [mailto:chandru@gmail.com] *Sent:* 12 December 2008 11:58 *To:* BAJAJ POOJA *Cc:* mysql@lists.mysql.com *Subject:* Re: MYSQL startup problem Hi, can you please look at the mysql error logs? if you are not able to figure out the reason, please paste the error log. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 11:52 AM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, I am using wamp server version 1.1. When I try to start wamp server, It starts apache only, and does not starts mysql service. I tried to start mysql service independently, even that is not possible. Pls. let me know if any solution available. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078
Re: MYSQL startup problem
can you check the output of netstat -n or netstat -an and find if port 3306 is listening? Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:12 PM, chaim.rie...@gmail.com wrote: When you do the following Mysql -h localhost -u root. What do you get ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:43:45 To: chaim.rie...@gmail.com; Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, It doesn't allows me to telnet port 3306. If I ping that port I receive Request timed out. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -Original Message- From: chaim.rie...@gmail.com [mailto:chaim.rie...@gmail.com] Sent: 12 December 2008 13:02 To: BAJAJ POOJA; Chandru Cc: mysql@lists.mysql.com Subject: Re: MYSQL startup problem It says that is started mysql Can you telnet to port 3306 ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:27:02 To: Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, Pls find logs attached below in mail: 080605 15:28:08 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 9:32:18 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 9:32:18 InnoDB: Starting shutdown... 080606 9:32:21 InnoDB: Shutdown completed 080606 9:32:21 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 9:30:58 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 11:43:53 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 11:43:53 InnoDB: Starting shutdown... 080606 11:43:54 InnoDB: Shutdown completed 080606 11:43:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 11:43:55 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 9:29:04 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 10:14:40 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 10:14:40 InnoDB: Starting shutdown... 080609 10:14:41 InnoDB: Shutdown completed 080609 10:14:41 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 10:26:27 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 16:45:52 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 16:45:52 InnoDB: Starting shutdown... 080609 16:45:54 InnoDB: Shutdown completed 080609 16:45:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 17:01:50 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080610 11:05:35 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080610 11:05:35 InnoDB: Starting shutdown... 080610 11:05:36 InnoDB: Shutdown completed 080610 11:05:36 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080610 11:11:23 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080613 17:28:21 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080613 17:28:21 InnoDB: Starting shutdown... 080613 17:28:24 InnoDB: Shutdown completed 080613 17:28:24 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080616 11:28:22 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080701 11:10:31 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080701 11:10:31 InnoDB: Starting shutdown... 080701 11:10:33 InnoDB: Shutdown completed 080701 11:10:33 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080701 11:12:34 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080701 11:15:12 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080701 11:15:12 InnoDB: Starting shutdown... 080701 11:15:13 InnoDB: Shutdown completed 080701 11:15:13 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080701 11:16:01 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080818 16:40:28 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 081211 13:10:16 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 081211 13:10:16 InnoDB: Starting shutdown... 081211 13:10:17 InnoDB: Shutdown completed 081211 13:10:17 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown
Re: MYSQL startup problem
Please also let me know how do you try to connect to mysql? Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:18 PM, Chandru chandru@gmail.com wrote: can you check the output of netstat -n or netstat -an and find if port 3306 is listening? Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:12 PM, chaim.rie...@gmail.com wrote: When you do the following Mysql -h localhost -u root. What do you get ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:43:45 To: chaim.rie...@gmail.com; Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, It doesn't allows me to telnet port 3306. If I ping that port I receive Request timed out. -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.comemail%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 -Original Message- From: chaim.rie...@gmail.com [mailto:chaim.rie...@gmail.com] Sent: 12 December 2008 13:02 To: BAJAJ POOJA; Chandru Cc: mysql@lists.mysql.com Subject: Re: MYSQL startup problem It says that is started mysql Can you telnet to port 3306 ? Sent via BlackBerry from T-Mobile -Original Message- From: BAJAJ POOJA pooja.ba...@alcatel-lucent.com Date: Fri, 12 Dec 2008 08:27:02 To: Chandruchandru@gmail.com Cc: mysql@lists.mysql.com Subject: RE: MYSQL startup problem Hi, Pls find logs attached below in mail: 080605 15:28:08 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 9:32:18 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 9:32:18 InnoDB: Starting shutdown... 080606 9:32:21 InnoDB: Shutdown completed 080606 9:32:21 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 9:30:58 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080606 11:43:53 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080606 11:43:53 InnoDB: Starting shutdown... 080606 11:43:54 InnoDB: Shutdown completed 080606 11:43:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080606 11:43:55 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 9:29:04 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 10:14:40 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 10:14:40 InnoDB: Starting shutdown... 080609 10:14:41 InnoDB: Shutdown completed 080609 10:14:41 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 10:26:27 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080609 16:45:52 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080609 16:45:52 InnoDB: Starting shutdown... 080609 16:45:54 InnoDB: Shutdown completed 080609 16:45:54 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080609 17:01:50 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080610 11:05:35 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080610 11:05:35 InnoDB: Starting shutdown... 080610 11:05:36 InnoDB: Shutdown completed 080610 11:05:36 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080610 11:11:23 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080613 17:28:21 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080613 17:28:21 InnoDB: Starting shutdown... 080613 17:28:24 InnoDB: Shutdown completed 080613 17:28:24 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080616 11:28:22 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080701 11:10:31 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080701 11:10:31 InnoDB: Starting shutdown... 080701 11:10:33 InnoDB: Shutdown completed 080701 11:10:33 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080701 11:12:34 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080701 11:15:12 c:\wamp\mysql\bin\mysqld-nt.exe: Normal shutdown 080701 11:15:12 InnoDB: Starting shutdown... 080701 11:15:13 InnoDB: Shutdown completed 080701 11:15:13 c:\wamp\mysql\bin\mysqld-nt.exe: Shutdown Complete 080701 11:16:01 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 080818 16:40:28 InnoDB: Started c:\wamp\mysql\bin\mysqld-nt.exe: ready for connections. Version: '4.0.18-nt-log' socket: '' port: 3306 081211 13:10:16 c:\wamp\mysql\bin\mysqld-nt.exe
Re: Replication
Hi Marcel, Can you tell me how many slaves are connecting to the master? If there are more than one slaves, can you please check the server id's of all the slaves, if they are unique. please also enable warning using log-warnings=2 in the config file, so that warnings are logged, which can give a clue what is happening. also let me know what are all the replication related paramenters that are set in the cnf. Regards, Chandru www.mafiree.com On Mon, Dec 8, 2008 at 4:31 PM, Ananda Kumar [EMAIL PROTECTED] wrote: did u check if any of the file system holding bin-logs/data files are having enough free space. If the slave runs out off disk space, then you need to rebuild the slave from scratch. regards anandkl On 12/8/08, ewen fortune [EMAIL PROTECTED] wrote: Hi, On Mon, Dec 8, 2008 at 9:20 AM, Marcel Grandemange [EMAIL PROTECTED] wrote: WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Here you are filtering your replication, are you happy the filter is correctly applied and that you understand this configuration option. Peter from Percona wrote a blog post about this last year. http://www.mysqlperformanceblog.com/2007/11/07/filtered-mysql-replication/ Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. This Seconds_Behind_Master: 0 combined with Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the slave is both up to date and running without problems caused by connectivity issues. How are you testing the differences between tables? to really know what is different you need to perform something like a table checksum, maatkit has a tool for that. http://www.maatkit.org/doc/mk-table-checksum.html If there really are differences between the two versions of table data I would suggest you are either filtering replication incorrectly (remember what filters are for master and which are for slaves) or you are using non-deterministic functions which when executed on the slave give a different result, something like this. http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-triggers-in-mysql-is-bad-for-replication/ Cheers, Ewen What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table - InnoDB
Hi jones, Innodb does not release the space unless you optimize the tables. To dot that you need to run dummy alter on all tables, by issuing Alter table table name engine=InnoDB but the space shall not regaing unless you start the table with innodb_file_per_table option. Then if you run the alter you shall gain space that was occupied. But in your current scenario, there is space that is occupied that is not shall not get released from OS even you run alter on the table. The space shall be available in the table space ibdata. If you have a option of reimporting then take a dump, reimport the same with innodb_file_per_table option enabled. Regards, Chandru. www.mafiree.com On Thu, Dec 4, 2008 at 4:19 PM, Shachi Govil [EMAIL PROTECTED]wrote: I thought you always have to go to the physical location and delete the tmp files manually. These are created in tmp folder. I am not sure if restarting helps... Regards, Shachi Govil - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: Jonas Genannt [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 04, 2008 4:09 PM Subject: Re: Alter Table - InnoDB So, now u dont have free space in your file system. Is this a production db. I think, restarting the db, should not cause any harm. Which version of mysql. regards anandkl On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote: Hi Ananda, Since u have cancled the job, those in-complete temp files can be deleted from the file system. ok - but I'm using InnoDB. The IBdata file is bumped up. There are no temp files on the database directory. Greets, Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating from MySQL 4.0.12 to 5.0.67 Community
Hi Bai, In case you want the privileges of the old system, please take a backup of the mysql database as inserts and reimport the same in the upgraded server. If you want a new admin user to be created, then please check the below page for the syntax: http://dev.mysql.com/doc/refman/5.0/en/grant.html Regards, Chandru www.mafiree.com On Tue, Dec 2, 2008 at 3:48 AM, Claudio Nanni [EMAIL PROTECTED]wrote: Hi Bai, I think the best option is to read carefully the installation guide, there are some post installation steps to set up initial privileges, and everything is explained quite well. After that if you still have problems please provide more informations on platform and other points like the steps and commands you issue and the output you get, so you 'help' others to help you, the more you are precise, the more you get help. Sincerely, Claudio Bai Shen wrote: I currently have an application that runs on MySQL 4.0.12. I'd like to update it to the newest version of MySQL so I can have multiple applications using the one database. However, I'm running into some permissions problems. When I install MySQL 4.0.12 I set up an administrator user. Then I run mysql.exe and I'm able to create a new database for the application. I don't log in or anything like that. Now I've stood up MySQL 5.0.67 on another machine. I've recreated the same administrator user, but I'm not sure how to give them the priveleges, since I didn't do anything like that with the older version. And when I try to create the database using mysql.exe I get the following error. ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'newdb' So I'm assuming that my permissions aren't setup correctly, but I'm not sure what I need to change. 5.0.67 seems to handle the administration fairly differently than 4.0.12. Also, I'm not running the newer version as a service. I don't think that would effect things, but I figured I should mention it. TIA. Bai Shen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql master slave security considerations and planning
Hi Madan, Nice to hear that you have got started up in MySQL and setup your first replication setup. MySQL has SSL replication that can be configured between master and slave if you feel that data is going to be transferred outside the network, I don't think that you will face such a scenario. People usually have the master and slave in a single network usually, which is protected from the external access. It is good to have access restrictions to all the users who access the database and give only access that is required. It is better to have security from the network level thank the database level. You can have hardware or software load balancer that can share the load between all the masters and slave. Yes your application should be written to distinguish between select queries and data modification queries. Fail over can be done by swapping the IP's between the master and the slave, by having a floating IP that is available in the master. You can have Heatbeat service do this task for you. Regards, Chandru. www.mafiree.com On Sun, Nov 30, 2008 at 3:09 AM, Madan Thapa [EMAIL PROTECTED]wrote: Hi, First of all I am newbie to MySQL administration, so if my questions seem weird, please forgive me. I am testing a mysql-slave setup on test nodes and was successful in setting it up. Here are the results. Please note this is a basic setup, I will explore more options that can be added to my.cnf , if required. mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.1 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.02 Read_Master_Log_Pos: 98 Relay_Log_File: mysql-relay-bin.03 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.02 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) mysql 2) Can you advise, what security considerations are to be taken into account for a master - slave setup? 2) In order to distribute load, how can we make writes on master only and reads on both? It seems to be, it is something that has to be handled at script coding, instead of administration level, am I right? In that case, should applications be developed to write to master and read on both master and slaves, what considerations are to be taken into account, so that we can switch to slave quickest enough to avoid outage length, should the master node crash? Thank you.
Re: How to disable some of the mysql query?
Hi wayne, If you dont want any query with select * from table by mistake then you can start the mysql by using the command --safe-updates or --i-am-a-dummy this does not allow queries to be executed if they dont have any where clause. Regards, Chandru www.mafiree.com On Fri, Nov 28, 2008 at 11:04 PM, Steve Edberg [EMAIL PROTECTED] wrote: At 1:07 AM +0800 11/29/08, Wayne wrote: for example,one user has the 'select' right on talbe 'test'(innodb,million records),however,he sometimes execute query like 'select * from test'.That will slow down the whole database. Is there a way to disable some queries for some users? Or,mysql's authority can be more detailed? thx As far as I know, you can't impose a limit on max number of records returned/hour. You can limit number of queries or updates or connections per hour: http://dev.mysql.com/doc/refman/5.0/en/user-resources.html It doesn't appear this has changed in MySQL 6. Depending on your server setup, you could implement some sort of per-user bandwidth limit outside of MySQL (perhaps imposing limits only on port 3306 traffic using a packet shaper/traffic shaper), or activate, monitor parse the query log http://dev.mysql.com/doc/refman/5.0/en/query-log.html or at least the slow query log. On an active server, I would imagine the overhead of a query log and then parsing it (eg; tail -f /path/to/log | grep ...) would be significant. If MySQL allows you to log directly to a Unix pipe (eg query_log = | some_program_that_monitors_activity) that might reduce the load sufficiently. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT locking tables.... in other databases
Hi David, can you please let me know what is the select query and the update query along with the explain plan of the same. can you please let me know if you are using innodb storage engine? Regards, Chandru www.mafiree.com On Thu, Nov 27, 2008 at 4:45 PM, David Scott [EMAIL PROTECTED]wrote: show full processlist userX is the user the site is using to connect databaseX is the database in question 1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', '' 13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', '' 13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', '' 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', '' 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist' 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', '' Show innodb status: ' = 081127 11:12:38 INNODB MONITOR OUTPUT = Per second averages calculated from the last 1 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 164975, signal count 155476 Mutex spin waits 0, rounds 7441650, OS waits 120688 RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966 TRANSACTIONS Trx id counter 0 25118320 Purge done for trx's n:o 0 25118124 undo n:o 0 0 History list length 89 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448 MySQL thread id 13833472, query id 57762790 IPX dave SHOW INNODB STATUS ---TRANSACTION 0 25063765, not started, process no 19166, OS thread id 1182529872 MySQL thread id 13508974, query id 57762327 IPX dave ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112 MySQL thread id 13775621, query id 57762659 IPX sen ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392 MySQL thread id 13759139, query id 57665031 IPX sen ---TRANSACTION 0 25117242, not started, process no 19166, OS thread id 1188387152 MySQL thread id 1976156, query id 57760643 localhost beta_cc FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 2310107, used cells 513846, node heap has 859 buffer(s) 34.97 hash searches/s, 99.90 non-hash searches/s --- LOG --- Log sequence number 1 501773721 Log flushed up to 1 501773721 Last checkpoint at 1 500074343 0 pending log writes, 0 pending chkp writes 12329746 log i/o's done, 79.92 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1201497898; in additional pool allocated 558592 Buffer pool size 64000 Free buffers 3328 Database pages 59813 Modified db pages 253 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 65, created 59748, written 573841 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 19166, id 1171347792, state: sleeping Number of rows inserted 17853779, updated 10095603, deleted 18, read 948444635 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s END OF INNODB MONITOR OUTPUT ' I hope that helps-- Dave 2008/11/27 Ananda Kumar [EMAIL PROTECTED] can u please do show full processlist when the update is happening, and if its innodb please do SHOW INNODB STATUS, which will give complete activity on innodb engine, including lock information. Please show use the output of these. regards anandkl On 11/27/08, David Scott [EMAIL PROTECTED] wrote: Hi list.We have 2 tables, both have a few inserts, many updates and the occasional select. When running a select joining the 2 tables (which can take upto 20 seconds to complete, they are large tables) all updates are blocked and the maxconnections is quickly reached. We tried copying the data to a 2nd database (in the same MySQL install) to run the select on that but for some reason that still prevents the updates on the original database, we watch
Re: too many connections
Hi, I think you need to take into consideration the following thins: 1. The number of connections that can come at any point of time (depending up on the statisics) set global max_connections=1000 2.decrease the wait time out variable wait_timeout=30 or even lower value depending upon connections. 3. Also check if the queries are getting locked or do you have any slow queries during that time. please let me know what are your current values for the above parameters what is the value of `netstat -an |grep -i est |wc -l` during the time of the error Regards, Pradeep Chandru. Brent Baisley wrote: One thing a lot of people miss is that web server KeepAliveTimeout setting has an effect on pconnect. Apache will keep the thread handling that client open for the KeepAliveTimeout duration, which will keep the database connection open for reuse. You can lower your KeepAliveTimeout or not use pconnect. Brent Baisley On Fri, Sep 19, 2008 at 3:51 PM, Jaime Fuentes [EMAIL PROTECTED] wrote: You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn 10 hidden secrets from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro. ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi joerg, that was a excellent explanation. Regards, Pradeep Chandru. Joerg Bruehe wrote: Hi ! Parikh, Dilip Kumar schrieb: Hi , So you are trying to say that 1) when the Table has Low Cardinality, Mysql wont use Index? Is this the logic behind your words? Extreme example: If you are manually looking up one entry from a list of five (say, in a cookbook), would you go through the index or just scan sequentially ? And also do you mean that the select query without index will be faster than that of the select query with Index? I just don't believe it. Then what is the purpose of Index?? Please clarify... Another extreme example: If you were to find a list all male soldiers in a typical army, would you go through an index on sex or just scan the payroll list, skipping the female ones ? For both cases, the logic is: Going through an index causes some overhead over a sequential scan (access the index, for each match follow the pointer to the real data) which you want (the system) to take only if that overhead is less than the overhead of scanning the base data and skipping the non-matches. Typically, both the index and the base data might be arranged sequentially, so scanning to the next entry is cheap, but following a reference from the index to some base record is a random access which is costly. So using the index is efficient only if the cost of (find matches in index) + ((hit rate) * (random data access)) is less than that of (sequential data scan). I have seen a 15 % hit rate used as a rule of thumb: If that optimizer expected a hit rate of more than 15 % (better: a selectivity worse than 15 %), it did not use the index at all but scan the base table. The reasoning was that sequentially scanning 6 - 7 entries (possibly using some read-ahead, disk caches etc) costs less than accessing one data record randomly. Regards, Jörg ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0 | PRIMARY |1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |1 | dt_pub | A |3115 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |2 | pris| A |3115 | NULL | NULL | | BTREE | | | ad| 1 | webbid |1 | ad_key | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | ad_id|1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ Tag_ad_map Create Table: CREATE TABLE `tag_ad_map` ( `ad_id` int(11) NOT NULL default '0', `tag_id` int(11) NOT NULL default '0', `termfreq` int(11) NOT NULL default '0', `weight` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`ad_id`), KEY `tag_id` (`tag_id`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Re: Why dont my query use the index keys?
Hi johan, you have allocated 4M for your sort buffer size is ok. So i feel that the query is loading more than 4MB of data so it is creating a temporary file to do a sorting. please try to put some logic into the query that you use to load the appropriate data. I find that the tag_id has a cardinality of 35921 which is low which tells that there are more records matching the condition tm.tag_id IN (99, 10807, 20728, 447, 807) you might have to put some logic to over ride the same. Even putting some partitioning can help you. Regards, Pradeep Chandru. Johan Thorvaldsson wrote: mysql show global variables like 'sort%'; +--+-+ | Variable_name| Value | +--+-+ | sort_buffer_size | 4194296 | +--+-+ 1 row in set (0.01 sec) 18 sep 2008 kl. 08.05 skrev chandru: Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: - Montania System AB Halmstad http://www.montania.se Johan Thorvaldsson [EMAIL PROTECTED] Kristinebergsvägen 17, S-30241 HALMSTAD Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation "Using temporary; Using filesort" In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Frn: "Ananda Kumar" [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: "Johan Thorvaldsson" [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Frn: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0 | PRIMARY |1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |1 | dt_pub | A |3115 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |2 | pris
Re: Fwd: Why dont my query use the index keys?
Hi nagaraj, sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index that can give the best performance. i missed that. Regards, Pradeep chandru. Nagaraj S wrote: Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path
Re: Fwd: Why dont my query use the index keys?
Hi Dilip, MySQL algorithm decides based on cardinality. Cardinality means the value of unique entries in the table. Hence when MySQL finds that there are very less unique values, mysql does not use that index. If the table is optimized then value of the cardinality will stay updated. Scanning the complete table can be faster than a index lookup that is going to search more than 30 % the table size. What i meant by increase data fetch is the time of data fetch. Regards, Pradeep Chandru Parikh, Dilip Kumar wrote: Hi pradeep, What are you trying to say ?. The query clearly denotes that Index is not been used anywhere. So i strongly suggest to use the index explicitly for better performance. Forcing that can only increase the volume of data that is fetched. ? I cant understand how it increases the volume of data that is fetched. Can u please explain on this ??. In this case, index is no where present in the select query, so naturally we have to force the query to use the index. *Thanks Regards,* *Dilipkumar* *From:* chandru [mailto:[EMAIL PROTECTED] *Sent:* Thursday, September 18, 2008 12:23 PM *To:* Parikh, Dilip Kumar *Cc:* Johan Thorvaldsson; mysql@lists.mysql.com *Subject:* Re: Fwd: Why dont my query use the index keys? Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL
Re: Fwd: Why dont my query use the index keys?
Hi nagaraj, yes by default, but if you know that the cardinality value that mysql has calculated is very old ( since you have not optimized it) then forcing a index can improve your performance as Dilip has mentioned in the before mail. If you are not sure always better to let mysql decide on the index to be used. Regards Pradeep chandru. Nagaraj S wrote: Then do u mean if there is low cardinality index will not be used?. Regards, Naga On Thu, Sep 18, 2008 at 2:41 PM, chandru [EMAIL PROTECTED]wrote: Hi nagaraj, sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index that can give the best performance. i missed that. Regards, Pradeep chandru. Nagaraj S wrote: Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED]mailto: [EMAIL PROTECTED] wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same.Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32)collate utf8_swedish_ci NOT NULL default '', `pris_original`varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub`datetime NOT NULL default '-00-00 00:00:00', `dt_updated`datetime NOT NULL default '-00-00 00:00:00', `ad_password`varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext`text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32)collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11)NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default
RE: How to delete all rows....
Hi, I prefer using TRUNCATE statement. syntax: TRUNCATE [TABLE] tbl_name This takes lesser time when compared to the delete from table. It takes hardly few seconds. take a backup before executing this if required. Regards, N.Pradeep Chandru. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 1:26 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: How to delete all rows DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to repair a table,
Hi, I think the syntax is to be verified. myisamchk *options * -u root -p *password /datadir/DB/tablename*.* mysql will accept only if the passwords is next to -p option *options*: some frequently used options are mentioned below -r, --recover -o, --safe-recover -e, --extend-check -q, --quick -S, --sort-index -f, --force ( to be used cautiously) i hope this will solve your issue :-) -- Regards, N.PradeepChandru, MySQL DBA, Cricinfo Pvt Ltd. Dilipkumar wrote: Hi, Yes it can be repaired using myisamchk -u root -p datadirectory the table name as tablename.* password this will check the data and also the index file also. Payne wrote: hi, I got a table where the myi isn't able to re be read. I tried to run myisam but it give an error about the index. Do I need to drop the table? Can it be repaired? Payne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in recreating .MYD files
hi, Just to add up i have read some where ( i assume in the mailing list only) that OS recognizes the file based on few codes added at the starting / ending of a file. So there are ways to create a MYD file through vi editor as well (this is not from the angle of restoring the data in that table). As Daniel said if there is no binlogs/ update / query logs there is no way of restoring data to the current date. -- Regards, N.PradeepChandru, MySQL DBA, Cricinfo Pvt Ltd. Daniel da Veiga wrote: On 5/9/06, balaraju mandala [EMAIL PROTECTED] wrote: Thank you Daniel for reply. Just in curiocity i want ask u, how u r taking daily backups, just using 'mysqldump' or using any Tools. I'm using mysqldump plus bzip2 to compress data for about 2 years now. Tried many tools, mysqldump scripted in bash (linux) or even in .bat (windows) was always better than any other tools. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ndbd --initial startup issues in mysql cluster
hi, i am trying to install a cluster. i am facing issues in starting up the ndbd. My configuration is as follows. *Config.ini:* [NDBD DEFAULT] NoOfReplicas=2 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] # Section for the cluster management node [NDB_MGMD] # IP address of the management node (this system) Id=1 HostName=192.168.0.103 # Section for the storage nodes [NDBD] # IP address of the first storage node Id=2 HostName=192.168.0.101 DataDir= /usr/local/mysql/data/ [NDBD] # IP address of the second storage node Id=3 HostName=192.168.0.102 DataDir/usr/local/mysql/data/ # one [MYSQLD] per storage node [MYSQLD] [MYSQLD] i added the following lines to the already existing *my.cnf* in both data nodes: [mysqld] ndbcluster # IP address of the cluster management node ndb-connectstring='host=192.168.0.103' [mysql_cluster] # IP address of the cluster management node ndb-connectstring='host=192.168.0.103' when i execute ndbd --initial in data node 1 i get no error when i execute ndbd --initial in data node 2 i am getting the following error: Date/Time: Tuesday 2 May 2006 - 14:17:06 Type of error: error Message: Invalid Configuration fetched from Management Server Fault ID: 2350 Problem data: Could not connect initialize handle to management server Object of reference: ProgramName: ndbd ProcessID: 22351 TraceFile: no tracefile ***EOM*** when i connect to the management node and give a show command i get the following output: show Cluster Configuration - [ndbd(NDB)] 2 node(s) id=2 (not connected, accepting connect from wci001.cricket.net) id=3 (not connected, accepting connect from wci002.cricket.net) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.103 (Version: 5.0.20) [mysqld(API)] 2 node(s) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host) As you can find here even the first data node is not connected. i have installed cluster from mysql-max-5.0.20-linux-i686-glibc23.tar.gz Can any one help me in solving this issue? Thanks in advance. -pradeep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
