MySQL Magazine -- Winter 2009 issue released
Hey everyone, This is one of the largest issues so far. In addition, it has some of the best content we have ever had. The articles are all in-depth with some exciting new information: * *Introduction to XtraDB*: an overview of the new XtraDB storage engine along with benchmarks and information about planned future improvements * *Changes in the MySQL DBA and Developer Exams for Version 5.1*: what’s coming in the new exams covering MySQL Server 5.1 * *Covering Indexes in MySQL*: how to create indexes that optimize query execution * *PBXT’s Coder’s Guide*: going in-depth on how you can work with the code for the new PBXT storage engine * *Coding Corner*: Peter’s regular column continues his look at transaction time validity It is available for download, along with all previous back issues, at http://www.mysqlzine.net. thanks, Keith -- Editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Call for articles for Fall Issue of Open Source Database Magazine
Hello everyone, I wanted to take a minute and extend an invitation to anyone who has interest in writing about MySQL or other open source database servers. July brought the first issue of Open Source Database Magazine and it was a resounding success. Our download count has skyrocketed. I was blown away when I checked my stats after the first two days of release and there were almost three times as many downloads as I had ever had of any of the previous issues of MySQL Magazine. Thanks to those who participated by contributing articles; I couldn't have done it without you. The feedback I have received is that the change in direction has been embraced by the open source database community. I am making multiple changes in the magazine but the one that will directly affect authors is that I am increasing the payment for articles. All the details about the coming changes are on an OSDB Magazine blog post here: http://www.osdbzine.net/wordpress/?p=3 I need to hear from you by October the 5th with any proposals and the article needs to be completed and turned in by October the 30th. Looking forward to hearing from you. Keith Murphy -- Editor Open Source Database Magazine http://www.osdbzine.net edi...@osdbzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Nov/Dec 2009 Issue of Open Source Database Magazine released!!
Exciting news! The next issue of Open Source Database Magazine (http://www.osdbzine.net) is now available. This information-packed issue has over 60 pages of information including: * Firebird’s Road Trip and What’s New with 2.5 * Coding Corner: Trees – Where’s the Performance? * PostgreSQL’s tsvector: Secret Sauce for Search Engines * The Lab: The XtraBackup Program for MySQL – Part Two * Drizzle – A Lightweight Database for the Web * Kontrollbase: Enterprise grade MySQL monitoring and analytics * Creating a Twitter Mashup with MongoDB * Introducing LucidDB Plus the usual news and views. All of this for $4.95. It is simply the biggest and the best issue we have ever released. Ready to sign up? Head over http://www.osdbzine.net/signup.html to register and then you can download the new issue. Curious as to what this is all about? I just posted an online addendum to the Drizzle article here: http://www.osdbzine.net/index.php?option=com_content&view=article&id=91:drizzle-table-format&catid=39:generalthat will give you a taste of what you can expect. Thanks to the contributors. You all did a great job and I appreciate it! thanks, keith -- Editor Open Source Database Magazine http://www.osdbzine.net
the mystery of the missing mysql.sock file
Take one perfectly functional production server running 5.0.77. It has been up and running under load for quite some time. I am using xtrabackup for backups and suddenly three or four days ago backup stop running. Investigation shows that the socket file '/tmp/mysql.sock' is no longer there. The mysql server itself is still functioning .. you can specify --host=localhost instead of --socket=/tmp/mysql.sock so the mysql client uses tcp/ip instead of the socket file to connect to the server. - Permissions did not change on the /tmp directory..I checked. - There are no errors in the .err log. - No cron jobs clearing out /tmp. - SHOW VARIABLES LIKE '%sock%' displays the proper socket location Any thoughts? I have seen this type of thing occasionally before, but never taken the time to really investigate. I would just restart the server. I will end up having to do so in this case, but I would really like to find out WHY this happened. Is this is bug in the server code? I can't come up with any other explanation. I would love for this to be explainable! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: the mystery of the missing mysql.sock file
Thanks Johan. Yes, it shows the mysqld holding it open. Not suprising really I guess. I did check the cron jobs and the history file searching for something/someone who deleted it, but didn't find anything. Still, that is what it looks like happend. I will move the sock file to /var/run/mysql so it is out of the way. thanks, keith On Tue, Dec 15, 2009 at 11:00 AM, Johan De Meersman wrote: > Presumably someone deleted it :-) You can try an 'lsof -U |grep mysql' to > see if any processes still have it open. If the mysqld process still has it > open, it's probably not the server. > > Why do you keep it in /tmp, btw ? My guess would be that someone (or some > process) decided it was time to clean /tmp out. > > > On Tue, Dec 15, 2009 at 4:46 PM, Keith Murphy wrote: > >> Take one perfectly functional production server running 5.0.77. It has >> been >> up and running under load for quite some time. I am using xtrabackup for >> backups and suddenly three or four days ago backup stop running. >> >> Investigation shows that the socket file '/tmp/mysql.sock' is no longer >> there. The mysql server itself is still functioning .. you can specify >> --host=localhost instead of --socket=/tmp/mysql.sock so the mysql client >> uses tcp/ip instead of the socket file to connect to the server. >> >> >> - Permissions did not change on the /tmp directory..I checked. >> - There are no errors in the .err log. >> - No cron jobs clearing out /tmp. >> - SHOW VARIABLES LIKE '%sock%' displays the proper socket location >> >> >> >> Any thoughts? I have seen this type of thing occasionally before, but >> never >> taken the time to really investigate. I would just restart the server. I >> will end up having to do so in this case, but I would really like to find >> out WHY this happened. Is this is bug in the server code? I can't come up >> with any other explanation. I would love for this to be explainable! >> >> thanks, >> >> keith >> >> -- >> Chief Training Officer >> Paragon Consulting Services >> 850-637-3877 >> > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: How to not lock anything?
Writers do block readers. Just at the row level vs the table level of MyISAM. It's just much less likely for writers to block readers. keith On Tue, Dec 15, 2009 at 11:57 AM, Perrin Harkins wrote: > On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso > wrote: > > I have an "items" table that is heavily updated with 40 million records > > every 1 or 2 days and I need all those items indexed so they can be > > searched. The problem that I'm having is that the table is constantly > > locked because an insert or delete is being performed. > > > > I am playing with InnoDB vs MyIsam and have been trying to figure out how > to > > get the best performance. > > Problem solved: use InnoDB. Writers don't block readers and vice versa. > > - Perrin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Free online training class on MySQL backups and recovery.
Hey everyone, I just posted new blog post about an online training class I will be doing January the 13th. This class will cover backups, recovery and disaster planning. It is completely free with no strings attached. If you are interested, take a look at the blog post here: http://www.paragon-cs.com/wordpress/?p=358 It contains all the details. There is a limited number of seats available so you need to respond soon. Looking forward to seeing you there! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: tmp tables
Victor, Don't want to butt in, and not trying to be rude, but he gave you advice. You don't seem inclined to take it. How else can he, or anyone else, help you? Clearly you don't understand some fundamental issue about relational databases. If you can't just accept his suggestion to put all carts in one table as the way to do it then there really isn't anything else to say. My 2 cents :) keith -- Chief Training Officer Paragon Consulting Services 850-637-3877 On Mon, Jan 11, 2010 at 10:30 AM, Victor Subervi wrote: > On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz wrote: > > > Victor, > > > > > That strikes me as messy. Each tmp table has as many rows as necessary > > for > > > the products that are to be bough. To do as you say I would have to > > create a > > > table with a zillion rows to accommodate however many products I > > *predict* > > > buyers would buy. Therefore, I guess I should probably create a new > > database > > > so as to not make a mess of the main database. > > > > You fundamentally misunderstand relational database design. I suggest > > reading this book: > > > > > http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-database-design-by-clare-churcher/ > > > > LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my > database design for the shopping cart I just finished building and need to > get this up __n_o_w__, what would your advice be? > V > > > > > Regards, > > Baron > > > > -- > > Baron Schwartz > > Percona Inc: Services and Support for MySQL > > http://www.percona.com/ > > > > > > -- > The Logos has come to bear > http://logos.13gems.com/ >
Re: optimization
♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really need to run two different types of tables. If I were you, I would use InnoDB exclusively unless there is legitimate reason to do otherwise. In an environment that is running 25% writes and a decent query rate you are bound to have contention issues with MyISAM. While there are always going to be edge cases for MyISAM, your default should be innodb and your config should reflect this. Changing your tables to InnoDB is a simple ALTER TABLE which you can script if there are a number of tables to convert. Allocate as much of your available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of total RAM) and I bet you would see a dramatic difference. That is simplifying things somewhat, but should give an idea. keith On Tue, Jan 26, 2010 at 11:53 AM, mos wrote: > Get yourself a copy of the book High Performance MySQL 2nd Edition > Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English > ISBN-10: 0596101716 ISBN-13: 978-0596101718 > > Here is a brief preview of the first edition: > http://books.google.ca/books?id=iaCCQ13_zMIC&printsec=frontcover&dq=high+performance+mysql&cd=1#v=onepage&q=&f=false > > Mike > > At 10:19 AM 1/26/2010, John G. Heim wrote: > >> From: "Jaime Crespo Rincón" >> >> Sent: Monday, January 25, 2010 5:30 PM >> >> >> 2010/1/25 John G. Heim : >> >>> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is >>> running the latest mysql-server from debian lenny (5.0.1). I have >>> databases >>> for drupal, moodle, spamassassin, horde3, and a small database for >>> departmental stuff. >>> >>> The problem is that inserts/updates are sometimes very slow, on the order >>> of >>> a minute. I am hoping somebody can sspot something wrong in my config. >>> Here's the optimization settings section (for your convenience). The >>> whole >>> my.cnf is reproduced below that: >>> >> >> Are your databases using MyISAM or InnoDB? >> >> Both. Maybe that's the problem? I started creating database tables for my >> own web apps with the default mysql configuration. I believe the default >> database engine is MyISAM. But then I wanted to use foreign keys and I saw >> that it required me to use InnoDB. So I converted some tables to InnoDB but >> not all. Maybe it was a mistake not to convert all of them. >> >> After that, I installed drupal, moodle, and mediawiki. I haven't looked at >> what kind of tables those packages create. They may not specify it and the >> default is still whatever it is when you install mysql, MyISAM I think. >> >> * If MyISAM, you could be suffering contention problems on writes >>> >> because of full table locks. No easy solution but engine change or >> database sharding. Also key_buffer, (and the other buffers) coud be >> too small for 16GB of RAM. Are you really using more thant 10% of it? >> You could also disable other engines if unused. >> * If InnoDB, you have not set innodb_buffer_pool_size nor log size. >> You could increase the pool to >50% of ram available. >> >> Those are very general suggestions. It depends a lot on your hardware >> (slow storage?), other apps installed on the same machine or the load >> of the server, among others. >> >> >> Well, it could be that the disks aren't real fast. The server is also >> running a print server (lprng). I don't think that's very CPU intensive but >> it might be slowing down writes. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: optimization
You absolutely *should not* convert the mysql database to InnoDB. Read the above sentence again :) All others, unless you had a specific reason not to do so, yes, I would convert them. keith On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim wrote: > Just to be clear, you're suggesting I convert all of the spamassassin, > drupal, and mediawiki tables to innodb too? Or just my own database? What > about the mysql database itself? I wouldn't convert those tables, would I? > > - Original Message - From: "Keith Murphy" > To: > Sent: Tuesday, January 26, 2010 11:06 AM > Subject: Re: optimization > > > > ♫ > I would recommend the same to you about reading High Perf. MySQL as Baron, > et al wrote a great book about performance on MySQL. That being said, it > has > been my experience that in 99% of client cases they don't really need to > run > two different types of tables. If I were you, I would use InnoDB > exclusively > unless there is legitimate reason to do otherwise. In an environment that > is > running 25% writes and a decent query rate you are bound to have contention > issues with MyISAM. While there are always going to be edge cases for > MyISAM, your default should be innodb and your config should reflect this. > > Changing your tables to InnoDB is a simple ALTER TABLE which you can script > if there are a number of tables to convert. Allocate as much of your > available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of > total RAM) and I bet you would see a dramatic difference. That is > simplifying things somewhat, but should give an idea. > > > keith > > > On Tue, Jan 26, 2010 at 11:53 AM, mos wrote: > > Get yourself a copy of the book High Performance MySQL 2nd Edition >> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English >> ISBN-10: 0596101716 ISBN-13: 978-0596101718 >> >> Here is a brief preview of the first edition: >> >> http://books.google.ca/books?id=iaCCQ13_zMIC&printsec=frontcover&dq=high+performance+mysql&cd=1#v=onepage&q=&f=false >> >> Mike >> >> At 10:19 AM 1/26/2010, John G. Heim wrote: >> >> From: "Jaime Crespo Rincón" >>> >>> Sent: Monday, January 25, 2010 5:30 PM >>> >>> >>> 2010/1/25 John G. Heim : >>> >>> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is >>>> running the latest mysql-server from debian lenny (5.0.1). I have >>>> databases >>>> for drupal, moodle, spamassassin, horde3, and a small database for >>>> departmental stuff. >>>> >>>> The problem is that inserts/updates are sometimes very slow, on the >>>> order >>>> of >>>> a minute. I am hoping somebody can sspot something wrong in my config. >>>> Here's the optimization settings section (for your convenience). The >>>> whole >>>> my.cnf is reproduced below that: >>>> >>>> >>> Are your databases using MyISAM or InnoDB? >>> >>> Both. Maybe that's the problem? I started creating database tables for my >>> own web apps with the default mysql configuration. I believe the default >>> database engine is MyISAM. But then I wanted to use foreign keys and I >>> saw >>> that it required me to use InnoDB. So I converted some tables to InnoDB >>> but >>> not all. Maybe it was a mistake not to convert all of them. >>> >>> After that, I installed drupal, moodle, and mediawiki. I haven't looked >>> at >>> what kind of tables those packages create. They may not specify it and >>> the >>> default is still whatever it is when you install mysql, MyISAM I think. >>> >>> * If MyISAM, you could be suffering contention problems on writes >>> >>>> >>>> because of full table locks. No easy solution but engine change or >>> database sharding. Also key_buffer, (and the other buffers) coud be >>> too small for 16GB of RAM. Are you really using more thant 10% of it? >>> You could also disable other engines if unused. >>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size. >>> You could increase the pool to >50% of ram available. >>> >>> Those are very general suggestions. It depends a lot on your hardware >>> (slow storage?), other apps installed on the same machine or the load >>> of the server, among others. >>> >>> >>> Well, it could be that the disks aren't real fas
Re: tcpdump mysql ?
You should look at www.hackmysql.com. He has a sniffed program strictly for Mysql. Should do what you want. HTH Keith On Apr 20, 2010 5:48 AM, "Brent Clark" wrote: Hiya I tried getting the following command running from the following youtube clip. http://www.youtube.com/watch?v=Zofzid6xIZ4 Look at 19:25 I know I can use tcpdump, with maatkit (Im not always able to install maatkit on clients machines). But based on whats above in the clip, Did Mr Callaghan make a typo or leave something out. This is the command as I understand it. tcpdump -c 100 -s 1000 -A -n -p port 3306 | grep SELECT | sed 's/\/\*.*\*\///g' | sed 's/.*\(SELECT.*\)/\1/gI' | sort | uniq -c | sort -r -n -k 1,1 | head -5 Other question is. What commnds do you use to help debuging and testing. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com
Re: Myisam advantages
Since everyone keeps bringing up Innodb's shared tablespace, I will point out that Innodb has a "file-per-table" option where each table has it own tablespace. This helps with management issues. While there is still a central datafile it doesn't contain table data and is much smaller than if you used a centralized table space. keith On Sat, Jul 17, 2010 at 12:37 PM, Jan Steinman wrote: > From: "P.R.Karthik" >> >> >> I am newbie to mysql can i know the advantages of myisam storage engine >> and some of its special features. >> > > Works better with file-based incremental backup systems. > > With InnoDB, you end up backing up a humongous file of all your InnoDB > tables, even if only one byte in one field of one table of one database was > touched. > > > There are only two ways to look at life: One is as if nothing is a miracle. > The other is as if everything is a miracle. -- Albert Einstein > Jan Steinman, EcoReality Co-op > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
MySQL rightly says you should dump and reload. As William said, you should read the release notes for every release between your current release and the target release. I have done this several times. I am currently planning a migration from 4.1.22 to 5.1.49 with a brief stop at 5.0 along the way. There is almost 200 gigs of data on the master server in this situation so it does take some foresight and planning. However, the end result will be no significant downtime. You can configure MySQL 5.0 as a slave of MySQL 4.1 so you might consider doing that. That way when its time for the actual upgrade you just point the application to the MySQL 5.0 server and shut down the MySQL 4.1 server (which CANNOT be a slave of MysQL 5.0 -- it's a one way relationship due to the changes in the binary logging. Hope that helps. keith On Mon, Aug 16, 2010 at 11:42 AM, Wm Mussatto wrote: > > > On Mon, August 16, 2010 07:26, Nunzio Daveri wrote: > > Hi all, I > was reading a few of the notes on this forum from a few months > > > back > > and it seems that ONE WAY of upgrading from 4.x to 5.X with > MyISAM only > > databases is to copy the actual data folder from the > 4.X version to a temp > > place, then remove 4.x from the OS, > install 5.X and then just put the 4.X > > data > > folder into > the 5.X folder??? > > > > > > Is it really that > simple? Has anyone done this and can verify this > > please? I > > am thinking I am missing a few commands you have to run at least??? > My > > database > > is pretty small is an only 1.8GB so I am > thinking this is a walk in the > > park :-) > > > > > Please advise... > > > > And as always... TIA... > > > > > Nunzio > What version of 4.x, I upgraded between > Debian stable versions and got burned because in the middle of the 4.x > group MySQL changed to a more "correct" version of JOINs. > Didn't effect to data, which will should work as you expect, but did have > subtle impact on the select statements embedded in various programs. > Read the release note between YOUR current and new versions. > -- > William R. Mussatto > Systems Engineer > http://www.csz.com > 909-920-9154 > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
I would really recommend that you have a second server set up running MySQL 5.0. Otherwise the complexity is going to drive you crazy. Either way you are going to have to get a backup of the master (4.1) server somehow. I am curious. If you can't take an hour or so take a mysqldump of the server how are you running backups now? And if you aren't runninng backups you need to run to your boss and say "It's REALLY REALLY REALLY critical that we start making backups." And do it beginning tonight at the latest. Otherwise something is going to happen, the data is going to be lost and you are best case going to look REALLY REALLY bad. I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is fairly trivial and doesn't require a dump/reload but I would still take the time to stop at 5.0 and make sure everything is working before moving on to 5.1. keith On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri wrote: > Thanks William and Keith. So how to have min down time since this is a > stand > alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port > 3307), > then replicate the data as it comes into 4.x to 5.x and when it is all > sync'd up > then turn 4.x off, remove it and have 5.x responding on port 3306? > > Mysqldump takes over an hour and then prob more to reimport? I only have a > 30 - > 45 min window. > > If I do a straight dump from 4.1.22 with all options why stop at 5.0 and > not > just go straight to 5.1.48? This is a single box, no replication or > clustering > going on ;-) Also all the data is in MyISAM, zero InnoDB :-) > > Thanks again for the advice :-) > > Nunzio > > > > > > From: Keith Murphy > To: Nunzio Daveri > Sent: Mon, August 16, 2010 9:42:07 AM > Subject: Re: Is upgrading from 4.X to 5.X really that easy? > > No, that would be a huge mistake. There are subtle differences between the > two > versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous > new > reserved words. > > > You need to think about this carefully before you do it. I know there is > binary > incompatability between Innodb tables (vers 4.X - 5.X). I will take you > word > that what you are saying would actually work, but I still wouldn't > recommend it. > > > Just my 2 cents... > > keith > > > On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri > wrote: > > Hi all, I was reading a few of the notes on this forum from a few months > back > >and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only > >databases is to copy the actual data folder from the 4.X version to a temp > >place, then remove 4.x from the OS, install 5.X and then just put the 4.X > data > >folder into the 5.X folder??? > > > > > >Is it really that simple? Has anyone done this and can verify this > please? I > >am thinking I am missing a few commands you have to run at least??? My > database > >is pretty small is an only 1.8GB so I am thinking this is a walk in the > park > :-) > > > >Please advise... > > > >And as always... TIA... > > > >Nunzio > > > > > > > > > -- > Chief Training Officer > Paragon Consulting Services > 850-637-3877 > > > > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
error log rotation problem
Hey everyone, I have run across something that has me stumped. I have some systems that have very large error logs because we haven't moved from statement-based to mixed-based replication yet so they get a lot of warnings logged. I need to rotate the error logs and have started looking at it doing so. The problem is that on one system a normal course of action works perfectly, but on anther it does not. And these systems were installed from the same RPM packages (5.1.50 -- downloaded from the MySQL website). Here is what I do: log in with mysql client and 'flush logs' OR mysqladmin --flush-log It should rename the old log file to mysqld.log-old and start a new mysqld.log file. On one system it works perfectly On the other...nothing. I tried moving the error log (mv /var/log/mysqld/mysqld.log /var/log/mysqld.log.old) and then issuing the flush logs command...it stays writing to the "old" file and never makes a new one. If I were to restart mysqld it would solve the problem but this is a production system and that isn't very practical. These systems are very similar. my.cnfs have been checked for differences. I searched the interwebs and specifically bugs.mysql.com for something similar. Not finding anything. I would appreciate any ideas! thanks, Keith
Re: error log rotation problem
Hey everyone, the problem was that I have the error log in /var/log/mysqld/mysql.error and SELinux was not configured to allow it to rotate. I have compiled a custom module to allow it so the problem is resolved. It was not an issue with MySQL Server itself. thanks again. Keith On Fri, Aug 12, 2011 at 12:57 PM, Prabhat Kumar wrote: > this will help you > http://adminlinux.blogspot.com/2009/09/mysql-log-file-rotation.html > > On Fri, Aug 12, 2011 at 9:43 AM, Paul DuBois wrote: > >> >> On Aug 11, 2011, at 2:30 PM, Keith Murphy wrote: >> >> > Hey everyone, >> > >> > I have run across something that has me stumped. I have some systems >> that >> > have very large error logs because we haven't moved from statement-based >> to >> > mixed-based replication yet so they get a lot of warnings logged. I need >> to >> > rotate the error logs and have started looking at it doing so. >> > >> > The problem is that on one system a normal course of action works >> perfectly, >> > but on anther it does not. And these systems were installed from the >> same >> > RPM packages (5.1.50 -- downloaded from the MySQL website). >> > >> > Here is what I do: >> > >> > >> > log in with mysql client and 'flush logs' OR mysqladmin --flush-log >> > >> > It should rename the old log file to mysqld.log-old and start a new >> > mysqld.log file. >> > >> > On one system it works perfectly >> > >> > On the other...nothing. >> > >> > I tried moving the error log (mv /var/log/mysqld/mysqld.log >> > /var/log/mysqld.log.old) and then issuing the flush logs command...it >> stays >> > writing to the "old" file and never makes a new one. >> > >> > If I were to restart mysqld it would solve the problem but this is a >> > production system and that isn't very practical. >> > >> > These systems are very similar. my.cnfs have been checked for >> differences. I >> > searched the interwebs and specifically bugs.mysql.com for something >> > similar. Not finding anything. >> > >> > I would appreciate any ideas! >> >> >> There was a change to log flushing that affects the error log in 5.1.51/ >> 5.5.7. >> It might be the cause of what you're seeing. >> >> http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html >> http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html >> >> " >> Incompatible Change: Previously, if you flushed the logs using FLUSH LOGS >> or mysqladmin flush-logs andmysqld was writing the error log to a file (for >> example, if it was started with the --log-error option), it renamed the >> current log file with the suffix -old, then created a new empty log file. >> This had the problem that a second log-flushing operation thus caused the >> original error log file to be lost unless you saved it under a different >> name. For example, you could use the following commands to save the file: >> >> shell> mysqladmin flush-logs >> >> shell> mv host_name.err-old backup-directory >> >> To avoid the preceding file-loss problem, renaming no longer occurs. The >> server merely closes and reopens the log file. To rename the file, you can >> do so manually before flushing. Then flushing the logs reopens a new file >> with the original file name. For example, you can rename the file and create >> a new one using the following commands: >> >> shell> mv host_name.err host_name.err-old >> >> shell> mysqladmin flush-logs >> >> shell> mv host_name.err-old backup-directory >> >> (Bug #29751) >> >> See also Bug #56821. >> " >> -- >> Paul DuBois >> Oracle Corporation / MySQL Documentation Team >> Madison, Wisconsin, USA >> www.mysql.com >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com >> >> > > > -- > Best Regards, > > Prabhat Kumar > MySQL DBA > > My Blog: http://adminlinux.blogspot.com > My LinkedIn: http://www.linkedin.com/in/profileprabhat > -- * Paragon Consulting Services* MySQL Operational Support, Systems Consulting and Training Services * Coming in April our new online training course!!! 850-637-3877
Re: How often the slave to pull data from master?
Yes, the slave pulls from the master. The slave io thread reads the information effectively as soon as it is written to the master's binary log. Keith On Jul 26, 2012 4:17 AM, "Zhigang Zhang" wrote: > Hello: > > > > I haven't read the source code of mysql ,but as I know ,the slave get data > from master server, rather than the master push data to slave. > > > > I think there is a timer to do this. > > > > Who can tell me the interval about the replicationhow often the slave > to > pull data from master? > > > > Thanks! > > > > Zhigang Zhang > >
Re: How often the slave to pull data from master?
Yes, the slave oulls On Jul 26, 2012 4:17 AM, "Zhigang Zhang" wrote: > Hello: > > > > I haven't read the source code of mysql ,but as I know ,the slave get data > from master server, rather than the master push data to slave. > > > > I think there is a timer to do this. > > > > Who can tell me the interval about the replicationhow often the slave > to > pull data from master? > > > > Thanks! > > > > Zhigang Zhang > >
Re: user last activity and log in
My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with "super" privileges won't be logged. Thanks, Keith ---------- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
One small correction. Init-connect doesn't require a restart of MySQL. I was thinking of init-file. So that's even better. On Thursday, October 4, 2012, Keith Murphy wrote: > My friend Dave Holoboff wrote this up some time ago: > > > http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html > > You know you people sound like children. > > Really unprofessional. > > Go ahead --- call me names. i left middle school almost 30 years ago. It > won't bother me. > > Can we knock off the name calling and actually offer advice and possible > solutions? I thought that was what this list was for. > > For those of us out in the field doing things ... This might be your > ticket. It requires a restart of MySQL (which may or may not be acceptable) > bit it's a fairly clean solution. > > Minimal load, easy to query for your last connection time and how often > connections are made by a user. > > Again, requires a restart to enable (and disable) . Oh, and users with > "super" privileges won't be logged. > > Thanks, > > Keith > > -- > > Keith Murphy > Senior MySQL DBA > Principal Trainer > Paragon Consulting Services > http://www.paragon-cs.com > 850-637-3877 > > > > > > > > -- > > * > * > (c) 850-637-3877 > -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, "Mike Franon" wrote: > Thanks everyone for suggestions. > > I am doing this on a test box with a copy of our db before doing this > on production db servers. > > I just upgraded from 5.0 to 5.1, and ran mysql_upgrade > > and see I have a few tables with the following error: > > error: Table upgrade required. Please do "REPAIR TABLE > `tablename`" or dump/reload to fix it! > > I got this on 4 tables so far, but it still checking, my database is > huge so might be a while. > > The question I have what is the best way to fix this? > > To install all I did was remove all of the 5.0, and then did a yum > install 5.1 on my AWS machine. and then just started mysql. > > Should I instead do a complete mysqldump, and use that instead? > > On Thu, Feb 14, 2013 at 7:40 PM, Rick James wrote: > > Sounds like something that, once discovered, can be fixed in the old > version > > -- then it works correctly in both. > > > > > > > > That is what happened with a 4.0->5.1 conversion years ago. With 1000 > > different tables and associated code, we encountered two > incompatibilities. > > One had to do with NULLs, the other with precedence of commajoin vs > explicit > > JOIN. > > > > > > > > From: Singer Wang [mailto:w...@singerwang.com] > > Sent: Thursday, February 14, 2013 3:41 PM > > To: Rick James > > Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; < > mysql@lists.mysql.com> > > > > > > Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 > > > > > > > > Its a very pedantic case, but we had a few instances where it was an > issue > > at my last job. It basically involved multi-table deletes and aliasing.. > I > > quote the change notes for MySQL 5.5.3 > > > > > > > > Incompatible Change: Several changes were made to alias resolution in > > multiple-table DELETE statements so that it is no longer possible to have > > inconsistent or ambiguous table aliases. > > > > § In MySQL 5.1.23, alias declarations outside the table_references part > of > > the statement were disallowed for theUSING variant of multiple-table > DELETE > > syntax, to reduce the possibility of ambiguous aliases that could lead to > > ambiguous statements that have unexpected results such as deleting rows > from > > the wrong table. > > > > Now alias declarations outside table_references are disallowed for all > > multiple-table DELETE statements. Alias declarations are permitted only > in > > the table_references part. > > > > Incorrect: > > > > > > > > DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; > > > > DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; > > > > Correct: > > > > > > > > DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; > > > > DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; > > > > § Previously, for alias references in the list of tables from which to > > delete rows in a multiple-table delete, the default database is used > unless > > one is specified explicitly. For example, if the default database is db1, > > the following statement does not work because the unqualified alias > > reference a2 is interpreted as having a database of db1: > > > > § > > > > § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 > > > > WHERE a1.id=a2.id; > > > > To correctly match an alias that refers to a table outside the default > > database, you must explicitly qualify the reference with the name of the > > proper database: > > > > > > > > DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 > > > > WHERE a1.id=a2.id; > > > > Now alias resolution does not require qualification and alias references > > should not be qualified with the database name. Qualified names are > > interpreted as referring to tables, not aliases. > > > > Statements containing alias constructs that are no longer permitted must > be > > rewritten. (Bug #27525) > > > > > > > > > > > > On Thu, Feb 14, 2013 at 6:11 PM, Rick James > wrote: > > > > Singer, do you have some examples? > > > > > >> -Original Message- > >> From: Singer Wang [mailto:w...@singerwang.com] > >> Sent: Thursday, February 14, 2013 2:59 PM > >> To: Mihail Manolov > >> Cc: Mike Franon; Akshay Suryavanshi; > >> Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 > >> > > > >> There are queries that works with 5.1/5.0 that do not work with 5.5, I > >> would test extensively.. > >> > >> S > >> > >> > >> On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov < > >> mihail.mano...@liquidation.com> wrote: > >> > >> > You could jump from 5.0 directly to 5.5 and skip 5.1. I have without > >> > any issues. There are some configuration file change, which you may > >> > want to consider checking. I definitely recommend upgradi
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, "Mike Franon" wrote: > I am having a real hard time upgrading just from 5.0.96 to 5.1 > > I did a full mysqldump and then restore the database, keep in mind our > database is 400 GB, mysqldump is 600MB file, about 30 minutes into the > restore get this error on one table on an insert: > > ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; > check the manual that corresponds to your MySQL server version for the > right syntax to use near ''2010-04-10 20' at line 1 > > It weird because If I upgrade 5.1 right over 5.0 without doing a > mysqldump, and then do a mysqlcheck it works, except for 5 tables, and > triggers, so trying to think of the best way to get to 5.1 > > On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy > wrote: > > While it might be GA I would not recommend that you deploy it for a > while. > > ... at least several point releases. There will be new bugs uncovered as > it > > moves out to a wider audience. > > > > Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off > it > > and test. Be patient. Save yourself some heartache. Just my two cents. > > > > Keith > > > > On Feb 15, 2013 9:27 AM, "Mike Franon" wrote: > >> > >> Thanks everyone for suggestions. > >> > >> I am doing this on a test box with a copy of our db before doing this > >> on production db servers. > >> > >> I just upgraded from 5.0 to 5.1, and ran mysql_upgrade > >> > >> and see I have a few tables with the following error: > >> > >> error: Table upgrade required. Please do "REPAIR TABLE > >> `tablename`" or dump/reload to fix it! > >> > >> I got this on 4 tables so far, but it still checking, my database is > >> huge so might be a while. > >> > >> The question I have what is the best way to fix this? > >> > >> To install all I did was remove all of the 5.0, and then did a yum > >> install 5.1 on my AWS machine. and then just started mysql. > >> > >> Should I instead do a complete mysqldump, and use that instead? > >> > >> On Thu, Feb 14, 2013 at 7:40 PM, Rick James > wrote: > >> > Sounds like something that, once discovered, can be fixed in the old > >> > version > >> > -- then it works correctly in both. > >> > > >> > > >> > > >> > That is what happened with a 4.0->5.1 conversion years ago. With 1000 > >> > different tables and associated code, we encountered two > >> > incompatibilities. > >> > One had to do with NULLs, the other with precedence of commajoin vs > >> > explicit > >> > JOIN. > >> > > >> > > >> > > >> > From: Singer Wang [mailto:w...@singerwang.com] > >> > Sent: Thursday, February 14, 2013 3:41 PM > >> > To: Rick James > >> > Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; > >> > > >> > > >> > > >> > Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 > >> > > >> > > >> > > >> > Its a very pedantic case, but we had a few instances where it was an > >> > issue > >> > at my last job. It basically involved multi-table deletes and > aliasing.. > >> > I > >> > quote the change notes for MySQL 5.5.3 > >> > > >> > > >> > > >> > Incompatible Change: Several changes were made to alias resolution in > >> > multiple-table DELETE statements so that it is no longer possible to > >> > have > >> > inconsistent or ambiguous table aliases. > >> > > >> > § In MySQL 5.1.23, alias declarations outside the table_references > part > >> > of > >> > the statement were disallowed for theUSING variant of multiple-table > >> > DELETE > >> > syntax, to reduce the possibility of ambiguous aliases that could lead > >> > to > >> > ambiguous statements that have unexpected results such as deleting > rows > >> > from > >> > the wrong table. > >> > > >> > Now
Re: Archive Engine Question
>From here: http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html The ARCHIVE engine supports INSERT<http://dev.mysql.com/doc/refman/5.0/en/insert.html> and SELECT <http://dev.mysql.com/doc/refman/5.0/en/select.html>, but not DELETE <http://dev.mysql.com/doc/refman/5.0/en/delete.html>, REPLACE<http://dev.mysql.com/doc/refman/5.0/en/replace.html>, or UPDATE <http://dev.mysql.com/doc/refman/5.0/en/update.html>. It does support ORDER BY operations, BLOB<http://dev.mysql.com/doc/refman/5.0/en/blob.html> columns, and basically all but spatial data types (see Section 12.16.4.1, “MySQL Spatial Data Types”<http://dev.mysql.com/doc/refman/5.0/en/mysql-spatial-datatypes.html>). The ARCHIVE engine uses row-level locking. *Storage:* Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/). You can use OPTIMIZE TABLE <http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html> to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE <http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html>, see later in this section). Beginning with MySQL 5.0.15, the engine also supports CHECK TABLE<http://dev.mysql.com/doc/refman/5.0/en/check-table.html>. There are several types of insertions that are used: You tell my why a customer who wants to place AUDIT information in a table might want that? Keith Murphy http://www.paragon-cs.com -- (c) 850-637-3877 On Tue, Sep 17, 2013 at 10:19 AM, Wayne Leutwyler wrote: > Hello List, > > I have a customer who is wanting to use the Archive Engine. I have no > experience with this engine, other than what I am been reading. Why would I > want to use Archive over InnoDB. They are only going to be placing audit > information in the table. > > Walter "Wayne" Leutwyler, RHCT > Sr. MySQL Database Administrator > Mobile: 614 519 5672 > Office: 614 889 4956 > E-mail: wayne.leutwy...@gmail.com > E-mail: wleut...@columbus.rr.com > Website: http://penguin-workshop.dyndns.org > > "Courage is being scared to death, but saddling up anyway." --John Wayne > >
Re: Stored Procedure help
I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. wrote: > Would you try this? > > CREATE PROCEDURE `reset_sortid` (IN category INT(11)) > BEGIN > SET @a = 0; > UPDATE > documents SET sort_id = (@a := @a + 1) > WHERE > document_category = category > ORDER BY > sort_id; > END > // > > > 2014-07-14 11:42 GMT+09:00 Don Wieland : > > > I am trying to create this stored procedure, but can't understand why my > > editor is chocking on it. Little help please: > > > > DELIMITER // > > CREATE PROCEDURE `reset_sortid` (IN category INT(11)) > > BEGIN > > DECLARE a INT; > > SET a = 0; > > UPDATE > > documents SET sort_id = (a := a + 1) > > WHERE > > document_category = category > > ORDER BY > > sort_id; > > END > > // > > > > > > Don Wieland > > d...@pointmade.net > > http://www.pointmade.net > > https://www.facebook.com/pointmade.band > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql > > > > > -- (c) 850-449-1912 (f) 423-930-8646
Re: Query time taken on disk
Satendra, Google "show profile" as it may give you all the information that you need. There is a lot more details in the performance_schema if you want to dig into it, but it can be quite difficult to get out. Here is one place to start if you want to pursue that angle: http://www.markleith.co.uk/2011/05/23/monitoring-mysql-io-latency-with-performance_schema/ keith On Mon, Jul 14, 2014 at 5:59 AM, Reindl Harald wrote: > > > Am 14.07.2014 12:48, schrieb Satendra: > > Hi there, I'm struggling to find the total time taken by a database query > > on the disk? As I understand when a database query start execution it > takes > > some time inside the database engine & some time to seek the result from > > disk (if that is not in cache/buffer) > > > > Can anybody from the group please suggest any clue about the execution > time > > on the disk? > > mysql can't know this in any useful way > > "disk" can be anyhting, real disk access, VFS and so cache > the application layer knows nothing about > > -- (c) 850-449-1912 (f) 423-930-8646
MySQL Magazine Fall 2008 Issue Released!
The next issue of MySQL Magazine has been uploaded to the website at http://www.mysqlzine.net. It is a free pdf magazine about MySQL administration and development. Download it while it's hot! thanks, Keith -- Editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
Check out Giuseppe Maxia's MySQL Sandbox program. It is a very easy way to run however many MySQL servers you want with separate config files and such .. heck..even separate versions if you want (one 5.0, one 5.1, one 6.0). It is available here: https://launchpad.net/mysql-sandbox Will take you 10 minutes to set up if you have any perl experience whatsover..otherwise it might take a half hour. Worth the time. Google as there are several presentations on it available online. Keith Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- Editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error after system setup/replication
This: 070605 15:07:27 InnoDB: Error: page 170144 log sequence number 71 2823728422 InnoDB: is in the future! Current system log sequence number 71 452597657. InnoDB: Your database may be corrupt. 070605 15:07:28 InnoDB: Error: page 61253 log sequence number 71 910331446 InnoDB: is in the future! Current system log sequence number 71 452599188. InnoDB: Your database may be corrupt. 070605 15:07:29 InnoDB: Error: page 56830 log sequence number 71 3280625836 InnoDB: is in the future! Current system log sequence number 71 452601958. InnoDB: Your database may be corrupt. shows up in the error log on a new server we are setting up. This system had the data copied to it from another db server. On the old server we locked the tables (flush tables with read lock), did a "show slave status" to get the bin log position and copied the relevant directories (50gigs) to the new server. Then we started up the new server. Everything appears to be going fine. Replication status showed that it was roughly 5000 seconds behind, but it synced up so that it is now 0 seconds behind. However, the same error message is still being written to the error log. I am not familiar with it at all. There is some mention of it in google but the answers really don't make any sense to me. Any ideas? Thanks, Keith -- Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877
Re: error after system setup/replication
I have resolved the issue. As Heikki said in another posting, basically injecting a large amount of data will cause it to catch up. I made a new DB on the server and am importing a 4 GB table into it. At some point during the process the error quit getting logged. After it is done importing I can just drop the table/DB and be done with it. I am going to look into this some more as I feel this is a definite "bug" and even though this work-around fixed it - it is very kludgy. And we actually did move the log files and have it recreate them on startup. - Original Message - From: "Juan Eduardo Moreno" <[EMAIL PROTECTED]> To: "Keith Murphy" <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Tuesday, June 5, 2007 4:24:22 PM (GMT-0500) America/New_York Subject: Re: error after system setup/replication Keith, When you copied the database, you need recreate de logfiles and the sequences. Then, startup the database. 1) clean your actually log files ( backup to another side) 2) When the database startup automatically recreate the log files and sequence. Send feedback about it. Regards, Juan On 6/5/07, Keith Murphy < [EMAIL PROTECTED] > wrote: This: 070605 15:07:27 InnoDB: Error: page 170144 log sequence number 71 2823728422 InnoDB: is in the future! Current system log sequence number 71 452597657. InnoDB: Your database may be corrupt. 070605 15:07:28 InnoDB: Error: page 61253 log sequence number 71 910331446 InnoDB: is in the future! Current system log sequence number 71 452599188. InnoDB: Your database may be corrupt. 070605 15:07:29 InnoDB: Error: page 56830 log sequence number 71 3280625836 InnoDB: is in the future! Current system log sequence number 71 452601958. InnoDB: Your database may be corrupt. shows up in the error log on a new server we are setting up. This system had the data copied to it from another db server. On the old server we locked the tables (flush tables with read lock), did a "show slave status" to get the bin log position and copied the relevant directories (50gigs) to the new server. Then we started up the new server. Everything appears to be going fine. Replication status showed that it was roughly 5000 seconds behind, but it synced up so that it is now 0 seconds behind. However, the same error message is still being written to the error log. I am not familiar with it at all. There is some mention of it in google but the answers really don't make any sense to me. Any ideas? Thanks, Keith -- Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877 -- Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877
determining if tables are being used
I have inherited database servers from a group whose main job was not working on mysql. I am not certain if all the tables on the databases are being used. Is there some efficient way of determining if a table is being accessed or when the last time it was accessed? Thanks, Keith -- Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
mysql user password funkyness
Everyone, So I have never seen this before. I set up a database yesterday. I added a user today in the normal manner.. ie grant usage on *.* to 'username'@'%' identified by 'password'; Here is the thing. You exit the console and try to log in with mysql -u username -ppassword and it won't let you in... BUT it log in with both "mysql -u username" and also "mysql -u username -p (hit enter key)". So the user account works without a password. executing the show grants command shows a password so this shouldn't work. Never had this problem before and kind of hard to google for "mysql user login without password but has password".. show grants for 'user'@'%'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password_hash' | ++ There are other privileges for the appropriate databases but they really aren't relevant. thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
Re: mysql user password funkyness
Thanks everyone for the replies. The problem revolved around '%' vs 'localhost'. To me, it seems logical that '%' would include localhost..not really true. So, I have it figured out. thanks, Keith - Original Message - From: "B. Keith Murphy" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, September 26, 2007 10:39:17 PM (GMT-0500) America/New_York Subject: mysql user password funkyness Everyone, So I have never seen this before. I set up a database yesterday. I added a user today in the normal manner.. ie grant usage on *.* to 'username'@'%' identified by 'password'; Here is the thing. You exit the console and try to log in with mysql -u username -ppassword and it won't let you in... BUT it log in with both "mysql -u username" and also "mysql -u username -p (hit enter key)". So the user account works without a password. executing the show grants command shows a password so this shouldn't work. Never had this problem before and kind of hard to google for "mysql user login without password but has password".. show grants for 'user'@'%'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password_hash' | +--------+ There are other privileges for the appropriate databases but they really aren't relevant. thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
Re: how to restore mysql database after Crash
Recover your files first and then you can see where you can go. I suppose you now have backup plans in place now? Keith David Winslow wrote: Hey, Environment: Windows 2003 server Mysql 5.0 server Problem: Our server crashed and the c drive was formatted. Unfortunately we did not have a backup of the mysql databases which we were using. We can hopefully retrieve the file structure through a file system recovery utility. What would the steps in order to restore the databases again ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: open_files_limit problem.
Paul, Take a look at the ulimit command. We had a similar issue, and it turns out there are OS level per-process limits as well. We have now configured the /etc/init.d/mysql file set the ulimit to what we need. here is the snippet form the file: echo $echo_n "Starting MySQL" ulimit -n 4096 We just added the ulimit line. Hope that helps. Keith - Original Message - From: "Paul Halliday" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Thursday, October 11, 2007 3:02:27 PM (GMT-0500) America/New_York Subject: open_files_limit problem. I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid mysql 21770 0.0 1.3 58188 26168 p3 S 3:50PM 0:00.21 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid Within /var/db/mysql/my.cnf I have: [mysqld_safe] open_files_limit = 32768 [mysqld] open_files_limit = 32768 But when I try something like "mysql> show variables like '%files%';": I get: open_files_limit 11095 Is there another variable that needs to be adjusted to bump this up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
MySQL Magazine "Call for articles"
Hey everyone, I am starting on putting together the Winter issue of the MySQL Magazine. This will be our third issue and it is getting better each time! Here is your opportunity to contribute to the community. I am accepting ideas/outlines/proposals for articles for this issue through November the 9th. That gives you almost two weeks to get back to me about that great idea you have in your head. Articles will be due to be completed by December the 1st. If you aren't sure about an idea send me an email. Hope to hear from you soon! Keith Murphy Editor-Mysql Magazine www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
I dashed off this email too fast. What I meant to say was "I am migrating our data partitions for MySQL from reiserfs to xfs because xfs has faster write rates than reiserfs. Craig Huffstetler wrote: I agree, Fedora is not very good at production work. Reiserfs is one of the best file systems I have come across for MySQL Production. On 10/28/07, *B. Keith Murphy * <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: > Hi, > > i want to ask for your opinions about the best combination for mysql > with filesystem (like ext2, ext3... and so on). > > We have problems with mysql under fedora 6 with ext3 and i want to > know under wich filesystem mysql works best. > > I will be glad to read your opinions. > > Thanks in advanced. > Regards, Ali Nebi! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb rollback question
I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with master/slave replication
Ryan, show slave status after it stops copying should give you some type of error information about the failure. What does it show? Keith Ryan Klein wrote: I am having an issue that I cannot determine the cause. We have a master server that is actually a production server and a slave server that is a fall back but after around 10 days, it stops coping data and the servers fall out of sync. Here is my my.cnf file for the master server: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 #max_connections= 100 #table_cache= 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. server-id = 1 log_bin = /var/log/mysql/mysql-bin # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian! #expire_logs_days = 10 max_binlog_size = 1000M binlog_do_db= mydns sync_binlog = 1 #binlog_ignore_db = include_database_name # and for the slave server: # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 #max_connections= 100 #table_cache= 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. server-id = 2 log_bin = /var/log/mysql/mysql-bin.log # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian! #expire_logs_days = 10 max_binlog_size = 1000M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name #master-host= 74.202.241.24 #master-user= slavedb #master-password= )vKe8m)?Eod> #master-connect-retry = 60 #replicate-do-db= mydns # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
Thanks everyone for the responses. Will put me on the right track here..something that was rolling through my head but I couldn't really define. I will be blogging about this later as I think it is fairly important, but often not understood by beginning/mid-level dbas. thank again, Keith William Newton wrote: Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication vs. mysql-table-sync
Michael Stearne wrote: Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael You need to use replication not mysql-table-sync for replication. mysql-table-sync is use to get it back in sync. keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql replication....
bruce wrote: hi... a quick question that i haven't found an answer to. i can use "replicate-do-db=foo" in a my.cnf file for replication, to replicate the master foo db on the slave. but this requires that i use/have a my.cnf set on the slave. is there a way to dynamically set this attribute/parameter within mysql on the fly. i thought it would be possible via "change master to" but didn't find the cmd when looking through the mysql information. basically, i'm going to have multiple databases, on multiple systems, that i'm going to be replicating to a single system. so, for each master server, i'd like to be able to set the databases that i'm going to replicate... thanks Can't do that currently in MySQL. It is called multi-master replication. You can do multi-slave replication which replicates from one master to multiple slaves, but not the other way around. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spfile in Mysql......
This has probably already been over-talked about :) but I will throw my two cents in. I would be very much opposed to a situation where changes made on-the-fly are stored permanently in the my.cnf file. If I decide to keep a change to a server setting I am very much capable of doing it myself. Besides, if it is permanent I need to make the changes to my subversion copies of my.cnf. Keith Sujatha S wrote: Yes! I think in oracle the dynamic changes are recorded in spfile.. so that it uses on instance startup.. rather making an permanent entry in pfile. Regards, On 11/28/07, Jay Pipes <[EMAIL PROTECTED]> wrote: Sujatha S wrote: Mysql should bring this as there new feature in there next release! Unlikely. Dynamic changes are, well, dynamic. Permanent stuff goes in the my.cnf. -jay Regards, Sujatha On Nov 27, 2007 11:44 AM, Shanmugam, Dhandapani < [EMAIL PROTECTED]> wrote: Hello, The dynamic changes made on mysql server instance gets vanished once the instance goes down...!! Is there any way for mysql to store the dynamic changes on my.cnf file automatically ..?(like Oracle) , so on next startup mysql automatically pickup the dynamic changes made from my.cnf file Unfortunately there is not. You should alter your my.cnf file to record the changes you make. -- MySQL General Mailing List For list archives: *http://lists.mysql.com/mysql*< http://lists.mysql.com/mysql> To unsubscribe: * http://lists.mysql.com/[EMAIL PROTECTED]< http://lists.mysql.com/[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: preferred way to backup a 20GB MySQL database
I would echo what Dan says. In addition, from the slave server, you
might look at running the new mysql-parallel-dump tool that Baron
Schwartz has developed. It essentially does a dump with a thread
running (by default) for each CPU core you have. A dual core box will
run two threads and dump roughly twice as fast as a normal mysqldump.
In addition, it compresses the output making it much more compact. He
has renamed the toolkit to Maatkit and it is available at
http://maatkit.sourceforge.net/.
Also, you might look into using an LVM snapshot to run the copy from.
That way it doesn't interfere with your operations as much. I do that
for some of our production slave servers myself.
Keith
Dan Buettner wrote:
I'd strongly recommend setting up replication, and then taking your backups
from the replica.
mysqlhotcopy works great, I used it for years myself, but it does require
"freezing" your database while the copy happens. And no matter how you do
it, copying 20 GB takes a little bit of time.
Dan
On Nov 27, 2007 4:35 PM, David Campbell <[EMAIL PROTECTED]> wrote:
Andras Kende wrote:
Hi,
What is the preferred way to backup a 20GB database daily,
without taking offline ?
MySQL 4.1 MyISAM - (will be updated to MySQL 5)
133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB
Mysqlhotcopy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Keith Murphy
editor: MySQL Magazine
http://www.mysqlzine.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Baron Schwartz wrote: What do you mean by "falls out of sync?" LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by "out of sync" :) On Nov 28, 2007 4:32 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL analysis tool - open source?
MySQL Proxy is good. It doesn't fit in some environments however. Another option would be the QPP (Query Processing Programs) that I and a co-worker have developed. They can be downloaded at http://www.paragon-cs.com/queryprogs. thanks Keith Michael Dykman wrote: Have you considered: http://forge.mysql.com/wiki/MySQL_Proxy On Dec 5, 2007 5:14 PM, Russell E Glaue <[EMAIL PROTECTED]> wrote: Does anyone know of an open source SQL analysis tool? I have query logging turned on in MySQL. And I would like to analyze all the logged SQL select queries to find out how the database is being used, and then optimize MySQL databases or the SQL views/statements accordingly. Perhaps it would generate a report of most used columns for searching and retrieving, across all logged SQL queries. We have a software package used to generate reports from data in MySQL, and the users are allowed to make ad hoc querying. So we do not (necessarily) have control over the SQL queries that are executed. And once in a great while, some unknowing user will execute a query (unoptimized of course) that takes longer than satisfactory to execute. So I want to analyze all the queries users have issued, and try and optimize the database tables accordingly, or adjust the views in the reporting software. Any tools (open source only) out there to help? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best practice MySQl backup onto tape
Jenny Chen wrote: Hi, I'm planing to do hot backup MySQL(innodb db) onto tape drive, and propose the following solution/script: # delete old mysql dumps rm -r -r /backup/mysql mkdir /backup/mysql # Dump all mysql databases mysqldump --all-databases -single-transaction --flush-logs > all_databases.sql mysqldump --database=mysql --lock-all-tables --flush-logs > system.sql # Do backup tar cvf /dev/rmt/0 /backup/mysql My question is: is there any other better solutions(including commercial solutions) to do hot MySQL backup to tape that can provide better performance, or can backup directly to the tape(no need to dump to disk first), etc. comparing to use mysqldump. Thanks in advance for your information, or comment on the above solution. Regards, Jenny You didn't specify if this was a master or slave. I certainly wouldn't dump off a master server. It will lock the database for the duration of the backup. One of the ways we do backups is to do an "lvmsnapshot" of the data partition on the slave and then just mount and rsync the snaphsot of the data off to the backup server. Inefficient for space, but would be vastly faster restoring a backup like this then running a restore from a mysldump. You will still need to tar this take it acceptable for tape. Another option would be to replace mysqldump with Baron Schwartz's mk-parallel-dump and mk-parallel-restore tools. They are faster than a traditional dump/restore and are much more compact. These tools (and a number of others) are located at maatkit.sourceforge.net.net. Hope that helps. Keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automation question
Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with "expect" and have touched "dsh"). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automation question
Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 3 Jan 2008 13:06:07 -0500 > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: automation question > > Everyone, > > So, I have been thinking recently about automation. This morning I > listened to a talk by one of the three dbas at Youtube (from the MySQL > Users Conference last year). Think about that. They mentioned > 100,000,000 pageviews in one day (the data was from 2006). And THREE > dbas. Of course this is done by automating anything you can. This > isn't the first time I have heard of such ratios of servers/dbas. > > While I don't have the servers or the traffic that they do at Youtube we > are growing quite nicely and adding db servers on a fairly regular > basis. It is time to put some serious thought into automating some > things. I was wondering what experience others have had with any > technologies (I am modestly familier with "expect" and have touched > "dsh"). > > Thoughts?? > > thanks, > > keith > > > -- > B. Keith Murphy > > Paragon Consulting Services > http://www.paragon-cs.com > 850-637-3877 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! <http://www.xbox.com/en-US/hardware/wheretobuy/> -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automation question
Martin, I took a quick look at the tools that you mentioned. Seems you are heavily involved in Java :) We don't use Java at all, and other than using cron (which I do use for backups and standard stuff like that) not really what I am looking for. After thinking about this, I suspect that this will all just be a custom scripting with something like dsh for the distributed part. Not really looking for source code control -- more like things to manage the distribution of standardized my.cnf files (which we already control using subversion) to 100 servers at a time. Looking to scale efforts so I don't have to manually do things. thanks, Keith B. Keith Murphy wrote: Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 3 Jan 2008 13:06:07 -0500 > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: automation question > > Everyone, > > So, I have been thinking recently about automation. This morning I > listened to a talk by one of the three dbas at Youtube (from the MySQL > Users Conference last year). Think about that. They mentioned > 100,000,000 pageviews in one day (the data was from 2006). And THREE > dbas. Of course this is done by automating anything you can. This > isn't the first time I have heard of such ratios of servers/dbas. > > While I don't have the servers or the traffic that they do at Youtube we > are growing quite nicely and adding db servers on a fairly regular > basis. It is time to put some serious thought into automating some > things. I was wondering what experience others have had with any > technologies (I am modestly familier with "expect" and have touched > "dsh"). > > Thoughts?? > > thanks, > > keith > > > -- > B. Keith Murphy > > Paragon Consulting Services > http://www.paragon-cs.com > 850-637-3877 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > ---- The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! <http://www.xbox.com/en-US/hardware/wheretobuy/> -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Red Hat EL and Datbase Setup
Jason, You really are going to need to test this for yourself as it will somewhat depend on your application. Raid 5, 10 or the mentioned 1+0 might work for you best. keith [EMAIL PROTECTED] wrote: Try reading on RAID1+0, though it's a bit expensive in implementation but its great on READ & WRITE.. Basing on the current stable version there is no built in table partitioning.. you can do it in an application level.. -Original Message- From: Jason Vinar [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 10:17 AM To: mysql@lists.mysql.com Subject: Red Hat EL and Datbase Setup Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine - Winter Issue released
I just posted the latest edition of MySQL Magazine. It is available for free download from http://www.mysqlzine.net. Take a look! Keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine Spring Issue -- Call for Articles
Just four days ago I released the winter issue of MySQL Magazine (http://www.mysqlzine.net). However, I wanted to go ahead and get the jump on the spring issue as this quarter will be quite busy. Many of us are either speaking at the MySQL User's Conference or attending it in April. The spring issue of the magazine will be released on the first day of the User's Conference so I wanted to make sure that everyone had time to submit an article if they had interest. Any topic relating to MySQL development or administration is acceptable. In particular, I would love to have some submissions on php development with MySQL. Hope to hear from you soon! keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: turn a slave back into its own master
I believe you would have been ok with stop slave and reset master. Saravanan wrote: i am ok with this mysql> change master to master_host=''; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G Empty set (0.01 sec) Saravanan --- On Sat, 1/19/08, Paul Berry <[EMAIL PROTECTED]> wrote: From: Paul Berry <[EMAIL PROTECTED]> Subject: Re: turn a slave back into its own master To: [EMAIL PROTECTED] Cc: "MySql" Date: Saturday, January 19, 2008, 6:03 AM thanks - i think i'm ok because i get these results mysql> show slave status; Empty set (0.00 sec) On 1/18/08, Saravanan <[EMAIL PROTECTED]> wrote: Hi, will removing the master.info help instead of executing change master? or stop slave; reset master; --- On Sat, 1/19/08, Paul Berry <[EMAIL PROTECTED]> wrote: From: Paul Berry <[EMAIL PROTECTED]> Subject: Re: turn a slave back into its own master To: "MySql" Date: Saturday, January 19, 2008, 5:31 AM i ended up doing this - it seems to have worked fine STOP SLAVE; SHOW SLAVE STATUS; CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=0, MASTER_USER='', MASTER_PASSWORD=''; RESET MASTER; On 1/18/08, Paul Berry <[EMAIL PROTECTED]> wrote: hi guys, quick question how do i undo a CHANGE MASTER TO command and make it a master of itself again? thanks Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql replication
Naufal, You probably need to start with a clean slate. Do a complete resync and start the slave back up from a known stopping point. Keith Naufal Sheikh wrote: Hello, Just a small question. I had mysql replication configured on my master and slave server. Due to some issues ( when I was on vacations ), logging on master server was switched off and server restarted. Now my question is that in order to resysnc my master and slave correctly, do I need to copy the master database again to slave and then turn on logging and start slave again, or by just enabling the logging again, slave will resync from last known state. I believe that if the logging on master was switched off, it will have no record of the changes made to database and I will need top recopy the database, but just to confirm or if there is any simpler way. Thanks -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: suggestion for disk configuration
Luca, In my testing I have had better throughput with RAID 10 (at least with XFS). I have not worried about the log files so much, so you might want to investigate that some. I would love to see benchmarks of your SAS array though. If you have a blog you should publish them if at all possible. I used iozone for raw throughput testing and then the querybench program (http://www.paragon-cs.com/queryprogs) to perform testing with production data against the server. Hope that helps. Keith Luca Ferrari wrote: Hi, I have to install a mysql server for managing several database, from small to quite big (tables with about 2M rows). I've got a SAS disk array and I was wondering what the best configuration could be: 1) raid 10 2) raid 5 3) a combination (e.g., raid10 for the data and raid 5 for the logs). Any suggestion or link? Thanks, Luca -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certified MySQL Associate (CMA) certification value
So I can relate a little personal knowledge about this. In July of last year I had someone approach me and ask how they could get a job as a DBA. After talking for a bit, it turned out that he had no database experience. He actually use to be a lawyer and decided to make a change. I told him to set up a database on a Linux computer and start playing around with it. The next month he said that he had done so. What next? Well, MySQL AB had just released the CMA exam. As Baron mentioned, it makes a great baseline to show your knowledge when you approach companies. I told him to start studying for the test using the MySQL Certification book (a subset of it covers the exam material). We met twice over the next few months to study a bit and then in December he took the test and passed it. Monday he actually started as our part time junior DBA. Is he making a lot of money? Nope. But, it is a good chance for him to get experience in a rapidly growing production environment and in another nine months or a year he *will *be able to get a decent job. So can you do it? Sure. Do you need a college degree? Not really. He certainly had one, but it had nothing to do with databases. What does it take? Desire, work and reasonable intelligence. That's all. I doubt you will make much at your first job, but learn from the experience and do everything you can to learn more. Once you get done with the CMA start working on the CMDBA exams. Unlike some other cert programs these aren't fluff. I am not sure of the statistics listed on MySQL's website, but there just aren't that many certified DBAs. And you won't find many jobs listed as "jr dba". Don't sweat that. Approach companies that already have dbas and tell them you want a chance to learn. Show them through the CMA that you are serious. Working for peanuts. If it were me, and I was just starting out, I would pick what company I wanted to work at and if they wouldn't hire me for eight or ten dollars an hour I would offer to work for free. Just remember it is only temporary. Good luck. Keith Thufir wrote: On Wed, Feb 20, 2008 at 1:51 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote: [...] In my opinion there is a tremendous shortage of qualified DBAs for MySQL. The certification may establish that you have at least a baseline of knowledge. If you're looking for a job, you could hardly go wrong with it, IMO. [...] Yeah, I think that I'm going for the certification, although I can't imagine what kind of job I could land -- junior DB admin? I don't have a college degree :( -Thufir -- B. Keith Murphy, CMA Paragon Consulting Services http://www.paragon-cs.com http://blog.paragon-cs.com (850) 637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL for Datamining
Sorry I can't recommend anything offhand, but this type of message does not need to go to the internals mailing list. Thanks, Keith Haitham Kaddoura wrote: Hi, does anyone use the or know a data mining package in MySQL? -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure locks all statemens
When it selects values from the myisam table it is locking the entire table. Saravanan wrote: Hi Lists, i have created a procedure. It selects values from a myisam table and updates an innodb table one by one ( not as huge update it clearly use where clause). but whenever I run the procedure other statements waits for longtime until my procedure completes updates. any idea why it blocks. i tried start transaction and commint for every single update. but the result is same Saravanan Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spring 2008 MySQL Magazine released!!
Hey everyone, I just posted the Spring issue of the MySQL Magazine. This issue contains a great group of articles that you will want to read. In addition, Sheeri Cabral did a fabulous job with a new layout for the magazine. It is as always FREE!!. Available for download from http://www.mysqlzine.net or http://www.paragon-cs.com/mag. thanks and enjoy!! Keith Murphy -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spring 2008 MySQL Magazine released!!
Daniel, Thanks for pointing that out. I haven't heard of this before so I really don't have a clue. We use Openoffice 2.4 to create the magazine and generate the pdf. It works fine in both OO and adobe acrobat (I am "blessed" with a Windows laptop). Keith Daniel Brown wrote: On Tue, Apr 15, 2008 at 2:20 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: Hey everyone, I just posted the Spring issue of the MySQL Magazine. This issue contains a great group of articles that you will want to read. In addition, Sheeri Cabral did a fabulous job with a new layout for the magazine. It is as always FREE!!. Available for download from http://www.mysqlzine.net or http://www.paragon-cs.com/mag. thanks and enjoy!! Keith Murphy Looks good at a glance, Keith! Congrats! I'll print it and check it out when I get home tonight, but I wanted to respond to the thread to let anyone else who may be using KGhostView 0.20, like myself, that all pages appear blank. This is not a problem with the magazine, but appears to be a bug in KGhostView itself, so you will probably have to view the PDF with a different version or with an official Acrobat release. I'm going to submit a bug report with the project, but just wanted to put out a heads-up here on the list in case anyone else runs into the same problem and gives up before checking into it further. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How hard is it to move from on server to another?
A simple rsync should do the trick. How long will depend on how much data you have. I would just shut down the server, copy over the data directory and start the new server up. Should be a piece of cake. Keith David Ruggles wrote: I have a MySQL 5.x box and I am thinking about moving it to another more powerful server. I would be able to schedule some downtime so that's not an issue. How complicated a process would this be? I don't want to upgrade the software or anything, just move the existing tables, users and permissions to another physical server. It would even have the same IP address. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? I have made this migration on multiple servers. It has never been any trouble. Your biggest gain would probably be the ability to address more RAM. I would just dump the database from the 32-bit platform and import it into the 64-bit server. Keith -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, "Mike" <[EMAIL PROTECTED]> wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein <[EMAIL PROTECTED]> wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Call for Articles for MySQL Magazine - Summer Issue
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. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine Summer Issue Released
The next issue of MySQL Magazine is now available for download. Get it while it is hot! At forty-two great pages it is our biggest and best issue yet. This issue is anchored by the first annual MySQL Usage Survey results. Downloads at the MySQL Magazine homepage: http://www.mysqlzine.net Thanks to everyone who contributed. I couldn’t have done it without you all!! Keith Murphy -- Editor MySQL Magazine [EMAIL PROTECTED] http://www.mysqlzine.net (850) 637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL e-zine
Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
Kevin Spencer wrote: On 4/26/07, Mike OK <[EMAIL PROTECTED]> wrote: I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Still, that leaves many other applications. Groups, gmail, reader, news et al... -- Kevin. My understanding from some posting is that they do specifically use it for gmail which makes a lot of sense. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Summer issue - MySQL e-zine
Everyone, I have received quite a bit of positive feedback on the idea of a MySQL e-zine and I am moving forward. I would like to have the first issue out by the 1st of June. I have decided to have a theme of the first issue on security? Why? Well, to be honest, it will any easier topic to start with. And second, unfortunately, I think security is a neglected aspect of many MySQL installations. Here is where the community comes in. I am looking for some articles. Article length will be around 550 words for a single page. An article can be up to 3 pages long. If you need to include graphics that will need to lower the word count of course. Please submit plain text and preferably png graphics although I can manage pretty much any graphic type if necessary. It would be best if you submitted an abstract of sorts - just a paragraph or two about what you want to write about. That way we can see if it will work before you write out the full length article. Some ideas for article topics: User account management for security Server hardening (OS and mysql server) SQL Injection attacks and coding to prevent them. Other non-security article ideas will certainly be considered as every article will not be about security. If a an article idea is of good quality and it won't fit in the first issue than it certainly be used for the fall issue. Unfortunately there is no way I can pay for articles right now. Down the road I would like to get some advertisers /sponsors and use that to pay for articles. Just remember that this a way that you can give back to the community. Thanks, Keith B. Keith Murphy wrote: Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Summer issue - MySQL e-zine
Everyone, I thought of a couple of other important things right after I sent out the previous message. Typical :) I am going to need final copy of any articles in by the 21st of May in order to have time to finish putting the issue together for release on June 1. So, if you are going to submit I would like an abstract by the 7th . Also, if anyone out there has graphic talent I would be most appreciative of some help with the cover. The magazine will need some type of logo and beyond that - just the general layout. If you have quarkxpress I can email you the cover as is. Otherwise, if you create a logo in some type of graphics format (png would be appreciated though) I can bring it into my layout work. If you have interest let me know and I will email you a pdf of the cover. thanks, Keith B. Keith Murphy wrote: Everyone, I have received quite a bit of positive feedback on the idea of a MySQL e-zine and I am moving forward. I would like to have the first issue out by the 1st of June. I have decided to have a theme of the first issue on security? Why? Well, to be honest, it will any easier topic to start with. And second, unfortunately, I think security is a neglected aspect of many MySQL installations. Here is where the community comes in. I am looking for some articles. Article length will be around 550 words for a single page. An article can be up to 3 pages long. If you need to include graphics that will need to lower the word count of course. Please submit plain text and preferably png graphics although I can manage pretty much any graphic type if necessary. It would be best if you submitted an abstract of sorts - just a paragraph or two about what you want to write about. That way we can see if it will work before you write out the full length article. Some ideas for article topics: User account management for security Server hardening (OS and mysql server) SQL Injection attacks and coding to prevent them. Other non-security article ideas will certainly be considered as every article will not be about security. If a an article idea is of good quality and it won't fit in the first issue than it certainly be used for the fall issue. Unfortunately there is no way I can pay for articles right now. Down the road I would like to get some advertisers /sponsors and use that to pay for articles. Just remember that this a way that you can give back to the community. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing Data Format
Baron Schwartz wrote: Hi, Tim Milstead wrote: Hello, Is it possible to enforce data formatting in fields using something like a regular expression? varchar is great but does not stop someone putting in the wrong reference number. I suspect the answer is no, you have to do it at a higher level. What aprroach would people suggest? Perhaps another table with two fields - field_name and field_regex? If you are using 5.x or greater, you can do some kinds of data validation with a trigger. Baron I would argue that your data-validation should really not be done with triggers - I would look more at the php/asp/whatever code to do this. Shouldn't this be more efficient than using triggers? At the very least doing validation on the web server will make it easier to add capacity with multiple web servers. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted Root Account
I have had exactly the kind of trouble you are talking about. Sorry I didn't see the messages until just now. Had to manually remove crap too even though I purged (rpms - centos I think). Hopefully this will help others down the road. Keith John Kebbel wrote: Even though I completely removed everything I could find related to MySQL in Synaptic Package Manager, a folder still remained at /var/lib/mysql/ containing all my old table information. If that info hung around, it seemes logical to assume the Grants table were still somewhere interfering with my attempts to login to mysql as root. However, when I followed instructions at http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html , I was able to kill mysqld and restart it with the skipping the grants table option. I was then able to to login as the the weird Debian/Ubuntu equivalent of root (a user using sudo) and access the mysql database. >From there I was able to reinvigorate root. The thing that surprised me was that all my data survived the reinstallation process. I thought the databases and tables I had created would go up in smoke when I removed mysql_server. They're all intact. Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit usage of mysql server's memory/cpu for each databases ?
Halid Faith wrote: I use mysql4.1.22, php4.4.6 and apache2.0.59. I want to put an quota for each databases and mysql users. How can I do that mysql will limit each database's memory usage? Because some mysql users sometimes use much memory while doing a sql query. Also How can I put an quota for each databases as data size ( like 1 Mbyte ) ? Halid, I don't think there is any way to do this from within MySQL. If you were ambitious you could implement the datasize quota with some type of script. Hope that helps. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: time taken by mysqldump
Have you considered replicating to a backup server and then dumping from it? No matter your processors with a 100gb db it is going to take a significant amount of time. Keith Ananda Kumar wrote: Hi All, We have database of around 100GB, and planning to take dump using mysqldump. Can you please let how much time it would take for 100GB. We have 8GB RAM and 4 intel latest processor. The mysql db is running on linux. regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.467 / Virus Database: 269.7.7/816 - Release Date: 5/23/2007 3:59 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
Sometimes partitioning is absolutely necessary. If you can't run a cluster - how else can you really scale writes to the database? Some companies can't use clustering because in 5.0.x (the "non-beta" release) clustering is all done in memory - all tables have to be in memory (just like the old heap tables). It isn't until 5.1.x that clustering allows your data to be stored on disc. Many companies still consider 5.1 to not be production ready. You might disagree but that is their thinking. So, if you don't use clustering, how else are you going to scale an application? I suppose you can set up master-master replication - but that doesn't really scale to a large extent. Some companies have huge applications with hundreds of gigabytes or even terabytes of data. I think if you read carefully through the presentations from the recent MySQL conference by companies such as Digg and Flickr you will find that they do partitioning as well as caching and such. I recall specifically reading through a presentation by livejournal about how they split up their load across multiple machines by the very partitioning we are talking about. I might be missing something. I can understand why you wouldn't want to work on such a system as it certainly adds complexity to the entire database. But that doesn't mean that it isn't something that isn't necessary sometimes. Just my two cents :) Keith Naz Gassiep wrote: Data partitioning? Sorry, I disagree that partitioning a table into more and more servers is the way to scale properly. Perhaps putting databases' tables onto different servers with different hardware designed to meat different usage patterns is a good idea, but data partitioning was a very short lived idea in the world of databases and I'm glad that as an idea it is dying in practice. - Naz Evaldas Imbrasas wrote: Since the question was about *really* big websites, the answer is both yes and no. Yes, they do turn off RI on the database side, simply because it's not possible to enforce RI on a database system where data is partitioned across server farms (or shards) both vertically and horizontally. And really big websites can't survive without the data partioning. No, they don't usually turn off RI just to improve performance, because the gains would be minimal, and for big websites, scalability is a much bigger issue that performance (although sometimes one depends on the other), and data partitioning is the way to go to solve the scalability problem. On 5/24/07, Naz Gassiep <[EMAIL PROTECTED]> wrote: I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one: "*Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level." A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
OK. Going to try this again. After reading through these emails I think I have learned a little more about the way you are thinking. I DO NOT want to start some kind of flame war. However, I disagree very strongly with what you are saying. Yes, you are right, sharding does require more complexity from the application layer. Sorry for all you developers out there (and I can safely say that I am NOT a developer!!). The fundamental issue for you, as I see it, is the increased complexity caused by sharding the application. That being said, I will say this...if you develop on some other RDBMS such as MS or Oracle is it possible to deleveop something like you are saying...an all-inclusive database that isn't "sharded"? Yep, when I worked at Netzero in 2001 for example we had two database servers running Oracle, one on the east coast in Virginia and one one the west coast in California. The east coast server was a backup of the west coast server. So one database server did the billing for all of Netzero's customers. Millions of customers..absolutely. All in one nice tidy box that I am sure was easier to develop the billing applications around. Here is the kicker. Each box was a top of the line Sun server that had 32 processors and 32 gigs of RAM. They could handle up to 64 procs and 64 gigs. And each cost well over a million dollars for the hardware alone. Running Oracle on it must have cost over 100,000 dollars for software licenses. Granted this was in 2001, but the licensing cost for Oracle haven't gone down any that I am aware of...and the hardware cost will still be quite steep to do this type of thing. So I ask you this.. Would it be better to go with that scenario or something like this: Implement the billing application using MySQL. Shard it. Create complexity. Your hardware cost saving alone will pay for multiple developers to handle any complexity increases. Any decent DBA is going to be able to handle multiple servers required to operate this setup. You will probably see a decrease in salary cost moving from Oracle to MySQL dbas. So for the bottom line of the company it is a overall win by far. It is only the inherent difficulty in moving complex systems from one type of DB to another that keep more companies from switching. Why hasn't this happend previously?? Because until version 4 of MySQL was stable there were many features not available in MySQL that were needed by these types of systems. It is my contention that as the clustering capabilities of MySQL continue to grow and mature (think of when version 6.0 goes stable) companies will move to MySQL in droves. THEN you have the ability to build a single "virtual" database (at least from the point of view of your application) that will scale simply and elegantly. As I said in the previous email it is only that 5.1 is in beta that keeps this from being available now. And many companies, such as Kaneva, are doing this right now. The only reason that companies like Digg and Flikr can exist and grow at such phenomenal rates is that they keep the cost of the development of the system to a minimum and the overhead of operating (licensing costs and hardware cost) down as low as possible. In addition, of course, they need the ability to scale out very quickly. Digg didn't get any significant funding until just recently. And yet they epitomize the web 2.0 companies. They did it by both keeping their cost down and having the ability to grow quickly. Couldn't have done it with Oracle or MS. Just my thoughts :) Keith Naz Gassiep wrote: Wow. The problem with sharding I have is the large amount of code required in the app to make it work. IMHO the app should be agnostic to the underlying database system (by that I don't mean the DB in use such as MySQL or whatever or the schema, I mean the way the DB has been deployed) so that changes can be made to it without having to worry about impacting app code. This is one of my fundamental design imperatives. Then again, I'm not a regular MySQL user so I don't know what is and is not the norm in the MySQL world. - Naz. Evaldas Imbrasas wrote: You certainly have a right to disagree, but pretty much every scalability talk at the MySQL conference a few weeks ago was focused on data partitioning and sharding. And those talks very given by folks working for some of the most popular (top 100) websites in the world. It certainly looks like data partitioning is the way to go in the MySQL world at this point, probably at least until production-ready and feature-full MySQL Cluster is out. And even then large percentage of dotcom companies would use data partitioning instead since it can be implemented on commodity hardware. Once again, we're talking *really* big websites using MySQL (not Oracle or SQL Server or whatever) here. Most websites won't ever need to partition their production databases, and different
SAN backend for DB's
So here is the brief situation. We have a coraid (www.coraid.com) SAN unit - the 1520 I believe. It is ATA-over-ethernet. Right now we have a about 500 gigs of data spread across five servers. To simplify things I would like to implement the coraid on the backend of these servers. Then all the data is served up out of the same place. Of course I would like to improve I/O throughput also. Googling shows that these units have good read speed but the write speed doesn't seem to be that impressive. Does anyone have any experience with these? Good? Bad? Maybe other SAN suggestions? Am I barking up the wrong tree? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine - Issue 1 available NOW!!!!
Everyone, I have just uploaded the first issue MySQL Magazine to http://paragon-cs.com/mag/issue1.pdf Please take a look at it. There is a great deal of information here and I think it is worth some time. Feedback is always welcome. thanks, Keith Murphy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: acquire mysql knowledge in short time
I would agree with the other person who posted about the MySQL 5.0 cert guide. It is quite thorough. I would also recommend "Pro MySQL" which is more in-depth on some things like replication. If you are going to be going into clusters..there is only book that specifically covers it. "MySQL Clustering" is published by MySQL. It is rather dated (not covering 5.1 features) but it is pretty in-depth. Keith - Original Message - From: "Peter Teunissen" <[EMAIL PROTECTED]> To: "MySQL" Sent: Tuesday, June 26, 2007 4:03:10 PM (GMT-0500) America/New_York Subject: acquire mysql knowledge in short time Hi All, For an opensource project at work, I need to get to know mysql in a few days. I need some advise on what to read and what tutorials to work through. We are going to build a data warehouse using mysql 5.0. We will try to build the entire ETL stream using sql scripts and stored procedures. It will be up to me to translate the data analysis and initial datamodel into a working database and ETL stream. I have a high working knowledge of sql on Oracle, DB2 and MS SQL Server 2k5. My knowledge of stored procedures is limited to an Oracle PL/SQL training some years ago with limited pracical experience. I have a basic knowledge of programming (Java, Python and VB) Within a few days, I will have about 4 full days to invest into getting to know MySQL 5.0. With the above objective, experience and most important, time limit in mind, what documentation (and important sections therof) and tutorials would you advise me to use? What parts of MySQL functionality should I focus on and what should I skip over for the moment? Thanks for your advise, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: toutrial needed
Weiqi, You might want to take a look at the book "MySQL Tutorial". It is in a second edition and available from Amazon. In addition there are numerous guides available on the Internet. Devshed (http://www.devshed.com/c/b/MySQL/) for example has 90 articles on mysql. Of course many of them are high-level but it is a useful resource. http://www.tizag.com/mysqlTutorial/ is another. google "mysql tutorial" I would really recommend that you do start with the book that I mentioned.."MySQL Tutorial". And Hal...really..the comment about the spelling was unnecessary. Not to mention that you provided no help what so ever. Hope that helps Weiqi. Keith - Original Message - From: "Hal Wigoda" <[EMAIL PROTECTED]> To: "Weiqi Wang" <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, June 27, 2007 11:45:37 AM (GMT-0500) America/New_York Subject: Re: toutrial needed first you need to learn to spell tutorial. On Jun 27, 2007, at 10:37 AM, Weiqi Wang wrote: > > Dear sir or madam: > > I'm totally new to mySQL and I have little pre-knowledge on > database, could anyone suggest a toutrial on mySQL which is > suitable in my case, please? I really appreciate any answer, very > much! > > Best Regards, > > > yours sincerely, > > Weiqi > > == > Weiqi Wang > Hertford College > Dep. of Engineering Science > Oxford -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
user backup tool
I know there is a tool out there to grab the user info from the mysql database and store it like versioning control. I thought it was in Baron's toolkit but unless I have had too much caffeine and can't read I don't see it on his website. What am I thinking of? Thanks, keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: user backup tool
I think it was Baron's toolkit I was thinking of... the command mysql-show-grants seems to do the trick. Thanks, Keith - Original Message - From: "B. Keith Murphy" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, June 27, 2007 4:11:54 PM (GMT-0500) America/New_York Subject: user backup tool I know there is a tool out there to grab the user info from the mysql database and store it like versioning control. I thought it was in Baron's toolkit but unless I have had too much caffeine and can't read I don't see it on his website. What am I thinking of? Thanks, keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: off-topic unsubscribe concern
I have been having the same problems. Have no idea what is up. - Original Message - From: "Michael Dykman" <[EMAIL PROTECTED]> To: "MySQL General" Sent: Friday, July 6, 2007 11:55:41 AM (GMT-0500) America/New_York Subject: off-topic unsubscribe concern Hi everyone. I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages.. They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
mysqldump/restore
hey all. Let me throw out a quick question. We are upgrading from some version of 4.1 to the latest version of 5.0. MySQL recommends that you dump and re-import data with this type of move. Not only do they recommend the dump/import - it would be nice to optimize all our tables at the same time!! I have done a test dump of a couple of gigs of data with just a straight mysqldump. That worked, but the import is happening slower than Christmas. I know that one technique for speeding up the imports is to where you configure the dump so that it generates file(s) with the schema for the table(s) and file(s) with actual data. Then you can use the load data infile command to import the data after generating the new tables (maybe by using 'mysql database.sql' to get a complete schema for all the tables in the database and then just 'mysql < database.sql' to set up all the tables at the same time. You can't do that with the import of the actual data. You have to individually load the data into each table. something like 'load data infile /root/table1.txt'. This is fine for one..but what happens when you have 100 tables? I can't take the time to sit there and hit the up-arrow to retrieve the command and then type in the new file name. Is there some way to automate this data import? Thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Next MySQL Magazine --call for articles
Hey everyone, I am starting to prepare for the second issue of MySQL Magazine. I am planning on putting it out on September 1. Thanks for the fabulous response to the first issue!! There was over a 1,000 downloads of the magazine. That is just fabulous!!! Special thanks to those who contributed (in no particular order): Peter Brawley, Dan Buettner and Baron Schwartz. It looks like there is going to be some publicity on a couple of podcasts over the next fews so there will probably be a decent upswing in subscribers. I am not going to concentrate on any particular theme this time. If you want to send me some ideas for articles you can either reply to this email address or bmurphy at paragon-cs.com. Thanks again, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
speeding imports
Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to 5.0.45. That's not the problem :). The problem is that MySQL recommends dumping and importing your data when doing this upgrade to make sure that things go as smoothly as possible. We have quite a bit of data spread over multiple servers. We have slaves off each master server and the plan is upgrade the slaves and then make them the masters. The problem is that I am realizing that this dump/import is going to take hours and in some cases days. I am looking for any way to speed this up. Any suggestions? Thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: performance of extended insert vs. load data
I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: "Sid Lane" < [EMAIL PROTECTED] > To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data & repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a "load data infile" against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables & can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: innodb to be removed? and...
Even if it is removed it would only be removed from future versions .. say 6.0. Even version 5.1 has been "feature-frozen", if I am not mistaken, meaning that features will not be added or removed. But, as others have said, Falcon is what I think MySQL has in mind to replace Innodb This transition, however, will take years just like the transition from MyIsam to Innodb. DBA's tend to be very set in our ways so transitions are slow! As long as Oracle doesn't do something stupid (which they might) Innodb will probably still be available as an option in the GA release 10 years from now so I wouldn't sweat it. Keith - Original Message - From: "Christian Parpart" <[EMAIL PROTECTED]> To: "MySQL General" Sent: Tuesday, July 24, 2007 5:48:38 AM (GMT-0500) America/New_York Subject: innodb to be removed? and... Hi all, recently someone said to know alot about mysql told us that InnoDB is about to be removed from the mySQL server. however, InnoDB seems to be the fastest storage engine in our case, as myisam take a hell longer to insert new rows e.g. so is it true, that innodb is to be removed? (coudn't find any trace in the net yet) and what about potential future storage engines in mysql, or will there be myisam as the one and only feature blown one? Thanks in advance, Christian Parpart. -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
What operating system are you running and is it 32 or 64 bit? Keith - Original Message - From: "Jen mlists" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 2:35:34 AM (GMT-0500) America/New_York Subject: run out of memory Hello, My server box has 8G memory and 8CPU (DELL Standard Server),when I configure Mysql server (5.0.45) using large memory,mysqld would say it run out of memory. For example,when this line appear in my.cnf, key_buffer = 4000M mysqld can't startup.When I change it to, key_buffer = 2560M mysqld startup successfully. Why this mysqld can't support large memory usage?How to improve it? Thanks! --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: Enterprise Wide Deployment
Really probably not the best list for this. But if it were up to me..I would use CentOS/RedHat Enterprise Linux or Debian. Keith - Original Message - From: "john_sm" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 3:20:36 AM (GMT-0500) America/New_York Subject: Enterprise Wide Deployment Hey guys, for an enterprise wide deployment, what will you suggest and why among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we can negotiate the support pricing down? -- View this message in context: http://www.nabble.com/Enterprise-Wide-Deployment-tf4265688.html#a12139896 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
So I stand corrected :) Sorry for the mis-information. Thanks Mark!! Keith - Original Message - From: "Mark Leith" <[EMAIL PROTECTED]> To: "Baron Schwartz" <[EMAIL PROTECTED]> Cc: "Michael Dykman" <[EMAIL PROTECTED]>, "Gu Lei(Tech)" <[EMAIL PROTECTED]>, "Jen mlists" <[EMAIL PROTECTED]>, mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:51:19 PM (GMT-0500) America/New_York Subject: Re: run out of memory Baron Schwartz wrote: > Mark Leith wrote: >> And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, >> rather than a full 4G. > > What are the practical memory limits for 64-bit binaries? I have > heard that MySQL's indexing code is only 32-bit safe anyway, and I > assume for example the MyISAM key buffers can still only be 4 GiB in a > 64-bit version. Is this true of all storage engines? Are there any > other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
I have had the same type of problems as this user when unknowing using 32-bit code. That was why I was asking about what distro he was using. As for your question Baron - I don't think that limit is true (anymore). I am fairly certain that it use to be, but has been corrected. If everyone is really curious I can dig around and even test it if need be. Keith - Original Message - From: "Baron Schwartz" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: "Michael Dykman" <[EMAIL PROTECTED]>, "Gu Lei(Tech)" <[EMAIL PROTECTED]>, "Jen mlists" <[EMAIL PROTECTED]>, mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:27:14 PM (GMT-0500) America/New_York Subject: Re: run out of memory Mark Leith wrote: > And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, > rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: make a slave db a master db
Ananda, The answer to your question is very simple actually, but there is some subtleties that you need to think of. To stop the slave from getting replication data from the master just issue a "stop slave" command. I would also recommend that you run a "reset master" command in addition. This will reset the master server information to empty (not pointing anywhere). The reason why it is subtle is that you need to know what state you want your data in. When you issue the stop slave command it breaks the replication with the master with your data on the old slave basically the same as the master. Is that what you really want? If not, you need to take other measures to get your data in the state you want before bringing it online as a standalone server. Hope that helps. Keith What data do you want on the new master? - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "MySql" Sent: Thursday, August 30, 2007 3:37:48 AM (GMT-0500) America/New_York Subject: make a slave db a master db Hi All, We have one master and two slave dbs. Now we want one of the slaves to work as a stand alone master db, so what are the steps that i need to take to make one of the slaves as a stand alone master db. regards anandkl -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
MySQL Magazine Issue Two released
Just wanted to let everyone know that the second issue of the MySQL magazine has been uploaded. It is available from http://www.paragon-cs.com/mag/mysqlmag.html Our featured articles include: MySQL Maintenance script -a multi-threaded tool for database maintenance MySQL Table Checksum How-To Optimizing MySQL with Rails - Part Two Index Optimization in MySQL Our regular features include news, a book review and the coding corner. Everyone enjoy!! If you want contribute to the next issue just get in contact with me via email. We are always looking for content about MySQL administration and application development with MySQL. In addition, I could use some help with graphics "stuff". Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
