RE: Question about MySQL
Hi Banyan, I'm really just talking about basic optimization techniques: 1. Install lots of RAM 2. Convert all table to innodb 3. Allocate about 80% of memory to innodb_buffer_pool_size If you haven't seen this script yet, I suggest you start here: https://launchpad.net/mysql-tuning-primer Regards, Gavin Towey -Original Message- From: Banyan He [mailto:ban...@rootong.com] Sent: Friday, August 07, 2009 11:12 AM To: Gavin Towey; joerg.bru...@sun.com; Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Gavin, I am interested in the things you made for the optimization. Can you share with us such things? Thanks a lot, -- Banyan He Network System Security Infrastructure Mail: ban...@rootong.com Blog: http://www.rootong.com/blog LinkedIn: http://www.linkedin.com/in/banyanhe Website: http://www.rootong.com From: Gavin Towey gto...@ffn.com Date: Fri, 7 Aug 2009 11:07:19 -0700 To: joerg.bru...@sun.com joerg.bru...@sun.com, Peter Chacko peterchack...@gmail.com Cc: mysql mysql@lists.mysql.com Subject: RE: Question about MySQL I always accepted that NFS was unacceptably slow for database access, until I actually tested it. Turns out that with lots of RAM and properly tuned caches, you're optimizing for minimal IO anyway. A good file server will have massive amounts of IO OPS. On top of that if you're using GigE over few hops, then it's really not slower than local disks. Remember: benchmark and test your assumptions! Regards, Gavin Towey -Original Message- From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Sent: Friday, August 07, 2009 1:19 AM To: Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers
Re: Question about MySQL
Hi all! First of all, please excuse the typo I made in my posting. I had written There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), and of course meant SAN, not NAS systems. As regards NFS: Peter Chacko wrote: And NFS is becoming better and better with the adoption of 10GbE, and NFSoRDMA ...i am sure at that point no body will complain about NFS performance for databases. And for a parallel database access, pNFS is also shaping up well. As NFS creators are now owned by ORACLE who themselves have developed technology like direct NFS, NFS and Database storage will be great buddies in future. thanks On Fri, Aug 7, 2009 at 11:37 PM, Gavin Toweygto...@ffn.com wrote: I always accepted that NFS was unacceptably slow for database access, until I actually tested it. Turns out that with lots of RAM and properly tuned caches, you're optimizing for minimal IO anyway. A good file server will have massive amounts of IO OPS. On top of that if you're using GigE over few hops, then it's really not slower than local disks. I immediately agree that NFS becomes faster due to technical progress, like any other part of computing. But however clever you optimize your NFS setup (including the network, the servers, ...), you always have the additional latency of the network and the NFS server (compared to the local disk). Remember: Database performance is not only about throughput, it is also about latency. So NFS can only be slower than a local disk, never the same turnaround time, let alone faster (assuming equal disks and machines, of course). Whether that is *too* slow is another question - depending on your software, your workload, and your hardware it may be fast enough. However, my main objection against using NFS for database storage is not performance, it is complexity: If your database server does not use local disks but NFS, then the network between the database server and the NFS server as well as that server suddenly become essential components for your database setup. As any component may fail, you increase the risk to your DB. You may reduce the individual risk by selecting better hardware, dual controllers, dual cabling, mirrored machines, ... as much as you like, the result will still be higher complexity and higher risks than if you had applied similar enhancements to your database server and its local disks. Remember: benchmark and test your assumptions! Agreed. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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: Question about MySQL
Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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: Question about MySQL
Hi Jorg, I really appreciate your help sharing your experience/thoughts. Yes, i fully concur with you, NFS is not designed for Databases. But you know there are Distributed SAN file systems (that use Direct IO to the SAN) are serving databases like DB2 in many installations for shared storage. NFS cannot match its performance...due to its added latency. But you know ORACLE has added a direct NFS support to its storage, that has no file system caching, and is far better than native NFS client driver. My goal here is to make NFS works much better, as a thin layer, optimized for Database IO by developing a new NFS stack in a clustered environment, as part of the clustering intelligence itself. If any one is aware of such efforts please share it with me ( That way we can avoid duplicate efforts and we can just complement what they are not doing ) Thanks On Fri, Aug 7, 2009 at 1:49 PM, Joerg Bruehejoerg.bru...@sun.com wrote: Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- Best regards, Peter Chacko NetDiox computing systems, Network storage OS training and research. Bangalore, India. www.netdiox.com 080 2664 0708 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Question about MySQL
I always accepted that NFS was unacceptably slow for database access, until I actually tested it. Turns out that with lots of RAM and properly tuned caches, you're optimizing for minimal IO anyway. A good file server will have massive amounts of IO OPS. On top of that if you're using GigE over few hops, then it's really not slower than local disks. Remember: benchmark and test your assumptions! Regards, Gavin Towey -Original Message- From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Sent: Friday, August 07, 2009 1:19 AM To: Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about MySQL
Hi Gavin, I am interested in the things you made for the optimization. Can you share with us such things? Thanks a lot, -- Banyan He Network System Security Infrastructure Mail: ban...@rootong.com Blog: http://www.rootong.com/blog LinkedIn: http://www.linkedin.com/in/banyanhe Website: http://www.rootong.com From: Gavin Towey gto...@ffn.com Date: Fri, 7 Aug 2009 11:07:19 -0700 To: joerg.bru...@sun.com joerg.bru...@sun.com, Peter Chacko peterchack...@gmail.com Cc: mysql mysql@lists.mysql.com Subject: RE: Question about MySQL I always accepted that NFS was unacceptably slow for database access, until I actually tested it. Turns out that with lots of RAM and properly tuned caches, you're optimizing for minimal IO anyway. A good file server will have massive amounts of IO OPS. On top of that if you're using GigE over few hops, then it's really not slower than local disks. Remember: benchmark and test your assumptions! Regards, Gavin Towey -Original Message- From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Sent: Friday, August 07, 2009 1:19 AM To: Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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=gto...@ffn.com The information contained in this transmission
Re: Question about MySQL
And NFS is becoming better and better with the adoption of 10GbE, and NFSoRDMA ...i am sure at that point no body will complain about NFS performance for databases. And for a parallel database access, pNFS is also shaping up well. As NFS creators are now owned by ORACLE who themselves have developed technology like direct NFS, NFS and Database storage will be great buddies in future. thanks On Fri, Aug 7, 2009 at 11:37 PM, Gavin Toweygto...@ffn.com wrote: I always accepted that NFS was unacceptably slow for database access, until I actually tested it. Turns out that with lots of RAM and properly tuned caches, you're optimizing for minimal IO anyway. A good file server will have massive amounts of IO OPS. On top of that if you're using GigE over few hops, then it's really not slower than local disks. Remember: benchmark and test your assumptions! Regards, Gavin Towey -Original Message- From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Sent: Friday, August 07, 2009 1:19 AM To: Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Peter, all, let me just concentrate on the NFS aspect: Peter Chacko wrote: [[...]] Another question is , whats the general experience of running MySQL servers on NFS shares ? I would *never* use NFS storage for any DBMS (except for some testing): NFS access is slower than local disk access, and it adds more components to the critical path. So your operations get slower, reliability decreases, and (in case of any trouble) analysis becomes more difficult. I cannot imagine any setup where you have a machine strong enough to run your DBMS on it, but not capable of driving sufficient local disks. The typical argument for having centralized disks serving many machines is based on economies of scale (huge disks), flexibility (partitioning), and centralized management (RAID replacement, backup). There may be some merit to this in a specialized setup (NAS systems - I'm not convinced of them, but don't claim expert knowledge about them), but IMO not using general-purpose machines and NFS. Whatever the economical advantages during normal operation may be, you should not forget the huge costs you would incur if any in-between component breaks and your database stops operating. This may be tolerable for some applications, depending on the required availability, but simply intolerable for others. We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. Sorry, I don't get the meaning of this in sufficient detail to comment. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? That may depend on the storage engine used. MyISAM uses a file-per-table approach, so must be run on a file system; InnoDB may be able to use a raw partition (I'm not sure there); for others, I can't tell. Will ocfs2 be better used for mySQL as well, in a clustered environment ? I strongly doubt it. AIUI, the big advantage of cluster file systems is that they allow all machines to access and modify shared data. With a DBMS, you don't want to share data across machines, you want the database to be accessed only be the one machine (possibly multi-CPU, but shared memory) running the database processes, because on that machine you have the various database caches. Then, that machine makes the data available to all clients, so you get a logical sharing on a higher protocol level (SQL). To have multiple machines accessing the same database storage, you would first need some protocol to ensure cache coherency, and that is not contained in MySQL (in the general server). To use MySQL on multiple machines for the same data, you set up replication. The alternative approach would be to use MySQL Cluster, which is designed to hold the data in main memory (for extremely low latency) and to use the disk only for backup purposes. I would appreciate if any one share with me their thoughts on this. My comments above are based on my experience during DBMS development (including distributed DBMS), but not on any financial calculations or DBA work. Weigh them with other answers. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named
Question about MySQL
Hello MySQL developers, Could any one tell me if MySQL's equivalent of PRO C concept in ORACLE? and Is there any equivalent for SQL *NET in MySQL? Other than C API/ODBC what are the other interfaces from client applications to MySQL database ? Another question is , whats the general experience of running MySQL servers on NFS shares ? We are working on developing a custom protocol for MySQL clustering that takes care of all file management as part of the database clustering protocol, rather than a storage engine feature. What are the likely setup of a database storage ? Is it on RAW partition or on a File system ? Will ocfs2 be better used for mySQL as well, in a clustered environment ? I would appreciate if any one share with me their thoughts on this. -- Best regards, Peter Chacko NetDiox computing systems, Network storage OS training and research. Bangalore, India. www.netdiox.com 080 2664 0708 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about mysql procedures
Hello, Can MySQL functions/stored procedures access database data? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mysql procedures
On Fri, Apr 11, 2008 at 2:15 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: Hello, Can MySQL functions/stored procedures access database data? Joshua D. Drake Yes. Is there something in particular you are looking to do? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mysql procedures
At 2:15 PM -0700 4/11/08, Joshua D. Drake wrote: Hello, Can MySQL functions/stored procedures access database data? Yes, with some limitations. You will want to read this section to see whether what you want to do is restricted: http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html For example, in a stored function, you cannot modify a table if the table is referenced by the statement that invokes the function. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mysql procedures
On Fri, 11 Apr 2008 14:52:30 -0700 Rob Wultsch [EMAIL PROTECTED] wrote: On Fri, Apr 11, 2008 at 2:15 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: Hello, Can MySQL functions/stored procedures access database data? Joshua D. Drake Yes. Is there something in particular you are looking to do? Just gathering information :) Thanks! Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question about MySQL installation
Dear, I have installed MySQL4.1. But why it can not startup when I open my computer? Also, how can I test MySQL can run or not? Thanks for your advice. Best Regards, Terry
RE: a question about MySQL installation
Suggest you review the MySQL documentation http://dev.mysql.com/doc/mysql/en/installing.html and in particular 2.3.13. Testing The MySQL Installation and 2.3.14. Troubleshooting a MySQL Installation Under Windows Bob -Original Message- From: Terry Leung [mailto:[EMAIL PROTECTED] Sent: Sunday, May 22, 2005 7:12 AM To: mysql@lists.mysql.com Subject: a question about MySQL installation Dear, I have installed MySQL4.1. But why it can not startup when I open my computer? Also, how can I test MySQL can run or not? Thanks for your advice. Best Regards, Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql start up again
Hi I have installed some software on aix5.2 . the list of software: apache-1.3.29-1.aix4.3.ppc.rpm php-4.0.6-5.aix4.3.ppc.rpm MySQL-3.23.47-3.aix4.3.ppc.rpm MySQL-client-3.23.47-3.aix4.3.ppc.rpm All of them was installed by using the command : rpm -i *.* then Apache and php can work but mysql can't run when I used the command safe_mysqld start the system told me Starting mysqld daemon with databases from /var/lib/mysql 040913 19:08:45 mysqld ended when I used the command mysql the system told me ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Many friends tell me to see the err log, the below is the err log(locaohost.err),but I can't find any useful things ,can anyone help me? 040915 19:14:06 mysqld started /opt/freeware/libexec/mysqld: unrecognized option `--key_buffer=16M' /opt/freeware/libexec/mysqld Ver 3.23.58 for ibm-aix5.1.0.0 on powerpc Copyright (C) 2000 MySQL AB, by Monty and others This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Starts the MySQL server Usage: /opt/freeware/libexec/mysqld [OPTIONS] --ansiUse ANSI SQL syntax instead of MySQL syntax -b, --basedir=pathPath to installation directory. All paths are usually resolved relative to this --big-tables Allow big result sets by saving all temporary sets on file (Solves most 'table full' errors) --bind-address=IP Ip address to bind to --bootstrap Used by mysql installation scripts --character-sets-dir=... Directory where character sets are --chroot=path Chroot mysqld daemon during startup --core-file Write core on errors -h, --datadir=pathPath to the database root --default-character-set=charset Set the default character set --default-table-type=type Set the default table type for tables --delay-key-write-for-all-tables Don't flush key buffers between writes for any MyISAM table --enable-locking Enable system locking -T, --exit-info Used for debugging; Use at your own risk! --flush Flush tables to disk between SQL commands -?, --helpDisplay this help and exit --init-file=file Read SQL commands from this file at startup -L, --language=...Client error messages in given language. May be given as a full path --local-infile=[1|0] Enable/disable LOAD DATA LOCAL INFILE -l, --log[=file] Log connections and queries to file --log-bin[=file] Log queries in new binary format (for replication) --log-bin-index=file File that holds the names for last binary log files --log-update[=file] Log updates to file.# where # is a unique number if not given. --log-isam[=file] Log all MyISAM changes to file --log-long-format Log some extra information to update log --low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects --log-slow-queries=[file] Log slow queries to this log file. Defaults logging to hostname-slow.log --pid-file=path Pid file used by safe_mysqld --myisam-recover[=option[,option...]] where options is one of DEAULT, BACKUP or FORCE. --memlock Lock mysqld in memory -n, --new Use very new possible 'unsafe' functions -o, --old-protocolUse the old (3.20) protocol -P, --port=...Port number to use for connection -O, --set-variable var=option Give a variable an value. --help lists variables -Sg, --skip-grant-tables Start without grant tables. This gives all users FULL ACCESS to all tables! --safe-mode Skip some optimize stages (for testing) --safe-show-database Don't show databases for which the user has no privileges --safe-user-createDon't new users cretaion without privileges to the mysql.user table --skip-concurrent-insert Don't use concurrent insert with MyISAM --skip-delay-key-write Ignore the delay_key_write option for all tables --skip-host-cache Don't cache host names --skip-lockingDon't use system locking. To use isamchk one has to shut down the server. --skip-name-resolve Don't resolve hostnames. All hostnames are IP's or 'localhost' --skip-networking Don't allow connection with TCP/IP. --skip-newDon't use new, possible wrong routines. --skip-stack-traceDon't print a stack trace
Re: question about mysql start up again
On Wed, 15 Sep 2004, [GB2312] ÀîÈñ wrote: Hi I have installed some software on aix5.2 . the list of software: apache-1.3.29-1.aix4.3.ppc.rpm php-4.0.6-5.aix4.3.ppc.rpm MySQL-3.23.47-3.aix4.3.ppc.rpm MySQL-client-3.23.47-3.aix4.3.ppc.rpm All of them was installed by using the command : rpm -i *.* then Apache and php can work but mysql can't run when I used the command safe_mysqld start the system told me Starting mysqld daemon with databases from /var/lib/mysql 040913 19:08:45 mysqld ended when I used the command mysql the system told me ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Many friends tell me to see the err log, the below is the err log(locaohost.err),but I can't find any useful things ,can anyone help me? 040915 19:14:06 mysqld started /opt/freeware/libexec/mysqld: unrecognized option `--key_buffer=16M' Why not try removing the option '--key_buffer=16M' from the safe_mysqld (or mysqld_safe) start up scripts? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about [mysql defunct] processes
Subject: Re: Question about [mysql defunct] processes Ken Sorensen [EMAIL PROTECTED] wrote: I am stumped. I have a very busy MySQL db that we've run for about 3 years with no problems. A couple of months ago we upgraded the system to RH-3AS with RedHat's distro mysql-server 3.23.58-1.9. Recently I've started getting zombie processes ([mysql defunct]). Remove the RedHat's distro mysql-server and install properly built MySQL binary version in RPM format. Download it from www.mysql.com. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com Hi Egor, Thanks for your reply. I realized after I posted, I was to ambiguous with my question. Sorry for that. I did change out the MySQL Distro., and as I suspected MySQL is not the problem! Neither was the OS. I cannot use a statically compiled Distro. because we've added functions to this implementation. This system does about 28 queries per second from DBD-Mysql, ODBC, remote mysql connections, etc. About any way you can connect to it, some process does. I used 'lsof' on the command line and found the processes in question were linked to pipes from MySQL to the OS. I found my defunct process! When we first built this implementation, we added a function that interacted with the OS. This added function worked from any query I sent it, but it intermittently threw out defunct processes. I recompiled this function using the current OS compilers and added it again to MySQL. When I tried to remove the function from MySQL, I received errors. I had to remove it from the mysql.func table directly. I added the function shared lib to my OS, used 'ldconfig', and used the 'CREATE function ...' in MySQL. That function is used about 100+ times a day in certain queries, and so far, I have no more defunct processes. It has been almost a week now under normal operating conditions. I consider this problem solved. Thanks Egor for your post, and your willingness to help me. Ken -- _ Ken Sorensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about [mysql defunct] processes
Ken Sorensen [EMAIL PROTECTED] wrote: I am stumped. I have a very busy MySQL db that we've run for about 3 years with no problems. A couple of months ago we upgraded the system to RH-3AS with RedHat's distro mysql-server 3.23.58-1.9. Recently I've started getting zombie processes ([mysql defunct]). Remove the RedHat's distro mysql-server and install properly built MySQL binary version in RPM format. Download it from www.mysql.com. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about [mysql defunct] processes
Greetings, I am stumped. I have a very busy MySQL db that we've run for about 3 years with no problems. A couple of months ago we upgraded the system to RH-3AS with RedHat's distro mysql-server 3.23.58-1.9. Recently I've started getting zombie processes ([mysql defunct]). I've checked/tested the obvious things (netstat/web logs/mysql logs). No connection on the network to the defunct process. Every mysql log entry has a connect/query(s)/quit entry. The web logs don't show anything conclusive during the time the mysql process goes zombie. It comes at different times from different scripts. Sometimes the server is busy, sometimes it's not when a process goes defunct. The /proc/(PID) of the defunct mysql process are empty files. When I restart the mysqld, the defunct processes disappear. My question is this, what is the mysql daemon waiting for to release the defunct thread? There is something holding up the system and creating these problems. The server is pretty buff (Dual Zeon, 2G RAM). I cannot just take the system apart, it's one of the flag ships of our department. I have googled this and found very little about [mysql defunct] processes. Any ideas where to start looking/testing? Your help in this matter would be greatly appreciated. Thanks for you help, Ken -- _ Ken Sorensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL 4.0.20 and make test failure on Linux
Jeff Smelser wrote: On Friday 18 June 2004 11:48 pm, Tom Williams wrote: Hi! I'm trying to build MySQL 4.0.20 on RedHat 5.2 (I think) Linux system with glibc-2.2.5 and gcc-3.4.0 (which I recently upgraded to). The compile runs smoothly, but make test fails. Here is my configure command: Whoa.. 2.2.5? Redhat 5.2? you realize redhat 5.2. was released in the early 90's?/ Anyway $ ./configure --prefix=/usr/local/mysql-4.0.20 --enable-assembler --enable-thread-safe-client --with-mysqld-user=mysql Here is the output from make test: snip ERROR: /home/tom/mysql-4.0.20/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.20]$ Are you trying to install this as a normal user? That's what your doing. What do you mean? He's not installing, he's running `make test`, which should run as a normal user. (It will log warnings about being unable to switch to user mysql, since he's not running it as root, but that's OK.) I don't think I have ever ran make test.. :) You should have rights since its your home directory.. I always run `make test` when I build from source. It can catch problems. For example, mysql 4.0.17 built with gcc in Mac OS X 10.2 passed all tests and ran flawlessly for me. On the other hand, mysql 4.0.18 and 4.1.1 built on Mac OS X 10.3 seemed to build fine but failed the test suite. Sure enough, there were problems with those combinations. Now I've built 4.0.20 and 4.1.2 on 10.3.4 and once again all tests pass. Failing tests is an indication that something isn't behaving as expected. That would probably indicate a problem with the build or the OS. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL 4.0.20 and make test failure on Linux
Michael Stassen wrote: I always run `make test` when I build from source. It can catch problems. For example, mysql 4.0.17 built with gcc in Mac OS X 10.2 passed all tests and ran flawlessly for me. On the other hand, mysql 4.0.18 and 4.1.1 built on Mac OS X 10.3 seemed to build fine but failed the test suite. Sure enough, there were problems with those combinations. Now I've built 4.0.20 and 4.1.2 on 10.3.4 and once again all tests pass. Failing tests is an indication that something isn't behaving as expected. That would probably indicate a problem with the build or the OS. Michael Thanks for the info. :) When you mentioned logging warnings above, I started looking more closely at the log files I could find and found this: CURRENT_TEST: ctype_latin1_de /home/tom/mysql-4.0.20/sql/mysqld: File '/home/tom/mysql-4.0.20/sql/share/charsets/latin1_de.conf' not found (Errcode: 2) /home/tom/mysql-4.0.20/sql/mysqld: Character set 'latin1_de' is not a compiled character set and is not specified in the '/home/tom/mysql-4.0.20/sql/share/charsets/Index' file I think this is my problem even though the master.pid message is what gets displayed. I'll research this and see what I come up with. Thanks! Peace... Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL 4.0.20 and make test failure on Linux
Tom Williams wrote: Hi! I'm trying to build MySQL 4.0.20 on RedHat 5.2 (I think) Linux system with glibc-2.2.5 and gcc-3.4.0 (which I recently upgraded to). The compile runs smoothly, but make test fails. Here is my configure command: $ ./configure --prefix=/usr/local/mysql-4.0.20 --enable-assembler --enable-thread-safe-client --with-mysqld-user=mysql Here is the output from make test: [EMAIL PROTECTED] mysql-4.0.20]$ make test cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --skip-warnings --language=../sql/share/english/ 040619 7:39:33 ../sql/mysqld: Shutdown Complete Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --skip-warnings --language=../sql/share/english/ 040619 7:39:33 ../sql/mysqld: Shutdown Complete Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TEST RESULT -- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] auto_increment [ pass ] backup [ pass ] bdb-alter-table-1 [ skipped ] bdb-alter-table-2 [ skipped ] bdb-crash [ skipped ] bdb-deadlock [ skipped ] bdb[ skipped ] bdb_cache [ skipped ] bench_count_distinct [ pass ] bigint [ pass ] binary [ pass ] bool [ pass ] bulk_replace [ pass ] case [ pass ] cast [ pass ] check [ pass ] comments [ pass ] compare[ pass ] constraints[ pass ] convert[ pass ] count_distinct [ pass ] count_distinct2[ pass ] create [ pass ] ctype_cp1251 [ pass ] ERROR: /home/tom/mysql-4.0.20/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.20]$ I've been trying to find info on the test suite in the online doc and I decided to post this question here while I continue pouring over the doc. If make test is failing, does that mean I shouldn't use the server I just built? Thanks in advance for your time and assistance! :) Peace... Tom Well, I found the solution to this problem: http://bugs.mysql.com/bug.php?id=1838 Recompiling with --with-extra-charsets=all seems to have worked! Peace... Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about MySQL 4.0.20 and make test failure on Linux
Hi! I'm trying to build MySQL 4.0.20 on RedHat 5.2 (I think) Linux system with glibc-2.2.5 and gcc-3.4.0 (which I recently upgraded to). The compile runs smoothly, but make test fails. Here is my configure command: $ ./configure --prefix=/usr/local/mysql-4.0.20 --enable-assembler --enable-thread-safe-client --with-mysqld-user=mysql Here is the output from make test: [EMAIL PROTECTED] mysql-4.0.20]$ make test cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --skip-warni ngs --language=../sql/share/english/ 040619 7:39:33 ../sql/mysqld: Shutdown Complete Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --skip-warnin gs --language=../sql/share/english/ 040619 7:39:33 ../sql/mysqld: Shutdown Complete Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TEST RESULT -- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] auto_increment [ pass ] backup [ pass ] bdb-alter-table-1 [ skipped ] bdb-alter-table-2 [ skipped ] bdb-crash [ skipped ] bdb-deadlock [ skipped ] bdb[ skipped ] bdb_cache [ skipped ] bench_count_distinct [ pass ] bigint [ pass ] binary [ pass ] bool [ pass ] bulk_replace [ pass ] case [ pass ] cast [ pass ] check [ pass ] comments [ pass ] compare[ pass ] constraints[ pass ] convert[ pass ] count_distinct [ pass ] count_distinct2[ pass ] create [ pass ] ctype_cp1251 [ pass ] ERROR: /home/tom/mysql-4.0.20/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.20]$ I've been trying to find info on the test suite in the online doc and I decided to post this question here while I continue pouring over the doc. If make test is failing, does that mean I shouldn't use the server I just built? Thanks in advance for your time and assistance! :) Peace... Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL 4.0.20 and make test failure on Linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 18 June 2004 11:48 pm, Tom Williams wrote: Hi! I'm trying to build MySQL 4.0.20 on RedHat 5.2 (I think) Linux system with glibc-2.2.5 and gcc-3.4.0 (which I recently upgraded to). The compile runs smoothly, but make test fails. Here is my configure command: Whoa.. 2.2.5? Redhat 5.2? you realize redhat 5.2. was released in the early 90's?/ Anyway $ ./configure --prefix=/usr/local/mysql-4.0.20 --enable-assembler --enable-thread-safe-client --with-mysqld-user=mysql Here is the output from make test: snip ERROR: /home/tom/mysql-4.0.20/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.20]$ Are you trying to install this as a normal user? Thats what your doing. I don't think I have ever ran make test.. :) You should have rights since its your home directory.. - -- You know you've been root too long when it's easier to change a password than to remember it. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFA08hDld4MRA3gEwYRAmRLAKDhgTLhvQaZW7NGpiVH6Fl7EiuewQCfesIA ZGQYT2LfYwj7XqKFBgTpcRc= =3kQ9 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question about mysql database backup.
Fred, Jim, Thank you very much for your kind help! I still have other questions about the MySQL operation. If a service host is redundant, one is in active status, the other is standby. I want to make the following operation . 1. database backup. This is done on the standby one with the following command. mysqldump --opt --quote-names --flush-logs database_name database.backup 2. database restore. Restore the database with the previous backup. I am not sure what to do. I guess there will be two methods to finish this. * disconnect the two host and make operation on each one individually as following mysql database_namve database.backup Then restart Mysql. * do restore on the active one with the following command. Because the standby host are the backup of the active one. So the data on both of the hosts will finally be identical in the end. I don't know whether it will be in such case. mysql database_namve database.backup If my description is wrong, Would you please let me know the correct methods? Thanks! Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:13 PM To: Li, Lenny Yong Bo (Lenny) Cc: [EMAIL PROTECTED] Subject: Re: A question about mysql database backup. Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
on 02/27/2004 12:11 AM, Li, Lenny Yong Bo (Lenny) at [EMAIL PROTECTED] wrote: 1. database backup. This is done on the standby one with the following command. mysqldump --opt --quote-names --flush-logs database_name database.backup How does this operations handle binary fields, password fields, blob fields etc? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
On 02/25/2004 09:13 AM Frederic Wenzel wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Are back-ticks actually part of ANSI SQL? Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question about mysql database backup.
Fred, Thanks for your quick response! Do you mean that only one step is enough for a backup mysqldump -F --opt --quote-names database_name database_name.backup? The second step is not needed at all. Am I right? I added the option -Q, it failed, but the --quote-names option works well. Maybe the version number is not high enough. Regards, Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:13 PM To: Li, Lenny Yong Bo (Lenny) Cc: [EMAIL PROTECTED] Subject: Re: A question about mysql database backup. Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question about mysql database backup.
Ms./Sir, Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup 2. stop mysqld if is running. Then start it with --log-bin option. The purpose of the second step is to update the database from the binary log file since the point I began to do backup. Then it will work normally. Am I right? But I once did a test on the database. I used command LOCK TABLES table_name READ to lock a table as root login, and then insert one row as lss login in another terminal. The insert operation hang there until the table was unlocked. I run command select * from table_name in both terminal finding the result are the same and new row was inserted. So I am confused about the functionality of the second step. If the above procedure is not correct, Would you please let me know the correct ones? I once read the mysql administration document and found some command about backup. But they are executed on mysql prompt like mysql LOCK TABLES, I need the procedures that can be run on command line. Thanks! Regards, Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about Mysql C API v 3.22 and 3.23
Hello, I am attempting a change in my use of mysql C API to use 3.23.54 instead of 3.21 and I am finding that the mysql_real_connect function is failing to run (it crashes the program). My mysqld version is also 3.22.27. Anyone know whether this is a bug in that version of the C API? Thank You, Joe Johnson Airlink Systems [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about Mysql C API v 3.22 and 3.23
At 8:51 -0600 1/12/04, Joe Johnson wrote: Hello, I am attempting a change in my use of mysql C API to use 3.23.54 instead of 3.21 and I am finding that the mysql_real_connect function is failing to run (it crashes the program). My mysqld version is also 3.22.27. Anyone know whether this is a bug in that version of the C API? I suppose it's possible, but it's unlikely because the MySQL client programs themselve use mysql_real_connect(). A bug in such a fundamental function would tend to show up pretty quickly. You might want to post your code that is failing to the list so that people can look at it. Thank You, Joe Johnson Airlink Systems [EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL and Oracle.
It depends a lot on the features that are needed. Right now we use mySQL for some things and Orcale for others. The vibes I have seen from both Oracle and MySQL is that for right now MySQL is a complementary product to Oracle. Soon I think MySQL will be right up there with Oracle. Check the following link to see a databse comparison done by eweek magazine. I hope this helps. http://www.eweek.com/article2/0,4149,1184846,00.asp Scott Pippin [EMAIL PROTECTED]
Question about MySQL and Oracle.
Hello! I have one question. MySQL is better than Oracle or not? Thank you. -- ___ OperaMail free e-mail - http://www.operamail.com OperaMail Premium - 28MB, POP3, more! US$29.99/year Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql backup
Hi all, Up until now we always created database backups using the mysqldump tool. But I'm wondering, if it is possible to just create a tar archive of the complete mysql data directory. We are running on Linux (2.4.18) and we use mysql 4.014. Thanks, Harm de Laat Informatiefabriek The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql backup
You may tar de mysql data directory but you shouldn't do it with mysql server running (because it is using the files). If you do not want to stop the mysql server (or lock it from writes), you can, for example, use mysqlhotcopy first to create an identical data directory and then do a tar on that copy (and then remove the copy). Cheers, On Mon, 2003-09-08 at 09:58, [EMAIL PROTECTED] wrote: Hi all, Up until now we always created database backups using the mysqldump tool. But I'm wondering, if it is possible to just create a tar archive of the complete mysql data directory. We are running on Linux (2.4.18) and we use mysql 4.014. Thanks, Harm de Laat Informatiefabriek The Netherlands -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql-4.x escaping with USE_MB
quoted from mysql-4.1/libmysql/libmysql.c = static ulong mysql_sub_escape_string(CHARSET_INFO *charset_info, char *to, const char *from, ulong length) { const char *to_start=to; const char *end; #ifdef USE_MB my_bool use_mb_flag=use_mb(charset_info); #endif for (end=from+length; from != end ; from++) { #ifdef USE_MB int l; if (use_mb_flag (l = my_ismbchar(charset_info, from, end))) { while (l--) *to++ = *from++; from--; continue; } #endif = notice that USE_MB(it exists in older version, all mysql 4.x) it use connection CHARSET to escape string differently but it is said mysql-4.1 can have different charset on column,table,database how can the client know which table it's querying on? i guess this is why it failed to insert blob data to GBK mysql servers. so do anyone use multibyte mysql server? do mysql developers tested insert blob data under UTF-8 Mysql server? _ MSN Messenger: http://messenger.msn.com/cn - 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
question about mysql installation
Hi all, Can i install mysql twice on the same comuter? And on the same hard disk? Thanks, Shilry _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - 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
Re: question about mysql installation
Yes, http://www.mysql.com/doc/en/Installing_many_servers.html - Original Message - From: shirly cohen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 20, 2003 11:42 AM Subject: question about mysql installation Hi all, Can i install mysql twice on the same comuter? And on the same hard disk? Thanks, Shilry _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - 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 - 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
re: question about mysql installation
On Thursday 20 February 2003 13:42, shirly cohen wrote: Can i install mysql twice on the same comuter? And on the same hard disk? Yes and Yes: http://www.mysql.com/doc/en/Installing_many_servers.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: question about mysql installation
Hi again I looked in the site and in some place they write: you should create an option file for each server. In your startup script that is executed at boot time you should specify for both servers safe_mysqld --defaults-file=path-to-option-file What is the startup script? is it somthing that i need to ceate? Thanks for all the ones who replied me. From: Jerry [EMAIL PROTECTED] Reply-To: Jerry [EMAIL PROTECTED] To: shirly cohen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: question about mysql installation Date: Thu, 20 Feb 2003 12:05:18 - Yes, http://www.mysql.com/doc/en/Installing_many_servers.html - Original Message - From: shirly cohen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 20, 2003 11:42 AM Subject: question about mysql installation Hi all, Can i install mysql twice on the same comuter? And on the same hard disk? Thanks, Shilry _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - 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 _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - 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
Re: Question about MySQL and lwp_mutex_lock and lwp_mutex_wakeup
On Thu, 12 Dec 2002 18:41:05 -0700 Mariella Di Giacomo [EMAIL PROTECTED] wrote: Hello, I am running MySQL 4.0.5b 64 bits on a Solaris 2.8. The server used has 8 CPUs. MySQL DB has access (and menage) in that system 2DBs. The first DB is only is accessed only through SELECTS and the second one has been used for UPDATES and INSERTS. Initially I was running a process that was updating the tables. In the program there is no contention among tables and there is only one process that executes UPDATES AND INSERTS. Apparently as soon as people start accessing the other DB (quite heavily) the time spent by MySQL in lwp_mutex_wakeup lwp_mutex_lock increases a lot . This is a screen shot taken with truss of the process in a certain timeframe. As soon as all the shutdown all the users from the DB that is used for only SELECT, now the same process that runs MySQL updates on the other DB has hardly ever lwp_mutex_wakeup lwp_mutex_lock calls. Could someone, please, help me trying to understand why that is the behavior ? Thanks a lot, Mariella The above is not necessarily related to UPDATE's of DELETE's, but to the more frequent creation / destruction of threads. MySQL enforces quite a few locks in order to enable safe operation, safe statistics colletion, memory management etc. -- Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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
Question about MySQL and lwp_mutex_lock and lwp_mutex_wakeup
Hello, I am running MySQL 4.0.5b 64 bits on a Solaris 2.8. The server used has 8 CPUs. MySQL DB has access (and menage) in that system 2DBs. The first DB is only is accessed only through SELECTS and the second one has been used for UPDATES and INSERTS. Initially I was running a process that was updating the tables. In the program there is no contention among tables and there is only one process that executes UPDATES AND INSERTS. Apparently as soon as people start accessing the other DB (quite heavily) the time spent by MySQL in lwp_mutex_wakeup lwp_mutex_lock increases a lot . This is a screen shot taken with truss of the process in a certain timeframe. __ signals SIGALRM1 total: 1 syscall seconds calls errors read 69.784597594 write 26.411851 close.46 105 time 87.915096 lseek 6.44 276 alarm.00 1 fstat 17.101503 access .72 105 fcntl 22.591503 poll1.89 105 lwp_sema_wait 4.88 562 lwp_sema_post 7.95 562 lwp_mutex_wakeup 30.893247 lwp_mutex_lock 31.172529 lwp_cond_wait.00 1 1 pread 2.73 137 pwrite 45.362361 signotifywait.00 1 accept 2.43 105 shutdown .99 105 getpeername .73 105 getsockname 2.18 105 getsockopt 22.551503 setsockopt 26.881713 lwp_sigtimedwait .07 1 --- -- sys totals: 412.11 28179595 usr time:.48 elapsed: 96.46 __ As soon as all the shutdown all the users from the DB that is used for only SELECT, now the same process that runs MySQL updates on the other DB has hardly ever lwp_mutex_wakeup lwp_mutex_lock calls. Could someone, please, help me trying to understand why that is the behavior ? Thanks a lot, Mariella - 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
Question about MySQL replication and OPTIMIZE TABLE
Hello, I am running MySQL 4.0.5 64 bit on a Sun Solaris and I am using MyISAM Indices. I would like to set replication (one master and one slave). My question is: When I OPTIMIZE TABLE table1, ... on the master will the slave be updated or if I want to optimize the same tables on the slave I need to explicitly run OPTIMIZE TABLE on the slave side ? Thanks a lot for your help, Mariella - 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
re: Question about MySQL replication and OPTIMIZE TABLE
Mariella, Thursday, December 05, 2002, 4:15:52 AM, you wrote: MDG I am running MySQL 4.0.5 64 bit on a Sun Solaris and I am using MyISAM MDG Indices. MDG I would like to set replication (one master and one slave). MDG My question is: MDG When I OPTIMIZE TABLE table1, ... on the master will the slave be updated MDG or if I want to optimize the same tables on the slave I need to explicitly run MDG OPTIMIZE TABLE on the slave side ? OPTIMIZE TABLE isn't written to the binary logs. So, OPTIMIZE TABLE isn't replicated. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
A question about mySQL scalability
Hello, I am new to the list, I've subscribed in last ressort when I couldn't find an answer, neither the web or the list archives... I've had a question from a client that I've answered in this way : One other question, when you have built the database, will it be expandable to the size for 10,000 (an estimate) houses etc.? Yes, mySQL prior to 3.23 handles table of a maximum of 4GB, now it's as large as 20GB depending on the file system used. each row in table house for instance will be way less than 5KB and 5KB * 10 000 is only about 50MB (1/80 of the former maximum limit) Do you think I am right trusting mySQL on this one ? What is the upper limit database wide ? I know it depends on the disk space and the CPU and probably the file system.. But what sort of figures do you have in mind when answering this type of question about mySQL ? Cheers, Damien - 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
a question about mysql++
Hi, I am trying to comile a c++ program but I got an error message as below: sqlplus.hh: No such file or directory I have mysql server and client 3.23... on rh 7.2 and I have installed binary RPM for SuSE 8.* built by gcc 3.2 ../Downloads/mysql++/mysql++-1.7.9gcc3.2-2.i386.rpm by: rpm -ivh mysql++-1.7.8gcc3.2-2.i386.rpm I checked sqlplus.hh file and it is under /usr/include/sqlplus directory. Any help would be appreciated, Jing [EMAIL PROTECTED] - 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
Status question about Mysql
Hi, I am running 3.23.49 and had a quick question I hope someone can answer. 70% of the time my sql server is doing just fine. I have 4 GIGs of memory, and a 1.33 Ghz processor.I am running Redhat 7.3 with a 2.4.19 kernel. 70% of the time my server is fine, the other 30% it is 100% maxed out and I have 0% free on the cpu for 10-20 seconds at a time. My question is this - Is there a program or something that I can run that will show me exactly what queries are being given to mysql. I want to know which queries I am giving to the server that are giving it such a hard time so I can find an alternative? If you have any ideas for the best way to do this I would really appreciate it. Matt - 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
Re: Status question about Mysql
On Tue, 20 Aug 2002, Matt Heaton wrote: Hi, I am running 3.23.49 and had a quick question I hope someone can answer. 70% of the time my sql server is doing just fine. I have 4 GIGs of memory, and a 1.33 Ghz processor.I am running Redhat 7.3 with a 2.4.19 kernel. 70% of the time my server is fine, the other 30% it is 100% maxed out and I have 0% free on the cpu for 10-20 seconds at a time. My question is this - Is there a program or something that I can run that will show me exactly what queries are being given to mysql. I want to know which queries I am giving to the server that are giving it such a hard time so I can find an alternative? If you have any ideas for the best way to do this I would really appreciate it. Hi Matt, There are two things to look at. One is mysqladmin process -- that will show you the current process list -- not much good if your system is completely locked up, however.. but worth a shot. What might even be better is to turn on your slow query logging. In your my.cnf file, add this line in [mysqld] and [safemysqld]: log-slow-queries= /var/log/slowquery.log Make sure that this file already exists, and give it the same owner as your mysqld process user. (i.e. from shell, echo /var/log/slowquery.log; chown mysql /var/log/slowquery.log) As your MySQL server runs into queries that take a long time to process (selects, inserts, etc.) it will record them in this log. Looks something like this: # Time: 020820 14:37:27 # User@Host: member[member] @ [10.100.3.1] # Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 19733 select CaseMem from FishTank where Smitty like 'Angelina' and active=1; And lastly, after you've stared at your process list and slow queries, you'll want to spend time figuring out WHY your database server is maxing out your CPU. I would look at server settings -- you'll want to be careful about how you tune things such as key_buffer, and especially thread_concurrency. Look at: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html (section 5.5.2) and then look on the web for tuning help pages as well. http://www.mysql.com/information/presentations/presentation-oscon2000-2719/index.html The Optimizing MySQL Presentation Slides Learn what each of the --set-variable settings does in your my.cnf -- don't get too ambitious with your limits either, because that can cause excessive memory and CPU usage. Neil Mansilla whatUseek.com - 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
Re: Question about MySQL
Sigalit, Sunday, May 12, 2002, 3:47:24 PM, you wrote: SCD I am new in the MySql business so I'm sorry if my question is too simple. SCD Is there any way to nest queries in MySql in the following manner: SCD select field from table where id = (select ...) No, MySQL doesn't have sub-selects yet. You can get around using JOIN, look at: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html http://www.mysql.com/doc/J/O/JOIN.html SCD Thanks for your attention, SCD Sigalit -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Question about MySQL
Hi, I am new in the MySql business so I'm sorry if my question is too simple. Is there any way to nest queries in MySql in the following manner: select field from table where id = (select ...) Thanks for your attention, Sigalit _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - 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
Re: Question about MySQL
As far as I know MySQL does not yet support nested SELECT statements. -Nick Hi, I am new in the MySql business so I'm sorry if my question is too simple. Is there any way to nest queries in MySql in the following manner: select field from table where id = (select ...) Thanks for your attention, Sigalit _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - 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 mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql- unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
Re: Question about mysql++ and g++
Mike Holmquist writes: Ok, i downloaded the mysql++-1.7.8-1.i386.rpm ftp://ftp.rpmfind.net/linux/MySQL/mysql++/mysql++-1.7.8-1.i386.rpm rpm file from RpmFind.net. I have mysql installed already on my machine, o ya and im running linux. I have gcc installed, version 2.96, i think everything installed correctly when i ran the rpm. But i am not sure. I made a simple program, here it is: #include sqlplus.hh int main() { return 0; } When i tried to compile it using g++ mysql.cpp i got a bunch of errors saying mysql.h not found (errors are printed on bottom of email). So i tried doing a locate mysql.h and it only found the mysql.h file from my windows partition (i have mysql++ installed on windows...). I am not sure if its my compiler, or the version of mysql++ i installed. Another question i have is it says to reset my database using reset-db, where would i do that? The mysql command line? If anyone can help me, give me some ideas if my install went wrong, or if i need to update gcc, or get a different compiler (i dont know any for linux yet! ) id appreciate it very much! ~Mike HI! The errors are caused by MySQL++ not seeing MySQL++ include directory. In your Makefile add -I.. directive for include directory and -L.. directive for lib directory. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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
Question about mysql++ and g++
Ok, i downloaded the mysql++-1.7.8-1.i386.rpm ftp://ftp.rpmfind.net/linux/MySQL/mysql++/mysql++-1.7.8-1.i386.rpm rpm file from RpmFind.net. I have mysql installed already on my machine, o ya and im running linux. I have gcc installed, version 2.96, i think everything installed correctly when i ran the rpm. But i am not sure. I made a simple program, here it is: #include sqlplus.hh int main() { return 0; } When i tried to compile it using g++ mysql.cpp i got a bunch of errors saying mysql.h not found (errors are printed on bottom of email). So i tried doing a locate mysql.h and it only found the mysql.h file from my windows partition (i have mysql++ installed on windows...). I am not sure if its my compiler, or the version of mysql++ i installed. Another question i have is it says to reset my database using reset-db, where would i do that? The mysql command line? If anyone can help me, give me some ideas if my install went wrong, or if i need to update gcc, or get a different compiler (i dont know any for linux yet! ) id appreciate it very much! ~Mike -ERRORS-- [stigmasix@localhost practice]$ g++ mysql.cpp In file included from /usr/include/sqlplus.hh:9, from mysql.cpp:1: /usr/include/defs:5:19: mysql.h: No such file or directory In file included from /usr/include/coldata1.hh:8, from /usr/include/sqlplus.hh:12, from mysql.cpp:1: /usr/include/type_info1.hh:10:19: mysql.h: No such file or directory In file included from /usr/include/vallist1.hh:6, from /usr/include/row1.hh:11, from /usr/include/compare1.hh:5, from /usr/include/sqlplus.hh:13, from mysql.cpp:1: /usr/include/manip1.hh:10:19: mysql.h: No such file or directory In file included from /usr/include/sqlplus.hh:14, from mysql.cpp:1: /usr/include/connection1.hh:10:19: mysql.h: No such file or directory In file included from /usr/include/connection1.hh:20, from /usr/include/sqlplus.hh:14, from mysql.cpp:1: /usr/include/query1.hh:10:19: mysql.h: No such file or directory In file included from /usr/include/query1.hh:13, from /usr/include/connection1.hh:20, from /usr/include/sqlplus.hh:14, from mysql.cpp:1: /usr/include/result1.hh:12:19: mysql.h: No such file or directory In file included from /usr/include/sqlplus.hh:57, from mysql.cpp:1: /usr/include/coldata3.hh:10:19: mysql.h: No such file or directory - 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
Question about mysql server Installation
Dear Anyone I am one of the Linux user. I use the freebsd operating system. I have one main problem. After installation completed , I put the command mysql in #prompt. But some error message will shows, I don't know how to solve it. The Error message is ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.socket ' (2) Please mail me regarding this problem. One of the mysql user from India. plakshmanan __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com - 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
Re: Question about MySQL GUI on Win32
Jason Holliston writes: This is probably something I'll smack my head about afterward, but here goes: is it possible to execute multiple queries in the MySQL client GUI on Win32? What's accepted on the command line or in a .sql script doesn't work when pasted into it. Thanks in advance! -Jason No, mysqlgui does not work with several queries. For scripts, mysql monitor program is the best. With mysqlgui, however you have an interactive command history (with deletion of entries and many other stuff) , plus hierarchy of queries on the main menu. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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
Question about MySQL GUI on Win32
This is probably something I'll smack my head about afterward, but here goes: is it possible to execute multiple queries in the MySQL client GUI on Win32? What's accepted on the command line or in a .sql script doesn't work when pasted into it. Thanks in advance! -Jason - 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
Re: Question about MySQL GUI on Win32
It depends on the gui you're using. Get the one at my.anse.de At 07:49 AM 4/24/2001 -0700, Jason Holliston wrote: This is probably something I'll smack my head about afterward, but here goes: is it possible to execute multiple queries in the MySQL client GUI on Win32? What's accepted on the command line or in a .sql script doesn't work when pasted into it. Thanks in advance! -Jason - 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 - Scott Baker - Webster Internet - Network Technician 503.266.8253 - [EMAIL PROTECTED] If it is not right do not do it; if it is not true do not say it. - Marcus Aurelius - 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
RE: Question about MySQL GUI on Win32
Yes, MySQLFront works much better with multiple queries at once. Nice, quick, simple Win32 UI. I was originally using MySQL GUI that's downloadable from http://www.mysql.com/. Oh, and for everyone's information, http://my.anse.de/ does not appear to work, at least for this morning. http://www.anse.de/ does, however. Thanks for the information. -Jason -Original Message- From: Scott Baker [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 8:12 AM To: Jason Holliston; [EMAIL PROTECTED] Subject: Re: Question about MySQL GUI on Win32 It depends on the gui you're using. Get the one at my.anse.de At 07:49 AM 4/24/2001 -0700, Jason Holliston wrote: This is probably something I'll smack my head about afterward, but here goes: is it possible to execute multiple queries in the MySQL client GUI on Win32? What's accepted on the command line or in a .sql script doesn't work when pasted into it. Thanks in advance! -Jason - 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 - Scott Baker - Webster Internet - Network Technician 503.266.8253 - [EMAIL PROTECTED] If it is not right do not do it; if it is not true do not say it. - Marcus Aurelius - 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
Re: Question about MySQL GUI on Win32
It depends on the gui you're using. Get the one at my.anse.de At 07:49 AM 4/24/2001 -0700, Jason Holliston wrote: This is probably something I'll smack my head about afterward, but here goes: is it possible to execute multiple queries in the MySQL client GUI on Win32? What's accepted on the command line or in a .sql script doesn't work when pasted into it. Thanks in advance! The old subdomain my.anse.de is no longer working, because I switched to another provider, which doesn't support subdomains. ;/ But he's giving me much more traffic for all you guys downloading MySQL-Front - 6000 downloads last month = about 3 GB !! ;) . I'm currently working on the next version 1.18, which comes with ODBC-Import, partial SQL-execution, table-diagnostics and the option to export tables directly from one database to another. Get the actual version at http://www.mysqlfront.de/ or http://www.anse.de/mysqlfront/ Greetings, Ansgar Becker [EMAIL PROTECTED] | http://www.anse.de AB7670-RIPE - 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
question about mysql under FreeBSD
Hi, I have test two systems with mysql Linux Slackware 7.1 kernel 2.2.16 and FreeBSD-4.2-STABLE. I have mysql 3.23.36 only running on this two hosts and hosts are the same (PIII 800 Mhz Ami MegaRaid RAID 1 18.2 Go 1 Go RAM). I copy the database from FreeBSD to Linux and I launch a same select on this two hosts. Results on FreeBSD: mysql select count(*) from transat; +--+ | count(*) | +--+ | 3394568 | +--+ 1 row in set (0.00 sec) mysql select * from transat where id='HGUI'; (...) | HGUI | 2001-04-20 17:35:03 | 79.7 | 8 | 0 | |0 | | +-+-++-+-+-+--+ 3951 rows in set (1.29 sec) Results on Linux: mysql select count(*) from transat; +--+ | count(*) | +--+ | 3394568 | +--+ 1 row in set (0.00 sec) mysql select * from transat where id='HGUI'; (...) | HGUI | 2001-04-20 17:35:03 | 79.7 | 8 | 0 | |0 | | +--+-++-+-+-+--+ 3951 rows in set (0.16 sec) I don't know why there is a big difference between this two systems. Somebody can explain me why ? Please reply to [EMAIL PROTECTED] Thank you. spe. -- [EMAIL PROTECTED]
Re: question about mysql under FreeBSD
Yes, I try to use with softupdates or async but results is the same. DD are SCSI U3W Disks. Have an idea ? spe. -- [EMAIL PROTECTED] - Original Message - From: "Andrey Nepomnyaschih" [EMAIL PROTECTED] To: "Sebastien Petit" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, April 22, 2001 7:35 PM Subject: RE: question about mysql under FreeBSD Hello over there, Do you have softupdates enabled? Have a good time, Andrey Nepomnyaschih [EMAIL PROTECTED] - 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
Hi, a question about mysql and servlets
Hello everyboy, nice to post you. I hope this question not to be very frequent but i'm new in this list. I'd like to know if anybody knows how to connect a servlet with MySQL. It doesn't matter if it is by a ODBC:JDBC bridge o a 100% JDBC Driver. I can download the drivers. I want to know the steps and the string connection to use. If you are so so so so kind and the driver u use is not big , plese post it. Thank's from a new MySQL user. Francisco Glvez Martnez [EMAIL PROTECTED] [EMAIL PROTECTED] --- Chatea con Isabel Pisano. Actriz , periodista y escritora. El martes da 13 de Marzo a las 20:00 en http://www.eresmas.com/encuentros - 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