Re: Query time taken on disk
Hi Satendra, On 7/14/2014 5:48 AM, Satendra wrote: Hi there, I'm struggling to find the total time taken by a database query on the disk? As I understand when a database query start execution it takes some time inside the database engine some time to seek the result from disk (if that is not in cache/buffer) Can anybody from the group please suggest any clue about the execution time on the disk? Many thanks Stdranwl As stated in other emails you can use the performance_schema. Mark Leith has provided a stable version of ps_helper now called the sys schema and can be obtained on github here, https://github.com/MarkLeith/mysql-sys . There is quite a bit of help and examples in the README.md. Also you check into the experimental portion of it called dba helper also on github, https://github.com/MarkLeith/dbahelper . I think you will be able to find what you are looking for utilizing these tools that make performance schema much easier to use and under stand. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: find a particular record.
I am doing this to move data from one table to other and getting below error. How can check this particular record at this row number 921888 in dats_tem table. insert into reports1.dats1 select * from dats_tem; ERROR 1292 (22007): Incorrect datetime value: '-00-00 00:00:00' for column 'P_LAST_SOLD_DATE' at row 921888 An extremely quick and dirty solution: mysqldump -uusername -p -t --skip-opt database_name dats_tem | grep INSERT | head -921888 | tail -1 replacing username with your username and database_name with your database name, of course. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Query Log -- No Timestamp
On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote: The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Which version of MySQL are you running? I'm running 5.0.22 on my desktop, but I'm fairly sure that older versions included timestamps in the general query log. I just enabled mine to test this (by adding log = /var/log/mysql.log to /etc/my.cnf) and it looks something like: 070629 8:17:44 6 Connect [EMAIL PROTECTED] on monitoring 6 Query set autocommit=0 6 Query select * from urls where active=y 070629 8:17:45 6 Query INSERT INTO results VALUES (NULL,5,now(),0.5833,35267) 6 Query INSERT INTO results VALUES (NULL,6,now(),0.0137,0) 6 Query INSERT INTO results VALUES (NULL,8,now(),0.7762,28130) 6 Query INSERT INTO results VALUES (NULL,9,now(),0.0348,4217) -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmarking
On 3/14/07, Clyde Lewis [EMAIL PROTECTED] wrote: System Configuration: Sun Microsystems sun4u Sun Fire E2900 System clock frequency: 150 MHZ Memory size: 65536 Megabytes CPU: 12 @ 1200 MHz I'm looking for a tool that will allow us to determine the max number of databases that can run in a single instance of MySQL on a pretty beefy server( Spec above). In total we will have about ~40 MySQL instances running on this server. Each instance of MySQL, there will have between 30-60 individual databases supporting an OLTP application. I know that there are no know internal limits that MySQL have regarding the number of databases that can be created, but I would like get my hands on a tool that can simulate the number of databases and identify where we would potentially run into performance issues. As I mentioned above, your performance issues are going to come not from the number of databases, but from (primarily) how well-designed your database tables and queries are, and (secondly) how you configure the mysql server(s). One important factor to bear in mind is that with 40 separate MySQL instances on the single 64GB server, you will have a maximum 1.6GB of RAM per instance (excluding memory used by the O/S and other applications). This will have to be divided up between the various memory buffers (key_buffer, innodb_buffer_pool, etc.) allocated by each mysql process, so you might want to reconsider if you really need to run 40 separate mysql processes, or whether all the databases can live in the same MySQL instance and thus probably make better use of the available RAM. With regards to stress-testing and benchmarking, two popular tools for benchmarking MySQL servers are: Super Smack: http://vegan.net/tony/supersmack/ Sysbench: http://sysbench.sourceforge.net/ We need to determine whether to have multiple servers to support the ~40 instances or have all ~40 instances on the same machine. Any help of ideas would be greatly appreciated with this decision. I would be inclined to have separate machines, rather than put everything on one huge server. By spreading the data around, you are reducing the risk if the one mega-machine were to become unavailable, and also reducing resource contention (on the disks, CPU, RAM etc.). -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how would mysqld restart affect dynamically set global variables?
We're running mysql 4.1.20. If I understand the manual correctly, I can change max_connections while mysqld is running without restart mysqld to make the change take effect Correct. But what if mysqld restarts later in some other situations, like machine reboot, would my (global) change on max_connections remain? No; you'll need to put it in my.cnf in order for it to persist after mysqld is shut down. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair query overloading server
On 3/8/07, Gary Sewell [EMAIL PROTECTED] wrote: I'm having a problem with a single repair query seemingly taking over the whole server. I'll try and explain…. Large table or 1.2m rows, running a repair on this causes the query queue to grow and grow and finally max out. The problem isn't table locks because no-one else is using the test database I am repairing. Assuming you're running both the test database and the live database on the same physical server, it sounds like the problem could be that the table repair is starving the disks I/O-wise, causing other queries to run very slowly. If at all possible, try and move the test database to its own physical hard drive (or ideally, to its own hard drive on a separate disk controller); that way, any disk-intensive table repairs won't affect disk performance of the live database. Or move the test database to its own server; running test and live systems on the same physical server is asking for trouble! Even the most simple select queries on the live database are taking 500 secs!! I want the repair to run but not to the detriment of other queries. This problem will be a lot worse if the queries aren't well optimised; have you checked them with EXPLAIN? 500 seconds does indeed sound a bit excessive, even for a system under heavy disk I/O load. Once the repair is over it can take about 3 minutes before the queue starts to evaporate also, shouldn't it kick back into gear as soon as the repair is over? In my experience, It can take a while for the kernel to re-balance things. Caching of MyISAM data pages is managed by the operating system's page cache, and not by MySQL; if a large table has to be re-built, this will most likely have an adverse effect on MyISAM performance until things have balanced themselves out. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb corruption help needed!
On 3/11/07, Jean-Sebastien Pilon [EMAIL PROTECTED] wrote: I am running a mysql database server and we experienced a power failure. The mysql server does not want to restart because innodb is corrupted. Version info: Mysql version 4.1.11-Debian_4sarge7-log Debian sarge Reiserfs filesystem What I have tried: - Ran reiserfsck to fix corrupted file system. It fixed some problems, now says no corruption. - Restarted mysql with no success - Restarted mysql with innodb_force_recovery = 4 option, with no success - Restarted mysql with innodb_force_recovery = 6 option, mysql starts, but cannot access any data in my different schemas/tables See below for mysql startup log What should I do next ? Unfortuantely, it looks like your InnoDB tablespace is beyond recovery. If this data is critical and you don't have a backup, it might be worth getting MySQL Enterprise Support for this server and have them try and recover it -- see https://shop.mysql.com/enterprise/ for details. Assuming you have a backup of your data and you want to get the server back up and running so you can restore from this backup, you need to shut down the server, delete the corrupted InnoDB tablespace and associated .frm files, and start it up again (which will cause the server to re-create the tablespace from scratch): 1. Shut down mysql (/etc/init.d/mysql stop on Debian, iirc) 2. Go into your data directory, and run: a. rm -f ib* b. for f in `find -name \*.ibd`; do b=`echo $f | sed 's/.ibd//g'`; rm -f $b.ibd $b.frm ; done 3. Start the server again (/etc/init.d/mysql start on Debian, iirc) 4. Restore your backup (I'm assuming this is in a .sql file) by running the relevant sections back through the mysql program. By the way, a power outage shouldn't cause the InnoDB tablespace to become corrupted like this, it's ACID-compliant. It's possible that your disk system is doing some caching you don't know about, resulting in MySQL thinking data is on disk (or guaranteed to be put on disk) when it actually isn't. See http://brad.livejournal.com/2116715.html for details on how to test (and fix) this. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default root password not blank?
On 3/13/07, Merlin [EMAIL PROTECTED] wrote: I just compiled mysql4.1.22 on a suse 9.3 machine. All went fine, but when I try to change the password I get prompted for one. So I hit return as it is supposed to be blank, right? But it seems to be not: /usr/local/mysql/bin # ./mysqladmin -u root -p password testpw Enter password: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' if I do the command without -p I get: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Try: ./mysqladmin password testpw -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gaining statistics from MySQL
On 2/20/07, Clyde Lewis [EMAIL PROTECTED] wrote: I'm looking to find a way to determine the number of transactions that a particular database is processing each min/hour/day/month/year http://dev.mysql.com/doc/refman/4.1/en/show-status.html http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html Looks like Com_commit is what you're looking for (though you can replace commit with most other types of query, i.e. Com_select, Com_insert...). This is a counter, so you'll need to record the value periodically and work out the differences for a period. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the db.opt ?
Would like to know what is the archive db.opt. http://www.google.co.uk/search?q=mysql+db.optstart=0ie=utf-8oe=utf-8client=firefox-arls=org.mozilla:en-US:official -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication error: Unable to get certificate
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /root/.mysql/cacert.pem
Master_SSL_CA_Path: /root/.mysql/
Master_SSL_Cert: /root/.mysql/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /root/.mysql/client-key.pem
Seconds_Behind_Master: 0
[...]
SSL:3017771936:error:0200100D:system library:fopen:Permission
denied:bss_file.c:352:fopen('/root/.mysql/client-cert.pem','r')
MySQL (which runs as the mysql user) isn't going to be able to read
the certificates out of root's home directory, which is only readable
by root.
Put the certificates somewhere where the mysql user can read them -
your data directory would be a sensible place.
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to keep myqsl backup server
I need to create an application where i will be requiring a backup server like when one server fails i can switch automatically to the other server hosted somewhere else , now the data needs to be consistent on both the servers, one sol is i keep automated backups on the primary server and restore it by a script on the backup server You want replication, which will automatically keep your data up-to-date between two servers in near-real-time. See http://dev.mysql.com/doc/refman/5.0/en/replication.html Note: You will need to configure master-master replication if you want to start writing to the other server if the master fails, otherwise your data will be inconsistent. but i need to know how will i switch between servers and if i use domain names instead of ips how will i make immediate switch. You probably shouldn't use DNS entries for an application that requires immediate fail-over; configure your application to connect to an IP address, then change this IP address if you have to fail over to the backup server. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing images in the database
On 10/10/06, Rodney Courtis [EMAIL PROTECTED] wrote: I want to store jpeg images in a mysql database from php in the form of 'blob' types. Is this possible and can you detail for me the steps to achieve this? For a nice size performance hit, yup: http://www.zend.com/zend/trick/tricks-sept-2001.php?article=tricks-sept-2001kind=trid=2033open=1anc=0view=1 -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning a Value from a Stored Procedure
Hello- I am running MySQL 5.0 on Windows XP hitting it from Python (2.4) routines. I have an id column (auto increment, primary key) on every table that I use to join other tables. I use stored procedures to insert new records into each table and would like to know how to return the id value of the recently inserted record (it's more a problem on how to *get* the id value to return than how to return it). If anyone can show me how to do this or point me to the appropriate documentation, I would be grateful. --greg
Re: Zip Code Distance
On 8/29/06, Jesse [EMAIL PROTECTED] wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? http://www.cryptnet.net/fsp/zipdy/ -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing MySQL on Windows Server 2003 Standard x64 Edition
Go to Control PanelSystemAdvanced. Click Environmental Variables and Find the PATH variable under System Variables and add the full path, i.e. c:\Program Files\...\mysql\bin to the variable. -Original Message- From: David Lazo [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 2:54 PM To: mysql@lists.mysql.com Subject: Installing MySQL on Windows Server 2003 Standard x64 Edition I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where may I find sqlplus.hh??
On Thursday, 3 August 2006 at 22:56:54 -0700, Ratnadeep Nayak wrote: Hello, I have downloaded a code on c++ to talk to MySQL database from net which included a file sqlplus.hh , where may I find the file?Please help me soon. It's not in our code. It must be in the code you downloaded. If you don't find it there, follow up with the author. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgp7AbqDspfBb.pgp Description: PGP signature
Re: Why release 5.0.23 instead of 5.0.24?
On Saturday, 29 July 2006 at 9:53:29 -0700, Abdullah Ibn Hamad Al-Marri wrote: Jim Winstead wrote: On Sat, Jul 29, 2006 at 08:35:41AM -0700, Abdullah Ibn Hamad Al-Marri wrote: Why MySQL 5.0.23 while it has serious bug? I thought you guys will release 5.0.24 instead. This is still the case. 5.0.23 will not be released, and 5.0.24 will be released as soon as it is ready. (I see that some 5.0.23 binaries are listed on the 5.0 download page -- this is a mistake.) That's why I wondered. So when will we get MySQL 5.0.25 for FreeBSD? I suppose you mean 5.0.24. I can't speak for the build team, but the FreeBSD Ports Collection will have it as soon as the maintainer updates it. He's on vacation until 21 August, so normally it wouldn't be until after that. If you have a good reason to need it earlier, let me know and I may be able to update it for you. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgp5hGabvwOtH.pgp Description: PGP signature
Re: Incremental Backup
On Tuesday, 18 July 2006 at 10:57:24 +0530, Kaushal Shriyan wrote: Hi ALL I would like to know following 2 things. 1) if i would like to take mysql incremental backup weekly cp /var/lib/mysql/ /backup/mysqlbak/ Others have already explained why this won't work. None of them have addressed the issue of incremental backups, though. To clarify: an incremental backup is one which bases on a previous backup, so it only contains data that has changed since that backup. None of the methods described can do that. Neither will the first release of our new online backup, though it's planned for later. Currently you have the following option: 1. Ensure that you have the binlog enabled. See the chapter on replication in the reference manual for details (http://dev.mysql.com/doc/refman/5.0/en/replication.html). You don't need to run a slave node, but you do need to create the binlogs, which will be your incremental backup files. 2. Make a complete backup at some point. All incremental backups require this step, of course. Currently mysqldump or mysqlhotcopy are your choices, though of course you can also back up the underlying files if you take the appropriate precautions to ensure that they're consistent. Note the binlog position at the time of the backup. 3. Back up the binlogs as they fill up. 4. To restore the database, first restore the complete backup. Then use mysqlbinlog (http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html) to output the incremental part of the backup to a mysql client. Use the --start-position option to mysqlbinlog to tell it where to start. 5. Optionally, use the --stop-position argument to tell it where to stop. This gives you the capability to recover to a point in time (like just before that monster query went wrong). For more information, see the Example Backup and Recovery Strategy at http://dev.mysql.com/doc/refman/5.0/en/backup-strategy-example.html. I *strongly* recommend trying this out with a dry run before entrusting your database to it; it's quite possible to shoot yourself in the foot. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpeCco6F3tqQ.pgp Description: PGP signature
Re: I don't understand why SCSI is preferred.
On Tuesday, 11 July 2006 at 16:41:24 -0700, Chris White wrote: On Tuesday 11 July 2006 04:18 pm, Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. A busy database needs really fast access, for making numerous fast calls all over the disk. Two different, unrelated things. I am more than willing to be called Wrong, slapped, and cast from a bridge. Be careful on that, databases do more work in memory than anything else. That said, I'd be more worried about your memory capacity. Now, if you rely mainly on swap(virtual) memory, then you might worry more on that :). Clearly when you're working in memory, the kind of disks you use don't have much influence. In fact, SCSI disks typically have (marginally) faster access times than ATA. They may also have higher transfer rates, but as Brian observes, this is of marginal interest. One of the things that we discuss internally from time to time is the influence of block size on database performance. On modern disks, random access to a single 4 kB block takes about 5.1 ms (5 ms seek, 0.1 ms transfer). Random access to a single 64 kB block takes about 6.6 ms (5 ms seek, 1.6 ms transfer). Clearly big blocks improve disk bandwidth; but if you only need 4 kB, the rest doesn't buy you anything. That's why we discuss rather than come to any useful conclusion. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpDBQluI8zU2.pgp Description: PGP signature
Re: mysqldump - dump file per table?
On Friday, 7 July 2006 at 14:53:11 -0500, Dan Buettner wrote: I'm preparing to implement some mysqldump-based backups, and would really like to find an easy way to dump out one SQL file per table, rather than single massive SQL file with all tables from all databases. In other words, if I have database DB1 with tables TBL1 and TBL2, and database DB2 with tables TBL3 and TBL4, I'd end up with files named something like this, containing just the table create and data for each: 20060707.DB1.TBL1.sql 20060707.DB1.TBL2.sql 20060707.DB2.TBL3.sql 20060707.DB2.TBL4.sql This would make selective restores a lot easier, and would also allow us to set up development/testing environments more easily than one big file. I'd use mysqlhotcopy but we're in an InnoDB environment. I can implement this with a little perl script but wondered if anyone was aware of a tool out there already? As has been discussed, there's no current method. It's certainly as an enhancement. doable (though we'd need to think about how to name the dump files). You could put in a bug report asking for the functionality, though honestly I don't see much likelihood of us doing it in the near future. But then, I don't decide the priorities. On the other hand, if the bug report is accompanied by working code to implement this functionality, there's a much better chance of it being accepted. If you or somebody else want to do this, please contact me first so that we can discuss the approach. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpBt1YxNlABd.pgp Description: PGP signature
Re: mysqldump: Got errno 27 on write. file too large
On Wednesday, 5 July 2006 at 9:12:52 -0400, Duhaime Johanne wrote:
I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4
gigas db.
...
The full directory that contains the *.frm, *.MYD,*.MYI files has the
following size:
du -ks /seqdata/mysql/autres_bds/regen
3702719 /seqdata/mysql/autres_bds/regen
... I get the output du -k: 2098184 myregendump
this error supposed to be:
bin/perror 27
Error code 27: File too large
As you can see I have plenty of space.
Error codes below 128 come from the kernel. It's possible for
applications to return error numbers in this range too, but it's not a
good idea, and mysqldump doesn't do it. So whatever's happening here,
it's being reported by the kernel.
There are two numbers:
#define EFBIG 27 /* File too large */
#define ENOSPC 28 /* No space left on device */
EFBIG refers to limitations in the size of one file; you can get it
even if there's plenty of space in the file system. ENOSPC is the
other way round: you can get it even if the file isn't at its maximum
allowed size.
In the error file I have multiple times the line:
InnoDB: Error: unable to create temporary file; errno: 2
mercure{mysql}66: bin/perror 2
Error code 2: No such file or directory
But the directory exist.
I'd guess that it doesn't. Unfortunately the message doesn't tell you
which file it's trying to create. This might be worth a bug report,
since it seriously hinders you in finding out what that particular
problem is.
Why does this not show up in your verbose mysqldump?
Then I tried a verbose mysqldump.
mercure{mysql}73: /seqweb/mysql/bin/mysqldump --opt --verbose regen
/seqdata/mysql/myregendump
-- Connecting to localhost...
-- Retrieving table structure for table cpgisland_Human_May2004...
-- Sending SELECT query...
-- Retrieving rows...
...
21 tables
-- Retrieving table structure for table unit_occurence_Human_May2004...
-- Sending SELECT query...
-- Retrieving rows...
/seqweb/mysql/bin/mysqldump: Got errno 27 on write
This table is 1 giga data and 500mb index.
Note that mysqldump is not very efficient in its format. How big was
the output file when it failed? I'd hazard a guess at 2 GB
(specifically, 2147483647 bytes). If this is the case, it's
definitely a file system limitation.
Then I tried a mysqldump of this table only:
/seqweb/mysql/bin/mysqldump --opt --verbose regen
unit_occurence_Human_May2004
and it works fine.
How can I solve this problem?
Well, you've found one workaround :-)
What file system are you using? Could this be (Sun's old) UFS?
I'm sure that Sun has file systems that aren't limited to 2 GB; you
could use one of them. They'll probably give you other advantages
too.
I have looked at the previous message in the forum but could not
find anything answering my problem.
I'm relatively confident that this isn't a mysqldump problem.
On Wednesday, 5 July 2006 at 12:28:53 -0400, Michael Stassen wrote:
My first thought is that Dominik is on the right track. I get
: perror 27
OS error code 27: File too large
which suggests there is some OS limitation. Perhaps the user
running mysqldump is limited? Do you have any larger files owned by
the same user? Can that user currently create a file larger than
that using another means?
Yes, this could be a disk quota issue.
The other possibility would be a bug. You are using version 4.1.7, which
is nearly 2 years old now (released October 2004). The current version is
4.1.20. If you have indeed hit a bug, your best bet would be to upgrade
and try again. You should probably at least read the *long* list of bug
fixes from 4.1.7 to 4.1.20 in the MySQL change history in the manual
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html.
While it's my duty not to stand up and say it's not a mysqldump bug,
I'd be very surprised in this case--see above for reasoning.
Greg
--
Greg Lehey, Senior Software Engineer, Online Backup
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286 Mobile: +61-418-838-708
VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]
Diary http://www.lemis.com/grog/diary.html
Are you MySQL certified? http://www.mysql.com/certification/
pgpu1arScIahf.pgp
Description: PGP signature
Re: FreeBSD 6 and MySQL with DBs on a NAS
On Monday, 26 June 2006 at 10:41:16 -0400, [EMAIL PROTECTED] wrote: *** This happens for me using FreeBSD 6.0 or FreeBSD 6.1 with the most recent MySQL 4.1 or 5.0 built from ports and when the DBMS data files reside on a NetApp NAS share shared over NFS. It only seems to happen with very frequently written-to tables. I sent this to the list last week and no one responded. *** Hi, I was wondering if anyone else had encountered this issue and/or come up with what needs to be done to resolve it: I currently have MySQL 5.0.22 built from ports on a FreeBSD 6.1 machine with the DB data residing on a NetApp share connected via NFS. A strange thing happens often after a few hours or a couple of days, some tables that are very active start to crash for no apparent reason as far as I can tell. Example output from check table tablename: ++---+--+---+ Table | Op| Msg_type | Msg_text ++---+--+---+ dbname.tablename | check | warning | Table is marked as crashed dbname.tablename | check | error| Found key at page 18259968 that points to record outside datafile | dbname.tablename | check | error| Corrupt ++---+--+---+ I've seen this happen on FreeBSD 6.0 and 6.1 with MySQL 4.1.x and MySQL 5.0.x built from ports. Has anyone else seen this and if so has a resolution been found? This is a complicated one. There are at least three variables: 1. MySQL is typically most heavily tested on Linux; you're running FreeBSD. 2. You're using the version of FreeBSD from the Ports Collection, not our own build. 3. You're running over NFS, to a different implementation. Of these variables, I'd say that (2) is probably completely irrelevant. Of the other two, I'd put my money on (3). You can test this if you can move the database to local disk, at least fora while. If the problem no longer occurs, there's a good reason to believe that my guess is right. In this case, it's not a MySQL problem. The best thing to do then would be to report it via the FreeBSD bug reporting system (http://bugs.FreeBSD.org/). If the problem still occurs, it would be good to get more information about the database and query structure. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpdHxyxEL6oh.pgp Description: PGP signature
Re: mysqldump locks dumping db mysql in 5.1.9
On Wednesday, 7 June 2006 at 11:35:56 -0700, James Barros wrote: Hello, I've got 5.1.9 running on FreeBSD and I'm trying to do some backups, but mysqldump is locking up on me. when I run: mysqldump -uroot --opt mysql (and yes, it's a test box, so there is no root password. I can duplicate this behavior with other accounts with sufficient priv's specifying a password) I get: /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; and then it just freezes there till I ctl+c out. Is this a legit bug I should report to the buglist or did I screw something up? mysqldump should not hang, so it's a bug. The real question is how you managed to trip it. Please enter a bug report and I'll look at it as soon as possible. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgprqsw4l2kvI.pgp Description: PGP signature
Re: i'm at a complete loss?
On Sun, 28 May 2006 20:17:53 -0400 Daniel McQuay [EMAIL PROTECTED] wrote: Hello list, I just installed MySQL on my FreeBSD box here at home and am having a few problems with privileges. I can connect to mysql using; mysql -u mysql and there is no password needed. However, when I try to connect to the server using root; mysql -u root I get an error; snip ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) /snip so reading that it appears that I need a password so i try; mysql -u root -p it prompts me for my root pass and when I put it in it does that same thing above but with (using password: YES). I went to the the MySQL web site and read 2.10.3 Securing the Initial MySQL Accounts and tried following along with that but with no luck. When checking google for help I read a lot about the initial installation. Something about /usr/local/bin/mysql_install_db will install a privilege table. I installed mysql using this guide here http://raybdbomb.com/p/mysql-install-on-freebsd.html and every thing seemed to go well but like I said I keep getting this error. Is there something else I should do? Any help on this would be MUCH appreciated. Hi, Wasn't clear to me in reading your post that you did this so here goes: Did you actually set a root password for mysql using a GRANT statement after logging in with the default BLANK password? mysql -u root -p [then just hit enter] HTH, G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with MySQL/InnoDB
On Sunday, May 07, 2006 6:14 PM, Greg 'groggy' Lehey wrote: On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. On Monday, 8 May 2006 at 8:15:17 -0700, paul rivers wrote: I would suggest looking at the functionality of Microsoft SQL Server or Sybase backups. It's extremely nice from an admin point of view, and certainly covers all of what Robert mentions. Yes, from an administrative perspective we're trying to make something that feels intuitive, and particularly the Microsoft approach seems a good starting point for this aspect. If you have a pet feature not discussed below, let me know. On Sunday, May 07, 2006 9:30 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. We're certainly planning incremental backups, but they probably won't be in the first release. We don't plan any size limitations (this is a streaming backup), and it will be transaction-safe (statement-safe for MyISAM) and online (i.e. concurrently with normal processing). Compression is a different issue. We haven't considered it so far, and though it's desirable, I don't see why we can't get an external program to do this (bzip2 or gzip, for example; the choice depends on your personal tradeoffs between time and space). On Monday, 8 May 2006 at 15:55:07 -0300, Daniel da Veiga wrote: On 5/8/06, David Hillman wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). The component we're working on at the moment is the streaming online backup API. Basically you issue an SQL command BACKUP DATABASE, and it outputs a data stream that you can point at your tape drive, to a disk, or across the network to something like VERITAS. We're very conscious of the multiple server issue, but it's going to have to wait until we can back up one server properly. Dealing with replication is a special case of multiple servers, so that will wait too. We will backup the binlog, though, and our current thinking is to use it for incremental backups, though this may change. On Monday, 8 May 2006 at 16:09:23 -0500, Rich Duzenbury wrote: In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. This is also another aspect of the backup solution we're working on. I'll put it down on the wish list. On Tuesday, 9 May 2006 at 7:18:28 +1000, David Logan wrote: Hi Greg, Maybe similar features to that of bacula (my current backup software of choice for my wifes business servers). This is a very comprehensive open source solution that has many of the features requested below. eg. multiple servers, pooling, aging etc. It is a good example of what my own requirements would be. I don't know Bacula, but I suppose I should investigate it. Do you know anybody in the project? Is the intention to have a MySQL type plugin? eg. will it have an api that will be open to other backup solutions being able to utilise what will be written? Yes, this is very much the intention. It's the API that we're defining now. We've been talking to Zmanda (http://www.zmanda.com/), who are interested in extending amanda with MySQL plugins, and we'd be more than happy for others to join in. It would be nice to be able to utilise a standard XBSA solution giving access to the database from any one of the major enterprise backup solutions, eg. Legato Networker, Veritas Netbackup, HP
Re: Backups with MySQL/InnoDB
On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ pgpTiC6AScuNm.pgp Description: PGP signature
Re: mySQL Backups
On Tuesday, 11 April 2006 at 18:40:23 +1000, Jim wrote: What is the standard procedure for mySQL backups. We need to backup at least once a day on a windows OS. Is there a SQL Server Job Agent type solution? At the moment it's a mess. We're working on cleaning up the mess, and you should see something soon. As soon as we have something to show, we'll let you know. In the meantime, we're still defining the architecture. If (any of) you have specific wishes, please let me know. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpX1TZjLbEws.pgp Description: PGP signature
Re: New MySQL Transaction Engine Announced
On Friday, 7 April 2006 at 9:52:46 -0500, mos wrote: In case anyone is interested in where MySQL is going with transactions http://www.computerworld.com.au/index.php/id;1262876365;relcomp;1 Before anybody goes overboard here based on the Subject: line, read the article. This is not New MySQL Transaction Engine Announced, but New MySQL Transaction Engine to be Announced: MySQL is due to unveil a new transaction database engine at its upcoming user conference later this month, according to a company executive. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpEQUM6FGFYV.pgp Description: PGP signature
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
On Monday, 3 April 2006 at 2:29:48 +0530, Sachin Petkar wrote: On 4/3/06, Eric Braswell [EMAIL PROTECTED] wrote: Sachin Petkar wrote: For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. ... There are several possible reasons for this. ... Thanks to everyone, MySQL is back up and running. This was definitely enlightening! Did you establish what the cause of the problem was? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgp7OhHs3uSkl.pgp Description: PGP signature
Re: Tuning a Server with 10,000 databases
Gary Huntress wrote: David Logan wrote: mos wrote: At 09:27 PM 3/31/2006, you wrote: I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were fast, and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden! Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress Gary, Just a guess, but could the problem be the 14,000 directories you have to store the 14,000 databases? The problem could be the OS directory structure. Putting the data into fewer databases will likely solve the problem or perhaps move half of the directories to another drive. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Gary, I think that Mike may have hit the nail on the head. I've a few unix directories with multiple thousand files and they do become a bit of a problem to manage speedwise. Perhaps, as Mike has suggested, place half of them on another drive. The other option could be to run multiple instances of MySQL, each having a different port number (this could be based on username or something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the number of dbs per instance (server) that way. Regards I agree with the diagnosis. I'm unsure how to move 1/2 the databases to a new drive though. That would be the simplest solution. As I understand it, MySQL will only use 1 data directory, so the best case would be symlinks. I'm not sure about this but 15,000 symlinks to multiple drives may be just as slow as 15,000 directory entries. Were either of you thinking of another way to split up the directories? Unfortunately, since I assign one database per user, I can't limit the number created. Multiiple servers may be my best option. Thanks, Gary Not necessarily sure this is the problem. But if it is, it could be solved by switching to a different filesystem. Or, if you are using ext3fs, you could try enabling dir_index on the filesystem (tune2fs -O dir_index ... man tune2fs), which could give you a boost in performance in a large dir (this could take a long time to complete). You may also want to up your table cache so that mysql can keep more of your commonly used tables open? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update or insert with a single SQL statement?
On 3/31/06, Brian Dunning [EMAIL PROTECTED] wrote: I have a really simple two-column database: domain_name (primary key) timestamp I'm trying to keep track of the referrer of every visit to a web site, and I'm looking for a single SQL statement (since my ISP limits the total number of calls I can make in a day) that will either insert a new record if the referring domain is not already in there, or simply update the timestamp if the referring domain is already in there. Possible? REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key creation on MyISAM storage engine
On Tuesday, 28 March 2006 at 10:09:16 +0200, Martijn Tonies wrote: Hello Taco, I previously tried this same question on the GUI tool list, but not much traffic there, so I thought I'd give it a go here. I've been reading some articles that suggest I should be able to create a relationship on a MyISAM table, it would be great if someone could confirm or deny this. has anyone experienced any problems creating foreign keys (relationships) on tables that are of storage engine MyISAM? Now, there will be people telling you that you CAN create foreign keys. Well, in a way, this is true - you can create columns with values that point to other tables. What you probably are asking, is if you can referential integrity constraints. The answer, for MyISAM tables, is NO. Currently foreign key constraints only work for InnoDB tables. We're working making foreign keys table independent, but I can't give you a completion date yet. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpxsSKuVLoQT.pgp Description: PGP signature
Re: SP Debugger (was: Re: ANN: Database Workbench 2.8.5 released!)
On Tuesday, 28 March 2006 at 8:29:08 +0200, Martijn Tonies wrote: Hello Nick, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.5 has been released today! I'd like to hear more about the stored procedure debugger -- does all the functionality in the documentation work with MySQL? Anybody used this with MySQL, who could describe their experience with it? I would really love a good SP debugger! I'm sorry to say the debugger only works with InterBase and Firebird. The MySQL server doesn't provide debugging hooks, by itself, this is a problem for 3rd party tool vendors. InterBase or Firebird don't provide these either, but we are emulating server behaviour at the client side. Although this works for a very large part, it isn't exactly easy and there are still problems sometimes, it's hard to get perfect. This is the reason why we haven't gone that route with MySQL. What would it take on our side for you to reconsider that decision? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpBr0QFv9y1F.pgp Description: PGP signature
Re: Replication from multiple masters?
On 3/1/06, Jeff [EMAIL PROTECTED] wrote: Does anyone know if it's possible to replicate to a single slave from different databases on different masters? For instance: M1:dbAM2:dbB \ / rep rep \ / Slave http://dev.mysql.com/doc/refman/5.1/en/replication-features.html snipMySQL only supports one master and many slaves./snip -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: manage multiple mysql servers on different hosts?
On 3/1/06, Bing Du [EMAIL PROTECTED] wrote: We're running several mysql servers for different applications on different machines. Instead of connecting to each mysql server manually to find out basic information, e.g. versions, mostly like what 'show variables' returns, plus what databases exist on each server, are there any tools that can do that? Can phpMyAdmin manage multiple mysql servers? Additional MySQL servers can be defined in phpMyAdmin's config.inc.php file. -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downgrade from MySQL 5 to MySQL 4.1
On Wednesday, 15 February 2006 at 19:38:18 +0700, Ady Wicaksono wrote: Dear All i would like to downgrade from MySQL 5.0.18 to MySQL 4.1.18, I found MySQL 5.0.18 makes my application unstable... please help me, since currently all table in row=compact format If you're having this kind of problem, could you please give us a bug report? Thanks Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpgdTQAZ0t3X.pgp Description: PGP signature
Re: Innodb table locked on delete
Innodb is indeed row level locking. You are likely thinking of BDB which uses memory page level locking. gw sheeri kritzer wrote: Innodb is not row-level locking -- it's memory-page-level-locking. A memory page is usually small, so it's almost like row-level locking, but not quite. Perhaps you're running up against that? What does the query log say which queries were running? How do you know it's the delete that's taking the lock, and not an update (the error message said an update or a delete)? -Sheeri On 2/10/06, Ady Wicaksono [EMAIL PROTECTED] wrote: So where's the row locking? I configure my database with Innodb + READ COMMITED, by this configuration by using autocommit=1, delete should be done on data commited to disk. Other thread should be able to insert/update. CMIIW Tables are locked on delete because, like an update, they are changing data. Imagine issuing a REPLACE statement after a DELETE statement. If the DELETE locks the table, then the REPLACE happens AFTER the DELETE, as you wanted. If the DELETE does not lock the table, then it's possible the REPLACE will happen before the DELETE, and the DELETE will delete the row you just replaced. -Sheeri On 2/9/06, Ady Wicaksono [EMAIL PROTECTED] wrote: Why table locked on delete? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Connection Problems
On Thursday, 9 February 2006 at 17:02:06 -0800, Michael Jeung wrote:
Periodically, exim throws the following error into its panic log:
Feb 9 13:24:51 mx_server_1 exim[69953]: 2006-02-09 13:24:51 failed to expand
${lookup mysql {select distinct domain from email_table where
domain='$domain'}{$value}} while checking a list: lookup of select distinct
domain from email_table where domain='bob.com' gave DEFER: MYSQL connection
failed: Can't connect to MySQL server on '127.0.0.1' (61)
(where bob.com is one of our customer's domains that we host.)
This has been happening for months, and I'm trying to get rid of
these errors. The come in bursts - in a single second, I could get as
many as 30 to 40 of these messages.
Now, I've taken a few different steps to track this down, but the
problem is very elusive and hard to replicate. The mySQL error logs
are unhelpful. I'm sure that my mySQL server isn't running out of
connections, because if it was, I would receive a too many
connection error message. (I deliberately lowered the max number of
mySQL connections to test this.)
Since you've checked the MySQL logs, can I assume that you've
confirmed that the server hasn't been failing and being restarted?
Even if you don't find any evidence of such problems, can you check
with ps to see when the mysqld was started?
I'm wondering if there's a system level limit that I'm running into
that would be independant of exim or mySQL's configuration. I'm a
little hazy in this area, but I would be talking about some sysctl
variable, a ulimit on open sockets, or something to that effect.
This doesn't seem consistent with the errors you're reporting. If you
did hit some FreeBSD-related limit, it would be reported in
/var/log/messages.
Greg
--
Greg Lehey, Senior Software Engineer
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286 Mobile: +61-418-838-708
VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]
Are you MySQL certified? http://www.mysql.com/certification/
pgp2Zc2X7AfT4.pgp
Description: PGP signature
Re: MySQL installation failure
On Wednesday, 1 February 2006 at 15:01:15 -0600, Hong Li wrote: Hi I am doing investigation to determine whether we can use MySQL in our project. But I got error message when I try to install MySQL on Unix server. Could you please help me? group name wheel not found in group table(s) This suggests that you're using a BSD-style package. BSD systems have group wheel, many UNIX systems don't. Can you give details of your system and what version of MySQL you're trying to install? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpTPx3cLWuAB.pgp Description: PGP signature
Re: Query: Order for the Earliest Latest date
In addition to the id_Site, you also need to grab the MAX(Time) so you have something to sort by. This requires a little trick known as a groupwise maximum. See http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html for an explanation and some examples. Greg Fortune On Tuesday 24 January 2006 09:06, Dan Baker wrote: [GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB pgpQ7novDk8tC.pgp Description: PGP signature
Re: problems with old isam tables
That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgp323tYrtfbt.pgp Description: PGP signature
Re: problems with old isam tables
Also, you may find that check table/repair table are able to fix the problem. See http://dev.mysql.com/doc/mysql/en/Table_maintenance.html for more information. Greg On Thursday 12 January 2006 08:28, Greg Fortune wrote: That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgpvBPtiiNNqf.pgp Description: PGP signature
Re: GUI (linux X based or web based) for mysql
Dotan Cohen wrote: I keep a lot of personal information in a MySQL database. I have a few querys premade for the wife to use, but we have gotten to the point where we need a solution for her to be able to run her own queries. She is NOT about to learn SQL, and myPHPadmin is a little too compicated. I looked at an Access installation the other day and it seemed to be just what we need- a GUI for non-programmers to use a database. Is there any such beast for MySQL? Anthing that will run on Fedora Core 4 would be great. Or even a web-based solution would be fine- I do have apache on localhost. Check out Open Office 2.0 and its Base app, available in windows and *nix versions. I think it can use MySQL through either ODBC or JDBC connectors, though I haven't played with it much (yet) to evaluate it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Band width consume
On 12/4/05, Luiz Rafael Culik Guimaraes [EMAIL PROTECTED] wrote: Dear Friends Is their an Page with data of how much bandwidth mysql consume when using remote servers? mysql show status like 'Bytes_%'; +++ | Variable_name | Value | +++ | Bytes_received | 1718034415 | | Bytes_sent | 2783965287 | +++ 2 rows in set (0.00 sec) This will give you an incrementing value that you can plot on a graph using MRTG (www.mrtg.org) or similar. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
On Wednesday, 16 November 2005 at 20:40:35 +0700, Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? ... InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This is obviously a bug. I've just checked the bug database, but I don't see a report on it yet. Could you please enter one? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start mysql 5.0.15
Saffa Kemokai wrote: I am unable to start mysql after several trials and fixes. I compiled a mysql 5.0.15 source for FreeBSD 5.3. It doesn't seem to have placed the files in their proper locations. Below is what I keep getting and I don't know now how to get it working. What do I need to do about this mysql.host table? How do I create it? 051110 20:59:33 InnoDB: Started; log sequence number 0 43655 051110 20:59:34 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051110 20:59:34 mysqld ended Are you installing from a tarball? If so, you should seriously consider using the FBSD ports system instead since it will make longer term management much easier. # cd /usr/ports/databases/mysql-server50 # make install clean G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on raid 1 or raid 5?
On Thursday, 20 October 2005 at 16:57:36 +0800, Hiu Yen Onn wrote: hi all, currently, i have a machine with raid 1 and raid 5 (backplane). in order to boost up the mysql performance, does it advise install /var/ (mysql data directory) into raid 1 or raid 5? please advise. thanks RAID-5 optimizes storage space, not performance. If you're looking for performance, RAID-1 is a better choice, though obviously there are lots of things to consider. In particular, if you're striping (RAID-10), choose as large a stripe size as possible. And of course it can use nearly twice as much disk space. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you set a Primary Key when uploading data into a table?
On 10/10/05, Bill Abel [EMAIL PROTECTED] wrote: How do you load data into a table and generate a primary key which auto increments by 1. I want to end up with a primary key value of int 1, 2, 3, 4, and so on. My table has three rows, Id (pri_key), Code, Description. I'm loading data from a text file into the table like this: load data local infile 'path_to_myfile.txt' into table sic_codes lines terminated by '\r' (code, description); Currently, the Id gets values of 99897, and then increments by one. Any help is appreciated. Add an auto_increment option to your Id field. -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/
Re: compare tables from two systems
On 10/5/05, Claire Lee [EMAIL PROTECTED] wrote: We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. mysqldump -l -B db1 db1.sql mysqldump -l -B db2 db2.sql diff db1.sql db2.sql diff.sql -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/
Re: Lost connection to MySQL server during query when calling stored procedure
On Thursday, 22 September 2005 at 17:06:32 +1200, Jasper Bryant-Greene wrote: Jasper Bryant-Greene wrote: I have a stored procedure defined as follows: ... Sometimes when I execute this stored procedure ... I get the error Lost connection to MySQL server during query. I am using MySQL 5.0.12-beta-log on Gentoo Linux x86. This only seems to happen when accessing MySQL from PHP's MySQLi API, not when accessing it using the mysql command-line client. Is this a bug I should report to MySQL or to PHP? I'm not able to test other APIs at the moment. If the server dies, that's a MySQL problem. Take a look at http://bugs.mysql.com/ for information on how to report it. In particular, the server logs should give some information. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia See complete headers for address and phone numbers. Are you MySQL certified? http://www.mysql.com/certification/ pgpjYgw1PlK8L.pgp Description: PGP signature
Re: mysql process 99.9 percent
On Monday, 26 September 2005 at 9:29:32 +0200, mark carson wrote: Joeffrey Betita wrote: hi do you have any idea why the mysql process is 99.9 percent? when i click one hyperlink it takes more than 1 minute to load the page. thank you very much. Most readers in this list expect OS and MySQL server information etc before responding. In our experience this is usually caused by poor SQL query construction e.g. large result set e.g. x million rows and/or index choice, database table design and/or MySQL server setup. Turn-on logging, rerun selected queries with and without the explain syntax, look at the MySQL administrator process list to see which query is the problem whilst the CPU is 99.9 %. FWIW, we have at least one case where the server gets itself into a loop for no obvious reason. See http://bugs.mysql.com/bug.php?id=414 and http://bugs.mysql.com/bug.php?id=12061 for more details. If the problem at hand is related to this bug, I'd be very interested in hearing from you, especially if you can reproduce the problem easily. Note that the submitter of the second-mentioned bug report has done a very good job of describing his problem. Only reports of similar quality would be of much help. If this isn't your (Joeffrey's) problem, it might give you an idea of the kind of information we're looking for. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia See complete headers for address and phone numbers. Are you MySQL certified? http://www.mysql.com/certification/ pgpc37noUE7K3.pgp Description: PGP signature
Re: Linux+AMD64+MySQL.
RV Tec wrote: Folks, I'm used to run MySQL (4.0) with OpenBSD (3.7). Now I've decided to give Linux a shot, to see if I could gain some serious performance. The server I'm using is a dual Opteron 246, with 2GB RAM, LSI MegaRAID 320. CentOS 4.1 x86_64 seems to be a good OS. Although I thought that the most interesting thing in running Linux+MySQL was the fact that LinuxThreads is available, the binaries are using pthreads. When I try to compile my own MySQL (pretty much the same way as the RPM is created, except that I'm trying LinuxThreads), everything goes smoothly, but the servers crashes with Signal 11 every 10, maybe 5 minutes. When I compile it with pthreads, everything is back to normal. I'm trying with MySQL 4.1.10a (I've tried the 4.0.25, 4.1.14 as well), with Centos 4.1 x86_64, Kernel 2.6.9-11.ELsmp. So, I've a few questions, maybe you guys can help: 1) pthreads is safer, but LinuxThreads is way faster? 2) What's the best io scheduler for 20GB MyISAM DB? I'm currently using cfq. 3) Does anyone have any tips on running MySQL+AMD64+Linux? Thanks a lot! Best regards, RV We fought this exact setup for some time. Some things I learned and our setup: - gcc 3.4 (we are using 3.4.3) - glibs 2.4.4 (w/ NPTL) - deadline scheduler - xfs for filesystem - 2.6 kernel - custom build of mysql 4.1 Everything else was either non-performant or unstable. Also, stay away from Fedora. We ended up switching to Gentoo and had massive performance and stability gains from the switch, but the reasons are more to do w/ gcc and glibc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data sync offline
Hi, Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Make a report like this...
You might try --xml or --html with a different separator. Greg -- Greg Fortune Enterprise Systems Eastern Washington University Phone: 509-359-6690 Email: [EMAIL PROTECTED] On Wednesday 20 July 2005 03:28 pm, Dan Bolser wrote: +--+---+++-+ | G_ID | TOTAL | G2 | NR | ASSEMBLY_LISTING| +--+---+++-+ | 1132 |34 | 1 | 1 | 1bf3-1,1bgj-1,1bgn-1,1bkw-1,1cc4-1, | | | | ||| 1cc6-1,1cj2-1,1cj3-1,1cj4-1,1d7l-1, | | | ||| 1dob-1,1doc-1,1dod-1,1doe-1,1ius-1, | | | ||| 1k0i-1,1k0j-1,1k0l-1,1pbb-1,1pbc-1, | | | ||| 1pbd-1,1pbe-1,1pbf-1,1pdh-1,1phh-1, | | | ||| 1pxa-1,1pxb-1,1pxc-1,2phh-1 | +--+---+++-+ | 1793 |10 | 1 | 1 | 1jdx-1,2jdw-1,2jdx-1,3jdw-1,4jdw-1, | | | | ||| 5jdw-1,6jdw-1,7jdw-1,8jdw-1,9jdw-1 | +--+---+++-+ Hello, I made the above report by hand after using a GROUP_CONCAT query. Is there any way to make this kind of format automatically in MySQL? It seems it would be a nice feature. Dan. pgpOWQjoXduTN.pgp Description: PGP signature
Re: Why does query load faster after executing 2nd time? (Query Caching DISABLED, Key-Cache already fully loaded)
Filesystem buffering? pow wrote: Hi everyone, Im puzzling over why a query loads faster the second time I execute it. I am sure it is not query cached, because that is off. I also made sure that the key that is used was already cached b4 i even executed the query the first time. So it is not like as if the 2nd execution used the key cache and the first attempt did not. I am executing the queries directly in MYSQL command prompt. Is there some other type of cacheing that i am missing? Thanks! Pow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure in MySQL 5.x
How effective? Very effective. Actually, I am testing it, and once I overcame a few syntax issues, it's working great. Procedures and functions both. I havent tested triggers yet. But so far I am using a mini-blog thing on my web site with them and I am loving the ease of Mysql combined (finally!) with the power of sprocs. Of course, it is beta right now, so I wouldnt stick it into anything you need to rely on yet, but get it going and try it for yourself. One thing to note, while the sprocs and functions work good on Windows, connecting with some client languages (python in my case) isnt working. Same goes for ODBC. If you use Linux, then it works fine. Oh, and another thing to note. If you use MySQL Query Browser to create the procecures, it has a bug and inserts the wrong delimiters in the drop if exists statement. That really messed me up at first. So, remove the // and insert the $$ and it will work great. Greg On 7/15/05, Scott Hamm [EMAIL PROTECTED] wrote: How effective is Stored Procedure in MySQL 5.x? -- Power to people, Linux is here. -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF failure
Looks like you're confusing Prepared Statements with Functions/Stored
Procedures. You don't compile a function, and you're using 4.1, you
need Mysql 5. (specifically 5.0.3 I think, you'd have to look that
up, but you would want 5.0.7 anyway. Maybe I have that confused with
triggers, cant remember.)
Prepared Statements are C code using the C API. You have SQL Language
code, that looks correct, but you're implementing it incorrectly.
(with the wrong version) Functions are not compiled, like you are
doing with gcc.
Prepared Statements:
http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html
Functions:
http://dev.mysql.com/doc/mysql/en/stored-procedures.html
Hope that helps.
Greg
On 7/6/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
Check MySQL error log, server could die while loading the UDF.
Stored procedures appeared only in 5 version. This works fine for me on 5.0.7.
mysql create function hello (s char(20)) returns char(50) RETURN
CONCAT('Hello, ',s,'!');//
Query OK, 0 rows affected (0.00 sec)
Nic Stevens [EMAIL PROTECTED] wrote:
Hello,
I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux
(FedoraFC1). My UDF function seemed to blow up the server so I went to
the
source and tried to build udf_example.cc.
Using the precompiled binaries for Linux and using the source
distribution
for the same version (4.1.12) I was unable to build udf_example.cc as a
shared object (I followed the instructions in the source -- they didnt
work
so I used what seemed to make the most sense for building my object).
When
building the shared object the compiler spits out a warning from
asm/atomic.h (#warning Using kernel header in userland program. BAD!)
I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a
Fedore
Core 1 machine. I built the shared object with gcc -I/usr/include/mysql
-shared -o udf_example.so udf_example.cc
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
id of
1. Nothing is listed in mysql.funcs either.
There needs to be better documentation of the process for building
UDF's.
The code I need to run can be run nicely by MySQL but I can't declare a
stored function as documented. The stored function is fairly simple:
DELIMITER //
CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
BEGIN
DECLARE x REAL;
DECLARE y REAL;
DECALRE miles REAL;
SET x = 69.1*(lat - lat2);
SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3);
SET miles = SQRT(x*x+y*y);
RETURN miles;
END
//
The mysql client spits back :
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
Using the example given in the documentation I get:
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')' at line 1
DELIMITER ;
//
Can someone elucidate the mystery of either stored functions or UDF's?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Greg Fischer
1st Byte Solutions
http://www.1stbyte.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP MySQL connection problem
On 6/10/05, Andre Matos [EMAIL PROTECTED] wrote: I am getting slow connections between php4 and MySQL 4.1.9. There are some connections that are very fast, but others can take from 5 to 20 second or more (usually it takes less than 0.1 second to create a new connection). I tried to identify if there is a pattern but it seems that it does not exist, in other words, it can create 5 connections and then the 6th takes too much time. In another time, it process 13 connections fast and the 14th is slow again. If your going across network you might check your network cards. Had this happen to me once. Reseated the card in the database server, fixed it right up. -- Greg Donald Zend Certified Engineer http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Jeff Smelser wrote: On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff Are you sure? I don't see that from http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html It seems that w/ 4.1, you can specify any ONE timestamp col w/ default of CURRENT_TIMESTAMP. You are not limited to the 1st one, but still seems you are limited to a max of 1 timestamp. Or am I reading this wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Jay Blanchard wrote: [snip] On Wednesday 08 June 2005 11:16 am, you wrote: [snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless First, *I* wasnt requesting anything.. So you need to reply to the person who was complaining in the first place. I just thought it was funny.. Them fixing has no care in the world in my mind since I never use subqueries.. So take your comments elsewhere..Or direct them to the right person.. [/snip] Easy there boss, I was just responding to the thread and meant no offense. I saw the whole thing as funny. I personally find the idea that just because a product is free that people are not allowed to, or should not complain when they find shortcomings in that product, to be more than a bit short sighted. It is still VERY arguable as to wether Mysql is or is not the best open source DB on the market today. I am sure the Postgresql people would not agree, and they could likely make a very compelling argument. If Mysql wants to have the best product, then they NEED people to use their product, and to complain when something is not correct. And, they need to listen to those complaints from their users. For most any large open source project, there is more of a need for users and testers than there is for developers. Those people who use the free version of Mysql are arguably testers (those w/ support contracts are customers, and are arguably even more entitled to complain). Granted, Kevin's tone was a bit harsh, but his sentiments should be encouraged (frustration w/ a lack of feature). The concept that people should be happy with what they get for a free product only serves to keep the quality of free products below what they could be. The performance of sub-queries in mysql when used in an IN() clause is embarassingly slow. They are in most cases, unusable because they do not use indexes correctly. This is a legitimate complaint, and one that I personally hope Mysql looks into and repairs. I echo Kevin's thoughts and I rarely even have a use for sub-queries (given I got used to them not being available). Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
They do use indexes if you use them to build derived tables and are pretty fast. The only case where I see them not using indexes when I think they should is when you use a sub-query for an IN() clause. Kevin Burton wrote: OK... Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. So... 1. When will subqueries that actually use indexes be implemented? We've been promised this feature since 4.0 it was one of the biggest feature wins of 4.1. 2. If they won't be in 5.0 could you please abandon a feature for 5.0 and concentrate on subqueries? 3. If they won't be in 5.0 could you at least be honest and remove this feature since in the best case its useless and in the worse case its dangerous (god forbid someone should ship code that uses this)? Not trying to be obnoxious here but I really want this feature and the current implementation is very.. evil. :) Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 Crashing on Mandrake 10.1
Hmmm, I downloaded source and compiled, and had an instant segfault. Rolled back to 4.1.11. I assumed it was something funky w/ my compile, but after reading all the above posts ... ??? Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Donny Simonton wrote: I'm not sure but I know when I installed it yesterday via RPM it kept core dumping and restarting. Glad I still had 4.1.11 available. Donny Donny, Douglas, Something goofy went on with the production of those RPMs for 4.1.12 that didn't show up during release QA, so once we found out that users were having issues with them, we pulled them off the website until we can figure out what went wrong. For now, the tarballs should be okay if you can use those. It seems that it only is related to how the RPMs were packaged and/or post-processed. Regards, -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCjR8RtvXNTca6JD8RAprmAJ9Qmcp2dRI5ETK46eLB7NtwotyW+QCghBty GzP1vQKkPG3lOetGqk1Dp5E= =O7OB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing slaves from falling behind masters...
Donny Simonton wrote: With Mysql you should ONLY use RAID10. Everything else is not worth your time. I would argue that a large stripe (RAID0) would be a better solution for slaves in a large replicant network. Why waste the drive space and performance on a RAID10 when you have multiple replicants for HA. That said, all of our DBs are RAID10 as we never know which ones we may need to put in as master (if master fails). As long as you are using 15k SCSI drives, on both your master and your slave, your slave should rarely ever fall behind. Especially if you are doing less than 1,000 inserts per second on the master. Otherwise you should be just fine. The only time our slaves ever fall behind, is when you delete 40 records from table A then insert 40 new ones. But we do that for 600k records, so we do a few million deletes and inserts in a short period of time. Given that the slaves will never start a query until the master has finished it and passed it along to the slave, the slave is ALWAYS running a bit behind the master. And, if you are using MyIsam tables, and running w/ low-priority-updates (as the Mysql guide suggests for performance on slaves), then it is likely that a heavy hit replicant will lag to a noticable level when under high traffic. Innodb should lessen this a bit as it is not doing full table locks and you can have it do dirty reads. As for our replicant cluster, we do not run w/ low-piority-updates simply because we found it increased replication lags, and exposed replication bugs in our application (write to the rw pool, immediately look for that record in the ro pool and not find it). We also put our more powerful hardware in our replicant cluster as our rw machine does a fraction of the traffic (1/10th the qps of any of our replicants). Even w/ this, our replicants sometimes fall behind, but never more than 1 to 2 seconds. Donny -Original Message- From: Kevin Burton [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 11, 2005 7:30 PM To: mysql@lists.mysql.com Subject: Preventing slaves from falling behind masters... If you're running in a master/slave environment.. and you're application is using the slave too often... replication can fall behind which can then confuse your application. This can happen if the IO performance of both the master and slaves is equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1 on the master. Then when the transactions move to the slave all the IO is used up and any additional SELECTS will just cause the slave to fall behind. Has anyone else seen this? One way I was thinking of solving this is to use RAID5 on our master and then RAID0 on the slaves so that the master is a hard bottleneck. Then the slaves have no problem running transactions via replication and have load available to run SELECTS. Any other ideas? -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SATA vs SCSI
Newer SATA drives are supporting command queueing, which should really help their performance. I think when SATA-2 becomes more available, SATA will start being a more viable choice and start rivaling SCSI performance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
Care to share any secrets? You guys are running Suse w/ 2.4 kernel yes? Any specifics as far as kernel/glibc/gcc versions. Are you running mysql 4.1.*? Are you using NPTL? You using the binary from mysql, or building yourself? Are you running Innodb or Myisam. You mentioned reiserfs correct? Any problems w/ ext3? Sorry to bombard you w/ questions, but we have had nothing but horrible performance using Opterons, and any specifics you can give would help to clear up this mess. I know that I am not the only person who is seeing this flakyiness. Thanks, Greg Dathan Pattishall wrote: Subject: Re: Opteron HOWTO?! On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. We don't have any problems running Opterons at all. With all the tests me and my team have done, we know the ins and outs of getting Opterons up, running-stable, and blazing fast. Our entire datacenter are (about 200 servers) Dual Opterons with at least 4GB of memory running in 64-bit mode. All the databases (about 30) - Are dual opterons with 8 GB of memory connected to a Hitachi 9980 SAN-through a McData Switch. We do about 70K qps at peak for about 1 Billion Queries per day (only on 30 servers BOOYA). So, it's pretty stable. -- Dathan V Pattishall Sr. Database Engineer / Sr. Software Engineer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
I am all in favor of this idea. Currently, this info is scattered all over the web, and finding it can be time consuming (even w/ Google). I see lots of people jumping the same hurdles, so a central location for this info seems it would greatly benefit the community. Greg Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. I was wondering if we could start an Opteron HOWTO somewhere (mysql wiki?) which could illustrate the minefields they've had to walk to hopefully solidify MySQL on this platform and to prevent others from having the same bad experiences. We're considering an Opteron migration as well and as the 2G memory barrier fast approaches I'm expecting more MySQL users to migrate as well. Maybe this should be a X86_64 bit FAQ though since Intel is coming out with more EM64T stuff Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not using optimum disk throughput.
Kevin Burton wrote: Greg Whalin wrote: I suspect this is an OS issue. Our Opteron's were completing large data update queries aprox 2-3 times slower than our Xeons when running under 2.6. After a switch to 2.4, Opteron's are faster than the Xeons. I mentioned NPTL being shut off (LD_ASSUME_KERNEL=2.4.19 in init script). When we left NPTL running, we saw almost instant deadlocks just watching replication catching up (no other site traffic directed to the machine). This is in 2.4 btw, so this is the backported NPTL kernels from Fedora. I somewhat suspect NPTL being a problem in 2.6 as well due to impressions I get from sifting through mysql's bug tracking system. The IO scheduler was also an obvious culprit. Another point I wanted to note. What version of glibc were you running. We were running Debian with glibc 2.3.2 (libc6-i686-2.3.2) and were running into deadlocks with another piece of code. 2.3.2 has a number of known issues and we had to migrate to an experimental 2.3.4 build. I've been considering moving our databases to 2.3.4 but they weren't having any problems. It might be that opteron is raising these issue more than Xeon. FYI... We are currently running 2.3.2 (Fedora Core 1) on our Opterons. When we were still running linux 2.6, we were on 2.3.3 (Fedora Core 2). Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not using optimum disk throughput.
Kevin Burton wrote: Greg Whalin wrote: We are currently running 2.3.2 (Fedora Core 1) on our Opterons. When we were still running linux 2.6, we were on 2.3.3 (Fedora Core 2). Yeah... we were being bitten by 2.3.2's NPTL implementation for MONTHs before I heard a rumor that the Internet Archive moved to 2.3.4. This literally solved all my problems so I'd recommend upgrading to 2.3.4 if you notice this type of stuff again. Kevin Curious, were you seeing deadlocks in Suns JVM w/ Tomcat? We were forced to run Tomcat w/ NPTL off due to deadlocks under glibc 2.3.2+NPTL. Under FC2, the JVM runs fine w/ NPTL, though glibc is now 2.3.3. We have had no NPTL issues w/ the x86 version of mysql, but the x86-64 definite almost immediate deadlock (w/ 2.3.2). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not using optimum disk throughput.
Hi Peter, As for reporting bugs ... http://bugs.mysql.com/bug.php?id=7437 http://bugs.mysql.com/bug.php?id=10437 We have found Opteron w/ Mysql to be an extremely buggy platform, especially under linux 2.6, but granted, we are running Fedora. Perhaps we will try Suse, but I feel I have heard similar reports (from Friendster) about their use of Suse 2.6 and Opterons being similarly slow. We are currently running MyIsam tables, but plan on switching to Innodb in the next month or two btw, so our performance problems are w/ MyIsam. Greg Peter Zaitsev wrote: On Fri, 2005-05-06 at 19:01, Greg Whalin wrote: What drives are you using? For SCSI RAID, you definitly want deadline scheduler. That said, even after the switch to deadline, we saw our Opteron's running way slow (compared to older slower Xeons). Whatever the problem is, we fought it for quite a while (though difficult to test too much w/ production dbs) and ended up rolling back to 2.4. One more thing to try, if you have smart RAID would be noop scheduler, to let hardware to do the job. Smart optimizations OS do to reduce head movement may not make sense for RAID. In practice I've however seen close results. Also which storage engine are you using ? One of the things which was changed in 2.6 for some hardware configurations is fsync() performance. It was cases in some cases, so it was instant. This for example explained in many cases why people moving from IDE devices to much faster SCSI devices may observe performance degradation (IDE with 2.4 has typically fake fsync) In general we have very positive feedback from using Opterons with MySQL at this point. Sometimes it takes time to make it work right, especially it was the case when they were new but when it flies. Practically same applies to EM64T - It is very good to have now two inexpensive 64bit platforms available. We're getting some feedback about problems on some Fedora Core versions, well this is bleeding edge distribution so I'm nothing but surprised. SuSE both in SLES and Professional variants seems to work very well with Opterons as well as recent RH EL. Speaking about MySQL problems - if you have any MySQL issues on Opterons, please report them as bugs and we'll troubleshoot it. Kevin Burton wrote: Kevin Burton wrote: Greg Whalin wrote: Deadline was much faster. Using sysbench: test: sysbench --num-threads=16 --test=fileio --file-total-size=20G --file-test-mode=rndrw run So... FYI. I rebooted with elevator=deadline as a kernel param. db2:~# cat /sys/block/sda/queue/scheduler noop anticipatory [deadline] cfq (which I assume means I'm now running deadline. Is there any other way to find out?) And no performance diff. Note that you're benchmarks only show a 20M addition overhead. We're about 60x too slow for these drives so I'm not sure what could be going on here :-/ Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not using optimum disk throughput.
We have seen the exact same thing here. We used the deadline scheduler and saw an immediate improvement. However, we still saw much worse performance on our Opteron's (compared to our older Xeon boxes). We ended up rolling back to Fedora Core 1 2.4.22-1.2199.nptlsmp kernel and shut down NPTL and now our Opteron's are much much faster than our Xeons. The thing I find strange about this is that our experience (@ Meetup) seems to match that of Friendsters (I know of a few other high traffic sites that have mentioned similar issues), in that Mysql on Opteron and Linux 2.6 is not a good solution. Yet, Mysql recommends exactly this config and in fact, does not seem to even support (via support contract) a 2.4 solution for Opteron + Mysql. Greg Dathan Pattishall wrote: What kernel are you running. If your running 2.6.x use the deadline scheduler or downgrade to 2.4.23aavm 2.6.[0-9] has major problems with the IO scheduler since the process scheduler is very fast now. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Kevin Burton [mailto:[EMAIL PROTECTED] Sent: Friday, May 06, 2005 1:58 PM To: mysql@lists.mysql.com Subject: MySQL not using optimum disk throughput. We have a few of DBs which aren't using disk IO to optimum capacity. They're running at a load of 1.5 or so with a high workload of pending queries. When I do iostat I'm not noticing much IO : Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 13.73 128.43 252.94 1027.45 1695.10 513.73 847.55 7.1490.13 285.00 2.53 96.57 This is only seeing about 500k - 1M per second throughput. When I run bonnie++ on these drives they're showing 20M-40M throughput. Which is really strange. Most of our queries are single INSERTS/DELETES. I could probably rewrite these to become batch operations but I think I'd still end up seeing the above iostat results but with higher throughput. so I'd like to get to the bottom of this before moving forward? I ran OPTIMIZE TABLE on all tables but nothing. The boxes aren't paging. They're running on a RAID5 disk on XFS. Could it be that the disks are having to do a number of HEAD seeks since we have large tables? -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not using optimum disk throughput.
Kevin Burton wrote: Greg Whalin wrote: We have seen the exact same thing here. We used the deadline scheduler and saw an immediate improvement. However, we still saw much worse performance on our Opteron's (compared to our older Xeon boxes). We ended up rolling back to Fedora Core 1 2.4.22-1.2199.nptlsmp kernel and shut down NPTL and now our Opteron's are much much faster than our Xeons. Sweet... I'm going to take a look at that! Two votes for the deadline scheduler. Though I'm an NPTL fan but I'm not sure our DB boxes need this as they don't use THAT many threads. Deadline was much faster. Using sysbench: test: sysbench --num-threads=16 --test=fileio --file-total-size=20G --file-test-mode=rndrw run results: 2.6.10-1.14_FC2smp on dual Opteron 248s w/ 4GB RAM default scheduler (anticaptory): Operations performed: 6004 Read, 3996 Write, 12800 Other = 22800 Total Read 93.812Mb Written 62.438Mb Total transferred 156.25Mb (2.9186Mb/sec) 186.79 Requests/sec executed Test execution summary: total time: 53.5363s total number of events: 1 total time taken by event execution: 376.0398 per-request statistics: min:0.s avg:0.0376s max:18446744073709.4961s approx. 95 percentile: 0.1106s Threads fairness: distribution:70.15/87.92 execution: 88.48/93.88 deadline scheduler: Operations performed: 6006 Read, 3994 Write, 12800 Other = 22800 Total Read 93.844Mb Written 62.406Mb Total transferred 156.25Mb (4.4464Mb/sec) 284.57 Requests/sec executed Test execution summary: total time: 35.1411s total number of events: 1 total time taken by event execution: 289.2953 per-request statistics: min:0.s avg:0.0289s max:0.3520s approx. 95 percentile: 0.0870s Threads fairness: distribution:84.92/92.89 execution: 90.52/96.58 The 2.4 scheduler showed similar results to deadline under 2.6. The thing I find strange about this is that our experience (@ Meetup) seems to match that of Friendsters (I know of a few other high traffic sites that have mentioned similar issues), in that Mysql on Opteron and Linux 2.6 is not a good solution. Yet, Mysql recommends exactly this config and in fact, does not seem to even support (via support contract) a 2.4 solution for Opteron + Mysql. Wow... whats the consensus on Opteron here then? It seems to be a clear winner since you can give the mysql process more memory for caching. Is it an OS issue since few of the distributions seem to support Opteron (well). I suspect this is an OS issue. Our Opteron's were completing large data update queries aprox 2-3 times slower than our Xeons when running under 2.6. After a switch to 2.4, Opteron's are faster than the Xeons. I mentioned NPTL being shut off (LD_ASSUME_KERNEL=2.4.19 in init script). When we left NPTL running, we saw almost instant deadlocks just watching replication catching up (no other site traffic directed to the machine). This is in 2.4 btw, so this is the backported NPTL kernels from Fedora. I somewhat suspect NPTL being a problem in 2.6 as well due to impressions I get from sifting through mysql's bug tracking system. The IO scheduler was also an obvious culprit. Other issues I have noticed w/ Opteron ver of mysql ... - Under 2.6, if we took the db offline and ran myisamchk on a table w/ fulltext indexes, and then started back up again, the table would nearly instantly crash (upon first writes to it). Running repair table would seg fault. Shutting down to run myisamchk would only cause the table to crash again upon 1st write. Only solution ... alter table tablename engine=myisam; Then the table would run fine. We have since dropped all fulltext indexes and moved to Lucene (much more flexible and way faster anyhow). - Under 2.4 (just happened to me tonight and this is a scary one), we routinely archive and cleanup large tables w/ seldom used old data. After doing a DELETE FROM table WHERE ctime '2005-05-01', we would see a select count(*) show around 160k rows remaining (from 1st of the month). I would call repair table on the table, and the remaining rows would be deleted. Repair would make mention of dropping row count from 165k to 0. Yikes! This happened on both Opterons and did not happen on the Xeons (thank god ... was able to save the data). In any rate, I am 100% confidant in saying that Mysql (w/ myisam table engine ... not tried innodb yet) on linux on Opterons is not yet stable or speedy. Though we usually only see problems under large data cleanups (moving, deleting, repairing, etc). Greg
Re: MySQL not using optimum disk throughput.
What drives are you using? For SCSI RAID, you definitly want deadline scheduler. That said, even after the switch to deadline, we saw our Opteron's running way slow (compared to older slower Xeons). Whatever the problem is, we fought it for quite a while (though difficult to test too much w/ production dbs) and ended up rolling back to 2.4. Kevin Burton wrote: Kevin Burton wrote: Greg Whalin wrote: Deadline was much faster. Using sysbench: test: sysbench --num-threads=16 --test=fileio --file-total-size=20G --file-test-mode=rndrw run So... FYI. I rebooted with elevator=deadline as a kernel param. db2:~# cat /sys/block/sda/queue/scheduler noop anticipatory [deadline] cfq (which I assume means I'm now running deadline. Is there any other way to find out?) And no performance diff. Note that you're benchmarks only show a 20M addition overhead. We're about 60x too slow for these drives so I'm not sure what could be going on here :-/ Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
On 4/15/05, Scott Haneda [EMAIL PROTECTED] wrote: How are sites doing the search by zip and coming up with results within x miles? Is there some OSS zip code download that has been created for this? http://marc.theaimsgroup.com/?l=php-generalm=110547634827453w=2 -- Greg Donald Zend Certified Engineer http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subqueries *not* using indexes for IN clause
We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. greg Kevin A. Burton wrote: http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries: mysql EXPLAIN SELECT * FROM FEED, ARTICLE WHERE ARTICLE.ID = 1628011 AND FEED.ID = ARTICLE.ID *** 1. row *** id: 1 select_type: SIMPLE table: FEED type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec) Which is *great*. The join is using both of the PRIMARY indexes on the columns and only references one row. Can't get any better than that! Now lets rewrite the SELECT to use a subquery: mysql EXPLAIN SELECT * FROM FEED WHERE ID IN (SELECT ID FROM ARTICLE WHERE ID = 1628011) *** 1. row *** id: 1 select_type: PRIMARY table: FEED type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2316698 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec) And here's where the fun begins. The FEED table won't use *ANY* index! It really can't get ANY worse than that. So either this is a bug in both 4.1.10 and 4.1.7 or the optimizer is just plain broken. Note that using FORCE INDEX doesn't work at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remove trailing character
How about UPDATE table set email = TRIM(TRAILING '' FROM email) http://dev.mysql.com/doc/mysql/en/string-functions.html Greg Fortune On Thursday 10 March 2005 03:29 pm, Scott Haneda wrote: I managed to mess up and email storage addresses are in the format of [EMAIL PROTECTED] How I can strip off the , it does not exist on all, only some. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Limits
Any chance of condensing some of the flag fields into bit fields? Alternatively, can you represent the variables as (rule id, variable name, variable value) in a single table rather than using lots of columns/tables? Greg Fortune On Monday 07 March 2005 10:31 am, Kevin Cowley wrote: Unfortunately both limits are getting in our way. We have approximately 32,000 variables scattered across a number of tables that we need to convert to bitmaps. The problem is that about 1500 of these variables need to go in a single bitmap hence the problems with the 1024/64 column/table limit. If you're correct and its using a bitmap in the optimizer, then it means its possible to extend the value by replacing the bitmap with a structure and a clever set of functions. I've used this technique in another application to overcome a 32 bit limit - its probably going to be a bastard of a job to rewrite the relevant parts of MySql though. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 March 2005 18:16 To: Kevin Cowley Cc: mysql@lists.mysql.com Subject: Re: Join Limits The join table limit in MySQL is dictated by the arch that's running on. 32 tables for 32bit and 64 tables for 64bit (Somebody correct me if I'm wrong). I believe this is due to using a bitmap inside the join optimizer to keep track of tables. 64 tables is a very hefty query it makes me think that maybe you are doing something wrong when designing your application to need a join that big. When the optimizer optimizes a query it checks every possible execution path through every table so the more joins you have the more paths the opimizer has to check to find the optimal one (This isn't entirely true but close enough for my argument here). Needing more than 1024 columns in a table also seems like bad design. Maybe you can detail more of what you are doing and why you need so many columns -Eric On Mon, 7 Mar 2005 12:21:52 -, Kevin Cowley [EMAIL PROTECTED] wrote: Does anyone know if there is a method of circumventing or changing the default join limits of 64 tables or 1024 columns? We're running Mysql 4.1.4 using MyISAM tables Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk http://www.alchemetrics.co.uk Email: [EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net *** *** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. *** *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List
Re: what is select_limit (and what is it used for)?
It only affects the client and only when the --safe-updates, --i-am-a-dummy, -U flag is used. I assume it just places an explicit limit clause of LIMIT 1000 on the end of any select statement run from the client. It's meant to keep less experienced users from running a couple of cross product joins that output millions of lines of data. Greg Fortune On Monday 07 March 2005 10:48 am, Caron, Christian wrote: Hi, the default select_limit is set to 1000. What does this mean and how can it affect a server? Is there some information out there about this variable? I can't find anything except a one line description on the MySQL site... - Christian Caron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb and Linux 2.6 Async I/O ??
Just found and read this study: http://www.distlab.dk/badger/Publications/report0403.ps and was curious to see if anyone has any additional thoughts as to the contents? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Rich Lafferty wrote: On Thu, Feb 24, 2005 at 02:38:46PM +0200, DebugasRu [EMAIL PROTECTED] wrote: Probably fsync() had failed to flush some part of a 16 kB page to disk. so what ? one of trade-offs would be to re-read the data from the disk and compare it with what it should be (another copy on the disk) and only after that fix the transaction, That didn't get rid of the race condition, it just moved it. So now you write the transaction, and you read it off, and then you write that it was OK -- but what if that write gets interrupted? So you write the transaction, and you read it off, and then you write that it was OK, and then you read that off, and then you write that the OK was OK. No, that could get interrupted to... Meanwhile, it turns out that you've been reading back from cache instead of platters all along. Or perhaps it turns out that the battery-backed cache in the disk controller has a dead battery, or simply doesn't work. If the disk promises data is written but it is not then there is nothing MySQL can do to *tell* that something is amiss. I think you've confused an RDBMS with a system administrator. Next thing you know you'll be complaining that MySQL isn't ACID-compliant because it can't survive a fire. -Rich Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb - raw partition vs filesystem store?
Heikki Tuuri wrote: A journaling file system like ReiserFS does not help if fsync does not work. A journaling file system itself is actually a bit like a transactional database. A broken fsync might cause bad damage there. I would be happy if users tested the 'pull-the-plug' performance of Linux-2.6.10/InnoDB. Jens Axboe might have solved most fsync problems: http://groups-beta.google.com/group/linux.kernel/browse_frm/thread/bbe45994b0277f7a/cc6d86c50514da81?q=axboe+fsync+linux_done=%2Fgroups%3Fas_q%3Daxboe+fsync+linux%26safe%3Dimages%26as_scoring%3Dd%26lr%3D%26hl%3Den%26_doneTitle=Back+to+Searchd#cc6d86c50514da81 I am running 2.6.10 here. I will see if I can set up a test case this weekend. Also, I know you and others have mentioned that Linux 2.6 + Opteron + Innodb is a problematic situation. Could you expand on this? From our personal experience (running mysql on Opteron + linux 2.6.10 w/ myisam tables), we have seen very slow performance when running intensive IO operations (deleting 20 million rows from a 50 col table) and we have experienced a greater number of index corruption on the opterons than on our intel dbs. Greg -- [EMAIL PROTECTED] Meetup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Rich Lafferty wrote: On Thu, Feb 24, 2005 at 09:10:32AM -0500, Greg Whalin [EMAIL PROTECTED] wrote: Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). FWIW, my understanding of LiveJournal's integrity problem after the power outage involved tables they weren't yet able to migrate off of MyISAM, and getting replication content in all directions. The stuff migrated to InnoDB already came up fine. Their replication setup is a bit complex -- see http://www.danga.com/words/2004_mysqlcon/mysql-slides.pdf for an overview. (My kit at the same facility was hit too, and recovered correctly, for what it's worth. :-) At least this mailing list has progressed beyond Why didn't they have a UPS?, I suppose. :-) -Rich They had most of their dbs transitioned to innodb, but even some of those came up corrupted due to write caching being enabled on individual drives. Their myisam tables simply needed indexes rebuilt (which is a pain in the butt and takes forever). Their complete story: http://www.livejournal.com/community/lj_dev/670215.html Needless to say, between LJ and Wiki, I am fairly paranoid about db corruption now (though our writes are considerably less than either of those two places as we are 99% reads ... our reads are considerably less than either of those sites as well :) ) Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb - raw partition vs filesystem store?
What are pros/cons as far as performance, reliability, and ease of backup/restore? Anyone have any experience running Innodb on raw partition? Any thoughts as to best filesystem for Innodb? What about pros/cons of journaled filesystems when in use with Innodb (i.e. transactions)? How do the recent experiences of LiveJournal/Wikipedia sway these answers? Just going over some thoughts in my head and want to see if any good discussion can come from this? Greg -- [EMAIL PROTECTED] Meetup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Many data centers do not allow customers to install their own UPS inside the rack. I am not sure if this is the case with Wikipedia, but it is definitely the case at the data center we are hosted in. I would love to shove one in after reading the horror stories at Livejournal and now Wikipedia. Greg [EMAIL PROTECTED] Meetup.com Daniel Kasak wrote: Scott Haneda wrote: There is a thread over at /. about WikiMedia being out due to a power outage http://slashdot.org/articles/05/02/22/0151213.shtml?tid=95 MySql is getting bashed pretty hard in some cases as apparently, in power failures, you get database corruption. (this is all from the posts, not my opinion at all, I am asking only to learn more) Also, it is mentioned the MySql devs just do not have data corruption from power failure a high priority. Many are leaning on Postgres. For me, MySql has never let me down (Knock on PSU) but I have to ask, for more data on this matter. I run MySql on OS X, I sometimes have to reboot, it comes back up fine, I do however, do nothing special to shut down mysql, should I be? There are certainly some people who have nothing better to do than preach the benefits of Postgres over MySQL on Slashdot. The simple fact is that no matter how fancy your software is, if your computer experiences a power outage, you can't *insist* that one particular application somehow protect your data from the fate of everything else on your computer. We have a UPS connected to our 2 servers. The Linux server is running apcupsd and controlling the Win2k server ( also running apcupsd in slave mode ). Both servers are connected via a crossover cable to each other ( so we don't have to put the network switches on the UPS ). If WikiMedia can't implement something at least as good as the above, then how can they or anyone else point the finger at MySQL for data corruption? The mind boggles over what they expect. Having said that, our MySQL server has experienced a power 'issue' ( my boss tripped over the power cord coming from the UPS ). We both turned white :) But MySQL started up OK, rolled back a transaction or 2, and continued on it's way. There's nothing like a UPS though. Or better yet, a UPS, daily backups, and a transaction log. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
I was under the impression that fsync() was only buggy in Linux in the 2.4 kernels. Is it still problematic in 2.6 series? Greg -- [EMAIL PROTECTED] Meetup.com Heikki Tuuri wrote: Peter, a buggy fsync() in Linux is one of the possible reasons here. If an InnoDB tablespace gets corrupt in a power outage, it is most probably caused by a bad fsync() implementation or configuration in the operating system or hardware. An fsync() call should write the data physically to disk or to a battery-backed, non-volatile disk cache. If it fails to do so, then any database, PostgreSQL or anything, can get corrupt. In the Wikipedia case, James Day posted an error message that InnoDB printed. It showed that the page checksum was wrong. Probably fsync() had failed to flush some part of a 16 kB page to disk. Unfortunately, fsync() is badly implemented in many, or most, OS/hardware configurations. Some ways to improve the situation: - use hdparm, or proprietary disk configuration tools of the disk vendor to switch off write caching in the SCSI controller and the disk itself; - use a battery-backed disk cache in the SCSI disk controller; - use certified hardware from one vendor: for example, Solaris/Sparc might work more reliably than an arbitrary Linux/x86 configuration; - do not use disk striping, software RAID, NFS, or anything exotic; - use the MySQL replication to keep a backup server. None of the above is guaranteed to remove corruption problems, and switching off write caching can seriously lower performance. In the Wikipedia case, battery-backed SCSI controllers did not help at all. Thus, using an Uninterrupted Power System is probably the best way to prepare against power outages. It is possible to implement fsync() correctly and efficiently in the operating system and hardware. There are no physical limitations that would make it impossible. But, unfortunately, in practice, many implementations are seriously flawed. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Peter Wilm [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, February 22, 2005 12:40 PM Subject: Re: wikipedia down, slashdot covering, mysql mentioned Could this possibly be a problem with a bad fsync implementation in linux ( 2.6.5)? See: http://www.ussg.iu.edu/hypermail/linux/kernel/0403.2/0527.html Scott Haneda schrieb: There is a thread over at /. about WikiMedia being out due to a power outage http://slashdot.org/articles/05/02/22/0151213.shtml?tid=95 MySql is getting bashed pretty hard in some cases as apparently, in power failures, you get database corruption. (this is all from the posts, not my opinion at all, I am asking only to learn more) Also, it is mentioned the MySql devs just do not have data corruption from power failure a high priority. Many are leaning on Postgres. For me, MySql has never let me down (Knock on PSU) but I have to ask, for more data on this matter. I run MySql on OS X, I sometimes have to reboot, it comes back up fine, I do however, do nothing special to shut down mysql, should I be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Daniel Kasak wrote: Greg Whalin wrote: Many data centers do not allow customers to install their own UPS inside the rack. I am not sure if this is the case with Wikipedia, but it is definitely the case at the data center we are hosted in. I would love to shove one in after reading the horror stories at Livejournal and now Wikipedia. I see. Do the data centers offer their own UPS ( as opposed to the customer setting up one for themselves ), or do they simple ban the use of UPSs outright? Either way, if you *need* a UPS, you can't hide behind the excuse oh yeah but XYZ wouldn't let me. You find a way to make it happen ... or if you can't you at least don't start blaming your software for what is obviously a hardware issue. Our data center spans two power grids, has a massive battery backup, and is backed by two massive generators, making power loss pretty difficult. However, we once lost power to our entire cage by hooking into too many servers into a single circuit and tripping a breaker. We now have two power feeds coming into each rack, and redundant power supplies on each db server split between the two circuits. We also carefully watch our power utilization to make sure we don't come close to hitting this limit. As I understand it, both LJ and Wiki suffered from someone hitting EPO switch (emergancy power off used to kill all power for when fire fighters have to come through ... a safety to make sure the firemen are not electrocuted when putting out fires). This EPO switch is precisly why UPSs are sometimes not allowed in each individual rack. For safety reasons, they need to ensure that all power is off in event of fire/rescue crews having to come through. I am not sure that LJ or Wiki blamed mysql. In fact, the only mysql complaint I saw from LJ was that they had to spend time rebuilding index files for their few remaining myisam tables. This I can easily believe as I see myisam indexes get corrupted when I sneeze in their general direction. However, LJ mentioned that all of their innodb tables recovered with no problems. In fact, both Wiki and LJ mentioned drive write caching to be the major cause of their problems. Greg -- [EMAIL PROTECTED] Meetup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT queries hang on amd64
I have 2 dual proc opterons and also have not seen this using MyISAM (4.1.9), however, I have been less than impressed with mysql on opteron (using the x86-64 binary from their site). Performance is generally less than that of our remaining old 32 bit dual proc xeon machines (all our db machines have 4 GB RAM and similar my.cnf settings giving 1.5GB to keybuffer, 768M to sort_buffer). In some cases, we are seeing queries take twices as long on the opteron machines as on the xeons, and it is rare for the opterons to ever outperform our xeon cluster. It is also much more likely for our tables to crash during big alters on the opteron machines than on the xeon, especially if the table has a full text on it (http://bugs.mysql.com/bug.php?id=7437). We are planning a full switch to innodb, and I seriously hope that makes the mysql on opteron experience a more pleasurable one. Greg Donny Simonton wrote: I know this may be strange, but have you turned on innodb on the box? Even if don't use it? I have 8 amd64 boxes and have never experienced this problem you are talking about. They range from single proc to quad proc. Never this problem but all of them have innodb turned on. Turn it on and see what happens, it could be a bug inside the mysql code. Donny -Original Message- From: Michel Buijsman [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 16, 2005 5:41 AM To: mysql@lists.mysql.com Subject: INSERT queries hang on amd64 My problem: INSERT queries hang on amd64. This looks a lot like Don MacAskill's bugreport in http://bugs.mysql.com/bug.php?id=3483 Which is listed as closed, but the bug is apparently still there so maybe it should be reopened... I've just upgraded from 4.1.9 to 4.1.10 (binary) on a dual opteron with 8G ram, running Debian 3.1 pure64 gcc-3.4. Also tried 4.0.23 last week, but that had the same problems. Kernels I've tried are 2.6.11-rc1-mm1 and 2.6.11-rc2. I'm running 2 boxes in a replication setup, using the amd64 as the master (or standalone) gave me pretty much what Don describes in bug 3483, insert queries start hanging after a while with no way to kill them except kill -9 from the OS, which is a bit rough. ;-) The master or standalone setup required quite a bit of poking to get it to hang in a test environment, or just a few short hours running as a production server. I haven't been able to narrow it down to one specific thing, but it appears to have something to do with indexes, because it ran fine after dropping all of them. I hacked up a test script that's throwing a random selection of selects and inserts at it at random intervals between 0 and 2 seconds, from 20 concurrent threads. I could trigger it by running a repair table on it while the test script was running. The repair thread would wait for its turn, then lock everything else out and do its thing, and when it was done the first insert after that would hang indefinitely. (Left it hanging over the weekend, nothing.) Adding skip-concurrent-insert fixes the problem, but cripples the performance so that's not really an option. Then tried to run one of the amd64 boxes as a slave off of a xeon box: Same problem, the replication thread hangs within seconds of starting the server, and again won't budge for anything except kill -9. This is on a server without _any_ other connections except for a processlist. I've run out of things to try, so I hope someone here can help... my.cnf: [mysqld] snipped replication setup user=mysql socket=/tmp/mysql.sock skip-locking set-variable= key_buffer=2G set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= read_buffer=16M set-variable= max_allowed_packet=10M set-variable= thread_cache=64 set-variable= thread_stack=512K set-variable= tmp_table_size=16M set-variable= interactive_timeout=600 set-variable= wait_timeout=600 set-variable= max_connections=1024 set-variable= query_cache_type=2 set-variable= query_cache_size=100M set-variable= join_buffer_size=8M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M skip-innodb -- Michel Buijsmantty.nl -- 2dehands.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb auto increment - reset itself automatically?
http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html Rishi Daryanani wrote: Hi, My database is mostly made up of MyIsam tables, and some InnoDB tables. One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate. So, there are only a very few records in the table at any given time. It was being used and the auto increment value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'. I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3 I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5. So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0 Can anyone think of a reason why this would happen? I'm really worried about the database now :( Thanks very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
Remember, a low cardinality index will possibly be ignored by the optimizer and an index on month will never have a cardinality of more than 12. For testing purposes, you might try added a column for month and populating it off your current data. update the_table set the_field=MONTH(the_field) Then, add an index on that column and test your queries against the new index. I'd be surprised if you saw much increase in speed, especially as your data set grows. Greg On Wednesday 09 February 2005 09:17 am, Gabriel B. wrote: Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Plus sign doesn't concatenate strings in MySQL?
Jochem van Dieten wrote: On Sat, 15 Jan 2005 11:11:05 -0500, Robert Alexander wrote: Each language is going to have its own personality. If they all did things the same way, we wouldn't have the wealth of different ones to choose from. DBMS's are not languages, they are implementations. Might not be a good idea, Dave, to take MS products as examples of what accepted standards are. MS has a long history of lack of respect for established standards. I suspect MySQL is more ANSI compliant than MS SQL Server. Can you substantiate that suspicion? Jochem This entire discussion is somewhat pointless in the face of reality. Sure it would be nice if every DB vendor had the exact same syntax, but that is not the case. They all differ enough so that anything more than basic SQL will not migrate. Accept it, deal with it, and RTFM to learn how to use SQL in each implementation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring replication in mysql
Tucker, Gabriel wrote: Anil Write a script that does a slave status and check if either of the threads are running. You could further check for error numbers and descriptions. This is what we do. Gabe -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 21, 2004 6:05 AM To: [EMAIL PROTECTED] Subject: Monitoring replication in mysql Hi, we have no of mysql replication setups in our setup. how to monitor those replication setups.my aim is if any slave goes down my script should immediately send an alert mail to me. if anybody having already developed scripts please let me know otherwise just give me an idea what to monitor in in replication setup. Thanks Anil DBA We have a script that monitors output from SHOW SLAVE STATUS, but actually had one time when replication died, but output from above command looked perfectly fine. It was due to massive table corruption, which was in turn due to filesystem corruption. Now, we have the same test running, but we also have a backup monitor which inserts a value in the master and tries to read it from all replicants. We allow an acceptable delay (5-10 minutes) before we page all admins with this backup test. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need more info about currently running queries
Yep, mysqladmin can give the same info. As noted in my original message, I need much more detailed info. The crux of the problem is that I need to run a query that could take several hours when it's using indexes correctly, but it is not working correctly so it takes longer than I've been willing to wait (about 14 hours). I need to make changes to indexes and run the query again, but I don't want to wait hours until I know whether the change was effective or not. I really need a way to find out how far the query has progressed after 15 minutes so I can calculate how much improvement the changes made. Greg On Tuesday 21 December 2004 02:25 am, Gleb Paharenko wrote: Hello. Using SHOW PROCESSLIST you can just check if your query running, or is waiting for some lock. For more info, you can run ps axm in shell and look for the thread state, but that's more related to the kernel stuff. See: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html Greg Fortune [EMAIL PROTECTED] wrote: Is there anyway to get additional information about a query that is currently running? I've got some performance problems I'm trying to analyze while loading large data sets and I'm running a test query, but I don't have any idea how far the query has progressed. The test query is a count(some_field) with the JOINs that I need and I'd like to know how many rows it has counted or how many rows it has visited from each table, etc, etc, etc. I know ahead of time that the result should be about 2.6 million and am just trying to get timing data. Is there any way to get at this kind of info? Greg -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need more info about currently running queries
Is there anyway to get additional information about a query that is currently running? I've got some performance problems I'm trying to analyze while loading large data sets and I'm running a test query, but I don't have any idea how far the query has progressed. The test query is a count(some_field) with the JOINs that I need and I'd like to know how many rows it has counted or how many rows it has visited from each table, etc, etc, etc. I know ahead of time that the result should be about 2.6 million and am just trying to get timing data. Is there any way to get at this kind of info? Greg -- Greg Fortune Enterprise Systems Eastern Washington University Phone: 509-359-6690 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
We also use Linux Virtual Server for load balancing, but only on our read-only cluster. Our current levels of RW traffic do not demand more than one machine. Russell E Glaue wrote: We have fail-over using Linux Virtual Server, now upgrading to Red Hat Cluster Suite. We do not implement load-balancing. Here is why. In order to have full true load balancing, you need to have two or more MySQL database server replicating data to each other in real time. Currently your only choice is master-master replication. master-master replication is not fast enough for real-time updates for a load-balanced environment. It is good enough for fail-over which is what we currently implement. You can still have load-balancing in a distributed fashion. If you have two MySQL database servers for your cluster, and you designate half your databases for one server and half for the other, technically you are implementing load balancing as your distributing your data among two servers. Although this is not really the best practice. However in this setup, each server can be a fail-over for the other. So if one server fails the other takes over as master for it's databases. fail-over recovery. This is one issue worth considering. If your MySQL database server comes back online, LVS (Linux Virtual Server) wants to immediately throw it back in as master/primary. However, once the server is up, it needs time to get back up to date from the other master. So for recovery, you will need to perform this manually and not let LVS do this automatically. Bring the server back online, allow time for the replication to complete, then go to the LVS and set the server as active primary. -RG Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank Febbraro wrote: The MySQL-client is 4.1.7, too? Yes The localhost mysql command is version 4.1.7-standard The remote query browser is version 1.1.1 gamma The remote JDBC driver is version 3.0.16-ga Are you seeing the slowdown only from your java app? Or from all clients? In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Got me then?! I am still running 4.0.22. Waiting for the connecter/J to come out of gamma and for 4.1.* to stabilize a bit, so I can't offer much other than what I have read. Frank Febbraro wrote: Are you seeing the slowdown only from your java app? Or from all clients? I see this slowdown from my java app AND from the remote MySQL client application (called MySQL Query Browser 1.1.1 and 1.1.2). Basically I see the slowdown from all clients that are remote machines, but I do not see it when I execute the queries on localhost (the server on which the database is running) In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. I experienced the slowdown with the 3.0.15 driver so I upgraded to the 3.0.16 driver and have the same problem. Any insight? THanks again, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy in replication setup run from slave
So, ran into a problem with using mysqlhotcopy to build a backup capable
of starting a new replicant. The current option (record_log_pos)
attempts to get master info from show master status, regardless of
whether running on master or slave. It also uses show slave status to
see where the slave is in processing master logs. I have found two
problems with this ...
- seems show master status returns nothing if you are not a master (and
not logging a binlog)
- takes the log position from the Read_Master_Log_Pos, which is only
what the IO thread has read, and not what has been applied
I have patched to add a new option --on_slave, which changes the
behavior a bit. It now does not run show master, but instead gets all
data from show slave status. It uses the Exec_master_log_pos to get the
current log position and dies if Slave_IO_Running is not Yes.
See attached patch. Any comments? Does this make sense to people? Any
better ideas? I can say that this works, whereas the default does not.
Meaning, I am able to take a snapshot, and build a new replicant off
of that using the data retrieved from (record_log_pos).
Greg
--
Greg Whalin
Meetup.com
[EMAIL PROTECTED]
--- /usr/local/mysql/bin/mysqlhotcopy 2004-10-23 12:15:09.0 -0400
+++ mysqlhotcopy 2004-12-07 13:17:12.780790784 -0500
@@ -75,6 +75,7 @@
--resetslave reset the master.info once all tables are locked
--tmpdir=# temporary directory (instead of $opt_tmpdir)
--record_log_pos=# record slave and master status in specified db.table
+ --on_slave tells record_log_pos that this is a slave box
--chroot=# base directory of chroot jail in which mysqld operates
Try \'perldoc $0\' for more complete documentation
@@ -115,6 +116,7 @@
suffix=s,
checkpoint=s,
record_log_pos=s,
+on_slave,
flushlog,
resetmaster,
resetslave,
@@ -478,8 +480,8 @@
$dbh-do( RESET SLAVE ) if ( $opt{resetslave} );
if ( $opt{record_log_pos} ) {
- record_log_pos( $dbh, $opt{record_log_pos} );
- $dbh-do(FLUSH TABLES /*!32323 $hc_tables */);
+ record_log_pos( $dbh, $opt{record_log_pos}, $opt{on_slave} );
+ $dbh-do(FLUSH TABLES /*!32323 $hc_tables */);
}
}
@@ -743,28 +745,41 @@
}
sub record_log_pos {
-my ( $dbh, $table_name ) = @_;
-
+my ( $dbh, $table_name, $on_slave ) = @_;
eval {
- my ($file,$position) = get_row( $dbh, show master status );
- die master status is undefined if !defined $file || !defined $position;
-
- my ($master_host, undef, undef, undef, $log_file, $log_pos )
- = get_row( $dbh, show slave status );
-
- my $hostname = hostname();
-
- $dbh-do( qq{ replace into $table_name
- set host=?, log_file=?, log_pos=?,
- master_host=?, master_log_file=?, master_log_pos=? },
- undef,
- $hostname, $file, $position,
- $master_host, $log_file, $log_pos );
-
+ my ($file, $position, $master_host, $log_file, $log_pos);
+ my $hostname = hostname();
+
+ if (defined $on_slave) {
+ ## try to get from slave status
+ my $io_running;
+ ($master_host, undef, undef, undef, $file, $position, undef, undef, $log_file, $io_running, undef, undef, undef, undef, undef, undef, $log_pos, undef)
+= get_row( $dbh, show slave status );
+
+ if ($io_running ne 'Yes') {
+die IO thread is not running, so can't get reliable master info;
+ }
+ }
+ else {
+ ## try to get from master and slave status
+ ($file,$position) = get_row( $dbh, show master status );
+ die master status is undefined if !defined $file || !defined $position;
+
+ ($master_host, undef, undef, undef, $log_file, $log_pos )
+= get_row( $dbh, show slave status );
+ }
+
+ ## update the log table
+ $dbh-do( qq{ replace into $table_name
+ set host=?, log_file=?, log_pos=?,
+ master_host=?, master_log_file=?, master_log_pos=? },
+ undef,
+ $hostname, $file, $position,
+ $master_host, $log_file, $log_pos );
};
if ( $@ ) {
- warn Failed to store master position: [EMAIL PROTECTED];
+ warn Failed to store master position: [EMAIL PROTECTED];
}
}
@@ -887,6 +902,13 @@
The name of the log-pos table should be supplied in database.table format.
A sample log-pos table definition:
+=item --on_slave
+
+This changes behavior of --record_log_pos to only check slave status.
+This allows us to get an indication of the actual position in the master
+log file we are at on this slave at the time of the hot copy so that you
+can use this data to build another replicant from this running slave.
+
=over 4
CREATE TABLE log_pos (
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 vs 4.1
Ronan Lucio wrote: Jeff, Actually, Ive never had such problem. Im just afraid of it because Ive read some issues about corruption in MySQL tables and the own MySQL Manual says that exist a command just to repair MyISAM tables (myisamchk - http://dev.mysql.com/doc/mysql/en/Table_maintenance.html). Once I dealing with hangs problems with MySQL + FreeBSD-4.x, Im afraid that the table could crashs when MySQL hangs... :-/ Ronan Never had table corruption, though I have had index corruption in myisam tables many times on sound hardware. Usually, this is due to an unclean shutdown, though I have seen it happen other times when the server is supposedly running with no problems. A repair table tablename always fixes the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Tue, 2004-11-30 at 11:50 -0600, Jeff Smelser wrote: On Monday 29 November 2004 11:35 pm, Greg Macek wrote: I was thinking about this as well, but was hoping to minimize the amount of work I would have to do. However, if this makes the most sense for upgrading, perhaps this is what I'll do. Actually, I think you will be minimizing going the 4.0 route. These major upgrades are no small tasks anyway. Jeff OK, sounds like what I should do is the following: * Upgrade current mysql install (3.23.49) to latest stable 4.0 series (4.0.22 according to the website) * Test out all applications and make sure everything is working as expected. * Test new features in 4.0.x vs. 3.23.x * After sufficient time to test, upgrade to latest stable 4.1.x version. I think I can make this scenario work. :) Thanks all for the direction. Otherwise, I may have run into issues doing the huge jump. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Wed, 2004-11-24 at 09:15 -0600, Jeff Smelser wrote: On Wednesday 24 November 2004 07:32 am, Hristo Chernev wrote: How to upgrade from 3.23.58 to 4.1.7? Which is the easiest way with minimal risk and downtime? Yeah right.. ;) Background: Heavy loaded mysql server, only one database but it is huge - 5GB.The database is replicated to another server.Mysql 3.23.58.Linux OS. Here is the my plan, please correct me if there is a better way or if I am doing unnecessary actions: 1. Stop the site and mysql servers and backup database. 2. Upgrade mysql versions on the two machines. 3. Compiling apache and php with new mysql 4.1 client lib. 4. Recreate users and rights ( the provided script fix_privilege_table will not work correctly form 3.23. to 4.1. will it?). 5. Start main mysql server without replication. 6. Rename database to olddb. 7. Create new database named db. Do one of 8a or 8b: 8a.Dump olddb with mysqldump, then run the result file to fill the new db (in order to have native 4.1 database ). 8b.Using SQL queries (or phpmyadmin) copy all tables from olddb to new db (in order to have native 4.1 database ). 9. Assure that all is correct then delete olddb, copy database to the slave and start mysql servers. 10. Confirm replication is ok, then start the site. This is a good list.. My suggestion would be to do this on some sort of back/dev machine.. php doesnt just work with 4.1 automatically (depending on version). Not sure which distro u use, that could be a driving factor of problems you may see to. Jeff I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
