Massive memory utiliazation
Hello, We have currently tuned MySQL for a high rate of traffic. But, now we are seeing issues with memory usage. It reaches about 2GB and the server becomed wildly unstable. Below is our my.cnf file. Can anyone point out any glarring errors? We are running this on a Dell 2650 with Red Had Advanced Server v2.1 with Kernel 2.4.9-e.25smp and Hyper threading. Thanks a lot! [client] port= 3306 # The MySQL server [mysqld] datadir = /var/lib/mysql port= 3306 skip-locking set-variable= max_connections=800 set-variable= key_buffer=1500M set-variable= max_allowed_packet=1M set-variable= table_cache=16384 set-variable= sort_buffer=256k set-variable= record_buffer=256k set-variable= record_rnd_buffer=256k set-variable= thread_cache=64 set-variable= thread_concurrency=32 set-variable= myisam_sort_buffer_size=64M set-variable= interactive_timeout=300 set-variable= open_files_limit=6 set-variable= wait_timeout=300 set-variable= long_query_time=5 set-variable= tmp_table_size=16M server-id = 0 # Adding bin log for PIT recovery log-bin #set-variable = bdb_cache_size=768M #set-variable = bdb_max_lock=10 log-slow-queries=/var/log/slowqueries.log [safe_mysqld] open-files-limit=6 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout
Re: Newbie Question
Rhino [EMAIL PROTECTED] wrote: I'm new to MySQL but I have extensive experience with DB2 so I'm getting quite confused about how MySQL is supposed to work. I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am trying to create a pair of InnoDB tables that are related to one another via a foreign key. I created the tables successfully but when I try to insert a row into the child table that violates the foreign key, MySQL loads the bad row, even though the foreign key doesn't exist! [skip] I got a very big clue when I ran this command: show table status from Sample; It showed that my two tables were type MyISAM, *not* InnoDB. If my tables really are MyISAM, then I'm not surprised that the foreign key constraint doesn't work since MyISAM doesn't support foreign keys, at least as I understand the manual. However, this doesn't answer the big question: *Why* aren't my tables InnoDB since I explicitly defined them that way?? Can any MySQL veterans clear up this mystery for me? If you try to create table type that is disabled or not compiled-in, MySQL creates MyISAM table type. Execute statement SHOW VARIABLES LIKE have_innodb; If you see 'DISABLED' in the output, it means that you run MySQL server with skip-innodb option. If you see 'NO' in the output, it means that MySQL server was configured without InnoDB support. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default UTF-8 Encoding
David Perron [EMAIL PROTECTED] wrote: Is there a way to change the default mysql encoding to be something else, say UTF-16LE at the session level? MySQL doesn't support UTF-16LE. If you want to set up connection character set you can: - execute SET CHARACTER SET from the client - run mysql client with --default-character-set option (or put this option to the my.cnf file) http://www.mysql.com/doc/en/Charset-connection.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow sql on one dataset, fast on others...how to use explain and optimize
This SQL takes 2 minutes to run on one database, and seconds on another. The tables has the same indexes, but different number of rows in them. Slow database: persontbl2 = 25000 rows memberstbl = 196000 rows groupchildrentbl = 9000 rows structuretreetbl = 58000 rows structureacl = 8800 rows Fast database: persontbl2 = 43000 rows memberstbl = 128000 rows groupchildrentbl = 5200 rows structuretreetbl = 28900 rows structureacl = 4900 rows The explain for the slow database is: SQL-query: EXPLAIN SELECT DISTINCT pers.id, pers.firstname FROM PersonTbl2 pers, GroupChildrenTbl gg1, StructureTreeTbl outree, MembersTbl memb1, GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl WHERE memb2.personid = 440287252 AND memb2.groupid = gg2.gchildid AND gg2.groupid = sacl.groupid AND sacl.group_access = 100 AND sacl.structid = outree.orgid AND outree.orgchild = gg1.groupid AND gg1.gchildid = memb1.groupid AND memb1.personid = pers.id AND pers.user_type = 3 GROUP BY pers.id, pers.firstname ORDER BY pers.firstname; table typepossible_keys key key_len ref rows Extra memb2 ref PRIMARY,groupid,persgrp 4 const 9 Using where; Using index; Using temporary; Using filesort personid,persgrp gg2 ref PRIMARY,gchildid,groupid gchildid 4 memb2.groupid 1 sacl ref PRIMARY,groupid,structid, groupid 4 gg2.groupid 1 Using where group_access outree ref PRIMARY,orgid, orgid 4 sacl.structid 7 orgchild,prim_orgtree_ix2 gg1 ref PRIMARY,gchildid,groupid groupid 4 outree.orgchild 1 memb1 ref PRIMARY,groupid, groupid 4 gg1.gchildid 6 personid,persgrp pers eq_ref PRIMARY,usertypeIX PRIMARY 4 memb1.personid 1 Using where The explain for the fast database is exactly the same, but the rows number is now: 3,1,1,6,1,3,1 instead. Why do these queries run so differently? We have over 100 databases that run this query fast, all with different rowcounts in the tables. But in one database it runs slow... - Are Pedersen Development Team Leader Server Operations manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: Load Infile (Fixed Length)???
Hello. I've been using 'load data infile...' to build a data warehouse and it seems to be extremely efficient. However, I receive very large, fixed delimited file and (so far) have to parse them with a little Perl code. It's not a terrible situation, but a step I'd like to eliminate if I could use 'load data infile...' from a known fixed file format. Is this possible? Thanks in advance. Tony
Re: best-practices backups
In a message dated 2/11/2004 4:44:00 PM Eastern Standard Time, [EMAIL PROTECTED] writes: Hi, I do just this at the moment - I have a cron job that runs MySQL dump, gzips the output, and will then ftp the important files to a machine that get's backed-up to a tape drive. I also time the dump, and it currently takes just over 3 minutes which is quite acceptable for what I'm doing. I'm thinking about piping the output of mysqldump straight through gzip and then ftp'd away to ease the disk access too, but that maybe later. I would still like a best-practices guide though, so that if everything does go wrong I'm sure that I've got everything I need to reconstruct the system as swiftly as possible. I've done some dry runs, but still feel that this isn't the same as learning from that gleaned by others that may have actually been faced with disaster in the past! Thanks, Mike Mike, This is a great topic of interest to me, as I am rolling out MySQL throughout our enterprise and naturally, the MS SysAdmin are not comfortable doing backups on a Linux box--so I move the dumps to their backup server. Have you tried to do all of that in one step using SSH? For example, I often transfer big datafiles using the following command: tar cf - BigUncompressedDataFile | ssh -C [EMAIL PROTECTED] tar xf - This effectively compresses the data on the fly, without creating a temporary tar file; pipes it to the remote host over SSH ( I use -C for SSH compression in case any more could be squeezed out) and then uncompresses the file on the remote host. Seems to me that your process makes perfect sense, I am just lazy and would want it one in one command in my cron job. However, that's just what I use to transfer files to a place I want to work on them, in an uncompressed format on the remote host...obviously not what you'd do for backups. I'll mess with trying this with secure copy (SCP) to replace the SSH portion above. Just thinking out loud. A Backup/Restore Best Practices Guide would be very valuable to everyone I should think. Happy to help develop/host one if anyone wants to pitch in ideas. /T
Re: best-practices backups
In a message dated 2/11/2004 2:26:09 PM Eastern Standard Time, [EMAIL PROTECTED] writes: I read this over and over.. I am curious why replication is such high finance?? I run it here. The Production system is a high finance machine and the replicated box is a old clunker basically.. It doesn't take much for the stuff to be replicated over.. The high dollar has queries, this and that. The replicated machine is just simply keeping up with the changes.. That's it. You could do that with just about any decent machine.. I would think.. sure, there is going to be the few that load and change data constantly.. But I still think that would be ok.. (have to test it). Do you guys agree? Hmmm...not in all cases. While I'll agree that this would be a cost-effective method for many MySQL installations, I use MySQL for in a data warehousing environment which typically has few, but extremely large bulk updates. We are in the multi-TB range, so this would not work for us.
Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Basically, only error-free statements are replicated.to the slave, thus ensuring that constraints are satisfied. Regards, Chris David Griffiths wrote: Thanks for the reply. So InnoDB (and even MyISAM) use transactions (expected with InnoDB) and slaves track their position in the binary log files as they are reading them in so as not to violate any constraints? David - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: David Griffiths [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 13, 2004 5:46 PM Subject: Re: Binary Logs and Transactions (with InnoDB and MyISAM) Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow sql on one dataset, fast on others...how to use explain and optimize
At 04:21 AM 2/14/2004, you wrote: This SQL takes 2 minutes to run on one database, and seconds on another. The tables has the same indexes, but different number of rows in them. It could be you have deleted a lot of rows from the table and therefore it is fragmented. You may want to try optimizing the table using OPTIMIZE tablename. See http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Also your keys may not be evenly distributed. Take a look at Analyze tablename. http://www.mysql.com/doc/en/ANALYZE_TABLE.html Mike Slow database: persontbl2 = 25000 rows memberstbl = 196000 rows groupchildrentbl = 9000 rows structuretreetbl = 58000 rows structureacl = 8800 rows Fast database: persontbl2 = 43000 rows memberstbl = 128000 rows groupchildrentbl = 5200 rows structuretreetbl = 28900 rows structureacl = 4900 rows The explain for the slow database is: SQL-query: EXPLAIN SELECT DISTINCT pers.id, pers.firstname FROM PersonTbl2 pers, GroupChildrenTbl gg1, StructureTreeTbl outree, MembersTbl memb1, GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl WHERE memb2.personid = 440287252 AND memb2.groupid = gg2.gchildid AND gg2.groupid = sacl.groupid AND sacl.group_access = 100 AND sacl.structid = outree.orgid AND outree.orgchild = gg1.groupid AND gg1.gchildid = memb1.groupid AND memb1.personid = pers.id AND pers.user_type = 3 GROUP BY pers.id, pers.firstname ORDER BY pers.firstname; table typepossible_keys key key_len ref rows Extra memb2 ref PRIMARY,groupid,persgrp 4 const 9 Using where; Using index; Using temporary; Using filesort personid,persgrp gg2 ref PRIMARY,gchildid,groupidgchildid4 memb2.groupid 1 saclref PRIMARY,groupid,structid, groupid 4 gg2.groupid 1 Using where group_access outree ref PRIMARY,orgid, orgid 4 sacl.structid 7 orgchild,prim_orgtree_ix2 gg1 ref PRIMARY,gchildid,groupidgroupid 4 outree.orgchild 1 memb1 ref PRIMARY,groupid,groupid 4 gg1.gchildid6 personid,persgrp perseq_ref PRIMARY,usertypeIX PRIMARY 4 memb1.personid 1 Using where The explain for the fast database is exactly the same, but the rows number is now: 3,1,1,6,1,3,1 instead. Why do these queries run so differently? We have over 100 databases that run this query fast, all with different rowcounts in the tables. But in one database it runs slow... - Are Pedersen Development Team Leader Server Operations manager -- 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: Slow sql on one dataset, fast on others...how to use explain and optimize
I am running OPTIMIZE table and ANALYZE table each night on all tables and databases. Shouldn't the explain statement tell me what's going on...it says 9,1,1,7,1,6,1 on rows, and multiplied up its not much. 378 rows to examine should run fast. mos wrote: At 04:21 AM 2/14/2004, you wrote: This SQL takes 2 minutes to run on one database, and seconds on another. The tables has the same indexes, but different number of rows in them. It could be you have deleted a lot of rows from the table and therefore it is fragmented. You may want to try optimizing the table using OPTIMIZE tablename. See http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Also your keys may not be evenly distributed. Take a look at Analyze tablename. http://www.mysql.com/doc/en/ANALYZE_TABLE.html Mike Slow database: persontbl2 = 25000 rows memberstbl = 196000 rows groupchildrentbl = 9000 rows structuretreetbl = 58000 rows structureacl = 8800 rows Fast database: persontbl2 = 43000 rows memberstbl = 128000 rows groupchildrentbl = 5200 rows structuretreetbl = 28900 rows structureacl = 4900 rows The explain for the slow database is: SQL-query: EXPLAIN SELECT DISTINCT pers.id, pers.firstname FROM PersonTbl2 pers, GroupChildrenTbl gg1, StructureTreeTbl outree, MembersTbl memb1, GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl WHERE memb2.personid = 440287252 AND memb2.groupid = gg2.gchildid AND gg2.groupid = sacl.groupid AND sacl.group_access = 100 AND sacl.structid = outree.orgid AND outree.orgchild = gg1.groupid AND gg1.gchildid = memb1.groupid AND memb1.personid = pers.id AND pers.user_type = 3 GROUP BY pers.id, pers.firstname ORDER BY pers.firstname; table typepossible_keys key key_len ref rows Extra memb2 ref PRIMARY,groupid,persgrp 4 const 9 Using where; Using index; Using temporary; Using filesort personid,persgrp gg2 ref PRIMARY,gchildid,groupidgchildid4 memb2.groupid 1 saclref PRIMARY,groupid,structid, groupid 4 gg2.groupid 1 Using where group_access outree ref PRIMARY,orgid, orgid 4 sacl.structid 7 orgchild,prim_orgtree_ix2 gg1 ref PRIMARY,gchildid,groupidgroupid 4 outree.orgchild 1 memb1 ref PRIMARY,groupid, groupid 4 gg1.gchildid6 personid,persgrp perseq_ref PRIMARY,usertypeIX PRIMARY 4 memb1.personid 1 Using where The explain for the fast database is exactly the same, but the rows number is now: 3,1,1,6,1,3,1 instead. Why do these queries run so differently? We have over 100 databases that run this query fast, all with different rowcounts in the tables. But in one database it runs slow... - Are Pedersen Development Team Leader Server Operations manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - Are Pedersen Development Team Leader Server Operations manager ([EMAIL PROTECTED]) Tel: +47 24 14 99 61 http://fronter.com Kongensgate 24 N-0153 OSLO, Norway Tel.: +47 24 14 99 99 Fax.: +47 24 14 99 98 - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql ended
Description: Downloaded, installed, and tried to run mysql server 4.0.18 on Red Hat ES 3x and recieved the following error: Starting mysqld daemon with databases from /var/lib/mysql 040214 12:08:16 mysqld ended I can't get the mysql server to run. Any suggestions on what could be wrong? How-To-Repeat: Download mysql-standard-4.0.18-pc-linux-i686.tar.gz from mysql.com. Follow the commands from the MySQL Technical Reference Manual (pg. 94) as follows: shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /root/mysql-standard-4.0.18-pc-linux-i686.tar.gz | tar xvf - shell ln -s /usr/local/mysql-standard-4.0.18-pc-linux-i686 mysql shell cd mysql shell scripts/mysql_install_db shell chown -R root . shell chown -R mysql data shell chgrp -R mysql . shell bin/mysqld_safe --user=mysql It is at this point that the following message appears: Starting mysqld daemon with databases from /var/lib/mysql 040214 12:08:16 mysqld ended Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.58 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.4.21-4.EL #1 Fri Oct 3 18:13:58 EDT 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.3/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-20) Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxr-xr-x1 root root 13 Feb 14 11:23 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1564956 Oct 2 20:51 /lib/libc-2.3.2.so -rw-r--r--1 root root 2461044 Oct 2 18:43 /usr/lib/libc.a -rw-r--r--1 root root 204 Oct 2 18:21 /usr/lib/libc.so Configure command: ./configure '--host=i386-redhat-linux' '--build=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-readline' '--without-debug' '--enable-shared' '--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb' '--enable-local-infile' '--enable-large-files=yes' '--enable-largefile=yes' '--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib' '--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' 'CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux' 'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL big problem
Since today I can only access to 2 databases (instead of 10) : mysql -u root show databases shows me only 2 db theres 10 in /var/lib/mysql I suspect that the grant tables are damaged . How can I repair them ? Note that I cannot do mysql -u mysql mysql which returns access denied to '@localhost' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update is deleting data
This is on my development machine using Lasso 6.0.4, MacOS 10.2, MySQL 4.0.12-standard. Using Lasso: On one database I can add and update records. On another I can add, but updating sets all the specified fields to NULL in the target record (the key and non-referenced fields stay OK), even though they are set to NOT NULL. Updating works fine using Terminal, CocoaMySQL, and PHP. So, firstly, does anyone know what might be going on? I've checked every setting I can find in Lasso and MySQL without finding any differences between the two databases, but should I be looking for something in particular? Secondly, in order to find out what MySQL is receiving from Lasso someone (on the LassoTalk list) suggested using an update log. Using Terminal, I stopped MySQL with mysqladmin shutdown, and then restarted it with ./bin/mysqld_safe --log-update=roddie I get a message about a daemon being started, but then Terminal hangs (10 mins) and if I use Ctrl Z is says Suspended. Eventually I can quit Terminal and MySQL seems to be running unaffected. I can open the roddie.001 log file in Pico but it is empty. What should I be doing to get the update log working? Any advice will be much appreciated. Roddie Grant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL big problem
I have resolved the problem by: - removing the /var/lib/mysql/mysql folder - reinstalling the mysql-server package Anyway I have no idea of where this can come from. This seems like an obscure bug Is there a way to reinstall a new 'mysql' database without reinstalling the software ? - Original Message - From: chatiman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 8:46 PM Subject: MySQL big problem Since today I can only access to 2 databases (instead of 10) : mysql -u root show databases shows me only 2 db theres 10 in /var/lib/mysql I suspect that the grant tables are damaged . How can I repair them ? Note that I cannot do mysql -u mysql mysql which returns access denied to '@localhost' -- 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]
REPLACE INTO and CASCADEs.
Hello, I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the behaviour of REPLACE INTO appears to have changed. I have two tables: table2 references table1 with ON DELETE CASCADE and ON UPDATE CASCADE on a primary key. Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the row in table1 gets replaced. No rows in table2 get deleted. Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 gets replaced, but this replacement gets cascaded into table2 and all rows in table2 with the primary key get deleted. Has anyone encountered something similar? I have only one box available so I am not able to re-test my observations again (I have reverted to 4.0.17). Thanking in advance. Regards, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: mysql and borland
- Original Message - From: Luiz Rafael Culik Guimaraes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 4:35 PM Subject: mysql and borland Dear Friends i uas using mysql 4.0.16 with out any problem now i updated my server to 4.0.18 regenerated my import lib and relinked my app now i get an GPF on this MYSQL * m; m=mysql_init(NULL); any help Regards Luiz --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 9/2/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 9/2/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL big problem
Hi, The install of MySQL is mainly only a unpack. If You make a new folder - ex. C:\mysql02 - and install in this, You have the \data available and can copy to Your first installation, or change You my.ini to use second installation. Best regards Peter WR - Original Message - From: chatiman [EMAIL PROTECTED] To: chatiman [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 9:08 PM Subject: Re: MySQL big problem I have resolved the problem by: - removing the /var/lib/mysql/mysql folder - reinstalling the mysql-server package Anyway I have no idea of where this can come from. This seems like an obscure bug Is there a way to reinstall a new 'mysql' database without reinstalling the software ? - Original Message - From: chatiman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 8:46 PM Subject: MySQL big problem Since today I can only access to 2 databases (instead of 10) : mysql -u root show databases shows me only 2 db theres 10 in /var/lib/mysql I suspect that the grant tables are damaged . How can I repair them ? Note that I cannot do mysql -u mysql mysql which returns access denied to '@localhost' -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and borland
Hi all Please ignore this message was an old version od libmysql.dll on my windows\system32 folder Regards Luiz --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 9/2/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld corruption
I just installed 4.0.16 linux (x86, libc6) rpms on a fresh RH7.3 installation. I am experiencing a problem that when I reboot the machine (shutdown -r now), the mysqld binary gets corrupted and mysql will not start. Before reboot: $md5sum /usr/sbin/mysqld 9e80e423401d959a7c1ecdb87ba49988 /usr/sbin/mysqld after reboot: $ md5sum /usr/sbin/mysqld 81089d38ef2753c831e929c53f7a4b58 /usr/sbin/mysqld do I have a bad hard drive or I am missing something here? Any help would be greatly appreciated. Thanks, Juan E.
Re: Slow sql on one dataset, fast on others...how to use explain and optimize
At 11:34 AM 2/14/2004, Are Pedersen wrote: I am running OPTIMIZE table and ANALYZE table each night on all tables and databases. Shouldn't the explain statement tell me what's going on...it says 9,1,1,7,1,6,1 on rows, and multiplied up its not much. 378 rows to examine should run fast. Are, Your SQL statement is using Select Distinct and Group By, which is redundant. You can remove the DISTINCT and get the same results. You may want to run MyISAMChk on the table to make sure there is nothing wrong with it. I would simplify the statement to see how many rows it is returning in the join in case it is doing an outer product on one of the tables, which is easy enough to do if you forget one of the links in the Where clause. So try something like: SELECT count(*) FROM PersonTbl2 pers, GroupChildrenTbl gg1, StructureTreeTbl outree, MembersTbl memb1, GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl WHERE memb2.personid = 440287252 AND memb2.groupid = gg2.gchildid AND gg2.groupid = sacl.groupid AND sacl.group_access = 100 AND sacl.structid = outree.orgid AND outree.orgchild = gg1.groupid AND gg1.gchildid = memb1.groupid AND memb1.personid = pers.id AND pers.user_type = 3 If too many rows are returned, play with the joins until you find the culprit. Mike mos wrote: At 04:21 AM 2/14/2004, you wrote: This SQL takes 2 minutes to run on one database, and seconds on another. The tables has the same indexes, but different number of rows in them. It could be you have deleted a lot of rows from the table and therefore it is fragmented. You may want to try optimizing the table using OPTIMIZE tablename. See http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Also your keys may not be evenly distributed. Take a look at Analyze tablename. http://www.mysql.com/doc/en/ANALYZE_TABLE.html Mike Slow database: persontbl2 = 25000 rows memberstbl = 196000 rows groupchildrentbl = 9000 rows structuretreetbl = 58000 rows structureacl = 8800 rows Fast database: persontbl2 = 43000 rows memberstbl = 128000 rows groupchildrentbl = 5200 rows structuretreetbl = 28900 rows structureacl = 4900 rows The explain for the slow database is: SQL-query: EXPLAIN SELECT DISTINCT pers.id, pers.firstname FROM PersonTbl2 pers, GroupChildrenTbl gg1, StructureTreeTbl outree, MembersTbl memb1, GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl WHERE memb2.personid = 440287252 AND memb2.groupid = gg2.gchildid AND gg2.groupid = sacl.groupid AND sacl.group_access = 100 AND sacl.structid = outree.orgid AND outree.orgchild = gg1.groupid AND gg1.gchildid = memb1.groupid AND memb1.personid = pers.id AND pers.user_type = 3 GROUP BY pers.id, pers.firstname ORDER BY pers.firstname; table typepossible_keys key key_len ref rows Extra memb2 ref PRIMARY,groupid,persgrp 4 const 9 Using where; Using index; Using temporary; Using filesort personid,persgrp gg2 ref PRIMARY,gchildid,groupidgchildid4 memb2.groupid 1 saclref PRIMARY,groupid,structid, groupid 4 gg2.groupid 1 Using where group_access outree ref PRIMARY,orgid, orgid 4 sacl.structid 7 orgchild,prim_orgtree_ix2 gg1 ref PRIMARY,gchildid,groupidgroupid 4 outree.orgchild 1 memb1 ref PRIMARY,groupid, groupid 4 gg1.gchildid6 personid,persgrp perseq_ref PRIMARY,usertypeIX PRIMARY 4 memb1.personid 1 Using where The explain for the fast database is exactly the same, but the rows number is now: 3,1,1,6,1,3,1 instead. Why do these queries run so differently? We have over 100 databases that run this query fast, all with different rowcounts in the tables. But in one database it runs slow... - Are Pedersen Development Team Leader Server Operations manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - Are Pedersen Development Team Leader Server Operations manager ([EMAIL PROTECTED]) Tel: +47 24 14 99 61 http://fronter.com Kongensgate 24 N-0153 OSLO, Norway Tel.: +47 24 14 99 99 Fax.: +47 24 14 99 98 - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld corruption
Hmm I'd check for hard disc problems (turn on SMART monitoring and look through your kernel logs), RAM problems (look at memtest) and rootkits. Regards, Chris On Sun, 2004-02-15 at 15:45, Juan E Suris wrote: I just installed 4.0.16 linux (x86, libc6) rpms on a fresh RH7.3 installation. I am experiencing a problem that when I reboot the machine (shutdown -r now), the mysqld binary gets corrupted and mysql will not start. Before reboot: $md5sum /usr/sbin/mysqld 9e80e423401d959a7c1ecdb87ba49988 /usr/sbin/mysqld after reboot: $ md5sum /usr/sbin/mysqld 81089d38ef2753c831e929c53f7a4b58 /usr/sbin/mysqld do I have a bad hard drive or I am missing something here? Any help would be greatly appreciated. Thanks, Juan E. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple concurrent transactions per connection
Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
