Entries Tagged 'MySQL' ↓

I have a new idea. Actually, to give credit were credit is due, my lovely wife had a good idea. She mentioned it to me a week or so before the spring issue of the magazine came out. Too late for that issue. However, I am going to implement it for the summer issue of the magazine.

So here is what happens. Almost every day I get emails, comments on my blog, or linked-in emails about MySQL DBA positions. I know that many companies are hurting, looking for dbas and MySQL proficient developers. The magazine is targeted at MySQL DBAs and developers. So when someone downloads the magazines you are almost assured of reaching your target audience.

Hmmm…why didn’t I think of this before? A natural match. Now, I don’t want to turn this into a Monster job site, but I think the magazine can offer a service. So, if your company is looking for a MySQL DBA or a developer send me an email at bmurphy AT paragon-cs.com and we can talk. There will be a very reasonable listing fee. The magazine currently comes out once a quarter. In addition to having a job listing in the magazine I will put together a page on the website (http://www.mysqlzine.net) for the job posting.

Hey everyone!! Just wanted you to know that it’s “that time”. The summer issue of MySQL Magazine will be coming out July the 15th. That means that it is time to start gathering the articles. If you have interest in having your words read by thousands of people and having fame and fortune come to you (ok..maybe fame but probably not fortune) than you should submit an idea for an article. Articles must be completed by June the 15th so don’t delay!!

Have I got your interest? Send me an idea for an article to bmurphy AT paragon-cs.com. Anything relating to DBA work on MySQL or software development around MySQL will be considered.

I am looking for someone who would be interested in trying out some software. Give it an honest spin. I need a review written for the upcoming summer issue of MySQL Magazine (http://www.mysqlzine.net).  Maybe two or three pages including screenshots.  The magazine is slated to be released on July the 15th and the article will need to be in by June the 15th.  Please contact me : bmurphy AT paragon-cs.com.  Authors get a small amount of money so there is some incentive!!

Hey everyone.  Haven’t been writing since I got back from the conference.  Been busy with other things.  But I would love to get some input on something I don’t have any experience.

I am evaluating possible solutions for expanding storage on our servers.  All the way from a full blown SAN to something as simple as ripping out the hard drives currently in the servers and replacing them with larger units.  One of the possibilities is using a Powervault MD300 which is a direct-connect RAID unit that can attach to up to four servers.  It would resolved some space issues we have on a few servers without require an enormous expense of a couple of SANs.    In theory it should actually be faster than the internal RAID as it has a larger cache.

Things I am particulary interested in:

• general impressions
• reliability
• is having four servers going to slow it down that much?
• the java software for management…I understand it can work from any client computer.  Does this mean you don’t have to install any additional software on the server itself?

Thanks!!

Hey everyone, I just posted a tutorial I wrote for the maatkit toolkit. This tutorial only covers the mk-table-checksum tool and the udf that Baron coded which speeds up the checksum process quite dramatically (small benchmark test included in tutorial). I really don’t have it linked into my website yet, but the direct download is here.

Baron, thanks for the hard work on the toolkit. We really appreciate it.

Enjoy!!

I have been following MySQL Proxy ever since it started.  It is really cool stuff.

Just recently at work we evaluated some various possibilities or a high availability (HA) setup.  Load balancing would be nice, but that wasn’t the primary point.  Well, we evaluated probably half a dozen alternatives and I believe we have a winner … MySQL Proxy.

With proxy you have something that is fairly simple to set up, can be built with redundancy so you don’t have a SPOF and is very flexible.  In addition to HA we can use it for load balancing, storing all queries that run through the proxy (for auditing) and even modifying queries for various reasons.  There are other uses but I would like to keep this post short.

Because it contains the Lua programming language you really have the ultimate flexibility.  Yesterday I was discussing proxy with our Junior DBA and he said that there were things that haven’t even been thought of yet that will be implemented down the road.  Well, here is on of those things.  Jan Kneschke just announced on his blog here how to use the proxy to determine what query caused an Innodb transactional rollback.  Who would think about using the proxy for this?  Well Jan did.  And I for one am thankful!

If you are a professional MySQL DBA I would recommend you invest some time to understand what MySQL Proxy is all about.  I would also take the time to set it up in a “sandbox” environment so you can start working on it.  I believe that the proxy will be an important part of the MySQL landscape in the years to come.

MySQL Proxy homepage: http://forge.mysql.com/wiki/MySQL_Proxy

Well, as I often do, I am going to weigh in on the topic du jour after pretty much everyone is done yelling about it. After evaluating what was said I believe I am going to offer something that is actually original. For background, in case you missed it, MySQL (the company) announced that they were going to charge for compression and encryption features of the new online backup that is coming in version 6.0.

This has raised quit the furor. I am not going to take the time track down all the posts about it, but there have been many. Just check out planetmysql.com if you want. Almost without exception the posts have been very negative.

Now that a little background has been laid out, let’s discuss. It is important to understand that this decision was made by MySQL AB management before the Sun acquisition. It is my understanding that this actually took Sun very much off guard. While Sun isn’t perfect, I do think that Jonathan Schwartz believes in the value of open source software and has every intention of keeping the MySQL server open source. It seems at this point that there is quite a difference in viewpoint between the upper management at MySQL and the people at Sun who are responsible for managing MySQL from here on out.

I think I know who is going to win in the long run in this little “battle” between Sun and MySQL. Sun paid a nice sum for MySQL and if I understand some basic business that means they are in charge. While with corporations of this size change takes time I am sure that Sun will be shaping MySQL somewhat “in its image”.

I doubt that this little scheme of charging for these features ever actually takes place. It is pretty much diametrically opposed to the what Sun says they want for MySQL. I think that by the time server version 6.0 is GA that every feature will be fully available for anyone. And that is why I have not taken the time to sharpen a pitchfork and join the mob. Because in the end I don’t think this will ever happen.

And cudos to Monty W. for leading chants of “We don’t ship crippleware” in his session on the future of MySQL at the conference.

Just my two cents.

Everyone has shirts, pens, flashy little do-dads, coffee cups, or whatever. But the best swag of the conference had to be:

Not the computer, that’s mine.  The boxers.  MySQL was giving them way.  They say ‘MySQL Freedom to Work Anywhere’.  I thought it was hilarious.

One of the benefits of going to the conference is that you can take various certification tests (CMA, CMDBA, CMDEV and the CMCDBA) for a very low price ($25.00 a test).

I planned on taking the  first CMDBA (the cert requires two test) and I was able to take the first test this morning.  I work daily in an environment where all I do is “DBA” functions (I don’t do any coding).  Plus, I have been studied the certification book for a while.  I was fairly confident that I could pass the first test.  Even so, I was nervous going in to take the test.  Not enough sleep this week, overloaded with session information.  Not really ideal.  However, I really breezed through it.

Then I started thinking I should take the second test.  I didn’t study for it and while we run 5.0 in production we don’t currently use any stored procedures/triggers/etc.  So, I don’t have a great deal of experience on something that was probably a third of the test.  Well, I did take it.  And while many people would probably think it was actually easier than the first test I was a little worried I might not pass.  However, about an hour ago, I did find out that I passed the second test so I am “unofficially” a CMDBA.  Yeah!!

Should you do it?  Well, for conference attendees I am afraid it is too late.  Testing stopped at 3:00 pm today or something like that.  However, MySQL offers the tests through Pearson/vue (sp?) testing centers.  It is worth it, and does show you know a great deal about the MySQL server.  I wouldn’t recommend you try and take this test without production experience.  The certification guide says that you need six months of production experience if I am not mistaken.  I agree, although you better study well also!

Goals

Make Innodb scale on big servers

• fix bottlenecks on big SMP server
• utilize server with many disk
• support thousands of database connections
• handle corruption in mormroy and on disk
• make query plans perdictable
• support thousands of tables and accounts

Mentioned a problem with SANs?

Desirable Features

• throughput scales with number of CPU cores
• efficient support for 128 GB buffer cache
• performance for servers with man and or remote disk
• recover from corruption

CPU Problems

• Mutex implementations favor portability over performance
• Mutex hotspots
  • buffer cache
  • memory allocation
  • transaction log
  • adaptive hash latch

Symtops of CPU problems

• adaptive hash latch contention
  • SHOW INNODB STATUS displays the session that holds the adaptive hash latch
  • a background thread logs SHOW INNODB STATUS into the error log when there is along lock wait
• excessive mutex contention
• server has many queries, is slow and is not IO bound
• on linux, vmstat will report a lot of idle time

Workarounds for CPU problems

• Upgrade
  • MySQL 5.0.30 has import improvements
  • MySQL 5.0.54 fixes a bug that causes some threads to miss wakeup signals
• experiment with innodb_thread_concurrency to limit the number of threads that run concurrently

Making the InnoDB RW-mutex fast

• Use atomic ops to change internal state (replacing the Innodb spin lock mutex)
• use separate events to wake readers and writers

Work in progress for CPU problems

• change the innodb mutex to use atomic ops
• change the Innodb read-write mutex to use atomic ops

Performance Tests

Eight-CPU core server. Data is from the sbtest table generated by sysbench. Data is cached (key buffer, InnoDB buffer cache).

It was amazing the difference that these patches make. The scalability for these servers (number of sessions) was almost linear.

Memory

To support a 128GB buffer cache:

• data structures must scale
  • walking a list with 8M page entries might be slow
• Resources need to be split
  • more than one mutex might be needed for the buffer cache and LRU chain
• Detection of corruption is more important
  • memory will be corrupted by software and hardware bugs

Detect and correct corruption

Features in InnoDB

• page checksums to detect corruption that occurs after a page has been written to disk
• doublewrite buffer to correct partial page writes that occur during a server crash

Innodb crashes when it reads a page with an invalid checksum

if this is a page for a secondary index, than the index can be rebuilt

Disk performance

• Innodb uses one background thread to process prefetch requests.
• Innodb uses one background thread to flush dirty buffer cache pages to disk. This is fine as long as writes go to the OS buffer cache. Otherwise, writes may be slow
• The background IO threads assume a server with one disk and don’t run fast enough when there is work to be done

Fixes

Google patch adds support for multiple background IO threads.

The Google patch will soon have an option to tune the maximum rate of background IO

Connections

• need to support thousands of connections, but not will to use one thread per connection
• MySQL 6 separates threads from connections

Query Plans

• innodb uses sampling to gather stats for the optimizer

• stats are not stored on disk
• stats are collected the first time a table is used after startup and after many rows has been modified

stats can be off between slaves with identical dataset. This happens (sometimes) except if the is a unique key

Overall a good talk. Mark got off several times on things that, frankly, if you weren’t deeply involved in the Innodb code you wouldn’t understand (code snippets). However, it sounds like they are solving some important problems. I am really looking forward to these being put into the “official” MySQL code.