Re: WL#946 and Changing time literal format
Hi! Roy == Roy Lyseng roy.lys...@sun.com writes: cut The question here is how PostgreSQL and ANSI does this and also what is the logical interpretation of the number. Roy ISO 9075 (ANSI SQL) is very strict about this. It only allows TIME Roy literals with 3 or 4 digit groups, and it only allows the ':' separator Roy (except after the seconds part). There is no possibility for ambiguity, Roy as the first number is always interpreted as an hour field. Roy This is a literal format that is seen only by the SQL programmer, so Roy there is no need for extensions. Date values provided by end users need Roy to go through localization features, so that could be a different story. What is more important than ANSI is how our users are using TIME now and how they want to use it in the future. There is nothing wrong in making things easier for the end user by using a relaxed way to read in time constants. We don't want to break working applications that are already used to use our relaxed time format to read data. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Bernt == Bernt M Johnsen bernt.john...@sun.com writes: Michael Widenius wrote (2009-01-24 02:07:54): As Dmitri pointed out, we shouldn't deprecate '.' as substitute for dates. Another things is that we should stop making decisions about incompatible changes without listening to the MySQL users. They know more than we how MySQL is used and they are directly affected of any incompatible change we force upon them. Bernt Ok. And what is the users' verdict? Do they want a helpful best Bernt effort interpretation of time and dates or do they want a well-defined Bernt standardized portable scheme which reduces the possibilities of bugs? Normally they don't want their existing applications to break. Bernt And what does this helpfullness lead to? In Norway it is common to Bernt write dates as DD.MM.YY. So a buggy program that accepts 01.02.03 (for Bernt the date 2003-02-01) whould be able to insert it into MySQL without Bernt errors, but when retrieved the value is 2001-02-03. I don't think the Bernt user/programmer is happy with that. They will quickly notice this and fix their time order. In reality we haven't got many complains from Norway about this, so I assume they are smart enough in Norway to not run into this problem. Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Bernt We have a Norwgeian word for this helpfullness: bjørnetjeneste, but Bernt I'm not sure what the english idiom would be. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Michael Widenius wrote: Bernt We have a Norwgeian word for this helpfullness: bjørnetjeneste, but Bernt I'm not sure what the english idiom would be. A disservice. In German Bärendienst. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
* Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Monty, I disagree with this statement. Our current users use the current versions of the server. It's a separate question of what support we're willing to give them and for how long. In the new versions we should hold high the expectations of new users, and they are about standard compliance, and also about ease of migration. sql_modes are not a solution since they make the server code a mess, and won't let us make everyone happy anyway. MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. What's worse, is that while we're fighting internally when to make an incompatible change and when not, our change management process is a mess. We introduce incompatible changes in every major release, so people are forced to migrate their applications manually again and again. And yet we can't plan our changes in a way that a bulk incompatible changes in a certain area are done at once, forcing people to look into the problem once only, rather than on every upgrade. It's a pity we can't shift our focus and mental efforts from developing a shared understanding what incompatible changes are right and called for, to developing the best way of making changes. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Konstantin Osipov wrote: * Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Monty, I disagree with this statement. Our current users use the current versions of the server. It's a separate question of what support we're willing to give them and for how long. In the new versions we should hold high the expectations of new users, and they are about standard compliance, and also about ease of migration. Full ack! IMO, offering a variety of input formats just creates one big mess. How often have you read some date notation and wondered which format was used - if all values are in the 1 to 12 range, you have to guess. [[...]] MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. Being stricter on input comes with small costs but huge benefits (not only to us but also to end users), and we should be able to get that message to our users and customers. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: questions about merging
Robert, keep the two databases separated, that is on different mysql database (or schema as they are also called). If the wordpress blog is on a database named 'wpblog' for example import your dump in another newly created database. Could just use those statement in the dump file CREATE DATABASE `db169254900`; USE db169254900; it'll create and use a new database (logic separation / phisical different folders) You can have all the databases you want in MySQL, they are just logical separation between tables, and with MyISAM tables also phisical separation thru folders. cheers Claudio 2009/1/29 Robert D. Crawford rd...@comcast.net Hello, Olaf, Stein, Olaf olaf.st...@nationwidechildrens.org writes: the import of the dump will not remove your other tables unless there is a naming conflict, then the table will be overwritten. As far as the prefix goes you can edit the dump file, find the create table statement for each table and add the prefix. If you have lots of tables or a huge dump file it might make sense to use a more automated approach, perl or python scripts will work on most platforms. The prefix will also make sure you have no naming conflicts I guess I figured that would be the case but I really needed to make sure. The file is huge but judicious use of head, emacs, and cat should do the job just fine without having to write something. One other question concerns these lines: CREATE DATABASE `db169254900`; USE db169254900; I can just remove the CREATE DATABASE line and change the USE line to reflect the name of the current db, right? Thanks for your help. My partner will be rather happy to have her wiki up. rdc -- Robert D. Crawford rd...@comcast.net Laugh at your problems; everybody else does. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
mysql 5 performance
Sorry if this seems to be a double post, but not sure if the mysql list send yourself a copy (as I didn't get the 1st one). But a little has changed, not much, but basically I have an old fc5 machine running mysql 4.1 and a newer server running centos5 / mysql 5.0.45. The whole site is almost unusable now (the older server is having hard drive issues and being replaced), and I think it's just a mysql 5 startup or config change but I am not sure. Our test page shows the queries with the query times, and here is just one admin page; *mysql4:* getting all product infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (15 sec). I don't know but guessing now, mysql 5 shows the following 2 items running on a ps list; /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock Questions; 1.Do I need external-locking skipped? 2.Are there tools like mtop for mysql 5 to monitor what's going on? The box (even on the 200+ second page loads) is sitting at 95% idle with no real load. 3. MySql 4 process list shows an actual tree; 19188 ?S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/classic.pid 19219 ?S 0:03 \_ [mysqld] 19220 ?S 0:05 \_ [mysqld] 19221 ?S 0:00 \_ [mysqld] 19222 ?S 0:00 \_ [mysqld] 19223 ?S 0:00 \_ [mysqld] 19224 ?S 0:00 \_ [mysqld] 19225 ?S 0:00 \_ [mysqld] 19226 ?S 0:06 \_ [mysqld] 19227 ?S 0:00 \_ [mysqld] 19228 ?S 0:02 \_ [mysqld] I am not sure, but does that mean it's multi-threading allowing multiple things to be done at once? If so, is that possible on mysql5. I may be way off on one or all, but at least some sort of reply is appreciated. It's been 3 day's, I see some of the questions being thrown out and answered so I know it's an active list, so am I assuming no-one has upgraded from 4 to 5 and seen this? Anything to help me start fixing this is apprecited. Lr
Re: questions about merging
I think he only has one DB, I guess that is a provider restriction... On 1/30/09 8:52 AM, Claudio Nanni claudio.na...@gmail.com wrote: Robert, keep the two databases separated, that is on different mysql database (or schema as they are also called). If the wordpress blog is on a database named 'wpblog' for example import your dump in another newly created database. Could just use those statement in the dump file CREATE DATABASE `db169254900`; USE db169254900; it'll create and use a new database (logic separation / phisical different folders) You can have all the databases you want in MySQL, they are just logical separation between tables, and with MyISAM tables also phisical separation thru folders. cheers Claudio 2009/1/29 Robert D. Crawford rd...@comcast.net Hello, Olaf, Stein, Olaf olaf.st...@nationwidechildrens.org writes: the import of the dump will not remove your other tables unless there is a naming conflict, then the table will be overwritten. As far as the prefix goes you can edit the dump file, find the create table statement for each table and add the prefix. If you have lots of tables or a huge dump file it might make sense to use a more automated approach, perl or python scripts will work on most platforms. The prefix will also make sure you have no naming conflicts I guess I figured that would be the case but I really needed to make sure. The file is huge but judicious use of head, emacs, and cat should do the job just fine without having to write something. One other question concerns these lines: CREATE DATABASE `db169254900`; USE db169254900; I can just remove the CREATE DATABASE line and change the USE line to reflect the name of the current db, right? Thanks for your help. My partner will be rather happy to have her wiki up. rdc -- Robert D. Crawford rd...@comcast.net Laugh at your problems; everybody else does. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WL#946 and Changing time literal format
IMO, offering a variety of input formats just creates one big mess. How often have you read some date notation and wondered which format was used - if all values are in the 1 to 12 range, you have to guess. [JS] I agree 100%. I have to deal with dates from all over the world, and I often have to guess at the originator's format. Also, flexible input is a precursor to a demand for flexible output; before long you'd have as many input formats and output formats as you have collation options, and it would take serious research to figure out what was going on. [[...]] MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. Being stricter on input comes with small costs but huge benefits (not only to us but also to end users), and we should be able to get that message to our users and customers. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 performance
On Fri, Jan 30, 2009 at 9:32 AM, lance raymond lance.raym...@gmail.com wrote: basically I have an old fc5 machine running mysql 4.1 and a newer server running centos5 / mysql 5.0.45. So, different hardware, different OS, different database server... could be anything. I suggest you run EXPLAIN plans for the slow queries on both servers and compare them. The most likely reasons for a difference that large are missing indexes or vastly different filesystem performance. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL University session on February 5: MySQL Performance and Scalability Project - Issues and Opportunities
MySQL Performance and Scalability Project - Issues and Opportunities http://forge.mysql.com/wiki/MySQL_Performance_and_Scalability_Project_-_Issues_and_Opportunities Next Thursday (February 5th), we're continuing our series of sessions on MySQL performance measuring and improvements with Allan Packer's presentation titled MySQL Performance and Scalability Project - Issues and Opportunities. Allan works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server. Allan is based in Australia, so note that this session will take place in the morning (Europe) or evening (APAC), respectively. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks (see http://forge.mysql.com/wiki/MySQL_University for a better format of this list): February 5, 200908:00 UTC / 8:00 GMT / 9:00 CET / 11:00 MDT (Moscow) / 13:30 IST (India) / 16:00 CST (Beijing) / 17:00 JST (Tokyo) / 19:00 EDT (Melbourne) MySQL Performance and Scalability Project - Issues and Opportunities Allan Packer February 12, 2008 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Using DTrace with MySQL MC Brown February 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Developing MySQL on Solaris MC Brown Trond Norbye February 26, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Backing up MySQL using file system snapshotsLenz Grimmer March 5, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow)Good Coding Style Konstantin Osipov March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown The session address (Dimdim URL) for all sessions is: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity Please bookmark this address, since it will remain valid for all future MySQL University sessions. Remember, though, that the meeting room will open only 15 minutes before the session starts. Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording (slides and voice), and more. All you need to do to attend MySQL University sessions is point your browser to the address given above. All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash file (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page: http://forge.mysql.com/wiki/MySQL_University Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 performance
creating product tablesdone (273 sec). Can you tell us what queries this code actually executes? This doesn't tell us too much ;) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Fri, Jan 30, 2009 at 3:32 PM, lance raymond lance.raym...@gmail.com wrote: Sorry if this seems to be a double post, but not sure if the mysql list send yourself a copy (as I didn't get the 1st one). But a little has changed, not much, but basically I have an old fc5 machine running mysql 4.1 and a newer server running centos5 / mysql 5.0.45. The whole site is almost unusable now (the older server is having hard drive issues and being replaced), and I think it's just a mysql 5 startup or config change but I am not sure. Our test page shows the queries with the query times, and here is just one admin page; *mysql4:* getting all product infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (15 sec). I don't know but guessing now, mysql 5 shows the following 2 items running on a ps list; /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock Questions; 1.Do I need external-locking skipped? 2.Are there tools like mtop for mysql 5 to monitor what's going on? The box (even on the 200+ second page loads) is sitting at 95% idle with no real load. 3. MySql 4 process list shows an actual tree; 19188 ?S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/classic.pid 19219 ?S 0:03 \_ [mysqld] 19220 ?S 0:05 \_ [mysqld] 19221 ?S 0:00 \_ [mysqld] 19222 ?S 0:00 \_ [mysqld] 19223 ?S 0:00 \_ [mysqld] 19224 ?S 0:00 \_ [mysqld] 19225 ?S 0:00 \_ [mysqld] 19226 ?S 0:06 \_ [mysqld] 19227 ?S 0:00 \_ [mysqld] 19228 ?S 0:02 \_ [mysqld] I am not sure, but does that mean it's multi-threading allowing multiple things to be done at once? If so, is that possible on mysql5. I may be way off on one or all, but at least some sort of reply is appreciated. It's been 3 day's, I see some of the questions being thrown out and answered so I know it's an active list, so am I assuming no-one has upgraded from 4 to 5 and seen this? Anything to help me start fixing this is apprecited. Lr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INNODB and Max Processors
A few weeks back I was reading an article that said that INNODB doesn't take adantage of servers using more than 4 processors. I think I also recieved this as a reply some time ago as to the same thing. I was wondering if this is indeed true. We are using 5.1.30 and wanted to pickup a new dual quad core with 32GB. Before we make the purchase we just want to make sure the database will be able to take advantage of it. Otherwise we will go for the dual core higher speed. This will support hundreds of connections per second and some complicated queries. Overall the data will be less than 50gb so we are looking at more ram to hope that it will support both application and os level caching. Any advice would be greatly appreciated. Gary
ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar
I am banging my head. First, is not a file permission problem. I cant import data on some replication slaves either in binary, or from a sql mysqldump. I use one innodb file per table and I am importing only two databases on a dozen. After a few hours seem that the problem is with the name of the database, since I can import on a different database/directory. I tried to import only the DDL(from the master where the dump comes from) to recreate all the tables to 'refresh' the information schema, then stop the DB, copy the .ibd datafiles in the directory, start the DB, and if I do a DESC dummy get: --- ERROR 1146 (42S02): Table 'foobar.dummy' doesn't exist --- If I drop the database I get the error in the subject --- ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar --- If I try to import from the SQL dump: --- ERROR 1005 (HY000) at line 23: Can't create table './foobar/dummy.frm' (errno: -1) --- I am stuck, any help really really aprreciated! Cheers Claudio
Re: ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar
* check on your filesystem if the directory actually still exists - rmdir on a nonexisting dir might throw a non-zero exit * Take your db offline and do an fsck. If you want, you could flush tables with read lock, then mount -oremount,ro and then fsck - that'll keep your db up for reads, at least. DON'T let fsck fix things that way, though - for that you really do need to shut down the db and unmount the disk. On Fri, Jan 30, 2009 at 7:29 PM, Claudio Nanni claudio.na...@gmail.comwrote: I am banging my head. First, is not a file permission problem. I cant import data on some replication slaves either in binary, or from a sql mysqldump. I use one innodb file per table and I am importing only two databases on a dozen. After a few hours seem that the problem is with the name of the database, since I can import on a different database/directory. I tried to import only the DDL(from the master where the dump comes from) to recreate all the tables to 'refresh' the information schema, then stop the DB, copy the .ibd datafiles in the directory, start the DB, and if I do a DESC dummy get: --- ERROR 1146 (42S02): Table 'foobar.dummy' doesn't exist --- If I drop the database I get the error in the subject --- ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar --- If I try to import from the SQL dump: --- ERROR 1005 (HY000) at line 23: Can't create table './foobar/dummy.frm' (errno: -1) --- I am stuck, any help really really aprreciated! Cheers Claudio -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: questions about merging
Olaf Stein olaf.st...@nationwidechildrens.org writes: I think he only has one DB, I guess that is a provider restriction... True. I would create a separate db for each application if I could. rdc -- Robert D. Crawford rd...@comcast.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar
Hi Johan, dirs and files exist (also chmodded to 777) the strange thing is that this behaviour is on more than one slave, I think it is a bug related to the Information Schema and to the fact I use innodb_file_per_table. Not being able to drop a database is definitely a bug, it seems that somewhere it's locked. Thanks, I continue investigating. C. PS: MySQL is 5.0.45 Johan De Meersman wrote: * check on your filesystem if the directory actually still exists - rmdir on a nonexisting dir might throw a non-zero exit * Take your db offline and do an fsck. If you want, you could flush tables with read lock, then mount -oremount,ro and then fsck - that'll keep your db up for reads, at least. DON'T let fsck fix things that way, though - for that you really do need to shut down the db and unmount the disk. On Fri, Jan 30, 2009 at 7:29 PM, Claudio Nanni claudio.na...@gmail.com mailto:claudio.na...@gmail.com wrote: I am banging my head. First, is not a file permission problem. I cant import data on some replication slaves either in binary, or from a sql mysqldump. I use one innodb file per table and I am importing only two databases on a dozen. After a few hours seem that the problem is with the name of the database, since I can import on a different database/directory. I tried to import only the DDL(from the master where the dump comes from) to recreate all the tables to 'refresh' the information schema, then stop the DB, copy the .ibd datafiles in the directory, start the DB, and if I do a DESC dummy get: --- ERROR 1146 (42S02): Table 'foobar.dummy' doesn't exist --- If I drop the database I get the error in the subject --- ERROR 1010 (HY000): Error dropping database (can't rmdir ./foobar --- If I try to import from the SQL dump: --- ERROR 1005 (HY000) at line 23: Can't create table './foobar/dummy.frm' (errno: -1) --- I am stuck, any help really really aprreciated! Cheers Claudio -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INNODB and Max Processors
Gary, I need to know a lot about your workload to say whether it will work well on InnoDB with 4+ processors. You can check http://www.mysqlperformanceblog.com/ for a lot of benchmarks in this area. But in general, my opinion is that for most workloads, 4 total processors (cores included) is reasonable. Not as good as it could be, but reasonable. The only real answer is to benchmark *your* workload and see what happens. And if you run into something that's a weak area, change it -- there are workarounds for many of the trouble spots. However, note that a single query will only ever run on a single core, so if latency is your concern, you need fast, not many. Baron On Fri, Jan 30, 2009 at 12:32 PM, Gary W. Smith g...@primeexalia.com wrote: A few weeks back I was reading an article that said that INNODB doesn't take adantage of servers using more than 4 processors. I think I also recieved this as a reply some time ago as to the same thing. I was wondering if this is indeed true. We are using 5.1.30 and wanted to pickup a new dual quad core with 32GB. Before we make the purchase we just want to make sure the database will be able to take advantage of it. Otherwise we will go for the dual core higher speed. This will support hundreds of connections per second and some complicated queries. Overall the data will be less than 50gb so we are looking at more ram to hope that it will support both application and os level caching. Any advice would be greatly appreciated. Gary -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 performance
Hi Lance, Please post specific queries and EXPLAIN output with the \G terminator. It's likely your queries that are the problem, and looking at 'ps' doesn't help you much. On Fri, Jan 30, 2009 at 9:32 AM, lance raymond lance.raym...@gmail.com wrote: Sorry if this seems to be a double post, but not sure if the mysql list send yourself a copy (as I didn't get the 1st one). But a little has changed, not much, but basically I have an old fc5 machine running mysql 4.1 and a newer server running centos5 / mysql 5.0.45. The whole site is almost unusable now (the older server is having hard drive issues and being replaced), and I think it's just a mysql 5 startup or config change but I am not sure. Our test page shows the queries with the query times, and here is just one admin page; *mysql4:* getting all product infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (15 sec). I don't know but guessing now, mysql 5 shows the following 2 items running on a ps list; /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock Questions; 1.Do I need external-locking skipped? 2.Are there tools like mtop for mysql 5 to monitor what's going on? The box (even on the 200+ second page loads) is sitting at 95% idle with no real load. 3. MySql 4 process list shows an actual tree; 19188 ?S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/classic.pid 19219 ?S 0:03 \_ [mysqld] 19220 ?S 0:05 \_ [mysqld] 19221 ?S 0:00 \_ [mysqld] 19222 ?S 0:00 \_ [mysqld] 19223 ?S 0:00 \_ [mysqld] 19224 ?S 0:00 \_ [mysqld] 19225 ?S 0:00 \_ [mysqld] 19226 ?S 0:06 \_ [mysqld] 19227 ?S 0:00 \_ [mysqld] 19228 ?S 0:02 \_ [mysqld] I am not sure, but does that mean it's multi-threading allowing multiple things to be done at once? If so, is that possible on mysql5. I may be way off on one or all, but at least some sort of reply is appreciated. It's been 3 day's, I see some of the questions being thrown out and answered so I know it's an active list, so am I assuming no-one has upgraded from 4 to 5 and seen this? Anything to help me start fixing this is apprecited. Lr -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 new install help requested
You didn't get a response on this one because you didn't really ask any questions :-) Baron On Thu, Jan 29, 2009 at 4:38 PM, lance raymond lance.raym...@gmail.com wrote: Man, the forums are good, but lists are just so much easier! Anyway, I left post unanswered now and I need to get this problem solved, so I am hoping this list is more direct. Basically a fc5/mysql4 server is having hard drive errors. We had a slightly newer/faster server, so I staged it with centOS5, mysql5. The server is expanentially slower, and I am trying to see if there is anything db wise I can look / tweak. Basically the mysql 4 server looks to have numerous threads running see the ps snapshot; 25243 ? S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/classic.pid 25274 ? S 0:25 \_ [mysqld] 25275 ? S 0:37 \_ [mysqld] 25276 ? S 0:00 \_ [mysqld] 25277 ? S 0:00 \_ [mysqld] 25278 ? S 0:00 \_ [mysqld] 25279 ? S 0:00 \_ [mysqld] 25280 ? S 0:00 \_ [mysqld] 25281 ? S 0:03 \_ [mysqld] 25282 ? S 0:00 \_ [mysqld] 25283 ? S 0:15 \_ [mysqld] The mysql 5 shows the following running; root 10245 0.0 0.0 4472 1092 pts/0 S 23:13 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log mysql 10281 5.8 1.1 141544 24028 pts/0 Sl 23:13 0:19 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid *--skip-locking*--socket=/var/lib/mysql/mysql.sock I first tried to start it by hand with the slow-log options, but never saw a file. Then tried in the my.cnf file and still nothing and also noticed the skip-locking option running where I don't see it in the startup. So all the above comes down to this question(s). Should mysql5 with a generic rpm install run this slow compared to mysql 4. A query that takes 24 seconds is taking more than 5 minutes and I am wondering if it's a thread issue, etc. as this is a live webserver with hundreds on in/out connections. Thanks on getting me started on this one -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
/var/log/mysql is taking up a lot of diskspace
Hi, I am running mysql 5.0.32 on debian etch on a VPS. The /var/log/mysql directory has grown to 5.8G, over 25% of the diskspace on my server. Can the contents be deleted saftely? Can mysql be configured to keep the log directory within, for example, 500mb (or whatever is considered reasonable)? Many Thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: /var/log/mysql is taking up a lot of diskspace
ok, cool. I was not sure if I could use logrotate with the mysql logs. Is it safe to delete what I have there at the moment? I need to free up some disk space. On Sat, Jan 31, 2009 at 5:07 PM, Bazooka Joe fastf...@gmail.com wrote: On Fri, Jan 30, 2009 at 7:54 PM, will will.va...@gmail.com wrote: Hi, I am running mysql 5.0.32 on debian etch on a VPS. The /var/log/mysql directory has grown to 5.8G, over 25% of the diskspace on my server. Can the contents be deleted saftely? Can mysql be configured to keep the log directory within, for example, 500mb (or whatever is considered reasonable)? Many Thanks, Will most people use logrotate http://linuxcommand.org/man_pages/logrotate8.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: /var/log/mysql is taking up a lot of diskspace
On Fri, Jan 30, 2009 at 8:13 PM, will will.va...@gmail.com wrote: ok, cool. I was not sure if I could use logrotate with the mysql logs. Is it safe to delete what I have there at the moment? I need to free up some disk space. this is what logrotate run before and after [ -e /var/lock/subsys/mysqld ] /bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2 /dev/null ` || /bin/true Assuming paths are the same I would run the above commands. Short of that I would take down mysql, delete log, then bring mysql back up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
