Diamond Notes

On December the 12th, our local MUG is having the honor of hosting Jay Pipes as the speaker for our meeting.  I personally am looking forward to meeting him as he has obviously had a great impact on the MySQL community with his work.  If you are in the area, or anywhere within driving distance, I would recommend that you come by and join the meeting!!

If you can make it and are not a normally Triangle MUG person you will want to let us know so we can plan on how many people are going to be here.   Please email me at “bmurphy” at “paragon-cs.com” to let me know and I will pass it along to the powers-that-be Meeting time is 7:00 pm and it will go until 8:30 pm.

We made a minor update to the QPP toolkit today (”ver. 1.01″). I uploaded the new files to the website. They can be found at http://www.paragon-cs.com/queryprogs. In addition, I have beginnings of a tutorial located at http://www.paragon-cs.com/queryprogs/tutorial.html. It will make this a bit easier to understand.

A couple of people have asked how the QPP toolkit is different than other programs. Please give me a minute to try and explain. QPP can be used to capture queries being executed on a production server at any time. In addition, it will take the captured queries and insert the text of those queries plus timing and host information into a database which you can then use to determine where your servers are spending time executing queries. Finally, you can take that same data that you gathered from a production server and use to generate benchmarking and load tests against a test server. If set up properly these tests can be repeated as many times as needed.

Hope that helps!!

Hey everyone. I just finished uploaded the latest versions of the three QPP programs. They are all working well and I consider them “version 1.0″. I will be updating the qpp pages as soon as possible with a tutorial on how all these programs work together.

The basic idea is that you use the querysniffer program to gather production data from your servers as needed without stopping/starting the MySQL server. Queryparse is actually used to control querysniffer and push the gathered data into a database from which you can use simple php/html pages to view the statistics.

Querybench is used to benchmark/stress-test a test server with production data (gathered, once again, with querysniffer). You can even set it up so that the database can be “rolled back” to its initial state after a run.

All in all, I am very pleased. I have fulfilled all the goals I set out to achieve when I began this little journey a few months ago. It will be very useful in my work and I hope others find it useful as well.

I would like to publicly thank Ian Kilgore who helped far above and beyond anything required. He is one of the system administrators where I work and is the genius programmer behind this stuff. I just had the idea and kind of directed things along. The only “programming” I have done is working on the documentation a bit and developing the web pages for the display of the statistics.

Feedback can be sent to “bmurphy” at “paragon-cs.com”.

Now what do I with all this free time I have left over *cough*?

So I recently came across a situation at work where I need to copy almost 150 million rows of data from a MyISAM table to an Innodb table. Here is the problem that I started thinking of approximately 130 million rows (and ten or eleven hours) into the transfer: what happens if the query gets aborted?

Having had large transfer die once before I know from experience it isn’t pretty. The way I ended up stopping the rollback the first time was to kill the mysqld daemon and rip all the data of the database out and restore the database after restarting the server. I could be so heavy handed during this recovery because it was a test server. Not something that you could do in production.

If I had to cancel the query I ran late last week it would have taken days for the transaction to rollback. So how could I keep a rollback like this from happening– or at least minimize it?

Here is what I ran:

INSERT INTO new_table SELECT * FROM old_table

Of course this selects everything from one table and copies it to a new table. What came to mind when I started thinking about this was:

INSERT INTO new_table SELECT * FROM old_table LIMIT X OFFSET Y

I posted a question to the mysql mailing list and got similar comments. I thought I might be on the right track.

So this seems to work. BUT…I am not convinced that it would. This is similar to the replication issue that Baron Schwartz has talked about this here:

http://www.xaprb.com/blog/2007/11/08/how-mysql-replication-got-out-of-sync/

Baron was having trouble because an ORDER BY….LIMIT statement was returning non-deterministic results. I think something similar could go on here.

First, let me say that I am in no way a “SQL expert”. I don’t pretend to be one. However, follow me for a minute. And feel free to comment :). When you execute a SELECT such as the one above against a database, the server is in no way constrained to give you the results in a deterministic order. When you run the above select statement multiple times with different offsets (to “chunk” the output so it’s easier to roll back) you aren’t guaranteed to get the proper results. At least that is what I understand.

I may be mistaken :). Be gentle if I am. Remember, I am not a SQL guru.

Another recommendation was to do something like the following:

INSERT INTO new_table SELECT * FROM old_table WHERE user_id mod [num_of_passes] = 0, 1, 2 . . . (num_of_passes - 1)

I believe that user_id would have to be a unique key. This query would return completely consistent results. I am not sure what you would do if you didn’t have a unique key to run the WHERE clause against.

Just some food for thought

****added…as Paul pointed out this is valid only as long as no inserts/updates/deletes are performed on the SELECTed table.  I was operating under that assumption (the table I was reading from was not being written too) but forgot to mention it.  Thanks Paul!!

Hey everyone. I am seriously thinking about changing somewhat the format of the magazine. So far I have been able to pull together enough articles each time to publish but I would like to look at an additional way to get material.

There are many high-quality blog entries written every month about MySQL. I would like to (with permission of the author) edit some of them for grammar and any clarifications needed and publish them as articles. I am already planning on doing this with two of Peter Zaitsev’s blog postings on Innodb tuning. Combining those two posts is going to make for a fantastic article. I have already discussed this with him and he agreed to a trial.

