solution on SWiK

MySQL Slave Delay Explained And 7 Ways To Battle It

Fri, 05 Sep 2008 11:08:36 -0700

Slave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series - one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:

Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a big WHERE clause that doesn't use an index). In this case, the query runs on the master for an hour, which isn't a big problem because it doesn't block other queries. However, when the query moves over to the slaves, all of them start to lag because it plugs up the single replication thread.

Sidenote: when I hear an argument that a master has to be the most powerful machine in the group, I cringe at the logic. If the master can crunch more INSERTs/UPDATEs after an upgrade to a better machine, then replication will fall behind even faster.

There is nothing you can do right now to fix the way MySQL handles replication. If the replication threads could run in parallel, I'm guessing horrible things would happen to the data integrity due to race conditions, canceled queries, slave restarts, differences in query execution times due to server load and configuration, etc. Replication is already an asynchronous, prone to getting out of sync business (hint: use maatkit tools by Baron Schwartz and specifically mk-table-checksum and mk-table-sync to sync up your slaves).

In order to see if a slave is lagging, execute the 'show slave status' command and look for the Seconds_Behind_Master value. The way this value is calculated can be slightly ambiguous and unclear, so I'll explain. It is simply a difference between the 2 timestamps - the time of the last received (and queued up in the relay log) query that already executed on the master and the time of the currently executing query on the slave. Thus this value is not real time (it is possible to catch up to the master much faster); it's an approximation, or special metrics if you will, that helps point out problems.

So what can you do if you start hitting replication lag? This is the ultimate question, and the answer depends on your application. Here are the things I came up with after dealing with MySQL for a few years (there are undoubtedly other techniques, but these all come from my own experience):

Normalize your data, if it is not already. Non-normalized tables lead to repetition and is generally considered bad practice. More data - more IO in most cases. There can be cases, however, where you can normalize too much. Having JOINs is much slower than not having them, and it can hurt your queries if you JOIN a lot. Finally, the extreme case is mentioned at highscalability.com: How I Learned to Stop Worrying and Love Using a Lot of Disk Space to Scale. "You–pause for dramatic effect–duplicate data instead of normalize it. *shudder*". Flickr is provided as an example.
Shard (meaning, slice) your data, horizontally and vertically. For example, you can horizontally partition by some sort of key, hash, username, or other properties. You can also partition vertically by moving out some table columns into other databases. As an example, if you had a database of videos, storing view counts, number of favorites, etc. is OK but if these fields receive a lot of frequent updates, you are bound to have slave lag. Instead, separate these into a dedicated stats table(s). You don't have to shard all of your data - even sharding the most active bits helps immensely (for example, you can choose to shard your stats tables and leave the main one alone).
Upgrade machines running MySQL (first slaves, then master, for the reasons given above). 99% of the time, disk IO is the bottleneck, CPU being the other 1%. Move to RAIDed setups (RAID10 or RAID0) with 6-10 15K RPM SCSI or SSD drives. Add a lot of RAM. Make sure you're running a 64 bit OS if you have more than 3GB of RAM, so that the mysql process may utilize more of it. My search for the best MySQL server under $10K may be of some help here.
Separate your applications onto different MySQL instances. If you are running separate applications A, B, and C that don't depend on each other, consider giving them their own machines, otherwise a single long-running UPDATE or INSERT query in application A will delay all writes by application B and C. This is actually quite common - even though the server may not appear to be loaded, the annoying slave delay will still show its cowardly tail. I want to highlight this again: the replication thread is shared between all databases on the server.
Another solution to (4) is multiple MySQL instances, granted that the MySQL machine isn't generally overloaded already. In that case, installing more than 1 mysql daemons would separate replication threads and allow running multiple applications, like A, B, and C on one machine, without affecting each other. MySQL sandbox achieves just that - it is my preferred solution.
Split up longer running queries into shorter ones. This should be pretty straightforward - a single query on 10 million rows may run a few hours. Splitting it into batches of 50,000, for example, will give other queries a chance to run in between. Of course, you should take care of data integrity and generally double check what you are doing.
Don't overload the same slave by sending all queries to it, as it will just make the matter worse. You can round-robin the queries using either round-robin DNS (eww), round-robin within the application logic (better), smarter application logic, like checking slave load and status from time to time, or my personal favorite - using MySQL proxy and having it pick the least lagging slave for you. An official solution utilizing mysql proxy, called MySQL load balancer, is apparently in the works (I was promised beta access but haven't got it so far).

