Re: InnoDB tables using 90% cpu
Well I recompiled with Linuxthreads with the same result. I ran explain on both queries and the only difference is innodb looks at 31 rows where myisam looked at 23. Both are of type range. Is there anything else I can do before giving up on innodb? - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Sasha Pachev [EMAIL PROTECTED] Cc: Cliff [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:14 PM Subject: Re: InnoDB tables using 90% cpu Sasha Pachev wrote: Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. FreeBSD's thread library is a user-space setup. This can result in major performance issues when doing things that block. -- 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 tables using 90% cpu
Cliff, - Original Message - From: Cliff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, March 13, 2004 11:32 PM Subject: Re: InnoDB tables using 90% cpu Well I recompiled with Linuxthreads with the same result. I ran explain on both queries and the only difference is innodb looks at 31 rows where myisam looked at 23. Both are of type range. Is there anything else I can do before giving up on innodb? how much slower is the query with InnoDB? What are the absolute times for MyISAM and InnoDB? Can you post the query and the EXPLAIN? Since the CPU usage is 90 %, it is clearly CPU-bound. And CPU-bound queries should run in about the same time for InnoDB and MyISAM, if the EXPLAIN is the same. Also post the output of SHOW INNODB STATUS. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Sasha Pachev [EMAIL PROTECTED] Cc: Cliff [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:14 PM Subject: Re: InnoDB tables using 90% cpu Sasha Pachev wrote: Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. FreeBSD's thread library is a user-space setup. This can result in major performance issues when doing things that block. -- 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: InnoDB tables using 90% cpu
Hi Cliff, Either way for a production system I recommend using Linuxthreads with FreeBSD4 (also works on 5 but threads are much improved on 5). Please use the ports and make WITH_LINUXTHREADS=yes and others that use (see make pre-fetch in /usr/ports/databases/mysql-favorite-version) or http://jeremy.zawodny.com/blog/archives/000458.html for Jeremey's building hints. I would also examine your innodb configuration and buffer sizes and isolation level. These are separate from myisam. Best of luck, Ken - Original Message - From: Cliff [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:56 PM Subject: Re: InnoDB tables using 90% cpu The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 08, 2004 9:18 AM Subject: Re: InnoDB tables using 90% cpu Cliff wrote: Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and relatively more time to get the data). The question is - does the query actually take less time? If not, it could be because a certain optimization available with MyISAM is not available with InnoDB. Isolate the trouble query, and do an EXPLAIN. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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 tables using 90% cpu
The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 08, 2004 9:18 AM Subject: Re: InnoDB tables using 90% cpu Cliff wrote: Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and relatively more time to get the data). The question is - does the query actually take less time? If not, it could be because a certain optimization available with MyISAM is not available with InnoDB. Isolate the trouble query, and do an EXPLAIN. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
Sasha Pachev wrote: Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. FreeBSD's thread library is a user-space setup. This can result in major performance issues when doing things that block. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
Cliff wrote: Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and relatively more time to get the data). The question is - does the query actually take less time? If not, it could be because a certain optimization available with MyISAM is not available with InnoDB. Isolate the trouble query, and do an EXPLAIN. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb tables key problem ?
Irwin, DROP FOREIGN KEY was implemented after 4.1.0. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Irwin Boutboul [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, September 17, 2003 2:46 AM Subject: innodb tables key problem ? --=_alternative 0082893685256DA3_= Content-Type: text/plain; charset=US-ASCII I have created a new database with those tables. DROP TABLE IF EXISTS ACL_GROUPS; CREATE TABLE ACL_GROUPS ( groupid char(30) character set latin1 NOT NULL default '', creationtime datetime default '-00-00 00:00:00', active smallint(6) default '1', priority smallint(6) default '1', PRIMARY KEY (groupid) ) TYPE=InnoDB; -- -- Table structure for table 'ACL_GROUP_LIST' -- DROP TABLE IF EXISTS ACL_USERS; CREATE TABLE ACL_USERS ( groupid char(30) character set latin1 NOT NULL default '', userid char(30) character set latin1 NOT NULL default '', PRIMARY KEY (groupid,userid), FOREIGN KEY (`groupid`) REFERENCES `ACL_GROUPS` (`groupid`) ON DELETE CASCADE ) TYPE=InnoDB; (there is no data in the tables). Then when I try to drop a foreign key, I get this error: mysql alter table ACL_USERS drop foreign key groupid; ERROR 1005: Can't create table './irwingrid/#sql-7515_3.frm' (errno: 150) it is mysql 4.1. Is this a known issue ? If I convert the tables to MyIsam and then try to drop the key, it works fine. Irwin Boutboul Advanced Internet Technology 150 Kettletown Road, B2-N06 Southbury, CT 06488 Phone: 203-486-5614 --=_alternative 0082893685256DA3_=-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables and full text search
On Thu, 19 Dec 2002, Deepa wrote: While creating InnoDB tables, I was not able to specify a fulltext column in the table creation sql. Is this a bug or a limitation with InnoDB ? There could be quite a few users who need FULLTEXT feature with InnoDB. It's a feature :-( FULLTEXT has not yet been implemented for InnoDB tables. Thomas -- sql, query - 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: InnoDB tables and full text search
At 10:35 -0500 12/19/02, Deepa wrote: While creating InnoDB tables, I was not able to specify a fulltext column in the table creation sql. Is this a bug or a limitation with InnoDB ? There could be quite a few users who need FULLTEXT feature with InnoDB. It's like the manual says: FULLTEXT is only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html sql, query - 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: innodb tables backup
ok ... that's right for data (insert into ...) ... but i get the error before ... when mysql try to create child table: CREATE TABLE ... idParent BIGINT, INDEX idpar_ind (idParent), FOREIGN KEY (idParent) REFERENCES parent(id) .. when mysql parses the foreign key, parent table doesn't exist yet! bye and thanks. Natale Babbo --- Victoria Reznichenko [EMAIL PROTECTED] ha scritto: Natale, Thursday, October 24, 2002, 10:57:00 AM, you wrote: NB Anyone knows how to backup innodb tables in the right NB sql order? NB ... i mean ... to allow restoring correctly NB without foreign key constraint violation (if in the NB backup file ddl code for the child table is before ddl NB code for the parent table i get an error). You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Mio Yahoo!: personalizza Yahoo! come piace a te http://it.yahoo.com/mail_it/foot/?http://it.my.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: innodb tables backup
Natale, Friday, October 25, 2002, 10:08:00 AM, you wrote: NB ok ... that's right for data (insert into ...) ... but NB i get the error before ... when mysql try to create NB child table: NB CREATE TABLE ... idParent BIGINT, INDEX idpar_ind NB (idParent), FOREIGN KEY (idParent) REFERENCES NB parent(id) NB .. when mysql parses the foreign key, parent table NB doesn't exist yet! No, it's true not only for data. Put SET FOREIGN_KEY_CHECKS=0; in the dump file and then restore tables. It works fine for me. NB --- Victoria Reznichenko NB [EMAIL PROTECTED] ha scritto: NB Natale, Thursday, October 24, 2002, 10:57:00 AM, you wrote: NB Anyone knows how to backup innodb tables in the right NB sql order? NB ... i mean ... to allow restoring correctly NB without foreign key constraint violation (if in the NB backup file ddl code for the child table is before ddl NB code for the parent table i get an error). You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: innodb tables backup
i tried it ... but nothing to do! when mysql parse the foreign key in the create table i get the error. perhaps i'm wronging in restoring tables/db! i use this method: shell mysql -u user -ppassword dump file is it wrong? thanks. Natale Babbo --- Victoria Reznichenko [EMAIL PROTECTED] ha scritto: Natale, Friday, October 25, 2002, 10:08:00 AM, you wrote: NB ok ... that's right for data (insert into ...) ... but NB i get the error before ... when mysql try to create NB child table: NB CREATE TABLE ... idParent BIGINT, INDEX idpar_ind NB (idParent), FOREIGN KEY (idParent) REFERENCES NB parent(id) NB .. when mysql parses the foreign key, parent table NB doesn't exist yet! No, it's true not only for data. Put SET FOREIGN_KEY_CHECKS=0; in the dump file and then restore tables. It works fine for me. NB --- Victoria Reznichenko NB [EMAIL PROTECTED] ha scritto: NB Natale, Thursday, October 24, 2002, 10:57:00 AM, you wrote: NB Anyone knows how to backup innodb tables in the right NB sql order? NB ... i mean ... to allow restoring correctly NB without foreign key constraint violation (if in the NB backup file ddl code for the child table is before ddl NB code for the parent table i get an error). You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Mio Yahoo!: personalizza Yahoo! come piace a te http://it.yahoo.com/mail_it/foot/?http://it.my.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: re: innodb tables backup
Natale, Friday, October 25, 2002, 1:44:19 PM, you wrote: NB i tried it ... but nothing to do! NB when mysql parse the foreign key in the create table i NB get the error. NB perhaps i'm wronging in restoring tables/db! NB i use this method: shell mysql -u user -ppassword dump file NB is it wrong? What version of MySQL server do you use? I already wrote you that it works only since 3.23.52 and 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: innodb tables backup
At 12:44 +0200 10/25/02, Natale Babbo wrote: i tried it ... but nothing to do! when mysql parse the foreign key in the create table i get the error. perhaps i'm wronging in restoring tables/db! i use this method: shell mysql -u user -ppassword dump file is it wrong? Depends. - DId you add the SET FOREIGN_KEY_CHECKS = 0; statement to the dump file? - What version of MySQL do you have? If you added the statement, and you have a recent enough version of MySQL, then it will work not only for the INSERT statements in the dump file, but for the CREATE TABLE statements as well. thanks. Natale Babbo --- Victoria Reznichenko [EMAIL PROTECTED] ha scritto: Natale, Friday, October 25, 2002, 10:08:00 AM, you wrote: NB ok ... that's right for data (insert into ...) ... but NB i get the error before ... when mysql try to create NB child table: NB CREATE TABLE ... idParent BIGINT, INDEX idpar_ind NB (idParent), FOREIGN KEY (idParent) REFERENCES NB parent(id) NB .. when mysql parses the foreign key, parent table NB doesn't exist yet! No, it's true not only for data. Put SET FOREIGN_KEY_CHECKS=0; in the dump file and then restore tables. It works fine for me. NB --- Victoria Reznichenko NB [EMAIL PROTECTED] ha scritto: NB Natale, Thursday, October 24, 2002, 10:57:00 AM, you wrote: NB Anyone knows how to backup innodb tables in the right NB sql order? NB ... i mean ... to allow restoring correctly NB without foreign key constraint violation (if in the NB backup file ddl code for the child table is before ddl NB code for the parent table i get an error). You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 - 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: innodb tables backup
Natale, Thursday, October 24, 2002, 10:57:00 AM, you wrote: NB Anyone knows how to backup innodb tables in the right NB sql order? NB ... i mean ... to allow restoring correctly NB without foreign key constraint violation (if in the NB backup file ddl code for the child table is before ddl NB code for the parent table i get an error). You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Tables Backup
... can someone add InnoDB to the list of keywords?? SQL ... QUERY ... Victoria Reznichenko wrote: You can set up SET FOREIGN_KEY_CHECKS=0, in this case foreign key constraints will not be checked. It's supported since 3.23.52 and 4.0.3 Out of curiousity, are the foreign key constraints checked within a transaction, or during commit? You could (if they're checked at commit) wrap the restore with a BEGIN COMMIT block. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: innodb tables problem
Oren,
what MySQL version you are running? What OS?
Send me a copy of your my.cnf, the error log 'hostname'.err, and the exact
sequence of SQL statements you used to crash InnoDB, including the table
CREATE statements. Did InnoDB run out of tablespace?
Regarding the performance, did you set innodb_flush_log_at_trx_commit=0 when
you tried to use the script to pipe inserts to MySQL?
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, May 07, 2002 3:16 PM
Subject: innodb tables problem
I have defined some InnoDB tables and try to feed one of them
with 3,000,000 records.
Using a script piped to mysql.
First I tried to insert the rows one by one. It worked very slow. I didn't
wait to see if it ends.
Next try was to fill some temporary table with the numbers 1..3,000,000. I
was able to do it with Log(3,000,000) inserts.
select count(*) from TmpTbl; returns 3,000,000 successfully.
And now I used 'insert null,Num,concat('user',Num) into .. select Num from
TmpTbl'
(null is for a auto_increment primary key).
This fails, the mysqld crashed, restarts, trying to roll back the
transaction,
and I've been wating for a long time and the database is still not
responsive.
Do I need to set some parameters?
Do you know a better way to do this?
P.S.
I'm also using log-bin (so another mysqld will be the slave of this one).
Oren Zeev-Ben-Mordehai
Infrastructure Engineer
PhoneDo Networks
office: +972-9-951-7771 ext. 204
fax:+972-9-951-7772
email: [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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: innodb tables problem
Oren,
I did not find any clear crash from the error log. Looks like MySQL somehow
failed in shutdown:
...
020507 9:59:12
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: Forcing close
of thread 109 user: 'root'
A mysqld process already exists at Tue May 7 09:59:39 IDT 2002
...
Below we see that the big rollback actually finished successfully in 45
minutes:
020507 10:25:39 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 94594158
InnoDB: Doing recovery: scanned up to log sequence number 0 94659584
InnoDB: Doing recovery: scanned up to log sequence number 0 94725120
...
InnoDB: Rolling back trx with id 0 315126
020507 10:51:38 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
97 98 99
InnoDB: Apply batch completed
InnoDB: Rolling back of trx id 0 315126 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file offset 0 7074979, file name ./sun06-bin.024
020507 11:11:26 InnoDB: Flushing modified pages from the buffer pool...
020507 11:11:27 InnoDB: Started
Status information:
Current dir: /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/data/
Current locks:
key_cache status:
blocks used: 0
not flushed: 0
w_requests: 0
writes: 0
r_requests: 0
reads: 0
handler status:
read_key:0
read_next: 0
read_rnd 0
read_first: 0
write: 0
delete 0
update: 0
Table status:
Opened tables: 0
Open tables:0
Open files: 2
Open streams: 0
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: ready for
connections
020507 11:11:27 Error in accept: Software caused connection abort
...
Looks like the communication between the client and the server is somehow
blocked. You can try killing all mysqld and client processes, and restarting
mysqld.
To get good performance please set my.cnf options as recommended in section
2 of http://www.innodb.com/ibman.html.
Regards,
Heikki
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Sent: Tuesday, May 07, 2002 5:26 PM
Subject: RE: innodb tables problem
my.cnf
--
[mysqld]
innodb_data_file_path = ibdata1:2000M
log-bin
server-id=37
hostname.err
(Attached)
--
I didn't use 'set innodb_flush_log_at_trx_commit=0'. I'm going to try
this.
Table creation:
create table A (
A1 int(11) not null auto_increment,
A2 varchar(32) not null,
A3 int(11),
A4 varchar(32) not null,
primary key (A1),
index (A4),
unique index (A3,A4),
foreign key (A4) B (B4)
) type = InnoDB;
insertion:
printf insert into A select null,concat('user',Num+1),1,$base_number+Num
from TmpTbl;\n
Where TmpTbl contains the numbers 1..3,000,000
Regards,
Oren.
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 07, 2002 3:11 PM
To: Oren Zeev-Ben-Mordehai
Cc: [EMAIL PROTECTED]
Subject: Re: innodb tables problem
Oren,
what MySQL version you are running? What OS?
Send me a copy of your my.cnf, the error log 'hostname'.err, and the
exact
sequence of SQL statements you used to crash InnoDB, including the table
CREATE statements. Did InnoDB run out of tablespace?
Regarding the performance, did you set innodb_flush_log_at_trx_commit=0
when
you tried to use the script to pipe inserts to MySQL?
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, May 07, 2002 3:16 PM
Subject: innodb tables problem
I have defined some InnoDB tables and try to feed one of them
with 3,000,000 records.
Using a script piped to mysql.
First I tried to insert the rows one by one. It worked very slow. I
didn't
wait to see if it ends.
Next try was to fill some temporary table with the numbers 1..3,000,000.
I
was able to do it with Log(3,000,000) inserts.
select count(*) from TmpTbl; returns 3,000,000 successfully.
And now I used 'insert null,Num,concat('user',Num) into .. select Num
from
TmpTbl'
(null is for a auto_increment primary key).
This fails, the mysqld crashed, restarts, trying to roll back the
transaction,
and I've been wating for a long time and the database is still
Re: InnoDB tables
On Tue, Apr 09, 2002 at 10:17:56AM +0200, Luke van Blerk wrote: Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables That's a great sign for you. :-) but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Of course things will be slower with foreign keys. The question is how much slower. I suspect that the difference will be very, very minor. But why not run a benchmark with FKs and see? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 63 days, processed 1,719,288,043 queries (312/sec. avg) - 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: InnoDB tables
Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB tables
Hi Richard I'm writing a web application in PHP with a MySQL backend. I'm a novice at both PHP and MySQL having started with both about November last year and I'm also new to programming at large so please excuse any question which may seem obvious. The foreign keys are to be used to enforce referential integrety in the application -- e.g. If I try to delete a specific entry from the 'town' table it won't allow me to if there are entries in the 'advertiser' table which refer to that town. At the moment I'm using a table called 'advertisers_towns' which stores the ID of each entity and thereby the relationship is maintained. But if I can have the foreign keys operating this would cut out the need for such a table and all the extra coding work which goes with it. I hope that explains why I'd like to use foreign keys. What do you think? Regards Luke - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 2:11 PM Subject: Re: InnoDB tables Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - 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 - 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: InnoDB tables
FOREIGN KEYs by themselves make it faster and easier to validate that your code is correctly maintaining referrential integrity. They also let O/R mappers do more of the work in generating code for accessing your data (Blatant Plug: http://freshmeat.net/projects/easyorm/ -- EasyORM is an O/R mapper that introspects a MySQL DB and generates PHP code (eventually it'll also do Java (JDBC and EJB) and Perl.), and uses hints from an XML document you provide to build object-oriented code to access your schema. It's really crude right now -- the version in development is vastly more robust -- but we're using it successfully in our production environment.) FOREIGN KEYs combined with CASCADE ON DELETE/UPDATE (which InnoDB does not support yet) make it easier to write correct code: It manages changes/removals of dependant objects so you don't have to even think about it, let alone code it. -JF -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 5:11 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB tables Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - 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 - 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 InnoDb tables
Hi, does any1 know what is the earliest version of Mysql for Solaris, with support for InnoDB? If I am not mistaken, the version that we have is 3.23.32. Can MySql Max be added or some patch be added? This is not on a personal machine. It's on a server in my department (computer's science) and this is part of my project. thanks, Sanjay __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb tables lose foreign keys after creating an index...
Hi! -Original Message- From: j.random.programmer [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Saturday, March 02, 2002 5:21 AM Subject: Re: Innodb tables lose foreign keys after creating an index... --- Heikki Tuuri [EMAIL PROTECTED] wrote: Heikki: Is there a fix planned for the alter table/foreign key issue ? Full foreign key support is in the TODO list of MySQL. For InnoDB, the top priority now is to get the non-free hot backup tool of InnoDB to beta testing. You can speed up adding of individual features to MySQL by buying a support contract. Best regards, [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB - 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: Innodb tables lose foreign keys after creating an index...
Rick, sorry, it is not mentioned in the manual that MySQL performs a CREATE INDEX by doing an ALTER TABLE. And ALTER TABLE has the feature (= documented bug) that it removes foreign key definitions. You should define all your indexes within the table create statement, like in: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Rick Flower [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Friday, March 01, 2002 2:32 AM Subject: Innodb tables lose foreign keys after creating an index... Hi all.. Are any of you aware of a way to get indexes to work at all with Innodb tables containing foreign keys? I'm finding that after doing a create index on a table which *had* foreign keys, after the create, the foreign keys are gone.. I've included a simple test below which shows off the problem quite well.. I've searched around on Google to see if anyone had run into this problem, but didn't find any reference.. This really makes foreign keys worthless in MySQL.. The more I dig into MySQL, the less I like it due to missing features or wierd side effects of existing ones.. Perhaps someone can shed some light on what I may be doing wrong.. By the way, I'm using MySQL 3.23.47 on a Solaris box -- if it matters.. To reproduce the problem: 1) create table test_fk_parent(id int not null, primary key (id)) type=innodb; 2) create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; 3) create index CHILD_KEY on test_fk_child (id); Below is the sample output : mysql create table test_fk_parent(id int not null, primary key (id)) type=innodb; Query OK, 0 rows affected (0.04 sec) mysql create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; Query OK, 0 rows affected (0.05 sec) mysql show table status; ++++--++--- --+-+--+---++-- ---+-+++--- --+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++--- --+-+--+---++-- ---+-+++--- --+ | test_fk_child | InnoDB | Fixed |0 | 0 | 16384 |NULL |32768 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB; (parent_id) REFER vista/test_fk_parent(id) | | test_fk_parent | InnoDB | Fixed |0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB | ++++--++--- --+-+--+---++-- ---+-+++--- --+ 2 rows in set (0.01 sec) [[[ NOTICE THE EXISTING FOREIGN KEY ABOVE ]]] mysql create index CHILD_KEY on test_fk_child (id); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show table status; ++++--++--- --+-+--+---++-- ---+-+++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++--- --+-+--+---++-- ---+-+++-+ | test_fk_child | InnoDB | Fixed |0 | 0 | 16384 |NULL |49152 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB | | test_fk_parent | InnoDB | Fixed |
Re: Innodb tables lose foreign keys after creating an index...
--- Heikki Tuuri [EMAIL PROTECTED] wrote: Rick, sorry, it is not mentioned in the manual that MySQL performs a CREATE INDEX by doing an ALTER TABLE. And ALTER TABLE has the feature (= documented bug) that it removes foreign key definitions. Heikki: Is there a fix planned for the alter table/foreign key issue ? Best regards, [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Tables/Databases
Deryck, the latest information on InnoDB you can always find at http://www.innodb.com Regards, Heikki Innobase Oy Here is two thing for you. First, GREAT source for search of pass problem on any thing really. http://marc.theaimsgroup.com/ Then, information on the InnoDB. Not hard to find really. http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#I nnoDB Have a good reading. Daniel OK, I will soon have a huge database, home to about 500,000,000+ records and believe that MySQL needs to be faster. I have heard good things about InnoDB but Ihave no idea what it is. Can someone please tell me (or give a url telling) what it is, the benifits, how I can set it up on an already existing database, and what it can do for me. Thank you. Also, if anyone knows a very good search query for this that would be good for a high-end search engine (google, yahoo, etc), please let me know. Thank You. - 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: InnoDB Tables/Databases
DO Here is two thing for you. DO First, GREAT source for search of pass problem on any thing really. DO http://marc.theaimsgroup.com/ DO Then, information on the InnoDB. Not hard to find really. DO http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#I DO nnoDB I would go directly to http://www.innobase.fi/ DO Have a good reading. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB Tables/Databases
Thanks you for the URL. (: I just added to my collections. Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ilya Martynov Sent: Friday, October 05, 2001 04:06 To: Daniel Ouellet Cc: Deryck Henson; MySQL Subject: Re: InnoDB Tables/Databases DO Here is two thing for you. DO First, GREAT source for search of pass problem on any thing really. DO http://marc.theaimsgroup.com/ DO Then, information on the InnoDB. Not hard to find really. DO http://www.mysql.com/documentation/mysql/bychapter/manual_Table_ty pes.html#I DO nnoDB I would go directly to http://www.innobase.fi/ DO Have a good reading. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/) | | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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
Re: InnoDB Tables/Databases
Deryck, others: I'm doing some reading on InnoDB and am pretty ignorant on the subjets. Can someone explain why would InnoDB be faster than MySQL? From what I read it looks like it will have more overhead to support the transactions and that should decrease the performance rather than improve it. I think I read something to that effect in one of the manuals (but I don't rememeber which one). Could someone shed some light on this please thanks AOD OK, I will soon have a huge database, home to about 500,000,000+ records and believe that MySQL needs to be faster. I have heard good things about InnoDB but Ihave no idea what it is. Can someone please tell me (or give a url telling) what it is, the benifits, how I can set it up on an already existing database, and what it can do for me. Thank you. Also, if anyone knows a very good search query for this that would be good for a high-end search engine (google, yahoo, etc), please let me know. Thank You. __ Do You Yahoo!? NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 - 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: InnoDB Tables/Databases
Hi. Heikki, please correct me, if I say something stupid. ;-) On Fri, Oct 05, 2001 at 10:07:04AM -0700, [EMAIL PROTECTED] wrote: Deryck, others: I'm doing some reading on InnoDB and am pretty ignorant on the subjets. Can someone explain why would InnoDB be faster than MySQL? Please note that InnoDB also belongs to MySQL. What you probably mean is the MYISAM table type. InnoDB is just another table type. From what I read it looks like it will have more overhead to support the transactions and that should decrease the performance rather than improve it. If you use transactions, InnoDB will be a lot slower than MYISAM tables. InnoDB seems to be faster, if you disable commits (or at least the disk flushes connected with that). I think, one of the main reasons that it can be faster than MYISAM tables is that it preallocates disk space. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Tables/Databases
On Fri, 2001-10-05 at 23:59, Benjamin Pflugmann wrote: Hi. Heikki, please correct me, if I say something stupid. ;-) On Fri, Oct 05, 2001 at 10:07:04AM -0700, [EMAIL PROTECTED] wrote: Deryck, others: I'm doing some reading on InnoDB and am pretty ignorant on the subjets. Can someone explain why would InnoDB be faster than MySQL? Please note that InnoDB also belongs to MySQL. What you probably mean is the MYISAM table type. InnoDB is just another table type. Well, table handler InnoDB is part of software MySQL. To be politically correct I have to say that company Innobase OY doesn't belong to MySQL AB :). InnoDB is by nature totally independent project which just contributing to MySQL. If you use transactions, InnoDB will be a lot slower than MYISAM tables. InnoDB seems to be faster, if you disable commits (or at least the disk flushes connected with that). I think, one of the main reasons that it can be faster than MYISAM tables is that it preallocates disk space. InnoDB is faster on write concurrency! This is the difference. Much smarter row-level locking. This gives the speed. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Tables/Databases
The original query is as follows: SELECT * FROM websites WHERE category LIKE '%search%' OR keywords LIKE '%search%' OR description LIKE '%search%' The search instances are from an ASP page. 'search' is a variable that is asigned the value of the query string 'search' dim search search = request.querystring(search) Thanx. I'm hoping to test this on my database soon (the InnoDB) ** - Deryck Henson - http://www.comp-u-exchange.com ** - Original Message - From: Deryck Henson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Monday, October 04, 2010 12:23 PM Subject: InnoDB Tables/Databases OK, I will soon have a huge database, home to about 500,000,000+ records and believe that MySQL needs to be faster. I have heard good things about InnoDB but Ihave no idea what it is. Can someone please tell me (or give a url telling) what it is, the benifits, how I can set it up on an already existing database, and what it can do for me. Thank you. Also, if anyone knows a very good search query for this that would be good for a high-end search engine (google, yahoo, etc), please let me know. Thank You. ** - Deryck Henson - http://www.comp-u-exchange.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
RE: InnoDB Tables/Databases
Here is two thing for you. First, GREAT source for search of pass problem on any thing really. http://marc.theaimsgroup.com/ Then, information on the InnoDB. Not hard to find really. http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#I nnoDB Have a good reading. Daniel OK, I will soon have a huge database, home to about 500,000,000+ records and believe that MySQL needs to be faster. I have heard good things about InnoDB but Ihave no idea what it is. Can someone please tell me (or give a url telling) what it is, the benifits, how I can set it up on an already existing database, and what it can do for me. Thank you. Also, if anyone knows a very good search query for this that would be good for a high-end search engine (google, yahoo, etc), please let me know. Thank You. - 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: InnoDB Tables/Databases
I love this one! (: That put a smile on a long night! Thanks! Daniel The original query is as follows: SELECT * FROM websites WHERE category LIKE '%search%' OR keywords LIKE '%search%' OR description LIKE '%search%' The search instances are from an ASP page. 'search' is a variable that is asigned the value of the query string 'search' dim search search = request.querystring(search) Thanx. I'm hoping to test this on my database soon (the InnoDB) ** - Deryck Henson - http://www.comp-u-exchange.com ** - Original Message - From: Deryck Henson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Monday, October 04, 2010 12:23 PM Subject: InnoDB Tables/Databases OK, I will soon have a huge database, home to about 500,000,000+ records and believe that MySQL needs to be faster. I have heard good things about InnoDB but Ihave no idea what it is. Can someone please tell me (or give a url telling) what it is, the benifits, how I can set it up on an already existing database, and what it can do for me. Thank you. Also, if anyone knows a very good search query for this that would be good for a high-end search engine (google, yahoo, etc), please let me know. Thank You. ** - Deryck Henson - http://www.comp-u-exchange.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 - 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: InnoDB tables : difficulties with COUNT(*), DELETE etc ...
Alex, slowness of count(*) is a well-known problem. I will fix it some time this fall. Not very easy, because of multiversioning and recovery. The other problem you have is that a delete operation ends up in a deadlock (I think it should not be called a 'crash'). I will write in October a selective deadlock resolution algorithm to InnoDB where a big transaction is not chosen as the victim in a deadlock situation. Unfortunately EXPLAIN does not work on a DELETE, and you cannot ask how MySQL does the delete. You should download the version 3.23.42 and do mysqlcreate table innodb_lock_monitor (a int) type = innodb; so that we would see what locks and lock waits happen during the delete. That could give us a clue how to fix the delete inefficiency. Regards, Heikki http://www.innodb.com At 04:17 PM 9/10/01 +0200, you wrote: Bonjour, I have a table created with : CREATE TABLE mybuffer_NAS_D ( id int(10) unsigned NOT NULL auto_increment, service varchar(10) NOT NULL default '', date datetime NOT NULL default '-00-00 00:00:00', data text, PRIMARY KEY (id), KEY id_date (date) ) TYPE=InnoDB; There are many rows in it. And InnoDB take a VERY long time to do count(*) :( mysql select count(*) from mybuffer_NAS_D; +--+ | count(*) | +--+ | 4611891 | +--+ 1 row in set (1 min 34.71 sec) If I select 100 first rows ordered by 'date asc', I see that there are less than 100 lines with date'2001-08-16 00:00:00'. mysql select * from mybuffer_NAS_D order by date asc limit 100; +--+-+-+--- ---+ | id | service | date| data | +--+-+-+--- ---+ | 51155706 | live| 2001-07-02 17:58:08 | TPEEIX?019.95?10?20?30?40?612001/7/2?7117:58:08?J0?K0.0 | | 51196330 | live| 2001-07-02 17:58:08 | TPEEIX?019.95?10?20?30?40?612001/7/2?7117:58:08?J0?K0.0 | ... cut ... | 51192850 | live| 2001-08-15 14:30:18 | TISMT?015.19?10?20?30?40?612001/8/15?71?J5.19?K-5| | 51192851 | live| 2001-08-15 14:30:18 | TISMTW?010.74?10?20?30?40?612001/8/15?71?J0.74?K-5 | | 51194111 | live| 2001-08-15 14:30:20 | TMAII?013.2?10?20?30?40?612001/8/15?71?J3.2?K-5 | | 51201163 | live| 2001-08-15 14:30:33 | TUSOLW?010.1?10?20?30?40?612001/8/15?71?J0.1?K0.0| | 51201418 | live| 2001-08-15 14:30:33 | TVFND?010.11?10?20?30?40?612001/8/15?71?J0.11?K-5| | 51203036 | live| 2001-08-15 14:30:37 | TZSEV?015.52?10?20?30?40?612001/8/15?71?J5.52?K0.0 | | 51145454 | live| 2001-08-16 02:00:22 | TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.0 | | 51152124 | live| 2001-08-16 02:00:32 | TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.0| | 51154024 | live| 2001-08-16 02:00:35 | TNESC?011.35?10?20?30?40?612001/8/16?71?J1.35?K0.0 | | 51162377 | live| 2001-08-16 02:00:48 | TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.0 | | 51186600 | live| 2001-08-16 14:30:08 | TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.0 | | 51194263 | live| 2001-08-16 14:30:24 | TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.0| | 51203050 | live| 2001-08-16 14:30:37 | TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.0 | | 51143821 | live| 2001-08-20 02:00:18 | TADSTW?010.12?10?20?30?40?612001/8/20?71?J0.12?K0.0 | | 51157301 | live| 2001-08-20 02:00:40 | TRLCOW?010.16?10?20?30?40?612001/8/20?71?J0.16?K0.0 | | 51159119 | live| 2001-08-20 02:00:42 | TSSLI?013.2?10?20?30?40?612001/8/20?71?J3.2?K0.0 | +--+-+-+--- ---+ 100 rows in set (0.42 sec) But if I ask Mysql to delete these rows, it crashes after a few minutes : mysql delete from mybuffer_NAS_D where date'2001-08-16 00:00:00'; ERROR 1030: Got error 100 from table handler I think I already read a lot about these limitations. But this is really a problem for me now :( Any improvement expected ? Regards, Alex. - 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
