Re: Access speed INNODB VS. MyISAM
On Jun 3, 2006, at 7:54 PM, Richard wrote: I have been using a database for several years that uses many 'lookup' style tables. i.e. no updates to these tables. I dumped the whole thing into an INNODB database for simplicity, but I now wonder if I can speed things up if I put only my updatable tables in INNODB (I need row level locking for these) and put all the lookup tables in MyISAM. The benchmarks I have looked at don't seem to reveal either as a clear winner, but I am probably not interpreting them properly. Probably the only real answer is to benchmark your particular application. That said, for any table that has high frequency writes while others are trying to read or write your only option is InnoDB. Similarly, if you want to do a live backup of a large table without shutting off write access you have to use InnoDB. If you are doing simple reads, even at very high volume you'll probably find MyISAM as fast or faster. You can mix and match them in general to get the right performance for each table. There are a few things that InnoDB won't handle (fulltext searches, some others) that will require MyISAM, but in general they are pretty much complete substitutes. InnoDB tables do take up more space than MyISAM in general, but other than that we've found no real downside to making everything InnoDB (other than the fact that the information sources are a little less common for InnoDB). From what you say, it sounds like all InnoDB or InnoDB for the tables with write and MyISAM for the others would work for you. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file per table
On Apr 26, 2006, at 3:54 AM, Dr. Frank Ullrich wrote: Duzenbury, Rich wrote: Hi all, I've inherited an innodb database that is configured like: innodb_file_per_table innodb_data_file_path = ibdata1:3000M;ibdata2:3000M;ibdata3:3000M;ibdata4:3000M:autoextend Um, doesn't this allocate 12G that winds up being unused, since innodb_file_per_table is set? If so, what is the correct way to reclaim the 12G? Thanks! Regards, Rich Duzenbury Hi, but you don't know __when__ innodb_file_per_table was set! So it's possible that many innodb tables actually reside in ibdata [1-4]. Check your data directory to see the individual innodb files/tables (*.ibd). This is true, and even on a fresh install that has always had innodb_file_per_table, InnoDB still needs the shared tablespace (though it probably doesn't need to be that large). Once you have an InnoDB tablespace the only way to reduce the size of the shared tablespace is to completely dump the data and recreate the tablespace. Roughly the sequence is: mysqldump to text...be very careful to keep a consistent snapshot, handle blobs, quoting names, etc Test this. Shut down mysql Rename/move old mysql data and log directories, create new, empty ones (copy over mysql database...it's not innodb and will keep the same users) Alter my.cnf, point to include new InnoDB shared table definition Start mysql, make sure InnoDB initializes correctly (check .err file) Read in dump file you took in step 1 Again, be careful with this. It essentially involves exporting and importing all your data, so make sure you have a valid export file. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
On Mar 21, 2006, at 1:08 PM, Marten Lehmann wrote: I had a lot of trouble today because the InnoDB integration in MySQL is lousy. I read the manual and worked with innodb_per_file_table. So when I shutdown mysql I should be able to delete ib_logfile0, ib_logfile1 and ibdata1, because all table-data should be stored in the .idb and .frm files. But this obviously is not the case! MySQL always reports errors that it can't find the table files, although they are in the directory of the database. And additionally: If I'm removing the files from a databases and try to create a table that existed before (but now doesn't exist because the files are removed), I can't create it any more. What is the function of ib_logfile and ibdata? I expected them to store transaction data only, but the seem to store more. But documentation is very sparse on that. This is spelled out pretty clearly in the manual: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. right from the section on using innodb_file_per_table: http://dev.mysql.com/doc/refman/4.1/en/multiple-tablespaces.html And as others have said, you always need the logfiles. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb questions
and move them to another server. InnoDB still needs the shared tablespace and logfiles in addition to the individual table's .ibd file. Moving just the ibd file is useless. But yes, in general innodb_file_per_table gives you some more flexibility, just not as much as with MyISAM. For what it's worth we've noticed no stability or speed issues with it and Heikki at one point said he could think of no downside to using it. Good luck, Ware Thanks, Grant Ware Adams [EMAIL PROTECTED] wrote: On Jan 31, 2006, at 9:54 PM, Grant Giddens wrote: Since changing these tables, I've noticed some large files in my / var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) These all look like binary log files, they aren't exclusive to InnoDB. You must have enabled binary logging in your my.cnf file as well. This is covered pretty extensively in the manual. Basically they are used for replication and recovery. In the latter you can take a point in time snapshot from a known binary log position and then if need be apply the logs (which is basically re-running the queries in them) to that to get back to where you databases was at any point in time. If you don't need any of these functions you can either get rid of the files or turn off binary logging. However, you probably don't want to just delete them from the file system. Have a look at the 'show master logs' and 'purge master logs' commands. This will delete the files and keep mysqld's internal index of the binary logs accurate. /var/lib/mysql/ibdata1(10.0 Gig in size) This is your InnoDB table space, you need it. You also need your ib_logfile0 etc... files. 3. Can I limit the size of the ibdata1 file? Only by limiting data in your tables. Also, in the shared table space (which you are using) you can't shrink this file. You can switch to innodb_file_per_table (see InnoDB manual) so that when you drop a table you save it's space (but not on deletion of individual records). However, even doing this you cannot delete your ibdata1 file or any shared tablespace files. You can recreate your entire tablespace (see below), but even then you'll need a (small) shared ibdata file. Is it too late to resize it? Yes, but you could use mysqldump to dump all data to text files, delete (or move) the tablespace, redefine it and then re-import. 4. What can I set to reduce the size of these files? Use innodb_file_per_table turn off binary loggin if you don't need it make sure index and field types are appropriate My innodb variables are: These came through poorly spaced, but I think it would help a lot to read the InnoDB sections of the manual (it's pretty manageable in size). InnoDB is really fantastic for certain applications, including heavy write load to large tables with concurrent reads. We've used it for several years on several billion records with 170 qps 50% of which is writes. There's no way to do this in MySQL other than InnoDB, realistically. That said, it has it's own learning curve. It's really an entirely new database engine, so there's lots to learn even if you've used mysql/myisam for years. In particular the tablespace layout and dependency on files other than ones linked to a particular table is a little daunting. --Ware - Bring words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
On Jan 31, 2006, at 11:01 AM, Adrian Bruce wrote: Thanks for the reply yep mysqldump also causes the service to crash. i have a few innodb tables mixed in amongst the myisam, it seems that it is the innodb tables that may be causing the problems but i am not sure why ??? If it's InnoDB crashing it's usually pretty good at logging some error. What does your .err file say? That's going to determine how much you can save if it's InnoDB. --Ware Imran Chaudhry wrote: On 1/31/06, Adrian Bruce [EMAIL PROTECTED] wrote: HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb questions
On Jan 31, 2006, at 9:54 PM, Grant Giddens wrote: Since changing these tables, I've noticed some large files in my / var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) These all look like binary log files, they aren't exclusive to InnoDB. You must have enabled binary logging in your my.cnf file as well. This is covered pretty extensively in the manual. Basically they are used for replication and recovery. In the latter you can take a point in time snapshot from a known binary log position and then if need be apply the logs (which is basically re-running the queries in them) to that to get back to where you databases was at any point in time. If you don't need any of these functions you can either get rid of the files or turn off binary logging. However, you probably don't want to just delete them from the file system. Have a look at the 'show master logs' and 'purge master logs' commands. This will delete the files and keep mysqld's internal index of the binary logs accurate. /var/lib/mysql/ibdata1(10.0 Gig in size) This is your InnoDB table space, you need it. You also need your ib_logfile0 etc... files. 3. Can I limit the size of the ibdata1 file? Only by limiting data in your tables. Also, in the shared table space (which you are using) you can't shrink this file. You can switch to innodb_file_per_table (see InnoDB manual) so that when you drop a table you save it's space (but not on deletion of individual records). However, even doing this you cannot delete your ibdata1 file or any shared tablespace files. You can recreate your entire tablespace (see below), but even then you'll need a (small) shared ibdata file. Is it too late to resize it? Yes, but you could use mysqldump to dump all data to text files, delete (or move) the tablespace, redefine it and then re-import. 4. What can I set to reduce the size of these files? Use innodb_file_per_table turn off binary loggin if you don't need it make sure index and field types are appropriate My innodb variables are: These came through poorly spaced, but I think it would help a lot to read the InnoDB sections of the manual (it's pretty manageable in size). InnoDB is really fantastic for certain applications, including heavy write load to large tables with concurrent reads. We've used it for several years on several billion records with 170 qps 50% of which is writes. There's no way to do this in MySQL other than InnoDB, realistically. That said, it has it's own learning curve. It's really an entirely new database engine, so there's lots to learn even if you've used mysql/myisam for years. In particular the tablespace layout and dependency on files other than ones linked to a particular table is a little daunting. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: users are always the last to know :-)
On Oct 7, 2005, at 11:46 PM, Jasper Bryant-Greene wrote: Walt Weaver wrote: Well, forgive me for being a bit skeptical and cynical but this sounds like spin to me. As a 17-year Oracle DBA I have never seen Oracle do anything that can remotely be called benevolent. Larry Ellison buys companies, guts them, chews them up and spits them in the gutter. I doubt he'll ever buy a company just because he's caught up in the groovy sunshine world that's Open Source. And, as an employee of a company that's very, very heavily committed to MySQL (we're just about completely moved off of Oracle and are using InnoDB) this whole thing leaves us rather worried. Remember that InnoDB is open source and GPLed. That means that even if Oracle were to start doing something evil, there's nothing stopping you, the MySQL team, or anyone else starting a fork that remained open source and carried on from the last available open source version. Of course that's technically true, but is it realistic? Are there non-MySQL AB forks of either myisam or innodb? It just doesn't seem that it's as easy for outsiders to pick up and run with this as it is with other OSS projects. Even within MySQL AB, how deep is the InnoDB knowledge? We have paid support, and when it gets to an InnoDB specific issue the question goes to an InnoDB OY employee fairly quickly. Before we even discuss someone forking InnoDB, would MySQL AB be able to support current InnoDB using customers if Oracle were to make Heikki et. al. unavailable? This is very worrisome to people whose business rides on InnoDB/MySQL and can't be covered with PR and spin. It would be nice to hear that MySQL has worked out the inclusion of whatever InnoDB becomes beyond the current contract expiration next year, but at a minimum they have to explain how they will support current InnoDB use when the people that largely did it aren't necessarily available. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spreading the disk load
On Sep 14, 2005, at 6:16 PM, Chris Kantarjiev wrote: I'd like to spread the disk arm load across multiple drives. At the moment, we mostly use MyISAM tables, but we are also experimenting with InnoDB. What's the 'best practice' for doing this? There's no obvious configuration that lets me designate one directory for index and another for data - am I meant to do this with symlinks? How can I do anything like that with InnoDB, which appears to put everything in one massive file? I think broadly you have these three options: Move and symlink directories. This works with InnoDB if you use file_per_table so it doesn't just use one big file. However, for this to work well you need to know the load across tables. Use a RAID setup. We moved from single SATA drives to 6 spindle FC- ATA RAID 5 and saw a large improvement. This is useful in that it distributes the load across the spindles. Separate logs and tables. Particularly with InnoDB, you have a lot of logging going on. We use only InnoDB, so we have the transaction logs plus binary logs which is a decent amount of I/O. These are also easy to move (and you know they are pretty constant load, unlike some tables which might only see sporadic load), so we put them on a separate RAID 5 array using a separate fibre channel port. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct way to use innodb_file_per_table?
On Jul 25, 2005, at 5:47 AM, Marvin Wright wrote: You recommend to dump tables before changing then re-import them back. But if all databases are in there own tablespace I should need to do this dump should I ? Unfortunately I think that's your only option to create a new table space. One way to avoid that and not waste the space might be to move some large tables into the shared table space and use file-per- table for new tables. You'd just switch file-per-table off, run ALTER TABLE tablename TYPE=INNODB to move the table into the shared space then switch file-per-table on. This won't work for a lot of table structures, but it might be a way for you to use the space. I want to reduce it to about 10gb, that should be enough for all its temporary storage and logs. You probably know this, but regardless of whether you use file-per- table or not you still need the separate InnoDB log files. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.13 OS X MAJOR I/O Degredation
On Jul 22, 2005, at 6:22 PM, Bruce Dembecki wrote: So it appears I am having an issue with 4.1.13 which I'm guessing is a bug... wanted some input before I file it... Setting up a new machine to take over for an old one, so it's clean, Operating System and some empty disks... the server does nothing other than MySQL so there are no other processes running. It has 16Gbytes of ram and the data disks are a 7 disk RAID5 array on a 2GBit/Sec Fiber Channel connection. If I create my data directories and copy the mysql database from another server with a simple copy (mysql is myisam so it's no issue) I am ready to launch mysqld... When mysqld launches it of course needs to create my InnoDB data files and log files before it comes up... I first did this under 4.1.13 Community edition and was SHOCKED by the results... one 2Gbyte shared data file for InnoDB, and 2 250Mbyte log files... what felt like an hour later it finished... Tried 4.1.13 Pro released today... same thing... Tried 4.1.12, better, still slower than I would expect, but better... let me quantify that a little. From the log files below you will see that the time to create the InnoDB files and get to the point of being ready to connect is: MySQL 4.1.13 Pro:54 minutes 51 seconds MySQL 4.1.12 Standard: 4 minutes 16 seconds Have you verified that actual queries are slow, or is it just the create? I'm wondering if this from the 4.1.13 changes could be the issue: InnoDB: When creating or extending an InnoDB data file, allocate at most one megabyte at a time for initializing the file. Previously, InnoDB used to allocate and initialize 1 or 8 megabytes of memory, even if a few 16-kilobyte pages were to be written. This fix improves the performance of CREATE TABLE in innodb_file_per_table mode. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 11:56 AM, Dan Tappin wrote: I am all most ready to give up on MySQL at this point. I'm still getting regular table corruption on multiple installs of OS X. I went as far as reporting it as a bug: http://bugs.mysql.com/bug.php?id=12066 They seem to want more info but my requests for clarification have gone unanswered. Has your mysql crashed or have your restarted the machine without first shutting down mysql manually? We only saw this error when mysql was not shut down normally. You can look in your .err file which should be in your data directory. Every mysql sartup sequence should be preceded by a line saying mysqld ended. If not it's crashing and that's the issue to address. 2.) Try Innodb This is a MyISAM error, but if mysqld is crashing or being shutdown abnormally InnoDB too will have to go through a crash recovery at startup (it's just automatic, but you'll see it in the .err file). I have changed one of the tables that consistently gives my trouble to Innodb. I will continue to watch for corruption. Given that can anyone point out any big things to note about innodb tables. I've read that they are in general better than myisam tables but you loose the fulltext index option. I think it's more that they are different. InnoDB has some complexities related to backups and slightly different functionality, and the number of tips on the Internet are much fewer than for MyISAM. However, if you are doing lots of inserts with simultaneous selects you really have to use InnoDB to get reasonable performance. Is there a typical work around for this?? I typically only have the odd tables that needs a full text search capability. I think what I have seen was to create a dummy myisam table with just the key and fields from the innodb table and add a fulltext index to search against. It would just be a matter of keeping the tables synced. I think that's what most people do. I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. 1) Shut down and manually copy, but make sure you get all the InnoDB parts...data files and transaction logs. One is pretty much useless without the other. 2) mysqldump, look at the options. This with InnoDB is pretty nice b/c you can use --single-transaction and get a point in time copy of the table without disrupting other users. In particular the comments on the mysql documentation page for mysqldump detail a good set of flags for InnoDB. 3) The (non-free) ibbackup which takes a backup while running without disturbing users I don't think there's any fundamental issue with MySQL (either myisam or innodb) on OS X, but I do think that MySQL is something that doesn't do well when run only through Apple's front end. It has two many options, configuration details, etc... if you are doing anything more than a few small tables. Plus, if you are relying on it all you'll want to be able to take advantage of bug fixes faster than Apple's MySQL updates allow you too. Fortunately, it's easy enough to download the mysql binary and use/ configure it independently. We've had quite good luck with MySQL on OS X with a pretty big install, but there are a lot of details to learn so it can seem tough to work out at times. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 1:58 PM, Dan Tappin wrote: On Jul 23, 2005, at 10:14 AM, Ware Adams wrote: Has your mysql crashed or have your restarted the machine without first shutting down mysql manually? We only saw this error when mysql was not shut down normally. You can look in your .err file which should be in your data directory. Every mysql sartup sequence should be preceded by a line saying mysqld ended. If not it's crashing and that's the issue to address. Nope... nothing like that... just lots of: [ERROR] Got error 127 when reading table No crashes at all. Then I don't know what to say about the MyISAM error, though I'd watch InnoDB pretty carefully as I think MyISAM is pretty robust on OS X too. We did also see these when a disk is failing, but I assume you've run disk utility. I guess it could be failing and not showing up in disk utility. If it's an XServe the SMART data might show you that, or if you have DiskWarrior I think it might monitor SMART data for you. 2.) Try Innodb This is a MyISAM error, but if mysqld is crashing or being shutdown abnormally InnoDB too will have to go through a crash recovery at startup (it's just automatic, but you'll see it in the .err file). so the repair is manual on MyISAM and auto on start-up for Innodb? Yes, but that's after a crash. When you start InnoDB after a crash you'll see: 050722 16:26:38 mysqld started 050722 16:26:40 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050722 16:27:09 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2875 3750200540. InnoDB: Doing recovery: scanned up to log sequence number 2875 3755443200 ... InnoDB: Doing recovery: scanned up to log sequence number 2875 3878800255 050722 16:27:18 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 050722 16:30:11 InnoDB: Flushing modified pages from the buffer pool... 050722 16:30:33 InnoDB: Started; log sequence number 2875 3878800255 /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.12-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) But that's after a power failure when and InnoDB could tell it had issues when we restarted. The thing I would be concerned about in your case is what's going to happen if the same thing that causes issues for MyISAM has an impact on InnoDB. If it were crashes InnoDB can handle that pretty well, but if it's some underlying disk issue then InnoDB could wind up with it's table space corrupt just like MyISAM. The issue with InnoDB is that the tablespace files are more dependent on each other. So if you have a corrupt MyISAM table you could literally shut mysqld down, delete the files for that table and everything else is fine. You'd lose the data for that table but not anything else. With InnoDB you have the transaction logs, the shared tablespace files and if you are using file_per_table a tablespace (.ibd) file for each table. At least the first two of these are completely dependent on each other. If you wind up with a corrupted shared tablespace file due to a disk error you can potentially lose everything. The transaction logs can be replaced, but only if there are no uncommitted transactions in them. If you lose a .ibd file in file_per_table you would just lose that table. I probably didn't explain that very well, you might want to read the sections on InnoDB's various files in the InnoDB manual http://www.innodb.com/ibman.php In short, InnoDB deals better than MyISAM with crashes of the mysqld server or the OS, but I don't think it's necessarily any better (nor maybe could it be) with dealing with an underlying disk corruption issue that keeps occurring. I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. 1) Shut down and manually copy, but make sure you get all the InnoDB parts...data files and transaction logs. One is pretty much useless without the other. 2) mysqldump, look at the options. This with InnoDB is pretty nice b/c you can use --single-transaction and get a point in time copy of the table without disrupting other users. In particular the comments
Re: MySQL Binlog/Replication and SET @variables
On Jun 24, 2005, at 10:56 AM, Markus Benning wrote: i have a problem with my replication setup and SET statments. The SET statments seem to be not logged with the queries. This is a replication limitation in versions prior to 4.1: Update statements that refer to user variables (that is, variables of the form @var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1. Note that user variable names are case insensitive starting from MySQL 5.0. You should take this into account when setting up replication between 5.0 and an older version. http://dev.mysql.com/doc/mysql/en/replication-features.html --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will multiple MASTER threaded writes break replication?
On Jun 20, 2005, at 5:37 PM, Kevin Burton wrote: Kevin Burton wrote: We're noticing a problem where if we were to write to the master with multiple threads that our slave DB will fall behind. BTW.. I should clarify.. when I mean break I really meant to say that the slave replication will fall WAY behind because it can't replay transactions as fast as the master. So if your slave is 10k seconds behind its essentially broken . Yes, that will happen b/c replication uses only a single query execution process. However, the queries are still being copied to the slave, they are just queued for execution. It depends on what you are using replication for as to whether you'd call it broken. It still works fine for disaster recovery in a lot of instances, b/c the queries are safely on the slave for execution even if the master dies. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring InnoDB databases from backups causing problems
On May 25, 2005, at 9:34 AM, David Brewster wrote: A client of ours has experienced some rather serious hardware failures resulting in disk errors and therefore corrupted InnoDB files. We could not get mysql to restart unless the innodb_force_recovery was set to 5. In this situation we encountered failure when attempting to read from various tables even using the mysql command prompt. At this stage we decided to try restoring from backups. Now mysqld seems to start but then die perpetually but restart itself each time. This means that if you try something from the mysql command prompt, it usually gives you, for example something like: What does the .err log say? It should be in the data directory. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring InnoDB databases from backups causing problems
On May 25, 2005, at 10:06 AM, David Brewster wrote: Here is the log dump :- Thanks David 050525 13:24:10 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.15-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 050525 13:24:11 InnoDB: Assertion failure in thread 114696 in file fsp0fsp.c line 3034 InnoDB: We intentionally generate a memory trap. Well, InnoDB is crashing immediately on startup. Is that how it was crashing when you were using the corrupt table space? You can scan back in the log or look in the log for that data directory. If it's crashing the same way this table space might be corrupt also. If it's a different cause, you might get lucky by upgrading InnoDB. The current 4.0 version is 4.0.24. I would _not_ upgrade to 4.1 until you fix this...you can't easily downgrade InnoDB that has gone to 4.1. If you do try upgrading to 4.0.24, I would work on a copy of this dataset (e.g. a copy of the backup which is now failing). You don't want to do any more damage. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lower version mysqlclient can not talk to the mysql server of higher version
On May 25, 2005, at 5:12 PM, Farid Hamjavar wrote: Situation: System XYZ RH AS 2.1 RH mysql client -- mysqlclient9-3.23.22-8 RH php -- php-4.1.2-2.2 php-mysql-4.1.2-2.2 System ABC RH AS 3.0 RH mysql server -- 4.1.10-standard The mysql client on XYZ can not talk to mysql server on ABC. ABC is running 4.1 which has a new password format, so anything 4.0 and before can't authenticate. Option of installing later version of the mysqlclient MySQL-client-4.1.12-1.i386.rpm (off of mysql.com) does not exist. In that case start ABC with: old-passwords in the my.cnf file. Note this is less secure than using new style passwords. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to Mysql server during query using a group by clause and sub query
On May 19, 2005, at 12:51 AM, Dan Rossi wrote: Hi there, I am having issues with this funny error message. I am trying to do a sub query and then a group by clause on a date. However i keep getting this annoying message for some reason. Happens via terminal aswell as my sql gui. Here is the query I am trying to do select (select count(discrepancy_originID) FROM report_log WHERE discrepancy_originID=1) as total FROM report_log GROUP BY MONTH (tx_date) let me know, if i take the sub query out its ok ? Is your server crashing during the query? Check the .err file which should be in the data directory for restarts. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP,Apache and MYSQL
On May 13, 2005, at 8:34 PM, Hassan Schroeder wrote: Ong Khai Chin wrote: Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in C: \Program Files\Apache Group\Apache2\htdocs\mysqltest.php on line 2 Could not connect: Client does not support authentication protocol requested by server; consider upgrading MySQL client what are the configuration i need to do for mysql in php?? You're apparently using a recent MySQL server with an old PHP build. So you should recompile PHP using the newer MySQL client libraries. Most likely you're using MySQL 4.1 and a php compiled against MySQL 4.0. If you are using a my.cnf file you could also add: [mysqld-4.1] old-passwords to it, which for MySQL 4.1 will use the old password methodology and should allow your php to connect. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems ...
On Mar 31, 2005, at 1:13 AM, Rafal Kedziorski wrote: I'm working with JBoss and MySQL 4.0.22 (and 4.0.18 on Testsystem). But under the load, I get sometimes Exceptions like this: java.sql.SQLException: Deadlock found when trying to get lock; Try restarting transaction message from server: Lock wait timeout exceeded; Try restarting transaction It looks like InnoDB problem. Is there a way to find out why this happens? InnoDB has detected a deadlock, which is described pretty well in the InnoDB manual (at innodb.com or the mysql.com version of the manual). When this happens run show innodb status\G from the command line client. InnoDB will print out status info, and at the top will be details of the last detected deadlock which will show the conflicting queries. You then need to modify the queries or the application so it doesn't happen. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating InnoDB tables in new database
On Mar 18, 2005, at 7:06 AM, Andy Hall wrote: Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? Would a server restart fix it? As someone else mentioned this won't work with InnoDB. InnoDB uses .frm files for table descriptions along with ibdata files and it's own log files. If you want to do something like this by moving files you could: 1. shut down mysqld cleanly 2. copy db directories, .frm files, all ibdata files and all InnoDB log files (not mysql binary logs, InnoDB logs) to the new machine 3. set ownership and permissions 4. start mysqld on new machine Or you could us InnoDB hot backup tool, but in that case you still need to copy the .frm files and database directories. This is all best described in the InnoDB manual (if you're using it you should read the entire thing as it handles a lot of things differently than MyISAM) and the MySQL replication section of it's manual. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Apple install
On Feb 22, 2005, at 10:44 AM, Boyd E. Hemphill wrote: I am considering the use of a new OS X machine with the Free BSD back end. I would like answers to the following if anyone has the experience. We use MySQL (exclusively InnoDB) on G5 XServes. What switches should be set for compiling? I only need the Innodb storage engine. The MySQL installer or the MySQL binary without the GUI installer work very well. However, when we tried to increase memory assigned to InnoDB (specifically innodb_buffer_pool_size) we were unable to get past 1,536 MB (1.5 GB). We then did a custom build using: CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt CXX=gcc CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --with-innodb This allowed us to startup MySQL fine with 2,048 MB assigned to innodb_buffer_pool_size (as someone else said it will take waiting for OS X 10.4 to get really large memory allocations to a single process). This worked fine for the most part, but after the server had run for a couple of months (this is a reasonably large data set...110 GB, 247 qps, 70% of the queries are inserts/deletes/updates) we ran into a crash that appeared to be related to InnoDB actually using all of that memory. MySQL support looked into it and suggested that OS X is currently limited to 1.8GB in a single chunk. At that point we reduced innodb_buffer_pool_size to 1.7 GB and the machine has been running almost 100 days without issue. At this point the custom compile is only getting us about 200 MB extra for InnoDB, so I don't know if it's really worth it. However, it's been just as stable as the MySQL build was (we basically took the config command from MySQL's site and added Apple's suggested G5 switches) so there seems to be no harm. Which is the best compiler to use for MySQL on an Apple machine? We used gcc 3.3 that comes with the current developer tools/XTools. Good luck, Ware Adams Best Regards, Boyd E. Hemphill WEST Project Manager MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405 M: (512) 470-6146 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave integrity.
On Jan 7, 2005, at 5:16 AM, RV Tec wrote: Folks, I'm doing replication (one master, two slaves) with MySQL 4.0.23. I'm really happy with it. Although, every once in a while, the slaves get desync'ed, one at a time (the DB is 6GB large, and has 140 tables). I can't help with verifying your slave, but this might be the cause of loss of integrity: http://bugs.mysql.com/bug.php?id=7011 We hit it on 4.0.23. It requires the use of multi table updates on the master and replicate-*-table options on the slave. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication update bug/error/problem.
On Dec 8, 2004, at 9:12 AM, Jay Ess wrote: I am not using cross database updates. It is all on one database but the update uses two tables. The query update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 is a stripped down version of a bigger but i stripped down to the point of failing. The failing factor is when i use content_review_site as a,site_rating_factors as b (not a cross database but a cross table query). I wonder if you're running into this bug http://bugs.mysql.com/bug.php?id=7011 We saw it on 4.0.22 and Mac OS X. MySQL has been able to reproduce it, but only on OS X, not Linux. The query works on the master, makes it into the binlog but doesn't update and records on the slave (and doesn't cause replication failure). When the query is run manually on the slave from the command line it works fine, the records are updated as on the master. However cross table updates won't run on the slave under replication. We had to downgrade to 4.0.21. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge Innodb file
On Sep 24, 2004, at 4:03 AM, MaFai wrote: Dear [EMAIL PROTECTED]: Here's my setting: innodb_data_file_path = ibdata1:10M:autoextend Now the ibdata1 has been grow up to 1.3G We try to add more ibdata file to store the data by the following setting. innodb_data_file_path = ibdata1:1500M;ibdata2:1500M:autoextend innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend The first one is closer, but you need to specify the size of the current file _exactly_ (as opposed to something a little larger than it as you have done). There's a pretty clear example in the manual: http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about innodb_thread_concurrency
Heikki Tuuri wrote: Devanada, normally you do not need to tune this option at all. I introduced the option when certain Linux binaries showed 'thread thrashing' at high concurrency. Setting the option to 1 or 2 could in some cases alleviate the problem. You can experiment by setting the option to 500 to disable restrictions on concurrent operations. I am considering changing the default value to 500, because we have not seen 'thread thrashing' on any platform for a long while. We did see something that looked a lot like this about a year ago on Mac OS X 10.3.x running InnoDB 4.0.x. Queries that normally ran fine all of a sudden started running incredibly slowly. The CPU was pinned, but things just weren't finishing in any reasonable time. Eventually InnoDB noticed some long waits and intentionally crashed itself. Thread concurrency was at 20-30 at the time, I think. We were running on a dual processor machine on a 6 drive RAID, so we followed the instructions in the InnoDB manual and set thread concurrency to 8. The problems immediately went away. Haven't tried increasing it now that we're on a G5, a few revisions of the OS and a few revisions of InnoDB newer. Performance seems fine as is. --Ware Adams Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html ... Hi, This is a question about optimization of mysql's InnoDB performance ... quoting the doc's The default value is 8. If you have low performance and |SHOW INNODB STATUS| reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this parameter lower or higher. The default value is 8. If you have low performance and |SHOW INNODB STATUS| reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this parameter lower or higher. What is a reasonable value for a dual Xeon 2.8Ghz with 6 disks? we are currently running with innodb_thread_concurrency=16. Each DB has 200-400 active php clients, and there appears to be quite a large backup within innodb, based on this. Any suggestions? Or experience with setting this option to 500? Thanks :) Devananda Neopets, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning InnoDB situation
Boyd E. Hemphill wrote: But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. innodb_data_file_path = ibdata1:15G:autoextend We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. This might be a long shot, but is InnoDB getting slowed down by extending its tablespace? You've got more data than your initial ibdata file will hold, so clearly it autoextended to hold your data. I think InnoDB extends in 10M increments, so if you're doing lots of inserts it could be repeatedly extending the file which seems like it would add some overhead. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB my.cnf settings on OS X 10.3 Server?
Kieran Kelleher wrote: Does anyone out there have a suggested innodb parameters or even a working set of params that I could start with. The InnoDB manual gives good suggestion. Some minor comments (none particularly OS X specific, but InnoDB works fine on it) innodb_buffer_pool_size is very important. We have ours at 900 MB, but this is a machine running only mysql with 2GB of RAM. You want as much as you can have here, but not so much to cause pageing. innodb_additional_mem_pool_size should be big enough to work around this bug (we use 50M and have avoided the bug, 10M did not, but it's usage specific) http://dev.mysql.com/doc/mysql/en/InnoDB_news-4.0.21.html If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the `.err' log. The workaround is to make innodb_additional_mem_pool_size big enough to hold all memory allocation. Use SHOW INNODB STATUS to determine that there is plenty of free space available in the additional mem pool, and the total allocated memory stays rather constant. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex when we set innodb_thread_concurrency too large we ran into behavior that looked a lot like a thread thrashing problem seen on InnoDB on Linux some time ago. We didn't have proof, but reducing innodb_thread_concurrency to (number of cpus + number of physical disks) made the issue go away. For us this value is 8. We use 4 logs and a log file size about 25% of innodb_buffer_pool_size. (set via innodb_log_file_size and innodb_log_files_in_group). This has implications for speed and recovery time after a crash. Also, does anyone definitively know what the maximum filesize is for OS X Panther (and consequently the max innodb tablespace filesize? It's huge. We use mainlu 4G files, but an autoextending one grew to 12GB without issue. Apple lists this in the knowledge base somewhere. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: sort buffer size 2097144 join buffer size 131072 max heap table size 16777216 max join size 4294967295 max sort length 1024 myisam max sort file size 2147483647 myisam sort buffer size 8388608 Upto my understanding mysql runs on a single thread and is not capable of utilizing both the processors. (please correct me if i am wrong). MySQL uses both processors on a G5 if more than one query is running. If there is only a single query running it is limited to one processor. So I don't think running multiple instances makes sense. Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Your key buffer looks very small, assuming you are using MyISAM tables: key buffer size 8388600 I'd look at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html as a first cut. You'll get a lot of benefit out of these and optimizing queries. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Your key buffer looks very small, assuming you are using MyISAM tables: key buffer size 8388600 I was in a view that if the key index size is more than the available memory there is no point in increasing the key buffer size since anyway it will use the disk read. If I am wrong, could you suggest me a value for this? That's true, and swapping will make performance horrible. But your original email said: PowerMac G5 1.6 GHz dual processor 3 G RAM Mac OS X (10.3.4) So you have 3 GB RAM and your using only about 8 MB for key_buffer_size. What else is running on the box? If it's only MySQL try cranking up key_buffer_size. If it's only MySQL on the machine, I might start at 1.5 GB. Watch top while your queries are running. You want mydqld at or close to 100% cpu (only one query running) or 200% (2 or more queries running). If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Yes Ware, the CPU is not fully utilized. It shows just 10 -15% utilization. Could you provide some direction on how to optimize the mysqld's settings? will using my_huge.cnf will do or do we need to change any parameters? This is a topic that could take pages of explanation: http://www.oreilly.com/catalog/hpmysql/ Are you running only on mysql on the box? That's a big issue. If so start with my-huge.cnf and tweak based on your usage patterns and the mysql manual. At a start, give mysqld a lot of memory. Next, look at your queries using explain statements. Nothing will be fast until yo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Yes Ware, the CPU is not fully utilized. It shows just 10 -15% utilization. Could you provide some direction on how to optimize the mysqld's settings? will using my_huge.cnf will do or do we need to change any parameters? [sorry, hit send too early] This is a topic that could take pages of explanation: http://www.oreilly.com/catalog/hpmysql/ Are you running only on mysql on the box? That's a big issue. If so start with my-huge.cnf and tweak based on your usage patterns and the mysql manual. At a start, give mysqld a lot of memory. http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Next, look at your queries using explain statements. You'll want to write your queries/add indices/create summary tables until you get the explains looking good. This too is a complex area. The mysql manual has a lot of info to start with: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Nothing will be fast until you utilize the cpu. We've found mysql runs very well on the G5. Our G5 XServe is about 2-4X faster than a DP 1.42 G4 Tower, depending on query. Some of that is the chip, some the bus, some the extra memory the G5 can take. It is of course specific to our queries and setup. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting MySQL in Panther
chueewowee wrote: [raisinspace:/usr/local/mysql] chueewow% Starting mysqld daemon with databases from /usr/local/mysql/data 040612 23:32:05 mysqld ended mysqld isn't starting. See what the error log says, it will likely be in /usr/local/mysql/data --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete temp data...
Jonathan Villa wrote: in /usr/local/mysql/data there is one dir named the same as the db they are working on, then there is this other file called ibdata1 which is 2.1G in size.I take a look at the first few lines and all I get is garbled data, as if it were a binary file or something. That's an innodb data file, and given that it is 2.1G it sounds like someone is using it to create InnoDB tables. As you create new tables this file can grow if it is set to autoextend. Even when using InnoDB exclusively you will see a directory corresponding to each database which holds the table definition. The data and indices are contained in the ibdata files. There's a lot of info on this in the InnoDB section of the MySQL manual: http://dev.mysql.com/doc/mysql/en/InnoDB_overview.html and at the InnoDB web site: http://www.innodb.com/ibman.php Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP mysql.sock is missing in Mac OS X
Jose Manuel Islas Romero wrote: I start MySQL and it seems to start the server normally TrentCioran:/usr/local/mysql TrentCioran$ sudo ./bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 040512 21:27:05 mysqld ended It's not starting normally. It's starting and dying, that's why it says mysqld ended. but when i try to open the mysql console it sends me the message: TrentCioran:/usr/local/mysql TrentCioran$ /usr/local/mysql/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I've been searching for the mysql.sock file but it is missing. That's because mysqld isn't running. When you get mysqld running this problem will go away. The reason why it's not running should be noted in the error file, which should be: /usr/local/mysql/data/hostname.err This will have a notation where mysqld tried to start and then died. It seems like lots of people starting mysqld on OS X for the first time have a permission error on the data directory. It should be owned by mysql (the user already exists in both 10.2 and 10.3) and the group should be mysql. This problem is error number 13, but other error numbers will be explained by the perror command: /usr/local/mysql/bin/perror errornumber Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB large mem allocation on G5
Has anyone had any luck getting MySQL 4.0.18 (specifically innodb_buffer_pool_size) to use large amounts of memory on a G5 running OS X Server 10.3.3? I initially tried with the MySQL built binary, and was unable to get innodb_buffer_pool_size to go beyond 1.5 GB. Here's the my.cnf section that allowed MySQL to start: set-variable = key_buffer=128M set-variable = max_allowed_packet=1M set-variable = table_cache=4096 set-variable = sort_buffer=4M set-variable = max_connections=40 set-variable = record_buffer=2M set_variable = tmp_table_size=2M set-variable = thread_cache=8 set-variable= interactive_timeout=2000 set-variable= wait_timeout=2000 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=32M default-table-type=innodb set-variable = long_query_time=120 log-slow-queries set-variable = query_cache_type=1 set-variable = query_cache_size=1M datadir= /raid/mysql-data # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /raid/mysql-data innodb_data_file_path = ibdata1:4000M;ibdata2:4000M:autoextend innodb_log_group_home_dir = /raid/mysql-data/ innodb_log_arch_dir = /raid/mysql-data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high set-variable = innodb_log_files_in_group=4 set-variable = innodb_buffer_pool_size=1536M set-variable = innodb_additional_mem_pool_size=10M # Set .._log_file_size to 25 % of buffer pool size set-variable = innodb_log_file_size=200M set-variable = innodb_log_buffer_size=16M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 If I put innodb_buffer_pool_size=2048M on the pre-built binary MySQL won't start, InnoDB dies trying to allocate memory: 040414 12:01:32 mysqld started *** malloc: vm_allocate(size=2147504128) failed (error code=3) *** malloc[5630]: error: Can't allocate region InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 15438152 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=2093056 max_used_connections=0 max_connections=40 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 376671 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Writing a core file 040414 12:01:33 mysqld ended I then looked at some previous posts on this list about needing to compile with G5 specific flags and the current OS X max memory per process being 4GB. List discussion: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm= bv3gfm%242bga%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq% 3Dmac%2520os%2520x%2520mysql%2520memory%2520g5%26hl%3Den%26lr%3D%26ie% 3DUTF-8%26oe%3DUTF-8%26sa%3DN%26tab%3Dwg G5 tuning: http://developer.apple.com/technotes/tn/tn2086.html#G5options So I compiled MySQL from source using this config line: CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt CXX=gcc CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --with-innodb Which is basically a combination of what Apple recommends and what MySQL says is the config for the pre-built OS X binary. This is using gcc 3.3 on the latest version of OS X Server with the latest version of the developer tools (XTools) installed. This improved things somewhat...innodb_buffer_pool_size will now go to 2 GB, but if I try to go beyond I get the same error. Does anyone have suggestions for pushing innodb_buffer_pool_size beyond 2 GB? We can decrease the MyISAM settings pretty drastically if necessary as everything but the grant tables are InnoDB. Still, even with the settings above I would have expected to be able to get InnoDB to 3 GB if the overall process limit is 4 GB. Thanks for any help, Ware Adams -- MySQL General
Re: MySQL Backup advice
Andre MATOS wrote: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? Yes. The basic steps are: 1) Turn on the binary log in mysql: http://www.mysql.com/doc/en/Binary_log.html 2) Take a consistent point in time backup using InnoDB Hot Backup and the perl script ibbackup that InnoDB provides: http://www.innodb.com/manual.php#backup.myisam 3) wait until you need the backup 4) Restore the data set using InnoDB Hot Backup http://www.innodb.com/manual.php#restore 5) The restore will note the position in the binlog at which the backup was taken, use this position to apply the binlogs, also from: http://www.mysql.com/doc/en/Binary_log.html Obviously try this before relying on it. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HOTBACKUP
No, InnoDB hot backup only backs up the table data, not the definitions. You need to back those up separately. This is all explained here: http://www.innodb.com/manual.php#Backing_up_myisam_and_innodb There's also a perl script that can help you with the backup. Even though it says MyISAM, you also need this for the InnoDB table definitions. Good luck, Ware Arnoldus Th.J. Koeleman wrote: I am implementing Hotbackup for innodb . My question is when the tables in the database directory so *.frm are deleted by mistake will they be recovered by the hotbackup script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB beginner question
Tofu Optimist wrote: When I set up the databases, I recall assigning a few large fixed-size files to the InnoDB engine for data (and I think) logs. I've been adding data to my databases daily and wish to know how full they are, Use this command: show table status from database_name like 'table_name'; where database_name.table_name is an InnoDB table. The last column of the output will show the InnoDB free space. and what is the process for extending the disk space allotted to them, should they need it. You could make the last table space file auto extending or shut the server down and add more table space files. You can't make them smaller easily. The details are here: http://www.innodb.com/ibman.php#Adding.and.removing Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld hangs with no CPU activity...
Paul Stearns wrote: As reported under the subject Random Database Slowdowns... on the win32 list, our database still hangs on an average of 1-2 times per day. I can find no error messages or logs associated to the problem. It affects both IIS ADO connections as well as local connections from tools such as mysqlcc, mysqladmin and command line tools such as mysql. I see no CPU activity associated with the hangs. I cannot stop and restart the service, but most of the time a reboot will resolve the problem. Sometimes the problem will reoccur within a few minutes of a reboot, other times it takes hours. I can't swear it's the same problem, but we had very similar symptoms some time ago (version was around 4.0.8 or so). This was also on Mac OS X Server 10.2.x. Seemingly random queries would just not finish. They were queries we had run before and could even run at the same time from a different client. But this process would just not finish. It would sit in show processlist forever. If it was the only query running the mysqld cpu load would drop to around zero. There was no i/o activity if it was the only query running. Any temp files associated with the query wouldn't grow. Sometimes it was a small query, sometimes a big one. If you tried to kill the process from within the command line client or mysqladmin it would show up as killed in the process list but never die. Issuing mysqladmin shutdown wouldn't shut down the mysqld server b/c it couldn't kill off the queries either. Even kill -9 mysqld pid would hang the machine. The only solution was rebooting while mysqld was running b/c we couldn't shut it down. We tried moving to InnoDB and got the same situation (and show innodb status\G revealed no work was being done within InnoDB). We tried swapping RAM, swapping hard drives, changing drive formats (HFS+ to UFS), installing Yellow Dog Linux on the machine, etc I was pretty certain it was just a MySQL on the Mac issue, but then I pulled the drives and RAM and put them in an identical Mac. Same drives, same RAM, same data, same OS, same MySQL...the problem disappeared. We sent the machine back to Apple (it was new) and they replaced the mother board. That machine would show random errors even in just desktop use, so though the solution seems onerous we really didn't have much choice. If you can, I'd try running the queries on a different machine. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, multi-table update work around
We'd like to set up replication in a simple master/single slave setup, but I can't figure out a way around an issue we'll have with multi table updates. The master will hold a set of databases, all of them will be replicated to the slave. The slave will hold the replicated databases plus a set of slave-only databases. To this point everything works fine. In order to prevent updates on the replicated tables on the slave, we would like to set up privileges so that users logging into the slave do not have the update privilege. In other words, on the slave: update replicated_db.table set val=10 where id=1; should fail. This is easy enough to set up by just removing the update privilege for these databases for our users on the slave machine. However, we would like users of the slave to be able to perform updates on their local, non-replicated tables using data from the replicated tables in a multi-table update statement like this: update non_replicated_db.table, replicated_db.table set non_replicated_db.table.val=replicated_db.table.val where non_replicated_db.table.val_id=replicated_db.table.val_id; So this query includes the replicated_db in the update statement, but only actually writes to the non-replicated one. Logically this is OK for our setup since only the non-replicated table is altered. However, the users on the slave machine don't have the update privilege for the replicated_db, so this query fails, presumably b/c the privilege system looks at all tables included in the update line rather than trying to figure out which ones are actually changed (which would be a lot more complex, I understand). My question is, is there a privilege setup that will make this work? If not, is there a simple alternative to the multi table update statement? I've thought of doing a 'replace into' in cases where the update is linked on the primary key, or we could select out the records that match to a temporary table, delete from the non-replicated table and read in from the temp table. Does anyone know of a more elegant solution or a solution via privileges? Thanks, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X
[EMAIL PROTECTED] wrote: After a fruitless search for MySqlCC for Mac OS X, I downloaded and built qt and MySqlCC. However, I ran into some errors on the CC build. Am I duplicating effort here? I know that we are a small market but it is a form of Unix and if I could get some assistance I would like to give this a try. I haven't tried it, but I did come across this the other day: http://www.pogma.com/blosxom.pl/2003/Aug/17#back Looks like it requires Fink. Also, you might try CocoaMySQL which works well: http://cocoamysql.sourceforge.net/ --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? No, OS X has a file size limit of 2 TB (prior to 10.2), 8 TB (10.2.x) or 16 TB (10.3). http://docs.info.apple.com/article.html?artnum=25557 --Ware Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone using MySQL 4.x on Apple's G5?
Gabriel Ricard wrote: On Wednesday, October 15, 2003, at 03:10 PM, Jeremy Zawodny wrote: I'd be interested to know if you can get a test running that uses either a key_buffer or an innodb_buffer_pool in the 3.5GB range. Interestingly enough, I can't seem to get MySQL to use more than 2GB of RAM. I get errors like this: *** malloc: vm_allocate(size=2042925056) failed with 3 *** malloc[489]: error: Can't allocate region I wrote a small C program to test malloc() and see just how much I could allocate, and I was able to get up to 3.5GB before being cut off by the OS, which leads me to believe that I should be able to use that much RAM for MySQL. I just can't seem to get it to do so. I even tuned down all of the other configuration options so that MySQL was only using about 30MB of RAM with a key_buffer of 0MB. Then I bumped up the key_buffer to 3.5GB: no go, 3GB: no go, 2.75GB: no go, 2.5GB: nope, 2250MB: yes! 'top' reports mysql to be using 255MB at that level. So, yeah, I can run some benchmarks on that... But I don't see how useful they'd be since all of the other parameters are tuned way down. (none are over 8MB) InnoDB benchmarks may be useful since it uses the buffer to cache records as well as keys. Have you tried setting innodb_buffer_pool_size to a number over 2GB? Thanks, Ware Adams - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Is disk I/O more important ? Frequently. open a terminal window and run top while the query is running. If mysqld is using 100% of a cpu on a single process then the query is CPU bound. If it's using signficantyly less (e.g. 30%) then it's probably disk bound. The solutions to being disk bound can be lots of things: 1) Better indexing 2) More RAM (the G5 will help here as it can go past 2 GB) 3) Faster disks, the G5's faster drives and faster bus will help In general #1 is far and away the biggest factor, you can speed up queries by a factor of thousands or more. The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). I don't know the structure, but that order of magnitude is doable in much less time. We imported 30 million records on a server running other queries in less than 3 hours. However, it was an InnoDB table and there were only numeric fields in it. You can probably improve things by tweaking your table structure and my.cnf file. There's a lot of detail in the mysql manual on the web. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbye speed question - which setup to use for indexing
Andy Eastham wrote: How big are the table and index files? Can your OS handle files bigger than 2/4Gb? Yes, OS X can deal with files larger than 4 GB. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) I haven't seen any documentation, and I'm not sure I'm explaining things properly, but here's what I've observed running MySQL on a decent sized data set over almost a year on OS X: When only one query is active in MySQL (observed via show processlist, all connection IDs show 'sleep' except one) the mysqld process in top never shows more than 100% (or never more than 105-110% to be absolutely truthful) When multiple queries are active in MySQL the mysqld process frequently approaches 200% (assuming each can hit 100% when run on it's own) When a single MySQL query is active and another heavy load process is running on the machine (e.g. running rsync on a big directory) mysqld will go to 100% and the other process will approach the level it would hit without mysqld running This is on a G4 1.42GHz dual proc running OS X and hooked up to an XServe RAID. My conclusions from this were that MySQL on OS X cannot use more than one processor for a single query, but it uses multiple ones fine when it has multiple queries to process. Also, it performs fine sharing the two processors with other applications. Can MySQL use multiple processors for a single query on other OS's? It's pretty key for us as we tend to run relatively few, long duration queries as opposed to lots of quick ones. Thanks, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pageouts
John May wrote: On OS X, when issuing the top command in the CLI, my pageouts value is: large number (0) eg: the number in parentheses is zero, but the other number is large. Is this a sign of a problem? It's a rather busy server with around 80 databases totalling 500MB or so. If this is a problem, how do I resolve it? It's not a problem. The large number is the number of pageouts since restart, the number in parentheses is the number of pageouts in the last second. If you see the number in parentheses is non-zero frequently, then you're havinig paging. In this case you should set up and edit a my.cnf file to tune the memory parameters of mysqld so it doesn't page. The details of top can be found by typing 'man top' in the terminal window. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
Andy Callan wrote: When i try to startup the mysqld it works but then I get msqld ended immediately afterwards, I tried to follow the two posts about that with the online documentation with no luck. If you need anymore info just let me know, thanks a lot for your response. Find the error log (which will be called machine-name.err and will be in your data directory) and see what it says. It generally prints an error that pretty much tells you what you need to do. I don't know where the package installer puts the data directory on OS X, but it might by /usr/local/mysql/data --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
Jonathan Hilgeman wrote: Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? It's going to depend of in your queries are disk bound or processor bound. Check the processor usage when a big query is running (and not much else is running). If the CPU usage is at 80-100% the query is processor bound, so you'll see some improvement (though I can't tell you how much, other than the clock rate scaling). On the other hand, if the CPU usage is low, you are likely disk bound, so a faster processor won't help much. It would be better to add RAM, tune your queries or get faster disks (or all three) in this case. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
Jeremy Zawodny wrote: What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? The client will be disconnected and thread closed. That's what I thought was supposed to happen, but we constantly wind up with sleeping connections longer than these values: +---+---+--++-++---+--+ | Id| User | Host | db | Command | Time | State | Info | +---+---+--++-++---+--+ | 1162 | carpenter | | a | Sleep | 40937 | | NULL | | 1818 | milberg | | b | Sleep | 45634 | | NULL | | 10460 | kokaz | | c | Sleep | 130282 | | NULL | +---+---+--++-++---+--+ +--+---+ | Variable_name| Value | +--+---+ | interactive_timeout | 600 | | wait_timeout | 600 | +--+---+ I think these are clients who have left either the mysql command line client open on their machine or have remained logged in via phpmyadmin. Eventually they go away, but that seems to be after they log out. Shouldn't these connections be shut down, or is there some other variable or option that I need to check. Thanks, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
Jonathan Hilgeman wrote: Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? It's going to depend of in your queries are disk bound or processor bound. Check the processor usage when a big query is running (and not much else is running). If the CPU usage is at 80-100% the query is processor bound, so you'll see some improvement (though I can't tell you how much, other than the clock rate scaling). On the other hand, if the CPU usage is low, you are likely disk bound, so a faster processor won't help much. It would be better to add RAM, tune your queries or get faster disks (or all three) in this case. [EMAIL PROTECTED] wrote: scuse the ignorance are u saying high cpu usage is better used on one query or better be freed up for other queries ? ram is always an issue as the queries get buffered I'm saying that if your current machine has the mysqld CPU utilization at 100% then what's keeping your queries from going faster is that you've run out of CPU capacity, so adding more (via a faster processor) will make the query go faster. If the mysqld CPU utilization is at 25% then the speed of the processor is not what's making the query go slow (you still have processor capacity left to use, but you can't utilize it because something else is slowing you down). In this case a faster processor won't help much...the processor isn't the limiting factor. This assumes you're doing the benchmarking with nothing else running, obviously. If MySQL is at 25% but you have other programs running heavily it doesn't tell you much, but if it's at 25% and the rest of the capacity is idle then the CPU isn't the problem. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using data in one table to update data in another table
Chris Nighswonger wrote: Hi All, Is it possible in MySQL to use UPDATE to update fields in one table with data from another table? For example, the following code seems to be the answer but fails with a syntax error in MySQL: UPDATE test_demo1.products SET nontaxable=(SELECT IMFSalesTax FROM IDX.InvMasFile); Multi table update is available if you're using version 4.0.4 or higher: http://www.mysql.com/doc/en/UPDATE.html --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Include Check and Repair in my.ini
Victor Pendleton wrote: You could create your own script that runs myisamchk then after completing starts the MySQL service and place this in the windows startup folder. -Original Message- From: Luis Lozano [mailto:[EMAIL PROTECTED] How can i check and repair DBs, before service in WIN32 started? I try to include group [myisamchk] in my.ini files, but nothing happen. How can i include it? Not sure if this is the case in Windows, but on OS X's version of mysqld_safe there is a line that you can uncomment that will automatically run myisamchk whenever mysqld is started: # Uncomment the following lines if you want all tables to be automaticly # checked and repaired at start # # echo Checking tables in $DATADIR # $MY_BASEDIR_VERSION/bin/myisamchk --silent --force --fast --medium-check -O key_buffer=64M -O sort_buffer=64M $DATADIR/*/*.MYI # $MY_BASEDIR_VERSION/bin/isamchk --silent --force -O sort_buffer=64M $DATADIR/*/*.ISM (two of those lines wrapped). Assuming you only have MyISAM tables (and no ISAM ones) just uncomment the first line that starts $MY_BASEDIR_VERSION/bin/myisamchk InnoDB will handle this on it's own. Of course this could all be different on Windows, if so sorry to waste your time. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with Mac OS X install
I think you have some slight errors in your connection line: Werner van Mook wrote: /usr/local/mysql/bin/mysqladmin -u root password password should be: /usr/local/mysql/bin/mysqladmin -u root --password=password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password should be /usr/local/mysql/bin/mysqladmin -u root -h localhost --password=password Then I replaced localhost with 172.0.0.1 and I got : localhost's IP address is 127.0.0.1, so this should be: /usr/local/mysql/bin/mysqladmin -u root -h 127.0.0.1 --password=password Also, you're running mysqladmin with no commands, which will just output the help pages. Do you mean to be running the mysql command line client to interact with the mysqld server? If so replace mysqladmin in all of the above with mysql --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). mysqldump is nice b/c it works for both InnoDB and MyISAM mysqlhotcopy makes a copy of the actual data files in your database. It is much faster than mysqldump, but the resulting backup is larger b/c it contains indices (unless you use the option to turn them off). Recovery is quicker as the tables exist in the backup directory in full MyISAM table form. With mysqldump you need to actually execute the dump files which can take a while for large tables. mysqlhotcopy does not work with InnoDB. --Ware Adams Jake Johnson wrote: Is this better than using mysqldump? mysqlhotcopy does your locking for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hot backup in mysql
InnoDB Hot Backup is non-free and works only with InnoDB. mysqlhotcopy is free and works with MyISAM tables but not InnoDB. mysqldump is free and works with both. The easiest thing to do is use mysqldump...you'll get text files that contain create and insert statements allowing you to restore you tables in MySQL. It's easy b/c it works with both major table types. The issue is that it's much slower then mysqlhotcopy (and presumably InnoDB Hot Backup). --Ware alx wrote: Hi all I'm new to this part of mysql. I've never done somme kind of backup/dump with mysql I read that for myIsam I can Just copy the frm, idx form the database directory But I've to manage hot backup with a database mixed with InnoDB tables and MyIsam. I searched for a tutorial / a software or any other kinda of information But there is something I don't understand: Mysql HOt Backup is a non-free solution Does it works onyl with InnoDB or also with MyIsam Can Someone suggest me some way to follow ? TIA Alx -- alx [EMAIL PROTECTED] -- 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]
Re: Data file size
Yes there is, that's what Paul was referring to regarding InnoDB...it has a table space made up of multiple files on the disk and the tables reside within the tablespace. Thus the tables are not bound by the file system's maximum file size. Details are in the MySQL manual in the table types section: http://www.mysql.com/doc/en/InnoDB.html or at InnoDB.com: http://www.innodb.com/ --Ware Titu Kim wrote: Hi, Thanks for the fast response. I wonder there is a 'tablespace' notion in mysql just like the one in Oracle. One can keep adding datafile from different disk to the same tablespace and don't have to worry about how the data is stored in the files. Thanks again. --- Paul DuBois [EMAIL PROTECTED] wrote: At 18:05 -0500 6/6/03, Paul DuBois wrote: At 15:59 -0700 6/6/03, Titu Kim wrote: 2. How can i add another file to a table if the .MYD file grows too large? Once the file size reaches its maximum, that's as far as you can go. I should add to this that one way to obtain an effective larger file size is to use the RAID options at CREATE TABLE size. Requires that RAID support is compiled into your server, though. Or perhaps you could use an InnoDB table, which allows very large table sizes that are not limited by the size of a single file. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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]
Stuck (unkillable) queries
Hello, I'm having an odd problem with my MySQL server that I thought someone might have some ideas on. Setup: MySQL 4.0.10-max Mac OS X Server 10.2.3 DP G4 1.25 GHz 1G RAM MySQL data and temp files on 400G striped RAID array (off an Acard hardware controller) that is 90% empty Clients accessing through a mix of command line, phpMyAdmin (just upgraded to 2.4.0rc1, this occurred under 2.3.3pl1 and previous versions) and php scripts PHP install on machine is 4.1.2 (the one that ships with OS X server) We have about 10 users running queries on a hundred or so databases that collectively have about 7G of data. The largest is 2.5 million records/600MB. What happens: Occasionally (every thousand queries or so) a user will run a query which they then try to kill (usually b/c they realize it will take forever to finish due to a mistake of their's). The query gets marked as killed, but it never actually dies, show processlist just shows it at killed even if we allow it to sit for days. Here's an early processlist of one (shortened) that stayed this way for 24 hours before I rebooted the server: +--+--+-+---+-+--+--+--+ | Id | User | Host| db| Command | Time | State| Info | +--+--+-+---+-+--+--+--+ | 4429 | tsou | dcen| constr| Killed | 2084 | Sending data | | The query that did this was: insert into tsou.new_table select cust_name, id, sales, abbreviation from tsou.customers, tsou.state where customers.id = state.id and state.abbreviation 'LA' Customers has about 800,000 records in it, and as it turns out none of them had a state.abbreviation of LA, so the output should have been 800,000 records. All of the tables are MyISAM. The user realized after sending the query that he hadn't indexed the two tables, so he decided to kill the query and then index (this is a query he'll be doing regularly). He issued the query from phpMyAdmin, and unfortunately the version he was using (2.3.3pl1) had a bug that prevented the display of the show processes link. So he opened a command line connection to the MySQL server (using the Win 2000 client) and issued a kill command for the query. The query had written about 250,000 rows (we know after the restart), but it wound up in the state shown above. It didn't seem to be consuming any resources as the mysqld process under top showed 0% cpu usage when this was the only query in the process list. Still, it locked us out of the database it was writing which meant it wouldn't allow our cron job to run mysqlhotcopy and backup all the databases. At this point we decided to try killing it with mysqladmin which didn't work, so we tried to kill -9 mysqld from the Mac OS X command line. This froze the machine and required a hard restart. So I was wondering if this sounded familiar to anyone and if there are any known bugs or work arounds for it. I think that every time it has happened the query has been issued from phpMyAdmin and then the kill has been issued from the command line. I'm wondering if this or some bug in php or phpMyAdmin is causing confusion. Failing any way to avoid these stuck queries in the first place, is there a way to successfully kill them so our backup can run? Or failing that is there a way to kill off mysqld without rebooting the machine? Sorry to be so long winded, but if you've read this far hopefully the detail has been somewhat helpful. Thanks for any help you can give, Ware - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