As a bonus, I wanted to throw in this idea of helping minimize a certain corner case cause of slave delay and feed it to the hungry MySQL minds. I'm not sure if it is mentioned anywhere else, as I have not Googled it. If it's a widely known fact, then I will consider this post as just adding my vote to the usefulness of the technique.

Here goes: if you have replication setups that use a lot of INSERT commands and you expect that most of such INSERTs would dupe with existing data (and you are using INSERT IGNORE, not REPLACE), consider replacing such queries with SELECTs, followed by only necessary INSERTs. The reasoning is simple: INSERTs propagate to all the slaves and have to run on a master. SELECTs can run on any slave and don't propagate anywhere, so if only 0.01% of the queries result in new rows, this technique will get rid of a lot of unnecessary slave query traffic.

Well, there you have it. Comments are open, so feel free to share your own replication strategies and thoughts about mine.

Bug #149076 in k3b (Ubuntu): "I can't write a cd"

Wed, 03 Sep 2008 21:57:11 -0700

sudo chmod 777 /usr/bin/wodim

COMMUNICATION SOLUTION VOIP TRANSMISSION GAP CODE

Wed, 03 Sep 2008 15:56:22 -0700

VoIP Solution is the transmission of code over a gap for communication. At this time, this process involves the sending of electromagnetic waves with the aid of transmitters. Presently, there are several devices are easily obtainable to aid the VoIP So...

MARKET EMERGING CTITERION SOLUTION VOIP

Tue, 02 Sep 2008 07:57:04 -0700

Communication in the 21st century is far better than it was a couple of years ago. A paradigm shift took place when accomplishments in VoIP Solution flourished. It has played a key role in bridging mortals in almost all parts of the planet for almost ...

Incite Software Pvt Ltd was founded on 17th May, 2006. Company started as an effective SEO Firm and later we expanded ourselves as Complete Web Solution Company. Incite is well known for quality work and has great reputation in Market.

Tue, 02 Sep 2008 05:23:31 -0700

Master Backend Server? - Ubuntu Forums

Sun, 31 Aug 2008 23:52:10 -0700

OpenNebula :: start

Sun, 31 Aug 2008 13:57:18 -0700

Create Digital Music

Fri, 29 Aug 2008 23:01:22 -0700

one of the most solid audio blogs over the web

PulseAudio - Ubuntu Wiki

Fri, 29 Aug 2008 17:56:43 -0700

Joomla! • View topic - Automatically read more after n characthers

Fri, 29 Aug 2008 12:53:58 -0700

it_support_nottingham

Thu, 21 Aug 2008 23:17:13 -0700

Littlefish IT Support hit 1000 unique vistors per day with new website http://www.littlefishsupport.com

It’s has been a fantastic few month’s steady growth from our website marketing and the opening of our new offices (Leeds and London) has meant great boost in IT support business from all over the UK. We have now hit the 1000 unique visitors per day marker (Over 9,600 hits) with the launch of our new website. Thanks to everyone in the company who has contributed to this and to all our clients who have helped make this a useful resource for future IT support business. Littlefish now have offices in the following cities, call now to see if we can help your business:

Birmingham Bristol Leeds Leicester London Manchester Nottingham

http://www.littlefishsupport.com/news/it-services/20080714/littlefish-it-support-hit-1000-unique-vistors-per-day-with-new-website

DIY Paper Telephone!

Tue, 19 Aug 2008 19:08:56 -0700

Tired of calling on your comdiatic (comcast + idiotic) digital phone?

