turning off replication
Hi, i'm working with mysql 4.1 I had a slave machine (ralph) as backup to a master machine (barney). I then made ralph the production server, and turned off barney. I now want to make barney a backup to ralph (so ralph would be the master). I see in the logs that ralph is still trying to connect to barney because the values from "CHANGE MASTER TO..." are still there.. how do i completely clear this? so i don't have any problems when i bring barney back to life. Marc Dumontier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance on query with ORDER BY clause
Thanks for your reply, Just to be clear...performing my query without the order by clause will always return the list sorted by the primary identifier? so that SELECT SubmitId from BINDSubmit ORDER BY SubmitId == SELECT SubmitId from BINDSubmit in this case Marc Dathan Pattishall wrote: This tells the optimizer to do a table scan. If you used INNODB it's already sorted by the primary key since INNODB supports clustered indexes. Doing a table scan on innodb is very slow due to it's MVCC control. It's going to take a long time. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message----- From: Marc Dumontier [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 02, 2005 12:02 PM To: mysql@lists.mysql.com Subject: performance on query with ORDER BY clause Hi, I have a simple query with an ORDER BY clause, and it's taking forever to run on this table. I hope i've included all relevent information...it might just be one of the4 server variables which need adjustment. the query is SELECT SubmitId from BINDSubmit ORDER BY SubmitId SubmitId is the primary Key, about 150,000 records table type is INNODB mysql> describe BINDSubmit; +-+-+--+-+ -++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+ -++ | SubmitId| int(10) unsigned| | PRI | NULL| auto_increment | | BindId | int(10) unsigned| | MUL | 0 || | UserId | int(10) unsigned| | MUL | 0 || | Delegate| int(10) unsigned| | MUL | 0 || | Visible | tinyint(1) | | | 1 || | Private | tinyint(1) | | | 0 || | Compressed | tinyint(1) | | | 0 || | Verified| tinyint(1) | | | 0 || | Status | tinyint(3) unsigned | | MUL | 0 || | CurationType| tinyint(3) unsigned | | | 1 || | RecordType | tinyint(3) unsigned | | MUL | 0 || | DateCreated | datetime| | MUL | -00-00 00:00:00 || | DateLastRevised | datetime| | MUL | -00-00 00:00:00 || | XMLRecord | longblob| | | || +-+-+--+-+ -++ 14 rows in set (0.00 sec) mysql> select count(*) from BINDSubmit; +--+ | count(*) | +--+ | 144140 | +--+ 1 row in set (5.09 sec) mysql> explain select SubmitId from BINDSubmit ORDER BY SubmitId; ++---+---+-+-+ --++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+-+-+ --++-+ | BINDSubmit | index | NULL | PRIMARY | 4 | NULL | 404947 | Using index | ++---+---+-+-+ --++-+ 1 row in set (0.00 sec) # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 40M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help would be appreciated, so far query has been running for 3000 seconds Marc Dumontier -- 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]
performance on query with ORDER BY clause
Hi, I have a simple query with an ORDER BY clause, and it's taking forever to run on this table. I hope i've included all relevent information...it might just be one of the4 server variables which need adjustment. the query is SELECT SubmitId from BINDSubmit ORDER BY SubmitId SubmitId is the primary Key, about 150,000 records table type is INNODB mysql> describe BINDSubmit; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | SubmitId| int(10) unsigned| | PRI | NULL| auto_increment | | BindId | int(10) unsigned| | MUL | 0 || | UserId | int(10) unsigned| | MUL | 0 || | Delegate| int(10) unsigned| | MUL | 0 || | Visible | tinyint(1) | | | 1 || | Private | tinyint(1) | | | 0 || | Compressed | tinyint(1) | | | 0 || | Verified| tinyint(1) | | | 0 || | Status | tinyint(3) unsigned | | MUL | 0 || | CurationType| tinyint(3) unsigned | | | 1 || | RecordType | tinyint(3) unsigned | | MUL | 0 || | DateCreated | datetime| | MUL | -00-00 00:00:00 || | DateLastRevised | datetime| | MUL | -00-00 00:00:00 || | XMLRecord | longblob| | | || +-+-+--+-+-++ 14 rows in set (0.00 sec) mysql> select count(*) from BINDSubmit; +--+ | count(*) | +--+ | 144140 | +--+ 1 row in set (5.09 sec) mysql> explain select SubmitId from BINDSubmit ORDER BY SubmitId; ++---+---+-+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+-+-+--++-+ | BINDSubmit | index | NULL | PRIMARY | 4 | NULL | 404947 | Using index | ++---+---+-+-+--++-+ 1 row in set (0.00 sec) # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 40M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help would be appreciated, so far query has been running for 3000 seconds Marc Dumontier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow date query
Thanks, works like a charm. Marc Dathan Pattishall wrote: Well 1st of all Date_format doesn't allow the use of a key. Do this. SELECT .. WHERE DateLastRevised >= '2004-12-07' AND DateLastRevisted < '2004-12-08'; -----Original Message- From: Marc Dumontier [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 11:34 AM To: [EMAIL PROTECTED] Subject: slow date query Hi, I'd really appreciate any help in speeding up this type of query SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; On a MYISAM table of 122,000 rows, this query takes very long, in the neighbourhood of 20 minutes. i'm using mysqld 4.0.20. I have an index on DateLastRevised mysql> show indexes from BrowseData; ++++--+- +---+-+--++--+-- --+-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | ++++--+- +---+-+--++--+-- --+-+ | BrowseData | 0 | PRIMARY|1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_dlr|1 | DateLastRevised | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |2 | RecordType | A | 122850 | NULL | NULL | | BTREE | | ++++--+- +---+-+--++--+-- --+-+ mysql> explain SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; ++--+---+--+-+--++-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--++-- ---+ | BrowseData | ALL | NULL | NULL |NULL | NULL | 122850 | Using where | ++--+---+--+-----+--++-- ---+ 1 row in set (0.00 sec) thanks, Marc Dumontier -- 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]
slow date query
Hi, I'd really appreciate any help in speeding up this type of query SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; On a MYISAM table of 122,000 rows, this query takes very long, in the neighbourhood of 20 minutes. i'm using mysqld 4.0.20. I have an index on DateLastRevised mysql> show indexes from BrowseData; ++++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | ++++--+-+---+-+--++--++-+ | BrowseData | 0 | PRIMARY|1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_dlr|1 | DateLastRevised | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |2 | RecordType | A | 122850 | NULL | NULL | | BTREE | | ++++--+-+---+-+--++--++-+ mysql> explain SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; ++--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--++-+ | BrowseData | ALL | NULL | NULL |NULL | NULL | 122850 | Using where | ++--+---+--+-+--+----+-----+ 1 row in set (0.00 sec) thanks, Marc Dumontier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
