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: Setup a replication slave without stopping master
Baron Schwartz wrote: In contrast, InnoDB actually needs to shut down to cleanly close its table structures before you can physically copy the filesystem. Actually, not true -- an LVM snapshot (or other snapshot) is a great way to take a backup of InnoDB. You just need a truly atomic snapshot, and then you can let InnoDB run its recovery routine on the snapshot to get back to a consistent state. Fascinating. From reading the mysql docs, I would never have assumed that I was doing the right thing by taking a snapshot of a live innodb instance with a flush tables. I will certainly keep this in mind for future. My current snapshotting procedure takes between 45s and 90s depending on which instance I snapshot, and that's about 20G of data that I start copying over. That's not counting time copying anything from the snapshotted volume. Why do I leave it firewalled? Because once you start writing to an LVM volume that's been snapshotted, you start copying disk extents like mad, creating a high load condition that can force queries to reach connect_timeout. I have my connect_timeout set pretty low in my environment. That will depend a lot on the workload. Yes, very pertinent point. I should have qualified how I have a write-intensive environment. I used to just drop the firewall after I restarted mysql, but when my application reliability was criticized during my snapshots, I had to leave the firewall up until the snapshotted copy was copied off before pooling it back in. Luckily I have four servers and there's only rare conditions when I need to switch masters. I'm grateful that I have a maintenance window for the site, too. Thank you, Baron! Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UPDATE jujitsu?
Consider the folowing dataset: +++-+-+---+ | id| Name | Location| OnOffFlag | Description | +++-+-+---+ | 1 | Paper| Cabinet | 0 | Blah| | 2 | Plastic | Cabinet | 0 | Blah| | 3 | China| Cabinet | 1 | Blah| | 4 | Glass| Cabinet | 0 | Blah| | 5 | China| Table | 0 | Blah| | 6 | China| Cabinet | 1 | Blah| +++-+-+---+ Is there a way to, using a single query, set the OnOffFlag to 1 for the record that matches [Name=China AND Location=Table] at the same time setting the OnOffFlag to 0 for records that match [Name=China AND Location!=Table]? I know I can do it in 2 queries but I am curious to know if it can actually be done in 1. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: UPDATE jujitsu?
How about this? update t set onoffflag = if (name 'China', onoffflag, ( if (location = 'Table', 1, 0) )); This leaves any onoffflag untouched if name is not China, which I assume you wanted to do. On Thu, Jan 8, 2009 at 2:18 PM, Christoph Boget christoph.bo...@gmail.comwrote: Consider the folowing dataset: +++-+-+---+ | id| Name | Location| OnOffFlag | Description | +++-+-+---+ | 1 | Paper| Cabinet | 0 | Blah| | 2 | Plastic | Cabinet | 0 | Blah| | 3 | China| Cabinet | 1 | Blah| | 4 | Glass| Cabinet | 0 | Blah| | 5 | China| Table | 0 | Blah| | 6 | China| Cabinet | 1 | Blah| +++-+-+---+ Is there a way to, using a single query, set the OnOffFlag to 1 for the record that matches [Name=China AND Location=Table] at the same time setting the OnOffFlag to 0 for records that match [Name=China AND Location!=Table]? I know I can do it in 2 queries but I am curious to know if it can actually be done in 1. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
can we set up this architecture?
Hi all, Recently I am redesigning our website architecture. I have two servers located at Location_A and Location_B serving the same http://www.ourexample.com and loadbalancing via DNS round-robin.Location_A and Location_B are connected via Internet(NOT in A LAN). The mysql database can be synchronized via master-master replication as far as we have seen,but we are now faced with file synchronization problem(I mean the files uploaded by users from Location_A and Location_B cannot see each other immediately.) Can anybody give me some advice on such case? 1.NFS over Internet for file sharing 2.sshfs 3.inotify(our system's kernel does not support this and we donot want to risk upgrading our kernel as well) 4.drbd in active-active mode 5 or any other solutions Any suggestions will be welcomed. Thank you in advance. Yours XuFeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