Now, we have a great solution, a paper telephone that will cost you less every time you call.

via craft, flickr

Brought to you by: Zedomax.com

DIY Paper Telephone!

SUPPLIER VARY VOIP PLANS RATES SOLUTION SERVICE RETAILER

Tue, 19 Aug 2008 13:11:18 -0700

VoIP Solution technical know-how is on the rise and main enterprises are moving their firm’s considerations over to VoIP Solution and households on the entire planet are in addition shifting to VoIP Solution. VoIP Solution is brought out by trans...

Filesystem path is neither a file nor a directory 160013 - SVN / Trac Error - recurser

Tue, 19 Aug 2008 07:19:41 -0700

Finalmente: como resolver meu problema com o SVN e o Trac

REGULAR PHONE VOIP SOLUTION

Mon, 18 Aug 2008 03:06:20 -0700

VoIP Solution is the transmission of code over a venue for communication. As of now, this procedure involves the sending of electromagnetic waves with the aid of transmitters. Presently, there are several equipments are accessible to aid the VoIP Solut...

Philips SRM5100 (mceusb2) - no /dev/lirc0 created - Ubuntu Forums

Wed, 13 Aug 2008 03:04:37 -0700

Philips SRM5100 (mceusb2) - no /dev/lirc0 created Mythbuntu

SPSource - Home

Sun, 10 Aug 2008 19:09:27 -0700

"Old" concept revisited: Open Source

Sat, 09 Aug 2008 09:08:58 -0700

Recently I represented a community I led to gave speech in a technology expo. I brought a classical topic: Open Source. The presentation title was "Open Source is Not An Alternative, It is The Solution: How Business Benefits from Open Source and How Open...

powerset

Fri, 08 Aug 2008 04:07:40 -0700

Spring Tips: Open Session In View

Fri, 08 Aug 2008 01:07:34 -0700

Warning: domdocument::domdocument() expects at least 1 parameter, 0 given - Google Docs Data APIs | Skupiny Google

Sat, 26 Jul 2008 18:53:40 -0700

Chapter 11 OpenLDAP HOWTO - Create a Group

Sat, 26 Jul 2008 01:45:00 -0700

"dn: cn=itpeople,dc=groups,dc=example,dc=com objectclass: groupofnames cn: itpeople description: IT security group # add the group members all of which are # assumed to exist under people member: cn=road runner,ou=people,dc=example,dc=com member: cn=micky

Top 10 Portable Speakers for iPod/iPod Touch/iPhones!

Fri, 25 Jul 2008 14:55:59 -0700

1. The iLive IBCD3817DTBLK Portable Boombox is probably the best bet for your money as it’s not only an iPod-dockable speakers, the Boombox itself is also a radio/CD player. This is probably the best speaker you can get for your money.

Specs:

Black iPod/CD/radio boombox with built-in iPod docking station

Pair of front-firing speakers and dual subwoofers produce rich sound

Vertical-loading CD player supports CD, CD-R, and CD-RW formats

AM/FM tuner; blue backlit LCD display; auxiliary input and A/V output

Measures 18.8 x 8.8 x 9.2 inches (W x H x D); 90-day warranty

2. If you can afford it, the Bose SoundDock might be the best choice for listening to your music with super crisp stereo treble and bass. I personally own Bose noise-cancelling headphones and Bose speakers in my car. They do pretty good job of offering treble and bass power while the speakers do not justify them.

Specs:

High-quality sound from your iPod® - now made portable. Enjoy acclaimed SoundDock® performance almost anywhere.The SoundDock Portable digital music system reproduces music with fullness and clarity unmatched by other battery-powered iPod speakers, from a design even slimmer than our original. Proprietary Bose® technologies let you enjoy your iPod out loud in more locations without compromising soundquality.A rechargeable lithium-ion battery (included) provides more power and longer playing time than most other rechargeable batteries. The rotating dock disappears for storage and protection during transit. And a molded handle makes it easy to carry the system from place to place.Just dock and play all iPod models featuring a click wheel, including all nano, mini, photo, and video models. The system also plays music from your iPhoneTM.* The iPod charges as it plays for uninterrupted music enjoyment. A custom remote easily controls the system and basic iPod functions, including playlist navigation. Auxiliary input lets you play other portable sources, too.The SoundDock Portable digital music system. Unsurpassed performance and portability for the serious iPod listener from the most respected name in sound.*In the event of audio interference, set iPhone to airplane mode.

