Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
On 10/15/2012 7:15 PM, spameden wrote: Thanks a lot for all your comments! I did disable Query cache before testing with set query_cache_type=OFF for the current session. I will report this to the MySQL bugs site later. First. What are all of your logging settings? SHOW GLOBAL VARIABLES LIKE '%log%'; Next. When you physically look in the slow query log, how long does it say that it took this command to execute? And last, before you can ask MySQL to fix a bug, you must first ensure it's a MySQL bug. Please try to reproduce your results using official binaries, not those constructed by a third party. If the problem exists in our packages, do tell us about it. If the problem is not reproducible using official MySQL products, then please report it to the appropriate channel for the product you are using. MySQL Bugs - http://bugs.mysql.com/ Thanks! -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
Will do. mysql SHOW GLOBAL VARIABLES LIKE '%log%'; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | expire_logs_days| 5 | | general_log | OFF | | general_log_file| /var/run/mysqld/mysqld.log | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_log_at_trx_commit_session | 3 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size| 2145386496 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_overwrite_relay_log_info | OFF | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | ON | | log_bin_trust_routine_creators | ON | | log_error | /var/log/mysql-error.log | | log_output | FILE | | log_queries_not_using_indexes | ON | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_queries| ON | | log_slow_rate_limit | 1 | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_timestamp_every| OFF | | log_slow_verbosity | microtime | | log_warnings| 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600 | | max_relay_log_size | 0 | | relay_log | /var/log/mysql/mysqld-relay-bin | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | | slow_query_log_microseconds_timestamp | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | suppress_log_warning_1592 | OFF | | sync_binlog | 0 | | use_global_log_slow_control | none | +-+-+ 51 rows in set (0.01 sec) Here is full output, but writing happens ONLY if log_queries_not_using_indexes turned ON. Query takes: # Query_time: 0.291280 Lock_time: 0.50 Rows_sent: 0 Rows_examined: 133876 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 1775 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F229398 SET timestamp=1350389078; SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time) WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50; 2012/10/16 Shawn Green shawn.l.gr...@oracle.com On 10/15/2012 7:15 PM, spameden wrote: Thanks a lot for all your comments! I did disable Query cache before testing with set query_cache_type=OFF for the current session. I will report this to the MySQL bugs site later. First. What are all of your logging settings? SHOW GLOBAL VARIABLES LIKE '%log%'; Next. When you physically look in the slow query log, how long does it say that it took this command to execute? And last, before you can ask MySQL to fix a bug, you must first ensure it's a MySQL bug. Please try to reproduce your results using official binaries, not those constructed by a third party. If the problem exists in our packages, do tell us about it. If the problem is not reproducible using official MySQL products, then please report it to the appropriate channel for the product you are using. MySQL Bugs - http://bugs.mysql.com/ Thanks! -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote: Will do. mysql SHOW GLOBAL VARIABLES LIKE '%log%'; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | expire_logs_days| 5 | | general_log | OFF | | general_log_file| /var/run/mysqld/mysqld.log | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_log_at_trx_commit_session | 3 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size| 2145386496 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_overwrite_relay_log_info | OFF | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | ON | | log_bin_trust_routine_creators | ON | | log_error | /var/log/mysql-error.log | | log_output | FILE | | log_queries_not_using_indexes | ON | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_queries| ON | | log_slow_rate_limit | 1 | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_timestamp_every| OFF | | log_slow_verbosity | microtime | | log_warnings| 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600 | | max_relay_log_size | 0 | | relay_log | /var/log/mysql/mysqld-relay-bin | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | | slow_query_log_microseconds_timestamp | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | suppress_log_warning_1592 | OFF | | sync_binlog | 0 | | use_global_log_slow_control | none | +-+-+ 51 rows in set (0.01 sec) Here is full output, but writing happens ONLY if log_queries_not_using_indexes turned ON. Query takes: # Query_time: 0.291280 Lock_time: 0.50 Rows_sent: 0 Rows_examined: 133876 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 1775 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F229398 SET timestamp=1350389078; SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time) WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50; 2012/10/16 Shawn Green shawn.l.gr...@oracle.com On 10/15/2012 7:15 PM, spameden wrote: T...
Odd Behavior During Replication Start-Up
Hi, I have had a MySQL replication pair going for a while now, and we recently had some hardware issues on the slave. We've fixed the hardware issues on the slave, and now I went to re-start replication. I've done this probably 100 times, but for some reason I just cannot get this one to go, and I suspect that something may be wrong with my InnoDB databases and tables. Here's the steps I've taken: On the functioning master server, I did: slave stop; reset master; flush tables with read lock; show master status; And I copied and pasted the master status to a text file for later. Then, in a separate window, I created a ZFS snapshot, like this: zfs snapshot tank/root/var@2012-10-16 And then once that was complete, back on the master server I did: unlock tables Then I rsync'd the data from the snapshot to the slave server: rsync --progress --recursive --delete --times --perms --owner --group --links --hard-links root@db-01:/.zfs/snapshot/2012-10-16/var/db/mysql/ /var/db/mysql/ But, when I started up MySQL, I get the following in my error log: 121016 10:40:20 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql 121016 10:40:20 InnoDB: The InnoDB memory heap is disabled 121016 10:40:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121016 10:40:20 InnoDB: Compressed tables use zlib 1.2.5 121016 10:40:20 InnoDB: Initializing buffer pool, size = 256.0M 121016 10:40:20 InnoDB: Completed initialization of buffer pool 121016 10:40:20 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 121016 10:40:20 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Error: trying to add tablespace 1182 of name './foo/TECH.ibd' InnoDB: to the tablespace memory cache, but tablespace InnoDB: 1182 of name './bar/patches.ibd' already exists in the tablespace InnoDB: memory cache! 121016 10:40:38 mysqld_safe mysqld from pid file /var/db/mysql/db-07.soe.ucsc.edu.pid ended So, I went back to the master server, backed up the foo database and dropped and re-created it, and then restored the data, and repeated the whole process, but then I just get the same error for another pair of database names. I did this three times before giving up. No data appears to be corrupted at all on the master server. The master is running 5.5.25 on FreeBSD 8.2p4, and the slave is running 5.5.27 on FreeBSD 9.0p4. So, am I doing something wrong here? It seems like something is broken with InnoDB, but I can't be sure. I'm doing an optimize table on all my tables across all my databases now to make sure there's no weird corruption going on, but I'm not holding my breath with regard to if that will fix anything. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
Interesting thought, but I get the same result. # Query_time: 0.001769 Lock_time: 0.001236 Rows_sent: 0 Rows_examined: 0 use kannel; SET timestamp=1350413592; select * from send_sms FORCE INDEX (priority_time) where time=@ut order by priority limit 0,11; the MySQL i'm using is 5.5.28 from dotdeb.org, pretty sure it's close to the original except packaging scripts. I will check this on the release from MySQL site and report back. Thanks to all. 2012/10/16 Michael Dykman mdyk...@gmail.com your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote: Will do. mysql SHOW GLOBAL VARIABLES LIKE '%log%'; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | expire_logs_days| 5 | | general_log | OFF | | general_log_file| /var/run/mysqld/mysqld.log | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_log_at_trx_commit_session | 3 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size| 2145386496 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_overwrite_relay_log_info | OFF | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | ON | | log_bin_trust_routine_creators | ON | | log_error | /var/log/mysql-error.log | | log_output | FILE | | log_queries_not_using_indexes | ON | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_queries| ON | | log_slow_rate_limit | 1 | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_timestamp_every| OFF | | log_slow_verbosity | microtime | | log_warnings| 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600 | | max_relay_log_size | 0 | | relay_log | /var/log/mysql/mysqld-relay-bin | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | | slow_query_log_microseconds_timestamp | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | suppress_log_warning_1592 | OFF | | sync_binlog | 0 | | use_global_log_slow_control | none | +-+-+ 51 rows in set (0.01 sec) Here is full output, but writing happens ONLY if log_queries_not_using_indexes turned ON. Query takes: # Query_time: 0.291280 Lock_time: 0.50 Rows_sent: 0 Rows_examined: 133876 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 1775 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F229398 SET timestamp=1350389078; SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time) WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50; 2012/10/16 Shawn Green shawn.l.gr...@oracle.com On 10/15/2012 7:15 PM, spameden wrote: T...
Re: Odd Behavior During Replication Start-Up
Am 16.10.2012 20:18, schrieb Tim Gustafson: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 121016 10:40:20 InnoDB: Database was not shut down normally! So, I went back to the master server, backed up the foo database and dropped and re-created it, and then restored the data, and repeated the whole process, but then I just get the same error for another pair of database names. I did this three times before giving up. No data appears to be corrupted at all on the master server. you can not simply copy a single database in this state innodb is much more complex like myisam * rsync on the master while it runs LOCAL * stop the master * rsync a second time to get a fast diff-sync * stop the salve * rsync the master-backup to the slave * start replication IMHO this is the only fast, safe and consistent way to start a replication - and yes FS snapshots are REALLY bad for such things i am doing the above since many years now BTW: you should take care that slave and master have the SAME mysql-version! signature.asc Description: OpenPGP digital signature
Re: Odd Behavior During Replication Start-Up
I have to agree with Harald on this: filesystem snapshots are not an effective way to clone innodb databases. The rsync-based method described has worked for me in large scale data situations very reliably. - michael dykman On Tue, Oct 16, 2012 at 3:20 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 16.10.2012 20:18, schrieb Tim Gustafson: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 121016 10:40:20 InnoDB: Database was not shut down normally! So, I went back to the master server, backed up the foo database and dropped and re-created it, and then restored the data, and repeated the whole process, but then I just get the same error for another pair of database names. I did this three times before giving up. No data appears to be corrupted at all on the master server. you can not simply copy a single database in this state innodb is much more complex like myisam * rsync on the master while it runs LOCAL * stop the master * rsync a second time to get a fast diff-sync * stop the salve * rsync the master-backup to the slave * start replication IMHO this is the only fast, safe and consistent way to start a replication - and yes FS snapshots are REALLY bad for such things i am doing the above since many years now BTW: you should take care that slave and master have the SAME mysql-version! -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Odd Behavior During Replication Start-Up
Thanks for all the responses; I'll respond to each of them in turn below: you can not simply copy a single database in this state innodb is much more complex like myisam I know; that's why I rsync'd the entire /var/db/mysql folder (which includes the ib_logfile and ibdata files, as well as all other database and table data), not just individual databases. I also made sure that flush tables with read lock had been executed before creating the snapshot. The steps I followed were verbatim what the MySQL documentation said to do. The MySQL documentation even mentions ZFS snapshots as an effective way to make a backup: http://dev.mysql.com/doc/refman/5.5/en/flush.html I have to agree with Harald on this: filesystem snapshots are not an effective way to clone innodb databases. The rsync-based method described has worked for me in large scale data situations very reliably. I'm confused: in the first sentence, you say snapshots are bad (which directly contradicts the official MySQL documentation), and in the second sentence you say rsync is good. Why would an rsync of a file system snapshot not be good enough? By the way: I forgot to mention that I also did create a snapshot when the MySQL server on db-01 was actually shut down, and got the same sort of results. You can do replication flawlessly for InnoDB tables without stopping master at all. what you need is issue on master: mysqldump --single-transaction -A ALL.databases.dump.sql mysql -e 'SHOW MASTER STATUS\G ALL.binlog All total, we have approximately 125GB of MySQL databases. That command would take hours to run. During that time, no new transactions could be committed to any of our databases, and performance for read-only queries would be seriously affected. Further, we have a combination of MyISAM and InnoDB databases and tables, and the --single-transaction parameter to mysqldump does not lock MyISAM tables. There used to be a MySQL command that basically did all that in one statement (LOAD DATA FROM MASTER), but they dropped it because of the difficulties in getting all the master data that way. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Odd Behavior During Replication Start-Up
load data from master never worked for innodb. On 2012-10-16 3:52 PM, Tim Gustafson t...@soe.ucsc.edu wrote: Thanks for all the responses; I'll respond to each of them in turn below: you can not simply copy a single database in this state innodb is much more complex like myisam... I know; that's why I rsync'd the entire /var/db/mysql folder (which includes the ib_logfile and ibdata files, as well as all other database and table data), not just individual databases. I also made sure that flush tables with read lock had been executed before creating the snapshot. The steps I followed were verbatim what the MySQL documentation said to do. The MySQL documentation even mentions ZFS snapshots as an effective way to make a backup: http://dev.mysql.com/doc/refman/5.5/en/flush.html I have to agree with Harald on this: filesystem snapshots are not an effective way to clone inn... I'm confused: in the first sentence, you say snapshots are bad (which directly contradicts the official MySQL documentation), and in the second sentence you say rsync is good. Why would an rsync of a file system snapshot not be good enough? By the way: I forgot to mention that I also did create a snapshot when the MySQL server on db-01 was actually shut down, and got the same sort of results. You can do replication flawlessly for InnoDB tables without stopping master at all. what yo... All total, we have approximately 125GB of MySQL databases. That command would take hours to run. During that time, no new transactions could be committed to any of our databases, and performance for read-only queries would be seriously affected. Further, we have a combination of MyISAM and InnoDB databases and tables, and the --single-transaction parameter to mysqldump does not lock MyISAM tables. There used to be a MySQL command that basically did all that in one statement (LOAD DATA FROM MASTER), but they dropped it because of the difficulties in getting all the master data that way. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: ht...
Re: Odd Behavior During Replication Start-Up
load data from master never worked for innodb. And the suggested mysqldump command does not work for MyISAM. Either way, the suggestion is a non-starter. I could flush tables with read lock and then do a mysqldump but again that would take hours and all the databases would be read-only during the whole operation, and even the read-only performance would be poor. I'd really prefer not to do that. I'll say again that the MySQL documentation specifically says that ZFS snapshots are a good way to make backups of MySQL databases. Is the documentation incorrect? Or perhaps does that also only work for MyISAM tables? If so, that ought to be called out more clearly in the documentation. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Odd Behavior During Replication Start-Up
2012/10/16 Tim Gustafson t...@soe.ucsc.edu Thanks for all the responses; I'll respond to each of them in turn below: you can not simply copy a single database in this state innodb is much more complex like myisam I know; that's why I rsync'd the entire /var/db/mysql folder (which includes the ib_logfile and ibdata files, as well as all other database and table data), not just individual databases. I also made sure that flush tables with read lock had been executed before creating the snapshot. The steps I followed were verbatim what the MySQL documentation said to do. The MySQL documentation even mentions ZFS snapshots as an effective way to make a backup: http://dev.mysql.com/doc/refman/5.5/en/flush.html I have to agree with Harald on this: filesystem snapshots are not an effective way to clone innodb databases. The rsync-based method described has worked for me in large scale data situations very reliably. I'm confused: in the first sentence, you say snapshots are bad (which directly contradicts the official MySQL documentation), and in the second sentence you say rsync is good. Why would an rsync of a file system snapshot not be good enough? By the way: I forgot to mention that I also did create a snapshot when the MySQL server on db-01 was actually shut down, and got the same sort of results. I think you can do this too, I did that once. But make sure you have changed server-id in the my.cnf and use change master to to select proper master, you can ofc reset master too if you want on the slave. Basically reset master means you're flushing all logs and resetting to the first log entry and position. You can do replication flawlessly for InnoDB tables without stopping master at all. what you need is issue on master: mysqldump --single-transaction -A ALL.databases.dump.sql mysql -e 'SHOW MASTER STATUS\G ALL.binlog All total, we have approximately 125GB of MySQL databases. That command would take hours to run. True, it's not fast. During that time, no new transactions could be committed to any of our databases, and performance for read-only queries would be seriously affected. Further, we have a combination of MyISAM and InnoDB databases and tables, and the --single-transaction parameter to mysqldump does not lock MyISAM tables. Yes, it's true, the thing I mentioned only relates to InnoDB, but for MyISAM you can issue for specific tables: LOCK TABLES for writing There used to be a MySQL command that basically did all that in one statement (LOAD DATA FROM MASTER), but they dropped it because of the difficulties in getting all the master data that way. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Odd Behavior During Replication Start-Up
Also, forgot to say you need to shutdown completely MySQL before rsync'ing it's data, otherwise your snapshot might be inconsistent thus InnoDB fail. Also make sure database shutdown was correct in the log. 2012/10/16 Tim Gustafson t...@soe.ucsc.edu load data from master never worked for innodb. And the suggested mysqldump command does not work for MyISAM. Either way, the suggestion is a non-starter. I could flush tables with read lock and then do a mysqldump but again that would take hours and all the databases would be read-only during the whole operation, and even the read-only performance would be poor. I'd really prefer not to do that. I'll say again that the MySQL documentation specifically says that ZFS snapshots are a good way to make backups of MySQL databases. Is the documentation incorrect? Or perhaps does that also only work for MyISAM tables? If so, that ought to be called out more clearly in the documentation. -- Tim Gustafson t...@soe.ucsc.edu 831-459-5354 Baskin Engineering, Room 313A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Can I measure the use of index?
Caution -- this includes only the indexes you have actually used since turning on the stats. -Original Message- From: Eric Bergen [mailto:eric.ber...@gmail.com] Sent: Monday, October 15, 2012 8:55 PM To: Lixun Peng Cc: Perrin Harkins; Carlos Eduardo Caldi; mysql@lists.mysql.com Subject: Re: Can I measure the use of index? For the record mariadb also has table and index statistics. Including statistics on temporary tables. On Mon, Oct 15, 2012 at 8:34 PM, Lixun Peng pengli...@gmail.com wrote: Hi, If you are using Percona Server, you can use this query: SELECT DISTINCT s.table_schema, s.table_name, s.index_name FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics indxs ON ( s.table_schema = indxs.table_schema AND s.table_name = indxs.table_name AND s.index_name = indxs.index_name ) WHERE indxs.table_schema IS NULL AND s.index_name NOT IN ( 'PRIMARY' ) ; It will display all indexes that not use. or this query: SELECT table_name, index_name, SUM(rows_read) FROM information_schema.index_statistics GROUP BY table_name, index_name; you can get the all indexes are using. On Tue, Oct 16, 2012 at 12:44 AM, Perrin Harkins per...@elem.com wrote: On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi ce_ca...@hotmail.com wrote: Somebody knows how can I log or measure the index use ? http://www.percona.com/doc/percona-toolkit/2.1/pt-index-usage.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Odd Behavior During Replication Start-Up
Am 16.10.2012 21:55, schrieb Michael Dykman: I'm confused: in the first sentence, you say snapshots are bad (which directly contradicts the official MySQL documentation), and in the second sentence you say rsync is good. Why would an rsync of a file system snapshot not be good enough? By the way: I forgot to mention that I also did create a snapshot when the MySQL server on db-01 was actually shut down, and got the same sort of results. i said nothing about rsync a snapshot! * rsync HOT while runnining master * stop master * rsync cold to get the copy synchron the two rsync runs because it minimizes downtime nearly to zero i do not trust any FS snapshot in this context i do not trust any documentation in this context fact is if you stop the master there are REALLY no open files and that is why i do two rsync runs, the sconed one only have to care about diffs for changed files between both ones signature.asc Description: OpenPGP digital signature
Re: Odd Behavior During Replication Start-Up
On 10/16/2012 4:02 PM, spameden wrote: 2012/10/16 Tim Gustafson t...@soe.ucsc.edu Thanks for all the responses; I'll respond to each of them in turn below: you can not simply copy a single database in this state innodb is much more complex like myisam I know; that's why I rsync'd the entire /var/db/mysql folder (which includes the ib_logfile and ibdata files, as well as all other database and table data), not just individual databases. I also made sure that flush tables with read lock had been executed before creating the snapshot. The steps I followed were verbatim what the MySQL documentation said to do. The MySQL documentation even mentions ZFS snapshots as an effective way to make a backup: http://dev.mysql.com/doc/refman/5.5/en/flush.html I have to agree with Harald on this: filesystem snapshots are not an effective way to clone innodb databases. The rsync-based method described has worked for me in large scale data situations very reliably. I'm confused: in the first sentence, you say snapshots are bad (which directly contradicts the official MySQL documentation), and in the second sentence you say rsync is good. Why would an rsync of a file system snapshot not be good enough? By the way: I forgot to mention that I also did create a snapshot when the MySQL server on db-01 was actually shut down, and got the same sort of results. ... snip ... The part you have all missed here is this: 121016 10:40:20 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! As InnoDB operates, it copies data in the background into the tablespace file(s). You cannot stop this using FLUSH TABLES WITH READ LOCK. What you need to do is to wait for the database to quiesce using the procedure documented at the bottom of this page for using ALTER TABLE IMPORT TABLESPACE http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html ### In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied: There are no uncommitted modifications by transactions in the .ibd file. There are no unmerged insert buffer entries in the .ibd file. Purge has removed all delete-marked index records from the .ibd file. mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. You can make a clean backup .ibd file using the following method: Stop all activity from the mysqld server and commit all transactions. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file. ### The same rules apply to performing a hot backup of the main tablespace file(s) as they do to backing up any individual tablespaces. Of course, if you have achieved a clean shutdown, then the on-disk image is consistent and, as mentioned in other replies, your rsync will work just fine. Also, if you are doing the cold-shutdown method, you can start replication from position 4 of the next binary log created after you restart your master. The step to save the master's binary log position can be skipped if you are not trying to do a hot (or warm) backup. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
2012/10/16 12:57 -0400, Michael Dykman your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. Quote: Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_nowNOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curdateCURDATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curtimeCURTIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-dateUTC_DATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-timeUTC_TIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/f! unctio ns.html#function_utc-timestampUTC_TIMESTAMP(), and to any of their synonyms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
That's exactly what I thought when reading Michael's email, but tried anyways, thanks for clarification :) 2012/10/16 h...@tbbs.net 2012/10/16 12:57 -0400, Michael Dykman your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. Quote: Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_nowNOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curdateCURDATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curtimeCURTIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-dateUTC_DATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-timeUTC_TIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/f! unctio ns.html#function_utc-timestampUTC_TIMESTAMP(), and to any of their synonyms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
error 13
hi all.. this hasn't happened before... i'm in as root on the command line cleint. trying to load data infile '/path/to/file' file is owned by mysql and it has 777 permissions. the directory where the file is is also owned by mysql. when i do: mysql load data infile '/path/to/file' etc... i get: ERROR 13 (HY000): Can't get stat of '/path/to/file' (Errcode: 13) ls -al /path/to/file lists the file... so why do i get errcode 13?! i'm on os x. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Error message I am getting today. All help appreciated.
Hi, I run www.canadianinvestors.com and am getting this error message. Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query in /var/www/domains/x.canadianinvestors.com/docs/common/library/db_connect.inc on line 15 Lost connection to MySQL server during query Able to log in to the phpmyadmin database account and the script that the query is pointing to has the right password and appears to the untrained eye to be working. I host with myhosting.com and the site has been down for about 8 hours. Any and all help is appreciated. Yours Sincerely, Adrian Burridge CanadianInvestors.com Inc. mail2web - Check your email from the web at http://link.mail2web.com/mail2web -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error 13
Hi, What's the dir permissions? For example, if we have a file in /a/b/file, dir a is 644, even though file is 777, we can't access file, too. On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net wrote: hi all.. this hasn't happened before... i'm in as root on the command line cleint. trying to load data infile '/path/to/file' file is owned by mysql and it has 777 permissions. the directory where the file is is also owned by mysql. when i do: mysql load data infile '/path/to/file' etc... i get: ERROR 13 (HY000): Can't get stat of '/path/to/file' (Errcode: 13) ls -al /path/to/file lists the file... so why do i get errcode 13?! i'm on os x. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com
Re: Error message I am getting today. All help appreciated.
Hi, What query is running? Is web server and DB server in the same server? Have something in error.log of MySQL? On Wed, Oct 17, 2012 at 10:32 AM, ad...@canadianinvestors.com ad...@canadianinvestors.com wrote: Hi, I run www.canadianinvestors.com and am getting this error message. Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query in /var/www/domains/ x.canadianinvestors.com/docs/common/library/db_connect.inc on line 15 Lost connection to MySQL server during query Able to log in to the phpmyadmin database account and the script that the query is pointing to has the right password and appears to the untrained eye to be working. I host with myhosting.com and the site has been down for about 8 hours. Any and all help is appreciated. Yours Sincerely, Adrian Burridge CanadianInvestors.com Inc. mail2web - Check your email from the web at http://link.mail2web.com/mail2web -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com
Re: error 13
is your mysql client on the same host as the mysql server? if not, google the docs for 'local infile' On 2012-10-16 10:45 PM, Lixun Peng pengli...@gmail.com wrote: Hi, What's the dir permissions? For example, if we have a file in /a/b/file, dir a is 644, even though file is 777, we can't access file, too. On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net wrote: hi all.. this hasn't happe... -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com
Re: Error message I am getting today. All help appreciated.
Hi There many reasons to lose connection to MySQL server. And what's more , the important thing is that you should provide the error code to us. If you got the error code , you can check it by perror X Anymore detail information would be appreciate ! On Wed, Oct 17, 2012 at 10:32 AM, ad...@canadianinvestors.com ad...@canadianinvestors.com wrote: Hi, I run www.canadianinvestors.com and am getting this error message. Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query in /var/www/domains/ x.canadianinvestors.com/docs/common/library/db_connect.inc on line 15 Lost connection to MySQL server during query Able to log in to the phpmyadmin database account and the script that the query is pointing to has the right password and appears to the untrained eye to be working. I host with myhosting.com and the site has been down for about 8 hours. Any and all help is appreciated. Yours Sincerely, Adrian Burridge CanadianInvestors.com Inc. mail2web - Check your email from the web at http://link.mail2web.com/mail2web -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- B.rgds / Whitepoplar (杨德华）
Re: error 13
On 10/16/12 10:44 PM, Lixun Peng wrote: Hi, What's the dir permissions? was 755. now 777. same result. For example, if we have a file in /a/b/file, dir a is 644, even though file is 777, we can't access file, too. On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net mailto:ka...@el.net wrote: hi all.. this hasn't happened before... i'm in as root on the command line cleint. trying to load data infile '/path/to/file' file is owned by mysql and it has 777 permissions. the directory where the file is is also owned by mysql. when i do: mysql load data infile '/path/to/file' etc... i get: ERROR 13 (HY000): Can't get stat of '/path/to/file' (Errcode: 13) ls -al /path/to/file lists the file... so why do i get errcode 13?! i'm on os x. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com http://gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error 13
On 10/16/12 10:49 PM, Michael Dykman wrote: is your mysql client on the same host as the mysql server? if not, google the docs for 'local infile' yes. my laptop. On 2012-10-16 10:45 PM, Lixun Peng pengli...@gmail.com wrote: Hi, What's the dir permissions? For example, if we have a file in /a/b/file, dir a is 644, even though file is 777, we can't access file, too. On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net wrote: hi all.. this hasn't happe... -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error 13
Hi, you can switch to mysql user su - mysql, and then stat this file. if you can't access, mysql user have not permissions. On Wed, Oct 17, 2012 at 11:49 AM, kalin ka...@el.net wrote: On 10/16/12 10:49 PM, Michael Dykman wrote: is your mysql client on the same host as the mysql server? if not, google the docs for 'local infile' yes. my laptop. On 2012-10-16 10:45 PM, Lixun Peng pengli...@gmail.com wrote: Hi, What's the dir permissions? For example, if we have a file in /a/b/file, dir a is 644, even though file is 777, we can't access file, too. On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net wrote: hi all.. this hasn't happe... -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com
