MySQL bogging down
Running MySQL (mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)) on RedHat 9, and running into a rather serious (for me) problem. Pretty much every business day, mysqld will at some point start to take forever to finish queries. During this time, it uses tons of swap and only 30 or so megs of real memory is in use by mysqld. Around 35 Megs of real RAM is reported as free at the time (I assume the rest is buffers as killing mysqld will have little effect on the total amount of real memory free). The only way to fix this is to restart the server. The feeling I get is it prefers to use swap rather than real memory. In my opinion there's no reason for it to be using swap ever. I'm using the my-medium.cnf and the server has 1 gig of RAM. 'free' (with mysqld freshly restarted) shows: total usedfreeshared buffers cached Mem 1030672 994620 36052 0 247192 683636 -/+ buffers/cache: 63792 966880 Swap: 1020116 22104 998012 I don't currently have a 'free' output for when the server is swapping. I've considered the possibility of this RedHat version of MySQL being bad (I believe his has happened before), but I didn't find anything through my searches of Google. The load on this server is relatively light. The database itself is only 92 Megs and it averages 0.5 queries a second. Prior to these problems, this database was running in production without issue for a good 3 months. Recently the load has increased, but only from .3 to .5 queries a second. Anyone have any ideas on where I should start looking? Thanks, Chris -- Chris Cameron UpNIX Internet Administrator ardvark.upnix.net bitbucket.upnix.net -- http://www.upnix.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innocent (looking) query pinning mysqld
The gist; the below query doesn't finish, can't figure out why. -- Query -- SELECT images.iid, images.locnetid, images.path, images.CD, lightbox_location_image_LINK.iid FROM images CROSS JOIN image_cat_location_link LEFT OUTER JOIN lightbox_location_image_LINK ON images.cid = lightbox_location_image_LINK.cid AND images.lid = lightbox_location_image_LINK.lid WHERE image_cat_location_link.lid = 3430 AND image_cat_location_link.cid = 278 ORDER BY images.iid 'show processlist' while this query is running: --- | 982 | root | localhost | loctest | Query | 31 | Copying to tmp table | SELECT images.iid, images.locnetid, images.path, images.CD, lightbox_location_image_LINK.iid FROM im | CREATE definition on 3 tables involved: --- CREATE TABLE images ( iid int(10) unsigned NOT NULL auto_increment, lid int(10) unsigned default NULL, locnetid varchar(20) default NULL, CD varchar(50) default NULL, path longtext NOT NULL, hero tinyint(1) default NULL, cid int(10) unsigned default NULL, PRIMARY KEY (iid), UNIQUE KEY iid (iid), KEY IX_images (iid), KEY lid_index (lid), KEY cid_index (cid) ) TYPE=MyISAM; CREATE TABLE lightbox_location_image_LINK ( lightid int(10) unsigned NOT NULL default '0', cid int(10) unsigned NOT NULL default '0', lid int(10) unsigned NOT NULL default '0', iid int(10) unsigned NOT NULL default '0', PRIMARY KEY (lightid,cid,lid,iid), KEY lightid_index (lightid), KEY lid_index (lid), KEY cid_index (cid) ) TYPE=MyISAM; CREATE TABLE image_cat_location_link ( iid int(10) unsigned NOT NULL default '0', cid int(10) unsigned NOT NULL default '0', lid int(10) unsigned NOT NULL default '0', PRIMARY KEY (iid,cid,lid) ) TYPE=MyISAM; I'm unable to run EXPLAIN on this query as it also hangs. Also, I've been fiddling with indexes, so they may be a little out of whack.. But I do think the right things are indexed. Any help would be appreciated. Thanks, Chris -- Chris Cameron UpNIX Internet Administrator ardvark.upnix.net bitbucket.upnix.net -- http://www.upnix.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 3.23.41 fails to install as service on Win 2000 Professional
Perhaps it's his platform of choice. On Sun, 2 Sep 2001, Sinisa Milivojevic wrote: What you are trying to do can be done on NT / W2K only. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Alternate word finding with '%'
I'm sure this is disscused somewhere, but I'm not sure how I'd look for it. Is it possible to supply a thesaurus-like file for mysql so when you go %oil% it finds petrol gas and/or lubricant? Does anyone know of a database that will do this? Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Thinking of switching from MyISAM to InnoDB
Is there a place that outlines the advantages/disadvantages of both MyISAM and InnoDB? Thanks, Chris -Original Message- From: Tonu Samuel [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 04, 2001 2:34 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Thinking of switching from MyISAM to InnoDB On 04 Aug 2001 16:25:14 +0300, Sinisa Milivojevic wrote: There is no 4 Gb limit in MyISAM with later 3.23 versions. This limit is imposed by a filesystem only. and RAIDed table can help to build aby size tables if only index file doesn't get too big. Actually InnoDB is nice thing. Just bith MyISAM and InnoDB have good advantages and you should decide, which ones you need more. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Managing user db's (quotas)
I sent this before, but it never made it.. Does anyone here have any advice on controlling the size of user databases? I've heard talk of symlinks before, anyone had any luck with that? Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL or MS SQL?
I'm sure if done right, MySQL could do whatever you want satisfactorily. Same with MS SQL (ignoring cost). However, something few people seem to know, but I've realized from experience is that when you offer people services based off free software, you get a different kind of customer. And not the good kind. Where I work (http://www.advantcomp.com (free plug!)) we offer both Windows and UNIX (usually OpenBSD) solutions. When people come to us looking for something to be done in free unix (Linux, OpenBSD, PHP, MySQL, etc), they expect to pay crappy prices. And frankly they're crappy clients. They don't know what they want, they're only available to talk after hours, etc. While there are a few people who are looking for serious applications done in free software, they are few and far between. On the other side of the coin is people looking for enterprise solutions. These are people expecting to pay a lot, so they give you a lot. These people usually look for MS SQL/ColdFusion sort of deal. The Windows side of our company gets -WAY- more money than the free-UNIX side. So if you're looking to make money, and have some capital to burn to start, I'd say use non-free software. That can be UNIX still, but not free-unix. Sybase, DB2 or Oracle for a database, and JSP seem to be the only enterprise solutions around for UNIX. But judging by your question you don't seem to be stuck with UNIX for a platform. If you're going NT I'd be careful with Microsoft software. IIS and MS SQL scares the crap out of me when it comes to security. Anywho, probably way beyond what you wanted to know, Chris -Original Message- From: Alexander Chernienko [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 18, 2001 11:51 AM To: [EMAIL PROTECTED] Subject: MySQL or MS SQL? Hi all, I'm currently writing proposals for a customer who want to hire our little programming team for creating corporate information system, wich includes huge database with number of records between 1-10 millions (incl. blobs - pictures). Expected database size is tens of Gb. There is idea of using MS SQL Server for some reasons, on other hand we have big experience with MySQL (but with smaller databases). So I'm very interested to hear advise, especially from people who had experience with both SQL servers - which SQL Server is preferable in such situation. In other words - huge database, blobs etc, MS SQL vs. MySQL, your opinion? Many thanks in advance, Alexander - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing Credit Cards
A client has asked us to make him an application that requires us keeping Credit Card Numbers. I'm a bit concerned as it immediately remined me of egghead.com (having all their CC #'s stolen). One of the ideas was to have the users password encrypted as an md5 hash, and then to encrypt the users CC with their password. So we wouldn't actually keep anything that could immediatly show credit card numbers on the server. The problem this creates is whenever we need to use their credit card, the user needs to enter in their password. Which would be quite inconvenient as we'd use it in many places (like showing the last 4 digits to verify it's the right card). The only other idea was to just stick them in plain text and keep people far away from the MySQL server. Has anyone had any experience with this? Or any suggestions? Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Expected speed of MySQL on a 166
I'm currently using a 166 UNIX machine for a dedicated MySQL database, and I'm interested in knowing what kind of speed I should be expecting from it. I ask because right now it's at a point of being unusable even for development on some of the larger queries. I compiled MySQL 3.23.37 myself with -O6's -march-i586 and various ./configure options I don't remeber. The database I'm using has 3 tables of about 9 thousand entries, un-indexed decimal(5,8)'s, and a few tinytext's. There are 168 columns. A query like 'SELECT 97TotalAssets, 98TotalAssets FROM Business WHERE SIC='112'' will take a good 5 seconds. Any more useful of a query will take up to 90 seconds. All the queries are SELECT's. Is there something I can do, or am I going to need something faster than a 166? Thanks Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Flakey mysqld under OpenBSD
Hi, Seems I've never had any luck with MySQL and OpenBSD, and I'm hoping I can finally get some help now that I've finally got around to recompiling it again. I'm running OpenBSD 2.7 and am compiling MySQL 3.23.38. I'm using the configure options; CXXFLAGS=-O6 -march=i586 CFLAGS=-O6 -march=i586 ./configure --with-unix-socket-path=/var/mysq/mysql.sock --with-mysqld-user=mysql --without-debug --low-memory --without-docs --without-bench --without-readline When it stops on sql_yacc.cc, I just compile that seperately and everything finishes nicely. Now, when I do a 'make test', I get exactly this (I appologize for the wrapping): lt-mysqltest: At line 1576: query 'select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ' failed: 2013: Lost connection to MySQL server during query real 148.53 user 0.28 sys 0.44 Aborting. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon lt-mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/usr/src/mysql-3.23.38/mysql-test/var/tmp/mysql-master.sock' (61)' Check that mysqld is running and that the socket: '/usr/src/mysql-3.23.38/mysql-test/var/tmp/mysql-master.sock' exists! master not cooperating with mysqladmin, will try manual kill ./mysql-test-run[781]: kill: 15515: No such process master refused to die. Sending SIGKILL ./mysql-test-run[781]: kill: 15515: No such process Master shutdown finished Slave shutdown finished *** Error code 1 Stop in /usr/src/mysql-3.23.38 (line 499 of Makefile). Almost every version of MySQL I've ever run on this machine has given me the error Lost connection to MySQL server. The last version I was running worked fine for about 50 days straight until that's all it'd print out without a refresh every half day (MySQL 3.23.35). -Any- help on this would be appreciated as all my other calls for help have gone unanswered. Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump hangs
I should start reading this list more.. I've had a similar problem, and good luck finding many people who'll help you with OBSD problems on this list (or any problems for that matter). Anywho, it's ports specific I think. I installed MySQL from source compiling with -O6 and the proper arch (likely not i386) and it stopped crapping when I did heavy things (like using Pronto). So I'd suggest compiling and installing it from source rather than ports. No it doesn't uninstall nicely, but you can do a --prefix="/usr/local/mysql" and --eprefix="/usr/local/mysql" I believe (pulling this off the top of my head). And if you run into the "out of virtual mem" problem mail me and I can help you with that (basically compile sql_yacc.c by itself). Hope this helps, Chris -- "Anyone who cannot cope with mathematics is not fully human. At best he is a tolerable subhuman who has learned to wear shoes, bathe and not make messes in the house." - Lazarus Long, "Time Enough for Love" - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dying Server
Whenever my MySQL server (3.23.33) is under heavy load it restarts itself. Stopping whatever was connected to it at the time. In my .err log I've just got; 010307 00:12:32 mysqld started /usr/local/libexec/mysqld: ready for connections Killed 010307 00:16:56 mysqld restarted /usr/local/libexec/mysqld: ready for connections Killed 010307 00:20:51 mysqld restarted /usr/local/libexec/mysqld: ready for connections Killed etc. Any ideas? Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database reading problems
After noticing all my PHP scripts using MySQL had stopped working, I noticed that any attempt to look through databases mith mysql brought up; Can't read dir of '.' (Errcode: 9) Can't read dir of './mysql' (Errcode: 9) Database changed I can no longer get any information from MySQL, and I'm hoping this is something I did and don't remember, rather than some other horrible, less reversable thing. I checked directory permissions and all that, but everything is as usual. Any help would be appreciated Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