3. The B&W Zeppelin iPod Speakers may be the modern solution to your modern bedroom with its aesthetically pleasing oval shape. At $599, it’s not a bargain but you do get what you paid for.

Specs:

Zeppelin allows you to have real hi-fi stereo sound wherever you need it: upstairs, downstairs or perhaps in a den, bedroom, kitchen, office or second home. Or you might just need a simpler, smaller and more stylish replacement for your traditional audio system.

Zeppelin rocks, wherever you are, yet is simple to set up and use. Plug it in, place your iPod on the docking port and hit the play button. You can vary the volume on Zeppelin itself, on its remote control or on the iPod. And connections at the back let you play other sources through Zeppelin or channel images from your iPod to a TV.

Buy Zeppelin iPod Speakers at Amazon

Buy Zeppelin iPod Speakers at Apple Store

4. Altec Lansing inMotion iM600 Portable Audio System isn’t for everyone but for people who are really concerned with sound quality. If you don’t want to spend more than $150 for quality portable speakers such as the Bose or Zeppelin, these Altec Lansing portable speakers might be your best option at under $120.

Specs:

Model Number: iM600

iPod compatibility: All iPod models

Power supply: Built-in lithium ion battery, AC power adapter (included)

Battery life: Up to 7 hours

Tuner: FM

Antenna: FM telescopic antenna

Display: LCD display

Connectors: Composite video output, audio input

Alarm clock: Integrated

Sleep timer: Integrated

Dimensions: 11.0 x 6.0 x 1.7 inches / 283 x 152 x 43 millimeters (W x H x D)

Weight: 2.1 pounds / 0.9 kilograms

Warranty: 1-year limited warranty

What’s in the Box: inMotion iM600 mobile speaker system, wireless remote, iPod docking adapters, AC adapter, user’s guide, quick connect instructions

5. The Athena iVoice Direct Radiating Dock & Sound System for iPod does a lot more than amplify sound. The Athena unit also can act as a communication medium between your iPod and your TV to stream images and videos via a wireless remote control. For people who use iPod/iPhone as a media device more than MP3, the Athena iVoice can be a great addition.

Specs:

Video output allows you to view images and video stored on your iPod photo or iPod video on your TV

Audio input allows the system to function as a PC speaker

Wirelessremote lets you adjust the settings from across the room

Stylish, high-gloss black cabinet finish; wall-mountable

Charges your iPod while docked

6. The Pure-Fi Elite speakers from Logitech can be great for all your bass heads. It comes with enough bass power for you to enjoy thump and hump from your iPod/iPhone devices.

Don’t buy from Logitech.com at $299, you can get it for less than half the price for about $125 at Amazon here

Specs:

The Pure-Fi Elite immerses users in high-quality acoustics. The system features two 1-inch high-definition soft-dome tweeters to ensure precision and clarity from even the highest notes. The two 4-inch high-power long-throw woofers are capable of filling the room with deep, powerful bass. Meanwhile, DSP-controlled, dynamic, digital EQ, and advanced filtering features minimize distortion to create true-to-life sound. Also included is a bi-amplified transducer design with digital electronic crossover for studio-quality audio that plays louder and sounds better. The onboard StereoXL technology dramatically widens stereo image, creating an immersive audio experience.

Composite and S-Video output jacks are included for when you want to view iPod video files on a big screen while enjoying outstanding audio pumped through the Pure-Fi Elite.

The Pure-Fi Elite supports all iPod models with dock connectors–including iPod nanos and fifth-generation iPods. The Pure-Fi Elite supports 1G through 3G iPod models (and other portable music players) via a 3.5-mm auxiliary input. With an easy-to-read digital clock, the Pure-Fi Elite measures 17.6 by 10.6 by 5.7 inches and comes backed by a two-year limited warranty.