Will I still accept articles written strictly for the magazine? Absolutely!! This just means I don’t have to go around twisting peoples arms :).

Thoughts? Comments?

I just wrapped up a test of the new mysql-parallel-dump and mysql-parallel-restore that Baron Schwartz has released as part of his toolkit. The toolkit is available from http://mysqltoolkit.sourceforge.net

I tested on an 8-core (dual processor/quad-core) server with 8 gigabytes of RAM. I baselined with the normal mysqldump command (no options) and then benchmarked variations of both the mysqldump command and the mysqlimport command against the mysql-parallel-dump and mysql-parallel-restore command.

The database tested was under two gigs in size so it wasn’t an ideal test but I was just trying to get an idea of how I can expect it to operate.

The results:

Test One: Parallel Dump & Restore

rm -rf /home/kmurphy/pdump/default/*time mysql-parallel-dump –databases pdump –tab –basedir=/home/kmurphy/pdump

mysql -e ‘drop database pdump’

mysql -e ‘create database pdump’

time mysql-parallel-restore –tab /home/kmurphy/pdumpmysql -e ‘reset master’

Test Two: Parallel Dump & Restore w/Chunk Size of 100,000

rm -rf /home/kmurphy/pdump/default/*time mysql-parallel-dump –databases pdump –tab –chunksize=100000 –basedir=/home/kmurphy/pdump

mysql -e ‘drop database pdump’

mysql -e ‘create database pdump’

time mysql-parallel-restore –tab /home/kmurphy/pdumpmysql -e ‘reset master’

Test Three: Default Dump & Restore

time mysqldump –databases pdump > /data/backup/testing/normal/dumptest.sql

time mysql < /data/backup/testing/normal/opt/dumptest.sqlmysql -e ‘reset master’

Test Four: Optimized standard dump with grouped insert statements and restore

time mysqldump –opt -e –databases pdump > /home/kmurphy/dump/dumptest.sql

time mysql < /home/kmurphy/dump/dumptest.sqlmysql -e ‘reset master’

Test Five: Optimized mysqldump and restore w/tab delimited files

time mysqldump -Q -T /home/kmurphy/dump/pdump  –opt –single-transaction pdumpcat /home/kmurphy/dump/pdump/*.sql > /home/kmurphy/dump/pdump/xyz.sql

time mysql pdump < /home/kmurphy/dump/pdump/xyz.sql

time mysqlimport pdump /home/kmurphy/dump/pdump/*.txtrm /home/kmurphy/dump/pdump/xyz.sql

mysql -e 'reset master'

Test Six: Optimized mysqldump and restore w/tab delimited files and extended inserts

time mysqldump -Q -T /home/kmurphy/dump/pdump  --opt --single-transaction -e pdump

cat /home/kmurphy/dump/pdump/*.sql > /home/kmurphy/dump/pdump/xyz.sqltime

time mysql pdump < /home/kmurphy/dump/pdump/xyz.sql

time mysqlimport pdump /home/kmurphy/dump/pdump/*.txtrm /home/kmurphy/dump/pdump/xyz.sql

mysql -e 'reset master'

The results are quite impressive. The mysql-parallel-dump with chunksize option that I ran didn’t seem to make any difference. Don’t be fooled as this was on a smaller data set. If you have a multi-gigabyte table with millions of rows in innodb format this could make a huge difference. Don’t underestimate it.

The clear winner is the mysql-parallel-dump paired with mysql-parallel-restore. The parallel import/dump over a normal mysqldump w/various options is around twice as fast. Using mysqlimport with mysqldump brings the time difference down to around two minutes but it is still fairly significant. With a larger dataset I think the difference would become more pronounced.

In short, you should strongly consider using the parallel dump and restore tool for your backup needs if you are currently using mysqldump.

We had a little fun yesterday. We had a mysql server that stopped because it turns out that it ran out of disk space in the /var directory. Someone was uploading a large list and it exhausted the disk space writing bin logs before our monitoring tools picked it up.

The server process ended up being killed and then restarted. I then cleaned up some bin-logs so that the server had room. That seemed to solve the problem. Everything looked like it was running. However, after about 15 or 20 minutes we realized that the application itself wasn’t running correctly.

Turns out that the application had continued to try and make connections to the database server while it was experiencing problems. These attempted connections were being logged as connection errors and after the default 10 connection errors the server essentially locks down.

The reference manual lists information here:

http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

We are going to raise the connection error count on our servers to keep this from happening in the future. Consider this carefully if your servers are exposed to the Internet (ours aren’t).

If you run the flush hosts command it will resolve this problem. I am not sure if restarting the mysqld daemon would have resolved the problem (I think it would have). Even though we had restarted the daemon, it was before clearing the log files so failed connections continued to occur.

One final note…in our case the error described in the reference manual did not show up in the error log. So don’t expect it to be there if this problem occurs.

Wednesday, Nov. 7, is the next meeting of the Raleigh/Triangle area MySQL users group. More information available here: http://mysql.meetup.com/144/

Come join the fun!! Meeting starts at 6:30 pm and ends around 8:15 pm.