MySQL command line remote monitoring tool for 5.0
Hello, I'd like to know if there is a command line tool I can run to collect vital health information for a remote mysql server (just like mysqltop) for 5.0 - I often see mysql swapping to disk and would like to know what causes that. Thanks Hitesh -- 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 runs on 16-cores server
Thanks for you reply. I read the official document and found the variable called thread_concurrency could only affect on solaris. On Sat, Apr 11, 2009 at 5:04 AM, mos mo...@fastmail.fm wrote: At 06:00 AM 4/10/2009, you wrote: Hi. If the server has 16 cores, how to set parameters to make MySQL runs well. Any reply is appreciated. Using more cores with MySQL doesn't mean it will run faster. In fact, it could slow it down. Make sure you have done benchmarking with your current computer so you can compare the difference. InnoDb and MyISAM don't scale well with multi-cores I'm afraid. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
rebooted mysql-server-5.0.77 at FreeBSD 7.0
I have problem at my replicated slaves. We have one master and 5 slaves. All of its live in similar FreeBSD 7.0 systems. But by without understood for me reasons 4 of 5 slaves are rebooted approximately one time at 30 minutes. There are no signals and other reasons in logs. But one of slaves with similar config never rebooted. Size of databases approx. 600Mb in dump. Consist of 5 databases and ~60 tables in total (only 4 of it are InnoDB, others has MyISAM type). Load of each slave is 50-150 queries per second. Updates not so often. A few days ago I has been looked at .err file and find such strings: 090416 18:22:06 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090416 18:22:06 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090416 18:22:06 InnoDB: Started; log sequence number 0 72443 090416 18:22:06 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem. 090416 18:22:06 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1 090416 18:22:06 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 445546465, relay log './beta23-relay-bin.11' position: 46472 090416 18:22:06 [Note] Slave I/O thread: connected to master 'bet...@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 445546465 090416 18:31:15 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090416 18:31:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090416 18:31:15 InnoDB: Started; log sequence number 0 72453 090416 18:31:15 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem. 090416 18:31:15 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1 090416 18:31:15 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 445592521, relay log './beta23-relay-bin.13' position: 46291 090416 18:31:15 [Note] Slave I/O thread: connected to master 'bet...@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 445592521 090416 18:47:38 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090416 18:47:38 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090416 18:47:38 InnoDB: Started; log sequence number 0 72473 090416 18:47:38 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem. 090416 18:47:38 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1 090416 18:47:38 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 446007370, relay log './beta23-relay-bin.15' position: 415084 090416 18:47:38 [Note] Slave I/O thread: connected to master 'bet...@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 446007370 090416 19:08:16 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090416 19:08:16 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090416 19:08:16 InnoDB: Started; log sequence number 0 72493 090416 19:08:17 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem. 090416 19:08:17 [Note] /usr/local/libexec/mysqld: ready
Recommend a tool for editing data
Dear List, Can someone recommend a web based tool for mass-editing tables in excel like fashion? Preferably view-based (can you even edit a view in mysql, as long as it's 'simple' (sorry, coming from Ora world) We have an application which only allows to edit 'rows' one by one... yes, i know there are app drawbacks to my approach, but it's an inventory db. I am open to other suggestions (e.g. I think something like that might be doable via Access too, but it's obviously not web based) Thanks, -Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommend a tool for editing data
I use http://www.dadabik.org/ since many years, I find it simply great. I do not know if it is exactly what you need, but give it a try! Claudio Andrey Dmitriev wrote: Dear List, Can someone recommend a web based tool for mass-editing tables in excel like fashion? Preferably view-based (can you even edit a view in mysql, as long as it's 'simple' (sorry, coming from Ora world) We have an application which only allows to edit 'rows' one by one... yes, i know there are app drawbacks to my approach, but it's an inventory db. I am open to other suggestions (e.g. I think something like that might be doable via Access too, but it's obviously not web based) Thanks, -Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
InnoDB best practices for ensuring unique tuple where one column can be NULL
hello, assume the following table: CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY, c1 INT UNSIGNED NOT NULL, c2 INT UNSIGNED NOT NULL, c3 INT UNSIGNED, UNIQUE (c1, c2, c3) ) engine = InnoDB; Our first issue is that the UNIQUE constraint on (c1,c2,c3) does not work in the case that a NULL value for c3 is inserted: mysql insert into t (c1, c2, c3) VALUES (1,1,NULL); Query OK, 1 row affected (0.01 sec) mysql insert into t (c1, c2, c3) VALUES (1,1,NULL); Query OK, 1 row affected (0.01 sec) Given this behavior, we cannot rely on the UNIQUE constraint to enforce two sets of otherwise identical values. However, in addition to the UNIQUE requirement that we have above, we *only* want the UNIQUE constraint to be checked when the c3 column has a NULL value, e.g.: -- insert of (1,1,NULL) and (1,1,NULL): error insert of (1,1,1) and (1,1,1): ok -- Clearly the latter case would not be allowed with a UNIQUE(c1,c2,c3) constraint. Attempting to ensure these constraints via triggers is problematic, because within separate transactions two different clients can insert identical values, and once finished with the transaction, the triggers will already have done their validation finding no error. Is there a standard way to perform this sort of checking? thanks! -lev -- 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 command line remote monitoring tool for 5.0
Innotop can satisfy you demand. On Thu, Apr 16, 2009 at 4:36 PM, Hitesh Shah hrs...@gmail.com wrote: Hello, I'd like to know if there is a command line tool I can run to collect vital health information for a remote mysql server (just like mysqltop) for 5.0 - I often see mysql swapping to disk and would like to know what causes that. Thanks Hitesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Error msg: MySQL server has gone away
Can you tell me which version of mysql you use in you test? On Thu, Apr 16, 2009 at 12:43 AM, Pete Wilson p...@pwilson.net wrote: Hi folks -- New to MySQL. I'm developing C-language connector software and, while debugging, I often get the error message MySQL server has gone away if, say, I've stopped at a breakpoint and then issued a call that resembles: mysql_query(pmysql, insert into usrs(usr,email) values(\pete\, \p...@pwilson.net\); ); I can imagine this happening when the connector is running in real life. So: What is the correct and reasonable way for a running connector to deal with this error intelligently? Thanks! -- Pete Wilson http://www.pwilson.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Retrieving results of a stored function using MySql C API
Yeah, please show us the source code of yours. On Wed, Apr 15, 2009 at 4:10 PM, Venu Gopal neo.v...@gmail.com wrote: Hi guys, I am using stored procedures and stored functions for the first time. And currently stuck at a this point where. I am unable to retrieve results returned by stored function using MySql C API. Kindly let me know how to do so. In case you need details I'll share the source code. Cheers, Venu -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- 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