7. The JBL Radial speakers are probably the most aesthetically pleasing for your eyes and ears. If you think you need a ornament that fits your modern-design home and play music, this is it for only $199.95 from Amazon.

Specs:

High-performance loudspeaker with built-in iPod docking station for charging and synching

4 aluminum-domed Odyssey full-range drivers and single low-frequency neodymium driver

Compatible with most docking versions of iPod; auxiliary input for MP3 players and other devices

Wireless RF remote lets you change tracks, browse libraries, adjust settings, and scroll extras

Measures 12 inches wide and 10.5 inches tall; weighs 2.5 pounds; 1-year warranty

8. The mStation Orb Speakers make great artisitic addition to your house while providing you with quality music. You have a choice of many colors so depending on your wall color, you can match it with the perfect speaker. At $129.95, it’s a bargain for a speaker that does not look like one.

Specs:

This Sleek and Stylish “Made for iPod” Desktop Docking Station/ Speaker System packs a powerful thumping Bass that only a Dedicated Subwoofer can produce. Every Orb includes a 10 Key Remote that allows you to navigate within Playlists from a distance, as well as adjust the Volume ±, Bass and Treble. At approx 8 inches in diameter the mStation 2.1 Stereo Orb is the perfect addition to any Home, Office, or Dorm Room and comes in 7 different colors to match your room, mood, or even your iPod! The mStation 2.1 Stereo Orb comes in White, Black, Blue, Green, Pink, Red, and Silver.

9. The I-Sonic Entertainment Speakers bring you great stereo sound with four specially designed speakers. It might be a great way to get full-system speakers without breaking your legs. You also get HD-radio. There’s two models, one with CD/DVD player and the other without at a $100 price difference. You can get it at the I-sonic website or Apple Store here for $499 w/o the CD/DVD player.

Specs:

Polk’s breakthrough I-Sonic acoustic system creates a stereo soundstage throughout your entire room. Here’s how it works. We start with four specially-designed full range speakers, two on the front and two on the back of the sleek I-Sonic Entertainment System console. Through the magic of Digital Signal Processing, Polk engineers create full stereo all the way around the system. And for lows so deep they will astound you, we include Polk’s patented PowerPort® bass technology.

10. The JVC NX-PN7 Dual iPod/iPhone Dock speaker system is perfect for those of you with multiple iPod/iPhone devices. The system also comes with Video outputs for connecting to your TV or LCD.

Specs:

Dual Dock Design for iPod / iPhone compatible

AM/FM Tuner

Dual Alarm

Multi-Color Illumination

iPod Video Output

Order from Amazon at $124.32

Brought to you by: Zedomax.com

Top 10 Portable Speakers for iPod/iPod Touch/iPhones!

Eclipse Plugin Central :: View topic - Linking an SWT TreeItem with a Popup Menu

Thu, 24 Jul 2008 00:54:12 -0700

Magento - Home - Open Source eCommerce Evolved

Tue, 22 Jul 2008 02:50:41 -0700

Magento - Open Source eCommerce (PIM) solution

Offshore Outsourcing India and Ecommerce Development in India

Mon, 21 Jul 2008 02:57:44 -0700

Techno Infonet provides offshore Outsourcing and Ecommerce Development Solutions in a professional way with the right combination of quality, service & support. You get the highest quality; Designing work. It also offers services such as Email marketing, SEO in India.

Techno Infonet is a unique Web Design firm offering a variety of services from basic HTML Website Designs to complete E-commerce Website development. We have created and launched many of successful Websites since our origin; our designers and developers can work on your ideas and implement them on your Websites to strengthen your presence on Internet.

Ecommerce is said to be the transaction done through the electronic means with most of Internet base and some of CD-Rom catalogue also.

We have been providing offshore Outsourcing highly Complex white label service to other Web Design Services Provider in the UK, GERMANY, USA and Australia and Canada.

