Re: innodb defragmentation question
the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of show table status and show innodb status for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | users_groups| InnoDB | Fixed | 0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic| 7 | 2340 | 16384 |NULL |16384 | 0 | 8 | NULL| NULL| NULL || InnoDB free: 8192 kB And the show innodb status: = 031231 9:31:03 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 TRANSACTIONS Trx id counter 0 7556158 Purge done for trx's n:o 0 2341232 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second -- -- BUFFER POOL AND MEMORY -- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s END OF INNODB MONITOR OUTPUT F. Heikki Tuuri wrote: Franky, since MySQL performs ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; wait some 70 sec here DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata files
innodb defragmentation question
since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: Hi all, if I try to defrag an InnoDB table (using alter table ... type=innodb;) I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb defragmentation question
Hi all, if I try to defrag an InnoDB table (using alter table ... type=innodb;) I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: innodb and fragmentation
Per Andreas Buer [EMAIL PROTECTED] wrote: Hello Heikki, Heikki Tuuri writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. and it seems to be what I asked for as well, thanks! Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump with innoDB
Daniel Kasak wrote: Paul DuBois wrote: If you have problems reloading the table due to the order in which the InnoDB tables appear in the dump files, add SET FOREIGN_KEY_CHECKS = 0; to the beginning of the file before reloading it. Our backups are quick large - over 500 MB. Opening the file and adding the above line at the top takes a lot of CPU time and memory - and when I'm restoring, I don't have a lot of time... Is there an easier way to get it there - can I 'cat' to the beginning of a file, or should I make my backup scripts cat the output of mysqldump to the end of a file with 'set foreign_key_check=0;' at the top? Maybe we could have a switch for mysqldump that does this for us? quick solution: put the line SET FOREIGN_KEY_CHECKS = 0; in a file (eg.header.txt) and then: cat header.txt mysql.dump mysqlgood.dump should take about 5 secs. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Round Robin Replication, Add a server; Renumber
Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 05:34:12PM -0500, Lewis Watson wrote: I currently have three mysql machines replication from A-B-C-A type fashion. I need to replace A. I am thinking that I could add D and have it pull from C. Then once A is removed restart D as A. Is this a good way to do this or is there a better way that I should do this? Can't you make D a slave of A? Then swap it for A when you're ready? Are you saying that D will detect the correct master_file pos when you change it's master from A to B then (since D was then a slvae of A and you replace A by D)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize tables and innodb
Hi all, for myisam tables we have optimize table that can be cronned to run at night, but is there something like this for the innodb table type as well? Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
some replication questions
Hi all, I'm trying to setup a failover mysql server, and for that I need replication. Now here's is how I would do it (3 servers, A and B are located next to each other, C is off site): A is master of B B is master of A B is master of C Now I don't think this will cause much problems, as the documentation describes circular replication is possible, so this should be possible too. To be sure not have collisions, the application only writes to A and will use B from the moment A is down (implementing this using a heartbeat trick with fake ip). As you can see, B is master of C, so changes go from A to B and from B to C. Now the questions come: 1) is it ok to put the following in each /etc/my.cnf file: [mysqld] log-bin server-id=unique number log-slave-updates other, non-replication, parameters 2) if B goes down, writes from A can't propagate to C. So on C, I would issue stop slave, change master to (with master uid/pass), start slave. But is this enough for C to be in sync with A then? Or does the change master to command also needs the master_log_file and master_log_pos parameters, as mentioned on http://www.mysql.com/doc/en/Replication_HOWTO.html ? 3) I need transactions, so I suppose I need innoDB type of tables/databases, correct? 4) if I create a table, do I need to specify type=innodb at the end? Or is this the default with 4.0.15? And if it is not the default, what should I put in my.cnf to make it so? 5) for logfile rotation/deletion (like the replication log, binary log, etc...), I would use the mysql-rotate-logs script (which does the sql command flush logs) and delete all logs more than 5 days old (if all slaves are up. Is this ok? tx for any responses already! Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
give me a sample to print out to printer
when i click the icon client1(in end client, it's show up from php application) that the icon has link to mysql and the result directly print the out put to printer (share or local printer) by coulomb. there's anyone knows about this, please help me. Rgds, Franky Aniversarius - 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
give me a sample to print out to printer
when i click the icon client1(in end client, it's show up from php application) that the icon has link to mysql and the result directly print the out put to printer (share or local printer) by coulomb. there's anyone knows about this, please help me. Rgds, Franky Aniversarius - 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
DBD::mysql
I have all the time the same error, I try different value but nothing is right! in the Perl Makefile.PL command i receive this message: Checking if your kit is complete... Looks good Note (probably harmless): No library found for -lz Using DBI 1.18 installed in /usr/local/lib/perl5/site_perl/5.6.1/i586-linux/auto/DBI Writing Makefile for DBD::mysql the problème is the libs?! but what can i do?! thanks! Franky [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
Best choice for index
What is the best choice for my index on this query? SELECT id_team, sum(IF(m.id_visitor = t.id_team,m.visitor_score, m.home_score)) AS But_pour, sum(IF(m.id_visitor != t.id_team,m.visitor_score, m.home_score)) AS But_contre FROM tab_teams t, tab_matchs m WHERE t.id_level =4 AND(m.id_visitor = t.id_team OR m.id_home = t.id_team) AND m.season = 2 GROUP BY id_team; --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- François Boucher [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
revoke privilege
I do: grant SELECT,alter,index,insert,update on SK_10.* to admin-ab-44 identified by ab-44; And : revoke all on SK_10.users_profiles from admin-ab-44; But the user cant always make a SELECT * from SK_10.users_profiles ; I want no acces to the table SK_10.users_profiles what can i do? --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- François Boucher [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
revoke privileges
Ok let me explain my problem: I do: grant SELECT,alter,index,insert,update on SK_10.* to admin-ab-44 identified by ab-44; -- So the use get all right... And : revoke all on SK_10.users_profiles from admin-ab-44; -- So now they have all right except in SK_10.users_profiles ... But the user cant always make a SELECT * from SK_10.users_profiles ; I want admin-ab-44 get access everywhere except to the table SK_10.users_profiles what can i do? --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- François Boucher [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
remove? revoke?
Can I remove the rigth for one user to see one specific table in database? for exemple: user: bob table: tab_secret database: Customer_service Bob can see any table in DB Customer_service except the table tab_secret. --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- François Boucher [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
conditional AND
Hi
I want to make query like this
Selectp.name
fromtab_person p, tab_list l
wherel.id_tab_list = 18
if (l.id_person != 0){
AND l.id_person = p.id_person
}
table person
id_person,name,tel
table list
id_list,id_person,title
how can i do?
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--
François Boucher
[EMAIL PROTECTED]
___
_ ( hello... )
Q _/\ __/
I
¸L
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--
-
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: conditional AND
I write this: Select p.name From tab_person p, tab_list l Where l.id_tab_list = 18 And l.id_person = 0 Or l.id_person = p.id_person i will receve all name in person table! --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- François Boucher [EMAIL PROTECTED] ___ _ ( hello... ) Q _/\ __/ I ¸L --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-- - 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
special select
Re: special select ( try 2)
Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query,table If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. You have written the following: C'est un message de format MIME en plusieurs parties. --=_NextPart_000_00A1_01C0F3F9.C56F8400 --=_NextPart_000_00A1_01C0F3F9.C56F8400-- - 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
query in different DB
Can I do query like this: Select Database1.table1.field1, database2.table1.field1, database2.table2.field2 FROM database1.table1, database2.table1, database2.table2 François Boucher [EMAIL PROTECTED]
