Re: 5.1.51 Database Replica Slows Down Suddenly, Lags For Days, and Recovers Without Intervention
Luis, How large is your database? Have you checked for an increase in write activity on the master leading up to this? Are you running a backup against the replica? Thank you, Tyler Sent from my Droid Bionic On Oct 23, 2011 5:40 AM, Luis Motta Campos luismottacam...@yahoo.co.uk wrote: Fellow DBAs and MySQL Users [apologies for eventual duplicates - I've posted this to percona-discuss...@googlegroups.com also] I've been hunting an issue with my database cluster for several months now without much success. Maybe I'm overlooking something here. I've been observing the database slowing down and lagging behind for thousands of seconds (sometimes over the course of several days) even without any query load besides replication itself. I am running Percona MySQL 5.1.51 (InnoDB plug-in version 1.12) on Dell R710 (6 x 3.5 inch 15K RPM disks in RAID10; 24GB RAM; 2x Quad-core Intel processors) running Debian Lenny. MySQL data, binary logs, relay logs, innodb log files are on separated partitions from each other, on a RAID system separated from the operating system disks. Default Storage Engine is InnoDB, and the usual InnoDB memory structures are stable and look healthy. I have about 500 (read) queries per second on average, and about 10% of this as writes on the master. I've been observing something that looks like between 6 and 10 pending reads per second uniformly on my cacti graphs. The issue is characterized by the server suddenly slowing down writes without any previous warning or change, and lagging behind for several thousand seconds (triggering all sorts of alerts on my monitoring system). I don't observe extra CPU activity, just a reduced disk access ratio (from about 5-6MB/s to 500KB/s) and replication lagging. I could correlate it neither InnoDB hashing activity, nor with long-running-queries, nor with background read/write thread activities. I don't have any clues of what is causing this behavior, and I'm unable to reproduce it under controlled conditions. I've observed the issue both on severs with and without workload (apart from the usual replication load). I am sure no changes were applied to the server or to the cluster. I'm looking forward for suggestions and theories on the issue - all ideas are welcome. Thank you for your time and attention, Kind regards, -- Luis Motta Campos is a DBA, Foodie, and Photographer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=tpol...@engineyard.com
Re: select ... into local outfile ... ???
Dennis, The following closely simulates the default INTO OUTFILE, you'll have to modify it slightly for your purposes: mysql --delimiter=comma -N select_statement.sql \ | sed 's/\/\\\/g' \ | sed 's/\t/\,\/g' \ | sed 's/$/\/g' \ | sed 's/^/\/g' \ | sed 's/\NULL\/\\N/g' \ | sed 's/\\t/\t/g' ' output.txt Tyler * *On 9/2/11 3:40 PM, Dennis wrote: hi, there, the following is my sql statement: SELECT HIGH_PRIORITY SQL_BIG_RESULT SQL_NO_CACHE tb.url_sign, m_url,m_title, m_weightINTO OUTFILE '/tmp/a.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' STARTING BY '=' FROM d_local.ta, d_news.tbWHERE ta.url_sign = tb.url_sign Before, the server and the client were on the same machine; now, I need to generate the output file on client (which is on a different machine from the server). But it seams that there is no select ... into LOCAL file statement.Any suggestion is appreciated. Dennis
Re: MySQL replication server
Additionally, if a user has the SUPER privilege (eg. all privileges on *.*) they can write to a database running in read-only mode. Yet another reason to never allow this privilege for general purpose users. Tyler On 11/22/10 8:08 AM, John Daisley wrote: The replicated database should not be accepting writes, if it is then you haven't set it up correctly On 22 November 2010 13:03,a.sm...@ukgrid.net wrote: Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisleydaisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richardsmachi...@rdc.co.za wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Changing database tables to different storage engine.
Machiel, Each table will be write locked while it is being altered so this will most likely impact the application. In addition to the write lock, the conversion causes each table to be completely rewritten in the new format so this will have a high impact on IO write activity and so it will impact overall IO throughput. If your application is mostly reads, is well cached in memory, and the tables are small this should be pretty fast and relatively pain free. If you aren't sure about the impact and conversion time you may want to restore a backup of the database to another location and run through the conversion while monitoring performance numbers. Tyler On 11/22/10 5:55 AM, Machiel Richards wrote: Thank you John I have in the meantime fond this to be the case (** someone changed config files without my knowledge it seems as this was setup properly and working**) Anyhow, in order for the innodb to be active again I need to restart the database, however aftewards I assume the tables will still be MyIsam. In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. Regards Machiel -Original Message- From: John Daisleydaisleyj...@googlemail.com To: Machiel Richardsmachiel.richa...@gmail.com Cc: mysql mailing listmysql@lists.mysql.com Subject: Re: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 10:51:23 + I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richardsmachiel.richa...@gmail.com wrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richardsmachi...@rdc.co.za To: mysql mailing listmysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: uninstall/reinstall
Tammie, You might just try resetting the root password http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-windows. Hope this helps, Tyler On 10/19/10 12:38 PM, Montgomery, Tammie wrote: I had an old version of mySQL on my computer but never used it. I thought I knew the root password but it wouldn't let me in. I went ahead and uninstalled it thinking I would get a newer version anyway. I used the Windows interface to uninstall the previous version. It appeared to be gone but when I installed the new version, it asked me what the previous root password was and then give new one. I left previous blank and gave it the new password. Now it has been sitting at the screen in the configuration wizard for Apply security settings for about 20 minutes. How should I recover from this? Barracuda 400 vers 3.5.12 Checked - Virus Free -- 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 crashes
Charlene, You should check the mysql error log and if there isn't a message about the cause there you may want to check /var/log/syslog for mysql being killed by oomkiller. Tyler On 6/21/10 11:11 AM, Charlene wrote: Anybody have any idea why MySQL would start to have this error message every 4 or so days at midnight: Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) This just started happening a little more than 2 weeks ago. The server has been running fine for almost two years. MySQL is on a Linux system. There are about 750 dbs and accessed by approximately half that number websites (375). Half of the websites are on a different server than the MySQL server. Charlene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