Techno Infonet develops innovative E-Commerce Development Techno provides the complete E-Commerce solution to get your business up and running online. Our affordable E-Commerce Website Design solutions are quick and easy to exercise. Nevertheless our highly creative web designs and Ecommerce solutions offer you a podium to present your self globally.

Techno Infonet with the base at India is a professional Web Designing Company. Our Web Designers focus on quality features of Web Design, Email Marketing and Multimedia Presentation. Company confers services such as Logo Designing and Search Engine Optimisation (SEO) in order to enhance the overall look of the Brand.

By providing an effective and efficient customized E-commerce solution as per our customer’s requirements we try to enhance the degree of easiness in our customers, which eventually augment the level of satisfaction.

This has been provided courtesy of TECHNO – http://www.technoinfonet.com in best-in-class Website Design, E-commerce development solution, Flash multimedia designing, logo designing and thus in ultimate Corporate Identity.

a2a_linkname="Offshore Outsourcing India and Ecommerce Development in India"; a2a_linkurl="http://www.technoinfonet.com/Blogs/web-design-india/offshore-outsourcing-india-and-ecommerce-development-2";

ActiveWidgets • input fields dont display the flashing cursor in firefox

Thu, 17 Jul 2008 06:52:20 -0700

'[PHP-INSTALL] Can't use mcrypt function with CGI php...' - MARC

Wed, 16 Jul 2008 21:50:21 -0700

How to convert DVD movies for the iPod or iPhone in Windows

Wed, 16 Jul 2008 09:58:06 -0700

Ouch “Voodoo” Toothpick Holder!

Tue, 15 Jul 2008 15:54:13 -0700

Do you have enemies you need to voodootize? Well, here’s a simple solution, an Ouch “Voodoo” Toothpick Holder where you can pick on the doll with toothpicks.

Even without the Voodoo effects, you can probably get some of that stress out during your breaks.

via amazon

Brought to you by: Zedomax.com

Ouch “Voodoo” Toothpick Holder!

solution on SWiK

MySQL Slave Delay Explained And 7 Ways To Battle It

Bug #149076 in k3b (Ubuntu): "I can't write a cd"

COMMUNICATION SOLUTION VOIP TRANSMISSION GAP CODE

MARKET EMERGING CTITERION SOLUTION VOIP

Incite Software Pvt Ltd was founded on 17th May, 2006. Company started as an effective SEO Firm and later we expanded ourselves as Complete Web Solution Company. Incite is well known for quality work and has great reputation in Market.

Master Backend Server? - Ubuntu Forums

OpenNebula :: start

Create Digital Music

PulseAudio - Ubuntu Wiki

Joomla! • View topic - Automatically read more after n characthers

it_support_nottingham

DIY Paper Telephone!

Related posts

SUPPLIER VARY VOIP PLANS RATES SOLUTION SERVICE RETAILER

Filesystem path is neither a file nor a directory 160013 - SVN / Trac Error - recurser

REGULAR PHONE VOIP SOLUTION

Philips SRM5100 (mceusb2) - no /dev/lirc0 created - Ubuntu Forums

SPSource - Home

"Old" concept revisited: Open Source

powerset

Spring Tips: Open Session In View

Warning: domdocument::domdocument() expects at least 1 parameter, 0 given - Google Docs Data APIs | Skupiny Google

Chapter 11 OpenLDAP HOWTO - Create a Group

Top 10 Portable Speakers for iPod/iPod Touch/iPhones!

Related posts

Eclipse Plugin Central :: View topic - Linking an SWT TreeItem with a Popup Menu

Magento - Home - Open Source eCommerce Evolved

Offshore Outsourcing India and Ecommerce Development in India

ActiveWidgets • input fields dont display the flashing cursor in firefox

'[PHP-INSTALL] Can't use mcrypt function with CGI php...' - MARC

How to convert DVD movies for the iPod or iPhone in Windows

Ouch “Voodoo” Toothpick Holder!

Related posts