RE: Connection speed
I think my mysql is resolving host names, and I would prefer it to not, where do I set skip-name-resolve to prevent it? Thanks. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 8:43 AM To: Daniel Kiss Cc: [EMAIL PROTECTED] Subject: Re: Connection speed On Thu, Nov 06, 2003 at 02:37:05PM +, Daniel Kiss wrote: It is definitely not a DNS problem. I use IP addresses. Any other idea? Reverse DNS. The server is lookup up the client's name unless you've used skip-name-resolve. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,000,684,535 queries (431/sec. avg) -- 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 flush holdup
Is there anything that can be done to increase the speed of a flush? Can it be run while other queries are running? The problem I'm having is that every time a flush is waiting to happen no new connections can be initiated and all my queries are on hold. If there is something long running before it everything comes to a halt for a good 30-40 seconds. I can't make these long queries any faster. What can I do? Thanks, Nihal -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:09 PM To: Nihal; [EMAIL PROTECTED] Subject: Re: INNODB flush holdup Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNODB flush holdup
I am running FLUSH TABLES manually. I am doing it each hour to generate a bin file, for incremental backups. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 10:05 AM To: Nihal Cc: [EMAIL PROTECTED] Subject: Re: INNODB flush holdup On Thu, Nov 06, 2003 at 09:40:41AM -0700, Nihal wrote: Is there anything that can be done to increase the speed of a flush? Can it be run while other queries are running? Are you actually running the FLUSH TABLES command? If so, why? The problem I'm having is that every time a flush is waiting to happen no new connections can be initiated and all my queries are on hold. If there is something long running before it everything comes to a halt for a good 30-40 seconds. I can't make these long queries any faster. What can I do? Increase disk I/O capacity or add some caching on the disk controller. Jeremy -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:09 PM To: Nihal; [EMAIL PROTECTED] Subject: Re: INNODB flush holdup Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,004,112,227 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
switch from gemini to innodb bottlnecks
I've recently switched from GEMINI to INNODB, to be able to get continued support and upgrades of MySQL. I'm running into to some major performance issues. The section in the MySQL manual is not very thorough on optimizing INNODB. I'm getting two problems, either all my queries pile up, or a 'show processlist' shows 50 sleeping processes that should be hard at work, either way everything comes to a crawl. The database server is a dedicated 2CPU pIII 2.6Ghz, 2GB RAM, with a 14 hardrive (10k) Raid5 array. Pretty much every query uses indexed fields, so things should be fast, I get the impression that there is a locking issue going on, but as I understood it, INNDOB is supposed to do row level locking? I haven't had this problem with GEMINI in the past. Can anyone give me some improvement suggestions? = 031105 10:51:32 INNODB MONITOR OUTPUT = Per second averages calculated from the last 2 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 3528797, signal count 2864755 --Thread 1105938 has waited at btr0cur.c line 390 for 1.00 seconds the semaphore: S-lock on RW-latch at 92b43f74 created in file buf0buf.c line 444 a writer (thread id 1105938) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1134643 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92fa9b84 created in file buf0buf.c line 444 a writer (thread id 167960) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1069100 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1294399 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1282108 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 922d78f4 created in file buf0buf.c line 444 a writer (thread id 1282108) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0sea.c line 753 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 96547444, rounds 309622046, OS waits 1589531 RW-shared spins 3471561, OS waits 1434935; RW-excl spins 1022533, OS waits 145621 TRANSACTIONS *** LOTS OF STUFF HERE *** 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: doing file i/o (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 192, 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 3135978 OS file reads, 80521 OS file writes, 41212 OS fsyncs 4 pending preads, 0 pending pwrites 223.89 reads/s, 72228 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 31, free list len 29, seg size 61, 110608 inserts, 100175 merged recs, 14569 merges Hash table size 4980539, used cells 665918, node heap has 702 buffer(s) 7813.59 hash searches/s, 3283.86 non-hash searches/s --- LOG --- Log sequence number 18 429778985 Log flushed up to 18 429778985 Last checkpoint at 18 429608118 0 pending log writes, 0 pending chkp writes 22237 log i/o's done, 2.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1470181283; in additional pool allocated 9935488 Buffer pool size 76800 Free buffers 0 Database pages 76084 Modified db pages 168 Pending reads 131 Pending writes: LRU 0, flush list 0, single page 0 Pages read 16098598, created 12247, written 85799 1923.08 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 977 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 48 queries in queue Main thread process no. 1225, id 28680, state: sleeping Number of rows inserted 292454, updated 132038, deleted 139429, read 359279518 0.50 inserts/s, 0.50 updates/s, 1.00 deletes/s, 12631.68 reads/s END OF INNODB MONITOR OUTPUT my.cnf #
INNODB flush holdup
OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GEMINI
Can anyone tell me what happened to GEMINI? We've been customers of MySQL for a while and about two years ago started using Nusphere's version to take advantage of their row level locking/ACID transaction safe table type GEMINI. Things went well for a while but one day I came back for help and poof, Nusphere has moved all support to Russia, then a couple weeks later no more support exists. I understand this was due to a violation on their part of the GPL. So here I am stuck with 80GB's of GEMINI data and an aging version of MySQL prone to crash every couple of months. Does anyone know, are the GEMINI developers from Nusphere somewhere else legally continuing this development? If not can someone give me some migration suggestions? I've looked at InnoDB some, but am worried, will it provide equal performance to GEMINI? Also to migrate this I have found one copy of MySQL, version 4.00beta, that has support for both GEMINI and INNODB, will using this version cause problems for me? Thanks, Nihal
