Re: table is full
On Wed, Sep 29, 2004 at 05:24:42PM -0500, Qunfeng wrote: Hi, I am running 4.1.0-alpha on a linux machine. When I use mysqlimport to load a big data file (~7 Gb), I get an error mysqlimport: Error: The table 'mytable' is full, when using table: mytable. I guess the table is too big. Is there any solution for this problem? Thanks a million! You didn't search the web for the answer before posting, did you? :-) I'll take a stab in the dark here: http://jeremy.zawodny.com/blog/archives/000796.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
On Wed, Sep 22, 2004 at 01:52:36PM -0400, Debbie L wrote: We are looking at a disk subsystem for a high transactional application. And management wishes to use disk appliance (IPStore or NetAPP). Does anyone know if placing the mysql MYISAM and INNODB datafiles on IPStore or NetApp disk applliances are supported? I'm not sure what you mean by supported (commercial paid support?), but it works. (We use almost all INNODB tables) If the disk appliance goes away, will it corrupt the database? Maybe not corrupt but it could lead to lost transactions. Where do you plan on storing the transaction logs? Also on the appliance or locally? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
On Wed, Sep 22, 2004 at 02:22:59PM -0400, Debbie L wrote: Your right, I shouldn't say supported... Is it a wise to put datafiles on a disk appliance? Coming from other database background, it is not wise to do such a thing and will cause problems when the disk appliance has problems. Well, it's very common in the world of big iron databases. I know that man companies routinely store A LOT of their Oracle data on Netapp or EMC gear. Some are doing it with MySQL now too. As for reliability, these disk systems tend to be far more reliable than the hosts connecting to them (at least on the low end). There's a lot of redundancy because they're designed for just this sort of application. As for the transaction logs, I haven't reallly thought of it, but to my understanding management wants it all on the appliance. That's how I'd do it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Fri, Sep 17, 2004 at 04:01:53PM +0300, Egor Egorov wrote: Jeremy Zawodny [EMAIL PROTECTED] wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Somewhere in this list I've seen controversial reports about FreeBSD/amd64. Seems like it's not yet stable and may give unpredictable performance behaviour, no? What's your opinion? Agreed. FreeBSD's production release is 4.10. The 5.x tree is still a work in progress, much like MySQL 4.1. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing DBIx::DWIW on CPAN
On Wed, Sep 15, 2004 at 11:27:55AM -0700, Sanjeev Sagar wrote: Hello All, I am trying to install DBIx::DWIW but giving me following error. No such file `DBIx-DWIW-0.41.tar.gz' I am trying to install from CPAN cpan install DBIx::DWIW Could not fetch authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz Giving up on '/root/.cpan/sources/authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz' Note: Current database in memory was generated on Wed, 15 Sep 2004 11:08:29 GMT Weird. Perhaps your CPAN mirror has an index that's out of sync with the actual data? I've seen that happen before (rsync isn't atomic in that way). And 0.41 is fairly new. I'd try another mirror. It's current on cpan.yahoo.com, for example. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing DBIx::DWIW on CPAN
On Wed, Sep 15, 2004 at 01:10:35PM -0700, Sanjeev Sagar wrote: I am trying on cpan.yahoo.com and it's giving me following error The requested URL /authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz was not found on this server. Hmm. wget http://cpan.yahoo.com/authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz Works for me. How about you? I checked and the mirrors on both machines behind cpan.yahoo.com are up-to-date. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing DBIx::DWIW on CPAN
On Wed, Sep 15, 2004 at 01:44:21PM -0700, Sanjeev Sagar wrote: Thanks ! I am trying to run make test but it's giving me following error [EMAIL PROTECTED] DBIx-DWIW-0.41]# make test PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/lib -Iblib/arch test.pl 1..1 # Running under perl version 5.008 for linux # Current time local: Wed Sep 15 13:42:27 2004 # Current time GMT: Wed Sep 15 20:42:27 2004 # Using Test.pm version 1.23 Can't locate Time/HiRes.pm in @INC (@INC contains: blib/lib blib/arch /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at blib/lib/DBIx/DWIW.pm line 12. BEGIN failed--compilation aborted at blib/lib/DBIx/DWIW.pm line 12. Compilation failed in require at test.pl line 10. BEGIN failed--compilation aborted at test.pl line 10. make: *** [test_dynamic] Error 2 Any help will be highly appreciable. Install Time::HiRes from CPAN. The Makefile should have caught that but didn't. I'll fix that on my end. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Mon, Sep 06, 2004 at 12:48:37PM -0700, Miles Keaton wrote: If my company wants to get the best-performing fastest platform for a MySQL server, what would it be these days? Opteron? Dual? Quad? And on a related note... If a 64-bit CPU, then I'm assuming it would need an operating system designed for that 64-bit CPU, to get best performance, right? I know that OpenBSD has an amd64 version and that the OpenBSD developers seem to say that Opteron is their favorite (and most-currently-developed) CPU. I've used OpenBSD in the past and like it a lot. Is anyone here using MySQL on OpenBSD+Opteron in a high-load situation? MySQL works quite well on Opteron machines. However, OpenBSD is a poor platform choice for running MySQL. It's known to run much better on FreeBSD or Linux (depending on your particular preference). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Tue, Sep 14, 2004 at 02:31:54PM -0700, Brian Abbott wrote: Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. I don't have any yet but should in a week or two. I just haven't run any benchmarks... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 cluster, help with BUILD/compile-pentium-max
On Fri, Jul 23, 2004 at 05:37:08PM +0200, crandler wrote: Hello, the following problem occurs when I want to configure MySQL 4.1: Building aclocal.m4 Running autoheader to build config.hin Running autoconf to build configure 645046 644916 Building RPC client/server files Building tags +++ cd innobase +++ aclocal +++ autoheader +++ aclocal +++ automake +++ autoconf +++ '[' -d gemini ']' +++ CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g +++ CXX=gcc +++ CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g +++ CXXLDFLAGS= +++ ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-readline --with-innodb --with-berkeley-db --with-embedded-server --enable-thread-safe-client --with-vio --with-raid --with-ndbcluster --enable-local-infile checking build system type... i686-pc-linux checking host system type... i686-pc-linux checking target system type... i686-pc-linux ./configure: line 1496: syntax error near unexpected token `mysql,' ./configure: line 1496: `AM_INIT_AUTOMAKE(mysql, 4.1.4-beta)' Your build toolchain is too old. I ran into this on an RedHat AS 2.1 box and had to upgrade autoconf/automake/etc. What a waste of time. On Debian it'd have been trivial. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kernel panic when mysql stop command issued
On Sat, Aug 14, 2004 at 03:01:06PM -0700, Demetrios Stavrinos wrote: Kernel panic: Fatal exception in interrupt...In Interrupt handler - not syncing message appears when the mysql-max stop is issued. Other than that everything works. I changed hardware (everything new) and re-installed Linux and MySQL and upgraded to latest 2.6.3 from mdk (It was happening with the previous 2.6.3 also). Problem is repeatable 4 out of 5 tries. Linux 2.6.3-15mdkenterprise #1 SMP Fri Jul 2 20:07:05 mysql MySQL-Max-4.0.20-3mdk. Has any one heard or seen anything like it? Try a different kernel. If MySQL is able to screw with the kernel, it's a kernel bug--or a weird hardware problem manifesting itself as one. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication blocked
On Fri, Aug 13, 2004 at 01:19:14AM -0500, Donny Simonton wrote: There is only one thread for replication on the slave. It does one step a time. If you use mysqlbinlog on one of your binary files on your master, you will see exactly how it all works. No, there are 2 threads: the IO (or relay) thread, and the SQL thread. Multi-threaded would probably cause thousands of problems. Unless it was threaded per table, but that would still cause problems because of multi-table deletes and updates. Agreed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Jeremy Zawodny's gcc flags or MySQL AB' for a FreeBSD?
On Thu, Aug 05, 2004 at 12:19:37PM +0900, Evgeny Chuykov wrote: Good day. From these sources: http://jeremy.zawodny.com/blog/archives/000458.html http://dev.mysql.com/doc/mysql/en/FreeBSD.html Jeremy is using -O -march=pentiumpro and MySQL AB -O2 -fno-strength-reduce. Does anyone compared this? Or it make no sense? PS. MySQL 4.x and FreeBSD 4.x I know that mine work. :-) I suspect you'll have a hard time finding a performance differnce between the two unless you try really hard to measure it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting custom information in processlist
On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote: Hi, I'm using a single shared user-login for a web-based application to my mysql database - is there a way of including some information at connect time or during processing that would show up when issuing `show processlist'? Only if you prefixed each query with a comment: /* foo #3 */ SELECTL * FROM world ORDER BY... But not at connect time. Basically, I need to able to distinguish potentially multiple connections with the same login and process information some how. This sort of thing is possible in other SQL engines so probably should be in MySQL too. Interesting. How do other systems handle this? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Memory Usage
On Thu, Aug 05, 2004 at 10:27:54AM -0500, Sashi Ramaswamy wrote: Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. I suspect most of that is shared memory, not private. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
On Wed, Aug 04, 2004 at 04:32:42PM +0300, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). And note that the sync() call not only physically writes .frm file to disk, but also everything else which is in write cache. If the server is under load, sync() call may take seconds, tens of seconds or even hundreds of seconds. As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) Wouldn't it make more sense to use fsync() on just the .frm file? Or am I missing something here? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.20 vs MySQL 4.1.3b
On Tue, Aug 10, 2004 at 02:12:50PM -0700, Haitao Jiang wrote: Hi, I just wondering if there is a list of important features or bug fixes comparing these two versions. We are debating whether to go one or the another. Also, when is the 4.1.4 going to come out? Is it still beta? Sure there is. They're listed in the documentation. http://www.mysql.com/doc/ Jeremy (Yes, I know that's the old URL, but my fingers memorized it years ago) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficiency of searching in SETs (InnoDB)
On Tue, Aug 10, 2004 at 11:40:28AM +0200, Jigal van Hemert wrote: How efficient is InnoDB with searching in such sets? Will it use an index or must it perform a full table search? Are there alternatives which are more efficient regarding search speed? Sets result in table scans if they're the only condition in WHERE clause. Until MySQL has a way of indexes them, you're stuck. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why drop a index takes so long to do?
On Thu, Aug 05, 2004 at 11:27:40AM -0700, Haitao Jiang wrote: Hi, I have a 3GB index which includes a fulltext index on 2 columns. I wanted to drop this fulltext index from the table, but it took forever. Why it is so slow? Is it because MySQL basically go through the index file (there is only one) and re-write it to another file and then copy it back? Yes. Any other fast way to drop a index like this? Not that I know of. Someday each index will be in a separate file (optionally?). Then it'sll be quite fast to drop an index. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql bin files
On Tue, Aug 03, 2004 at 11:16:43AM -0600, Michael Gale wrote: Hello, I am new to mysql and am in the middle of upgrading our mysql server to the latest release. Upon viewing our currently data directory I noticed a large amount of hostname-bin-### files. Some of hostname-bin-### have time stamps of January -- from the documentation I have read I can uses these to restore mysql data. So am I correct in thinking that I can safely backup any hostname-bin-### files older then let's say a week to a backup server and then delete them from the data directory ? I'd use the PURGE MASTER LOGS command rather than simply removing the files. We provide a sample script for something like this in Chapter 7 of High Performance MySQL. It's available here: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
On Tue, Aug 03, 2004 at 01:08:58PM -0400, [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? Because it's just not a problem for most folks. What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. If that's something you're really worried about, you probably shouldn't be using the autoextend feature on your tablespace file(s). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Not that I've heard of... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication + InnoDB = badness
On Tue, Aug 03, 2004 at 11:11:52AM -0700, Jon Drukman wrote: We were having terrible problems with a master/slave setup. The master does a huge amount of writes, and the slave simply started lagging behind, despite both machines being identical hardware-wise. This made the application basically unusable because eventually the slave was hours behind the master, and had no chance of ever catching up. I disabled InnoDB on the slave (skip-innodb in the my.cnf file) and now it has caught up and is keeping up fine. The weird thing is it worked fine with InnoDB enabled for many weeks. Also even after we re-converted all the slave's Inno tables back to MyISAM it *still* lagged out. Only after I disabled the Inno engine entirely did the problem abate. Any ideas why? Does InnoDB use resources even if there are no active tables using the engine? This is most confusing. You're not using InnoDB *at all* and it was slowing down the slave? What InnoDB options had you set in my.cnf anyway? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Tue, Aug 03, 2004 at 12:44:05PM +0530, [EMAIL PROTECTED] wrote: Hi Friends: We are using mysql version 4.0.17 on Linux with a master and a single slave both running on the same node. We have encountered a problem in replication in the following scenario: First the slave got abnormally terminated while there were some active connections to the master. The master was also terminated abnormally in quick succession. After both master and slave was brought up, we noticed that the last transaction in the master just before the abnormal termination is getting replicated twice in the slave. Thanks in advance. Please pardon me if there is a confidentiality note that gets automatically attached with this mail and do ignore it. I'm going to make an educated guess. There's a race condition and you lost. The slave copied that query from the master's binlog but was shutdown before it could update the master.info file to reflect it's new position in reading the master's binlog. When you started it back up, it fetched it a second time, not knowing it had done so previously. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what os to use for mysql on amd64?
On Fri, Jul 30, 2004 at 11:46:02AM -0500, Pete Harlan wrote: On Thu, Jul 29, 2004 at 06:26:23PM +0300, Egor Egorov wrote: ... No. I've forgot to tell that the -Max binary is linked dynamically because it uses SSL. Is there a reason the SSL libraries can't also be linked statically? I think it's a licensing issue, but I haven't looked at the OpenSSL license and don't remember the specifics from when I last heard. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking in MyISAM
On Fri, Jul 23, 2004 at 01:44:16PM -0400, Michael Sleman wrote: Does MyISAM lock the whole table when doing SELECTs? Yes, each client obtains a read lock on the table for a SELECT query. How about if there are several processors? Is there locking? It's no different with multiple CPUs. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perpetual connecting to master
On Sun, Aug 01, 2004 at 08:42:50PM +0300, Issac Goldstand wrote: I just set up another reeplication slave for one of my servers - it's not something new to me. I'm using 4.1.3-beta on a new server which will eventually take over as the master. I set up a server id, did change master to... and started the slave - but the slave seems to perpetually stay in a state of Connecting to master I've done thie before, but I've never encounterred this - can anyone give pointers on how to troubleshoot? The first thing I'd try is to connect from the slave machine to the master machine using the same credentials. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Mon, Jul 26, 2004 at 01:26:15PM -0500, gerald_clark wrote: Steve Richter wrote: exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. And this is where the confusion start. MySQL is covered by the GPL. So is Linux. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Mon, Jul 26, 2004 at 05:43:58PM -0400, Steve Richter wrote: looks like the answer is no. As soon as fee based software touches the mysql install on the PC, the user is obligated to pay the $250. At least I guess it is the user who has to pay. So a copy of MySQL on Windows is free until I buy a copy of MS Access to build forms that display the data stored in MySQL? I don't think your fee based software claim is accurate at all. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Mon, Jul 26, 2004 at 11:49:02PM -0500, mos wrote: At 04:43 PM 7/26/2004, you wrote: looks like the answer is no. As soon as fee based software touches the mysql install on the PC, the user is obligated to pay the $250. Actually I believe the MySQL 4.x license is more stringent than that. If the MySQL database is distributed within a company for free, then a license is still required, unless the application is distributed under the GPL. Huh? I bet it depends on the country. In the USA, companies are considered singular legal entities. Internal distribution is aking to giving copies to yourself. The GPL doesn't restrict that. Wouldn't it be nice if MySQL created a nice little table with a couple of columns License and No License and a bunch of examples down the left hand side? Yes, it would de-mystify a lot of this. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Mon, Aug 02, 2004 at 09:24:59AM -0700, Marc Slemko wrote: I believe that MySQL AB is deliberately vague and confusing on their licensing page to try to get people to buy mysql licenses. All their words there don't matter though, what matters is the actual license. It would, however, be nice if their commentary were a bit closer to the reality of what the GPL means. You're far from being alone in that assessment. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MaxDB and cluster
On Tue, Jul 20, 2004 at 04:18:42PM +0800, [EMAIL PROTECTED] wrote: I am new to MaxDB and clustering. It seems that both products provide enterprise features and high availability. Is clustering replacing MaxDB? What are the difference between them? Are they targeting different users? Based on what I've read, MaxDB's goal is to be able to host an SAP install. I suspect that MySQL will get to that point too, but it sounds like MaxDB is gonna be there first. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Mon, Aug 02, 2004 at 03:11:10PM -0500, mos wrote: At 03:41 AM 8/2/2004, Jeremy Zawodny wrote: I bet it depends on the country. In the USA, companies are considered singular legal entities. Internal distribution is aking to giving copies to yourself. The GPL doesn't restrict that. Well, I'm not so sure. Here it is straight from the horses mouth namely [EMAIL PROTECTED] Free use for those who never copy, modify or distribute. As long as you never distribute (internally or externally) the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not, http://www.mysql.com/products/licensing/opensource-license.html. Yes, I've read that bit of the web site before. You notice they say never distribute (internally or externally) the MySQL software in any way... I don't know if the MySQL Software they are referring to are the MySQL libraries libmysql.dll or libmysqld.dll files which is needed to run most MySQL applications, or any file that comes from MySQL AB. Given that all of the code is licensed under the GPL, it hardly matters which piece they're talking about. The GPL does not disallow me making a copy of libmysqlclient.so and putting it on a second machine. If you think it does, I'd like to know *where* in the GPL you (or whoever) sees it. From what the GPL FAQ tells me: http://www.gnu.org/licenses/gpl-faq.html Does the GPL allow me to require that anyone who receives the software must pay me a fee and/or notify me? No. In fact, a requirement like that would make the program non-free. If people have to pay when they get a copy of a program, or if they have to notify anyone in particular, then the program is not free. See the definition of free software. The GPL is a free software license, and therefore it permits people to use and even redistribute the software without being required to pay anyone a fee for doing so. http://www.gnu.org/licenses/gpl-faq.html#DoesTheGPLAllowRequireFee To argue otherwise (as some at MySQL AB seem intent on doing) is absurd. While their CEO speaks and conferences about the success of open source business models another arm of their organization seems to be trying hard to warp the GPL to suit their sales targets. And, quite frankly, I'm sick of it. I agree with you that it is rather stringent to prevent internal distribution of a MySQL application within a company, and I see no logical reason for preventing it. Probably because there isn't one. :-) As I read it, the language they're using prevents the finance department from receiving a copy of the database and application from the sales department. Right. Of course, every department is free to download a copy from the MySQL web site, right? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question/issue...
On Sun, Aug 01, 2004 at 09:22:21AM -0700, bruce wrote: hi... i'm testing an app where i want a parent app to create the mysql db connection link/handle, and basically pass this handle off to child procesess. is there any reason why this can't be done? are there any examples of this already being accomplished that you might provide??? as far as i can tell/see, it should be possible. as long as the parent is still running, the connection link/handle should still be valid. i'm going to ultimately be working in perl/php/c but if i can get it working in one language, i can get it in the others as well any thoughts/comments/criticisms/etc Can you establish a connection in the parent, fork a child, and then let the child use it? Yes, you can do that. But you need to be careful. If the parent and child *both* try to use it, chaos will ensue. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: better performance with optimize!?? (jboss)
On Sun, Aug 01, 2004 at 01:42:24PM +0200, Marc wrote: I use Mysql with JBOSS as applicationserver. i have strange response-time differences, which i can't explain. 1) after reboot the computer, it takes about 300ms to read 12 entities (cmp, read ahead, 2 rows each entity) One entity is accessed by primary key, the others by foreign key. 2) when i reboot the computer and run mysql optimize first, it takes only about 80ms to read the 12 entities!! Caching? In case #2, MySQL may have cached large amounts of the table during the OPTIMIZE. 3) when i do the same with pure java / jdbc (outside jboss), it takes only 30ms!!! it doesn't matter, if i run optimize or not! JBoss overhead? I dunno, I'm not a Java guy at all. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: soft link mysql socket?
On Thu, Jul 29, 2004 at 08:10:42PM -0700, Ginger Cheng wrote: Hello, mysql Gurus, Because of disk space issues, the data dir of my mysql DB is somewhere else other than /var/lib/mysql. I did not link /var/lib/mysql to the real data dir though. It works OK until I want to use perl DBI which complains can't connect to mysql thru '/var/lib/mysql/mysql.sock'. So I just create a soft link of '/var/lib/mysql/mysql.sock' pointing to the real socket and it works. I am wondering if this is gonna give me any problem in the future. It shouldn't be a problem. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql libs and multiple hostnames
On Mon, Jul 26, 2004 at 04:09:51PM -0400, Charles Sprickman wrote: I have a quick question about how programs linked against the mysql C libraries handle the following: -assume three mysql hosts, say 10.0.0.2, 10.0.0.3, and 10.0.0.4 -assume a dns name db.example.com that returns the following: [EMAIL PROTECTED] host db.example.com db.example.com has address 10.0.0.2 db.example.com has address 10.0.0.3 db.example.com has address 10.0.0.4 If my client program repeatedly connects to db.example.com and my nameserver round-robins through those IPs, will the client also keep cycling through those? What is the behaviour if one of those hosts does not respond? Will the client application then try the next one? The MySQL C client libarary doesn't treat this case specially. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Overhead and Benchmarks
On Tue, Jul 20, 2004 at 10:57:02AM -0700, Nathan wrote: Hi, Does anyone know of any written stats on how much overhead for CPU/ Disk IO replication has for a single master and a single slave? I am looking for any detailed stats on the proformance issues associated with replication. Overhead on the master? I've not measured it, but it's trivial. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query locking up the system
On Mon, Jul 19, 2004 at 07:52:01PM -, Aman Raheja wrote: Hello I executed a query on my server mysql select count(*) from mytab where change 2004070100 And another mysql select count(*) from mytab where change 2004070100 Would like to mention, the table is 240 million records and 78GB (MYD) I've been waiting forever to get any output. If the query is not using an index, it will do full table scan and take a very long time. Moreover the server is frozen. No response to the ssh client and I am not even able to login the console - the server is FROZEN. Sounds like an operating system or configuration problem. Running a big query against MySQL shouldn't affect a modern operating system's ability to multitask like that. Has anyone experienced this kind of load. I'm not sure what kind of load you have yet. This sounds very odd. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
On Tue, Jul 13, 2004 at 11:23:03AM -0400, Marc Knoop wrote: Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? That should be a problem at all. I know of much larger instances (millions of records) doing the same on similar (or less) hardware. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle 2 MySQL updates/replication?
On Tue, Jul 13, 2004 at 06:11:22PM -0700, Carl Edwards wrote: Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Golden Gate Software makes a product that does this. I'd have a look at what they offer. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
On Mon, Jul 12, 2004 at 03:49:33PM -0400, Marc Knoop wrote: I have 4 servers in my environment: DEPOT - master server WWW1 - web server #1 WWW2 - web server #2 WWW3 - web server #3 The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. Is configuration as simple as the correct entries in my.cnf? That is, can muliple entries for master-host, master-user... exist? Any caveats with this configuration? You cannot do that. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html See figure 7-2. You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solaris Performance Issue
On Mon, Jul 12, 2004 at 10:14:09AM +0800, Linda wrote: Hi Jeremy, I didn't find any resouce limit but the performace is very bad. Do you have any suggestion how to tune the solaris to provide the better performance for Solaris? Well, MySQL doesn't just randomly slow down. There's either a software or hardware cause. If you're not maxing out your memory, CPU, or disk bandwidth it's probably a software problem. And, not being a Solaris expert, I don't know where to start if you think the OS is the culprit. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solaris Performance Issue
On Wed, Jul 07, 2004 at 06:23:45PM +0800, Linda wrote: Hi, My old mysql is 3.23.56 on RedHat9(Intel). After moving mySQL to Solaris 9 (Sun F280R/2GB Memory) and upgrading mySQL to 4.0.20, I got a lot of complaints about the performance for select and update. Have anyone can tell me if there is anything I should tune for Solaris or MySQL to improve the performance. Without knowing what sort of bottlenecks you're seeing, it's really hard to say. What's the resource limit? CPU? Disk? RAM? BTW, I've found Sun boxes of that vintage (I have MySQL on a 280R also) to be quite a bit slower than much cheaper Intel hardware. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Thu, Jul 08, 2004 at 09:23:17PM +0300, Egor Egorov wrote: Jeremy Zawodny [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. http://jeremy.zawodny.com/blog/archives/000203.html and so on. :-) Assuming one follows the *bold* UPDATE link at the top, I still don't see it... What about SMP machines? Many of our MySQL/FreeBSD 4.x/LinuxThreads machines are SMP. In general the experience may vary but I have been unable to manage it to work fine. :( Like, updating the key_buffer variable on the fly to enlarge it causes the computer to hang at a certain point. I had LOTS of os-dependent things like that. How is that not a MySQL bug? :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Script to purge
On Wed, Jul 07, 2004 at 07:42:46PM +0200, Luis Mediero wrote: Hi, I would like write a script to purge every nigth the master log with a cron process. I need do it every nigth after load a lot of data into the master. I know if i do 'show master status' i can see the file_name of the last log file and then do -purge master logs to 'file_name'-. Is possible put the file name into a variable and then do - purge master logs to '$variable' -, into a script?. Someone have a example?. I can't use 'PURGE MASTER LOGS BEFORE ' because I use the 4.0.20 version. :-( Chapter 7 of High Performance MySQL covers this. And it's even availble free on-line: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html You'll find Perl script that does that. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Wed, Jul 07, 2004 at 07:11:17PM +0300, Egor Egorov wrote: Jeremy Zawodny [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. http://jeremy.zawodny.com/blog/archives/000203.html and so on. :-) Assuming one follows the *bold* UPDATE link at the top, I still don't see it... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 05:07:24PM +0300, Egor Egorov wrote: Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 12:48:39PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Egor Egorov wrote: Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. I thought that was pretty old news. I had also tried with Linux Threads and found similar poor performance. Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Interesting. Do you find the mysqld process using alot of CPU? Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Without looking at the box, I can hazard a few guesses. I suspect you're seeing one of two things (or both). I suspect that qmail, like some mail servers, makes heavy use of syncrous disk writes. And it's probably competing with MySQL for precious disk I/O resources. (Are they sharing a disk?) What's iostat look like? If you're not using LinuxThreads, you'll find that MySQL on FreeBSD behaves very poorly in high I/O situations. FreeBSD's userspace, self-scheduling threads just suck for database applications. There's no way around that. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Point of Sale
On Wed, Jul 07, 2004 at 12:05:01AM +0200, Schalk wrote: Hey there! Does anyone know where I can find information regarding connecting MySQL and a Point of Sale device? I suspect it'd depend on the device in question... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API 3.23 to 4.1
On Tue, Jul 06, 2004 at 03:40:02PM -0700, Ron Gilbert wrote: I am going to upgrade my MySQL server from 3.23 to 4.1, but I have a C program that needs to continue to connect to the new server, and it can't be recompiled. Is the old API 100% backwards compatible with a 4.1 server? I assume the performance is the same? You're confusing the API and the protocol. A 4.1 server can speak to a 3.23 client just fine if configured properly. See: http://dev.mysql.com/doc/mysql/en/Password_hashing.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 04:26:04PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Interesting. Do you find the mysqld process using alot of CPU? No. There's no one process chugging CPU juice, but many many small processes (qmail-remote, qmail-local, maildrop, etc.). Looking at vmstat, it seems like just keeping track of all the processes and scheduling them is problematic (sorry about the wrapping): procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90 [snip] Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( From the vmstat manpage, it appears that the faults line, specifically cs represents context switches, which the best I can understand is that that indicates how much the cpus are thrashing from process to process in the run queue. At some point this number gets high enough that processes block even though there's no memory shortage, swapping, or disk i/o problems: faults Trap/interrupt rate averages per second over last 5 seconds. in device interrupts per interval (including clock interrupts) sy system calls per interval cs cpu context switch rate (switches/interval) This might be a red herring, as I have no similarly loaded boxes to compare these numbers to. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) Without looking at the box, I can hazard a few guesses. I suspect you're seeing one of two things (or both). I suspect that qmail, like some mail servers, makes heavy use of syncrous disk writes. And it's probably competing with MySQL for precious disk I/O resources. (Are they sharing a disk?) Everything's on a 4 disk RAID 1+0 array. What's iostat look like? Not very heavy, it doesn't seem disk bound: tty da0 md0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id -0 -85 0.00 0 0.00 0.00 0 0.00 -90 -3-107 -5306 0 30 8.77 26 0.22 0.00 0 0.00 6 0 10 1 83 0 30 10.57 66 0.68 0.00 0 0.00 3 0 11 1 84 0 30 8.79 61 0.53 0.00 0 0.00 17 0 9 1 73 0 30 9.80 35 0.33 0.00 0 0.00 4 0 7 1 88 0 30 1.00 1 0.00 0.00 0 0.00 1 0 5 0 94 Yeah, you're not doing much I/O at all. Hmm. If you're not using LinuxThreads, you'll find that MySQL on FreeBSD behaves very poorly in high I/O situations. FreeBSD's userspace, self-scheduling threads just suck for database applications. There's no way around that. I was using LinuxThreads, but found that it made the situation worse; I think scheduling a few hundred procs was harder than dealing with the threads; just speculation on my part... Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? I also couldn't find good docs explaining LT on BSD very well. I was worried that each thread (in reality, a process) in the LT model had it's own memory footprint. No, the memory is almost all shared, so memory overhead isn't an issue. Of course I knew about LT from reading your site... I'll also reiterate a few datapoints about mysql for any latecomers: -Most queries are simple selects to grab user info (check password, check homedir). Using the query cache at all? -The few updates or inserts are for a relay table for smtp use; it simply tracks where each pop/imap user connects from and smtp can refer to that table to see if someone should be able to relay mail. -There is also an insert/update on a last auth from... table. The hardware is a dual Athlon MP-1600 smp box with 1GB of RAM. Queries/second is about 15 tops, and there is a hard limit on the mail side; the box will only accept a finite amount of inbound/outbound smtp connections. Also, out of curiousity, the db servers that you've mentioned
Re: slow response time
On Tue, Jul 06, 2004 at 08:05:43PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90 Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( You read it right. Lots. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) Interesting. I'm not really more than about 70% sure of what a context switch is, my best read of it is that it's bad when those numbers go up because the scheduler is inefficiently juggling process around in the run queue... A context switch is anytime the CPU switches processes or goes from user space back to kernel space. So a lot of syscalls would certainly do this. Yeah, you're not doing much I/O at all. Hmm. Yep, hmmm indeed. :) Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? I've worked with some much larger qmail installs, and the brick wall we hit in scaling it up is very similar; the box just seems to drown in syscalls. I think this is a feature of qmail; even if you're not very familiar with it, the basic gist is that a message goes from process to process rather than having a monolithic process like sendmail. At some point, I'm thinking this just doesn't scale well (we had trouble doing more than 2000 or so concurrent remote deliveries on a dual xeon box). Yikes. And to think that I always stayed away for philosophical reasons alone. :-) No, the memory is almost all shared, so memory overhead isn't an issue. Excellent, that's very good to know. -Most queries are simple selects to grab user info (check password, check homedir). Using the query cache at all? Not sure... I'm using the values for caches and whatnot from the my-large.cnf in the distribution. The my-large.cnf I'm looking at has a 16M query cache, but doesn't explicitly turn it on. See what show variables like 'query_ca% says: mysql show variables like 'query_ca%'; +---+--+ | Variable_name | Value| +---+--+ | query_cache_limit | 1048576 | | query_cache_size | 33554432 | | query_cache_type | DEMAND | +---+--+ 3 rows in set (0.01 sec) You see demand there because we set query_cache_type = 2. But if you had = 1 you should see either ON or ENABLED, I don't remember which. If not, it's probably OFF or DISABLED. Also, out of curiousity, the db servers that you've mentioned Yahoo is running are all likely dedicated mysql boxes, right?No dual-purpose stuff, correct? That's accurate for the majority of servers, yes. But not because apache and MySQL don't co-habitate well. It's because the raito of apache machines to mysql machines needed is rarely 1:1. Yeah, I was just hoping to find someone with a similar setup to see how their box is behaving. Well, we've run MySQL on the same box as various things (Bugzilla, RT, etc) and never had problems like that. From what you've described about qmail, I can understand why. You'd think, yeah. I don't know squat about qmail, having moved from Sendmail to Exim a few years back. Maybe it really hammers systems? Apparently. I've started playing with Postfix a bit more and I find it to be much nicer than qmail. But for the foreseeable future I'm stuck with qmail. If I feel real brave I'll raise the syscall issue on the qmail list. Good luck with that. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64 Bit Support
On Fri, Jul 02, 2004 at 10:27:04AM -0700, David Griffiths wrote: Sorry - didn't read your email closely enough. The Windows version is not native - runs under Cygwin. Is there a version of Cygwin for the Itanium 2? The Windows version of MySQL doesn't require Cygwin. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
On Thu, Jul 01, 2004 at 11:34:29AM +0800, MaFai wrote: Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? You need to identify the bottleneck. Is the slave's IO thread taking too long to pull binary log entries from the master? Or is the SQL thread hopelessly behind because of a slow hard disk, CPU, or memory shortage? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac OSX Tiger and 64 Bit
On Tue, Jun 29, 2004 at 10:57:41PM -0700, Bruce Dembecki wrote: So I have a question for those who understand developer speak and MySQL builds and so on... Apple announced their new OS earlier this week, including this information on the improvements to 64 Bit version using the G5 processor: http://www.apple.com/macosx/tiger/64bit.html One of our biggest problems to date on our G5 servers is despite the bulk ram we have installed, the current Apple OS isn't really 64 Bit so we can't give the InnoDB caches more than 2Gb of ram, and thus there are always no empty pages. This statement from Apple stops short of saying the OS was fully 64 bit... But I think they are saying that apps such as mysqld will be able to call larger chunks of memory, which is what we want. It sure looks to me like Tiger will remove those limits... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Production release of MySql 4.1
On Wed, Jun 30, 2004 at 11:52:32PM +0200, Jocelyn Fournier wrote: Hi, 4.1.3 is labeled beta in the bktree. So I assume 4.1.3 will be beta when it will be released ;) And that looks to be soon, based on the commits I've seen. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory to Memory INSERTS
On Tue, Jun 29, 2004 at 08:46:35PM -0400, Alejandro Heyworth wrote: Eric, I'm looking for a way to eliminate the construction, transmission, and parsing of the long multi-row INSERT queries that we are issuing from our client app. Since we are inserting 200k rows a shot, we're looking for every boost that we can find. * Connecting: (3) [want to use a connection pool] * Sending query to server: (2) [want to eliminate] * Parsing query: (2) [want to eliminate] * Inserting record: (1 x size of record) [no way around this] * Inserting indexes: (1 x number of indexes) [no way around this] * Closing: (1) [want to use a connection pool] Since we have already tuned the server, I'm looking for other ideas. Radical ideas are welcome! I missed the earlier part of the thread, but have you considered simply building raw MyISAM data files (.MYD) from your application? You could use a merge table over top of them after using myisamchk or ALTER TABLE to add the appropriate index(es). Just a thought. The file format is documented and not terribly difficult for some applications. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance issues
On Tue, Jun 22, 2004 at 01:34:39PM -0400, Aram Mirzadeh wrote: We have an internal SNMP monitoring system that is monitoring about 10,000 devices. Each device is pinged then pulled for about an average of 25-30 elements. Each of the ping results and elements are then stored in text file, then another system picks them up (NFS) and inserts them into a MyISAM (3.23.54) database. The data is kept for 13 weeks. The database system is a Xeon 4 way, 12GB of ram with a striped raid array dedicated to the database files and its indexes and such. Every 5 minutes another process goes through the last set of inserts and compares them for any threshold breaches, so the entire last set of data is looked at. We're falling behind on the inserts because the system can't seem to handle the amount of inserts, the front end that generates the web pages based on the previous records is dogging down. I have read the regular optimizations papers and have done as much as I felt safe, are there any huge database optimization papers? Anything I should be looking at? I'd consider bulking up the INSERTs, performing multi-row INSERTs rather than doing them one by one. That can speed things up quite a bit in my experience. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance issues
On Mon, Jun 28, 2004 at 09:21:04PM +0100, Andrew Pattison wrote: By default MySQL flushes keys to disk with every INSERT, hence the performance degredation with performing several single INSERTs one after the other. The following extract from the MySQL documentation hints at one way of changing this on a per-table basis: a.. Declaring a MyISAM table with the DELAY_KEY_WRITE=1 table option makes index updates faster because they are not flushed to disk until the table is closed. The downside is that if something kills the server while such a table is open, you should ensure that they are okay by running the server with the --myisam-recover option, or by running myisamchk before restarting the server. (However, even in this case, you should not lose anything by using DELAY_KEY_WRITE, because the key information can always be generated from the data rows.) There is also a way of getting MySQL to do lazy writing of indexes on a global basis but I couldn't find a quick reference to that. Delayed Key Writes: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html Search that page for delay and you'll find it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
On Wed, Jun 23, 2004 at 11:36:52AM -0400, Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Why is sorting required at all? Indexes *are* sorted already. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
On Wed, Jun 23, 2004 at 02:28:15PM -0500, gerald_clark wrote: I suspect he is refering to 3.23's inability to use an index on a ORDER BY xxx DESC In other words ancient history :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 06:57:51PM +0200, Leonardo Francalanci wrote: I'm doing some tests on a 16.000.000 rows table. Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed that disks are at 0.1%, while cpu arrives up to 80%. How is that possible? My HDs are IDE. MySql has around 30Mb of ram, I thought it should read a lot from disk. Even doing lots of queries by key I get only high cpu usage, not disk reads. Is that normal? Hard to say, but in a table scan the CPU does have a lot of work to do. It needs to do about 16,000,000 comparisons (based on your info). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 07:10:59PM +0200, Leonardo Francalanci wrote: Hard to say, but in a table scan the CPU does have a lot of work to do. It needs to do about 16,000,000 comparisons (based on your info). Why comparison? It's a sum... Oh. You didn't say thta. :-) And the table is not small: 272,000,000 bytes! And disk is very low (almost 0%) How much RAM does your machine have? If it's nearly all cached, you'll be CPU bound rather than disk bound. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: R: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 07:37:50PM +0200, Leonardo Francalanci wrote: Oh. You didn't say thta. :-) Well, I wrote Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed but don't worry ;) Oh. Damned Mondays. I never liked 'em. :-( How much RAM does your machine have? If it's nearly all cached, you'll be CPU bound rather than disk bound. 1Gb. I'm using Solaris 8. The SUM() works as expected now (disks works a lot), but accessing by key is very cpu-intense and still 0% work on disk. To sum up: table scans are very disk-intense (which make sense), accessing by key leaves disk on idle (I see that it should not work as with a table scan, but at least a little!) Hmm. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
On Tue, May 18, 2004 at 10:56:14PM +0200, Mark wrote: Mihail Manolov wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. I very distinctly remember reading the onsite documentation which stated that, since the C headers were changed, relative to 3.23.x, that I needed to reinstall the DBD drivers as well (not just DBI). Which I did. If you kept the old libmysqlclient.so.?? around that wouldn't be a problem. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On Fri, May 14, 2004 at 01:40:37PM +1000, Chris Nolan wrote: Jeremy Zawodny wrote: I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Didn't InnoDB gain PAE support on some platforms a little while ago? I think it may on Windows. That rings a very vague, distant bell. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM transactions
On Fri, May 14, 2004 at 02:16:05PM +1000, Chris Nolan wrote: Jeremy Zawodny wrote: On Fri, May 14, 2004 at 04:38:56AM +0200, Lorderon wrote: Will MyISAM support transactions in the future versions? Is it possible? It's possible, but I don't see it happening for quite a while... Additionally, is there much of a point considering the fact that MyISAM's strengths are simplicty and a lack of transaction related overhead? Couple this with InnoDB's excellent performance and it looks very unlikely to happen IMHO. I agree. But don't be surprised when MyISAM picks up at least a few of InnoDB's features... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote: Hi List, What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? It does not. The command doesn't replicate. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... :-( -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 04:29:29PM -0500, Dan Nelson wrote: In the last episode (May 14), Jeremy Zawodny said: On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... I think not having the slaves optimize is more surprising. Slaves get the exact same insert/delete/update queries as the master, so why should only the master get its tables optimized? Easy. In the case of ANALYZE, OPTIMIZE, or REPAIR one would generally want to stagger their execution on slaves. Otherwise the exact same tables are unavailable at the same time. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On Thu, May 13, 2004 at 10:21:15AM +0200, JFL wrote: The InnoDB storage engine can use raw disks without a filesystem. Would that be the fastest possible setup? Probably, yes. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:28:26AM +0200, JFL wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. Would that be faster than having converted the binary logs to sql commands in one file and then use that? It would be, yes. It's hard to say how much faster though. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On Thu, May 13, 2004 at 11:00:17AM +0200, JFL wrote: I've been told that InnoDB on a raw partition is the fastest setup. Actually, you've been told that it's probably the fastest. To setup my system for this, could I create a partition called /innodb and adjust the my.cnf like this? innodb_data_home_dir = /innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ Nope. I suppose that innodb_log_group_home_dir and innodb_log_arch_dir can not be on a raw disk? Check the InnoDB docs. They explain how to setup raw disk partitions. You'll be using device names, not mount points. Any other tips, pros and cons? Pros: performance and bypassing the filesystem cache. Cons: loss of transparency and limited backup options. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On Thu, May 13, 2004 at 11:16:18AM -0400, Peter J Milanese wrote: Does the filesystem matter as much as disk throughput? I'd imagine that is where the bottleneck would be, at least as I've seen... Throughput or seek time? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:16:52PM +0200, Jacob Friis Larsen wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. How should relay-log.info look like? Like this: linuxweb1-bin.001 linuxweb1-bin.002 linuxweb1-bin.003 ... That looks right. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 7:11 AM To: MySQL List Subject: Re: InnoDB filesystem On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? It depends, if your datafile is less then 16 GB then the system cache can help, but fill up the innodb_buffer_pool you'll get better performance. Think of innodb as being its own virtual filesystem. If you have 16GB it's probably a 64 bit OS, and mysql is available in 64 bit. I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM transactions
On Fri, May 14, 2004 at 04:38:56AM +0200, Lorderon wrote: Will MyISAM support transactions in the future versions? Is it possible? It's possible, but I don't see it happening for quite a while... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On Wed, May 12, 2004 at 12:39:59PM -0500, Chris W wrote: I thouught I read somewhere a while back that MySQL was working on an option to create a MySQL partition so as to avoide all OS filesystem overhead to speed things up and I think to save a small bit of over head. Is this true? The InnoDB storage engine can use raw disks without a filesystem. Perhaps you're thinking of that? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --read-only startup
On Wed, May 12, 2004 at 04:11:41PM -0400, Tucker, Gabriel wrote: Sorry for the second post, I realized that my first post might not be clear... I want the failover and the switch from read-only to non read-only to be dynamic, no downtime. You can't do that. The read-only setting cannot be changed on the fly (yet?). You could do it via GRANT changes, but that's a clumsy hack if you ask me. :-( Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB replication to MYISAM
On Wed, May 12, 2004 at 01:38:04PM -0400, Shayne Paddock wrote: In section 16.7.5 in the mysql manual it says that you can setup replication with InnoDB tables on the master to MYISAM tables on the slave. While I have proven this to be true, it does not warn you that if in your Master's contstraints you have ON DELETE or ON UPDATE clauses those clauses will not run on the slave, causing the slave to be out of sync with the master. Correct. That's perhaps a documentation bug. Paul? I don't suppose there is a way to get those updates and deletes written to the binary log? Or any other way to keep the slave in sync? No. The binary log records the queries you send to MySQL and a few other bits of metadata. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
On Wed, May 12, 2004 at 02:50:55PM -0700, Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). If you have unique ids that are 36 characters, then use a char(36). That seems like the obvious thing to do. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Mysqld Stability and maintanability
On Tue, May 11, 2004 at 09:26:57AM +0700, Winner H Manurung wrote: Dear All, I was an Oracle dba, now my new company want to use Mysql 4.0.18. Does anybody here has experience of running multiple mysqld (i.e. multiple instance on one machine). Is it stable and totally independent to each other? If you configure it properly, yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
On Thu, May 06, 2004 at 11:29:18AM -0600, Alfredo Cole wrote: El Jue 06 May 2004 11:05, escribió: On Thu, May 06, 2004 at 06:55:38AM -0600, Alfredo Cole wrote: At the time I wrote Chapter 8 of High Performance MySQL, I tried to discuss the available options: http://www.oreilly.com/catalog/hpmysql/toc.html However, some of the commercial information was hard to come by, so if you're looking at those, you may need to discuss with the vendors too. Jeremy: I have ordered your book from Amazon.com. Great, thanks. I hope it's helpful. But I am not planning to use a commercial solution. I want to use OpenMosix, which is released under the GPL. Any suggestions would be welcome. My knowledge of OpenMosix is extremely limited. I've not heard of anyone successfully using MySQL with OpenMosix for fail-over. That doesn't mean it hasn't been done, but it'd be news to me. I assume you've also asked on the relevant OpenMosix list(s). One would hope they'd know. But maybe not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
On Thu, May 06, 2004 at 06:55:38AM -0600, Alfredo Cole wrote: Hi: I would like to add an identical server to the one I already have: Double Xeon processors, 4 Gb RAM and RAID 5 (Hardware) HD's. I would also like to cluster them using OpenMosix, but I'm told that MySQL 4.0 will not take advantage of the cluster. Is there a way to cluster MySQL so that queries will migrate to the new node when needed? Is there any docs I could dig into to see if this can be done? Books, how-to's? At the time I wrote Chapter 8 of High Performance MySQL, I tried to discuss the available options: http://www.oreilly.com/catalog/hpmysql/toc.html However, some of the commercial information was hard to come by, so if you're looking at those, you may need to discuss with the vendors too. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: many updates really slow
On Thu, May 06, 2004 at 10:12:19AM -0700, William Wang wrote: Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. Network latency is killing you. Send the cmd.sql file to host A and execute it locally. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issue
On Wed, May 05, 2004 at 06:44:09PM +0200, Mechain Marc wrote: Hello, Why such a SQL request running well on the master is not correctly replicated on the slave, set @providerId='012345'; insert into DATA_TYPE values (1,@providerId,'DATA_TYPE',1); Here is an extract of the Slave Logfile: MYBCK.log.1:ERROR: 1048 Column 'PROVIDER_ID' cannot be null MYBCK.log.1:040503 17:44:18 Slave: error 'Column 'PROVIDER_ID' cannot be null' on query 'insert into DATA_TYPE values (1,@providerId,'DATA_TYPE',1)', error_code=1048 The question is: why is the value of @providerId not properly replicated ? Is it a bug ? Them manual says that user variables don't replicate properly. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 100% CPU Spikes
On Wed, May 05, 2004 at 03:25:14PM +0100, Nick A. Sugiero wrote: Hi, I recently installed MySQL on a Windows 2003 Server last night to pull some stats from a online game I run, however everytime a query is sent to the database it uses 100% cpu causing a 100% cpu spike for a breif second - I'm using .asp pages if that helps. Is there anyway to avoid this at all as it effects the performance of the server / game and I'm also worried someone could use such a thing to cause a Denial of Service attack. Make sure your queries are well optimized. If they're not, they'll generally result in excessive CPU utilization. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
On Tue, May 04, 2004 at 02:44:26PM -0700, Ron Gilbert wrote: I am wondering what the best backup strategy is for my database. The database is used to store a very large number of binary files, ranging from a few K to 20MB's. The database stores thousands of these files. I can not put this data on the file server, it needs to be in the database. Currently the database is about 1.7GB's and will grow over time to 4GB or higher. I created 20 identical tables to hold the binary data. I was worried about the 4GB/Tables limit, so figured I would spread it out over several tables, also there is no a single point of failure for loosing all my data. To do nightly backups (I don't need anything more frequent), I copy the whole database directory to another HD on the same server, then the files that changed are rsync'd to another server. One of the reason that I store the data in several tables is so only the tables that changed need to be rsync'd to the other machine. It is not on a local net, so it can take a while to do. In any given day, only 10 or so binary files are added, so not a lot changes from day to day, but it can be one some days When I move to 4.1 and start using InnoDB tables (or should I), will the same technique of copying the whole directory and sync'ing only that tables that changed still work? Nope. Is there a better way to be doing this given the huge amount of binary data I have? I'd consider enabling the binary log and backing it up. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to make mysql sgml aware?
On Wed, May 05, 2004 at 02:00:31PM -0300, Leonardo Javier Belén wrote: Hi folks, Is there a way to make mysql sgml aware or any plan to do it in the near time? I think the answer is no but I don't really know what that question means. Can you elaborate? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld too busy to check its grant tables?
On Wednesday, May 5, 2004, at 11:48 US/Pacific, Atle wrote: What versions are you seeing this on? We've seen it exclusively on 3.23.58, with or without LinuxThreads enabled. Various versions of 4.0.xx. We don't run 3.23.xx much of anywhere anymore. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range query on datetime with index - any optimization?
On Wed, May 05, 2004 at 10:53:13AM -0400, Pete McNeil wrote: Hello folks, I'm usinng MySQL 4.0.17. I have a table something like: RuleID int, GMTBase datetime, Credited bigint, ... I have an index built on GMTBase. I have rougly 8 million rows. GMTBase stores a datetime for the top of the hour on a given date. I want to build a summary of the last 2 days without scanning every record. It appears that there is no way to get MySQL to use the index on GMTBase to avoid scanning all 8 million rows. I estimate it should only scan about 267K rows. Explain mentions the GMTBase index but says it will examine about a million rows. That seems to roughly match my estimate of the number of distinct GMTBase values. The query I want to run is: select RuleID, GMTBase, sum(Credited) from RuleHistograms where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) group by GMTBase Have I done something wrong or is there simply no way to avoid scanning all of those records? *Something* is wrong. I'm virtually certain I've done this sort of thing before without having MySQL perform full table scans. I can't tell what it is off the top of my head, but it should be that bad. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two way replication lock protocol
On Wed, May 05, 2004 at 01:07:58PM -0400, Luis R. Rodriguez wrote: Hello, I am wondering if anyone is currently working on a two way replication lock protocol. If so, what's the status. I understand there's little gain in having two way replication but -- I am just curious. What exactly do you mean by two-way replication? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two way replication lock protocol
On Wed, May 05, 2004 at 03:44:00PM -0400, Luis R. Rodriguez wrote: On Wed, May 05, 2004 at 12:01:27PM -0700, Jeremy Zawodny wrote: On Wed, May 05, 2004 at 01:07:58PM -0400, Luis R. Rodriguez wrote: Hello, I am wondering if anyone is currently working on a two way replication lock protocol. If so, what's the status. I understand there's little gain in having two way replication but -- I am just curious. What exactly do you mean by two-way replication? Well I guess it would be a lock protocol for replication among master servers, not just two :). What's the locking for? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should I trust my data after an InnoDB recovery?
On Thu, May 06, 2004 at 09:55:30AM +1000, Daniel Kasak wrote: Hi all. My boss just pulled the power on our MySQL server. Yes, I've already thanked him. It's a 4.0.18 server, with MyISAM tables and InnoDB tables, running on a 2.6.5 kernel and XFS filesystem. The XFS recovery proceeded without any complaints. The InnoDB recovery also seemed to go smoothly ( great work by the way ): 040506 9:38:41 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1032429395 InnoDB: Doing recovery: scanned up to log sequence number 0 1032430197 040506 9:38:42 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 139443, file name ./screamer-bin.210 040506 9:38:42 InnoDB: Flushing modified pages from the buffer pool... 040506 9:38:42 InnoDB: Started I've done a 'myisamchk' on all the MyISAM tables. They were all OK. My question is: should I trust my data now? Yes. InnoDB is fully ACID compliant. So anyway, should I bother with a restore? What's the chance of having data corrupted / missing after a power 'failure' and recovery as above? The only missing data should be uncommitted transactions unless you've changed InnoDB's default flushing frequency. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Threads on FreeBSD 4.9
On Tue, May 04, 2004 at 12:36:19PM -0700, Max Clark wrote: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug I am running FreeBSD 4.9 with Mysql 4.0.18 compiled with Linux Threads. I am running large inbound concurrency on Postfix which is forking several processes. How do I tune my mysql db servers to resolve this error? How large is kern.maxdsiz on that machine? How many concurrent connections are you trying to use? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, May 03, 2004 at 12:39:48PM -0500, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? In reality, the order is often the same but you should never DEPEND on that being the case without an ORDER BY clause. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: float in PROCEDURE ANALYSE() / misleading CREATE TABLE error
On Mon, May 03, 2004 at 05:56:03PM +0200, Hans-Peter Grimm wrote: Hi, I think there is 1) a problem with FLOAT recommendations in PROCEDURE ANALYSE 2) a minor problem with CREATE TABLE(f FLOAT(negative_value,...)) I think that there is maybe: 3) a bug in PROCEDURE ANALYSE that you should report in the MySQL bug tracking system. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning on FreeBSD
On Sat, May 01, 2004 at 09:50:14PM -0700, Max Clark wrote: Hi all, I have a db that is connection heavy running on FreeBSD 4.9. The server was compiled with Linux Threads enabled. I am searching for performance tuning information. Outside of enabling Linux Threads I haven't been able to find much else. I am under the impression that I should be using innodb tables instead of myisam, and I am sure there are a slew of other things that I could tune. Is there a site/document dedicated to this? My copy of high performany mysql by Jeremy Zawodny is in the mail, so any help in the mean time would be greatly appreciated. I am getting cannot create new thread errors on my system when load gets extremely heavy, are there any additional things I can do to help this? Would FreeBSD 5.1 be any better? When you say load there, what exactly do you mean? How many concurrent threads are running? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
