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]
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]
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]
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]
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: 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]
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: 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: 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: 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: 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 mysql@lists.mysql.com 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 mysql@lists.mysql.com 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]
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]
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]
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]
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: 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]
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: 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: 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 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: 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]
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: 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]
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: 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
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: 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 mysql@lists.mysql.com 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
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
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]
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 mysql@lists.mysql.com 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
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: 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: 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 mysql@lists.mysql.com 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: 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
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
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
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 table1.sql'). So here is the problem. It worked, but it was slow. Not because of the speed of the import but because I am having to issue a command (and type in the root password) for each table import. I could do the following: 'cat *.sql 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
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 mysql@lists.mysql.com 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
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 mysql@lists.mysql.com 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 mysql@lists.mysql.com 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
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]
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]
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
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: 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: 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]
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]
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]
