Locking issue with MyISAM on Solaris 10
Hi! Nowadays we've conducted something what I can call extended testing of MySQL on Solaris10/x86. The most annoying issue is a problem with locking queries to MyISAM-type tables. Mechanism seems to be simple to explain - long running query locks table for READ, next there is some DML query which must wait for this lock to be removed, and then every other query must also wait (even SELECTs, because it seems they wait for DML to complete), while wait times became completely unacceptable However this is the issue only on Solaris. The same application/database running on Linux has no such problems. The other thing is, that the same set of queries runs on Solaris much slower than on Linux - what at least partially explains no problems on Linux There is enabled disk-buffering, key buffer is even bigger than on Linux, we try mysql in versions 4 and 5, nonetheless on Solaris there is locking, on Linux there is not. Queries beeing executed make count(*) by full scan (due to hardly selective conditions (sex etc)) on table with ~1 rows and join to another table with ~100 rows. Any comments ? Best regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: removing ibdata1 if some/all tables are not InnoDB?
Heikki Tuuri wrote: Carl, InnoDB does purge deleted rows from the ibdata files. Certain PostgreSQL advocates have been spreading a claim that InnoDB would not do that, but the claim is false. Could You explain more about reusing space previously taken by deleted rows? Is this concept similar to Oracle's concepts in this regard ? Regards Remigusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Background tasks performed by MySQL?
Viktor Fougstedt wrote: Hi, and thank you both for valuable tips. The MySQLd in question runs on a mailserver, and a large amount of processes (Postfix, Maildrop and Squirrelmail) connect to it, run one or two simple queries, and then disconnects. There is only one client that is constantly connected, namely a configuration server. It does not have a cache, and only asks a few small questions every now and then. I have modified the code so that it logs any SQL-queries that take more than 2 clock seconds, which should show me if the config server is the culprit. I tried SHOW INNODB STATUS when MySQLd was taking 100% of one CPU, and the "Main thread state" was "sleeping". Also from the same command all TRANSACTIONs seemed idle except for one, which was running my SHOW INNODB STATUS command. I draw the conclusion that whatever is happening, it's not InnoDB. Since all the tables that the configuration server uses are in InnoDB, it also seems likely that the config server is not the culprit either. Is there any way for me to find out exactly what queries have been run in the last X minutes? When the load goes up, I could check to see what queries ran before, to possibly find a pattern. If I could temporarily log queries and the time they took to complete, that would also be a good way forward. The General Query log doesn't seem to log the time a query took (as I read the manual). I have a cron-job that logs the current machine load and a "SHOW STATUS" every five minutes. I just awk:ed through it, and I might imagine it, but there is a possible connection between the "Max_used_connections" parameter increasing and the machine's load going up. Could a massive storm of connections be causing the slowdown? Some form of lock contention having to do with new connections or similar? Can I reset the Max_used_connections parameter so I get a maxlevel for the last five minutes rather than since the last restart? Lots of questions, so I am deeply grateful for any insights into any of them, /Viktor... Hi! I'm not sure if You've got any answers (I don't read the list systematically), but for me this behaviour seems a bit like locking issue. A good way to look at it is looking at processlist (SHOW PROCESSLIST) - where processes have their status displayed - look for "Locked" status. I administer one db, which is based on MyISAM tables. Sometimes it experienced similar behaviour - first comes one time consuming query, after it some DML, then other queries - all must wait for the first query. In my opinion first query acquire READ lock, then DML wait for WRITE lock and may be if it waits, other queries can not be performed due to this wait for WRITE lock. The solution here could be redesigning of database by limiting DML operations or using LOW PRIORITY DML queries Any thoughts or comments? Regards, Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case study analysis
Hi! While ago we perform in our company short experiment in the framework of mysql tests. One of our databases is mysql-4.0.15 on i386 Linux. It is MyISAM-based. Traffic on it consists of mix of selects (quite big share of count aggregates) and dml operations with quite high share of the latter in the whole. We haven't had any bigger problems with this database. We tried to replace it with mysql-4.0.26 on x86_64 Solaris 10 on better hardware. So as You see - version upgrade, architeture and os change, also different hardware configuration. We didn't take any export-import approach - and just move all data/index files to the machine with new enviroment. As we moved, firstly the database server responded quite well. However after few minutes operations on some more intensively used tables slowed down to unacceptable level (from milisec. to hundreds of sec.). SHOW PROCESSLIST indicated, that those operations were "Locked". As a first step we tried to tune server (os and db server). We have changed query cache settings, key buffer settings and some specific Solaris parameters with no effect. During this time neither memory nor processor resources were exhausted. Then we switched from x86_64 to i386 version of mysql. This didn't solve the problem, too. And then we went back to i386 4.0.15 version, though for Solaris 2.8 (and not Linux). Firstly things seemed to be as usually, but after few minutes SHOW PROCESSLIST output changed and there were no more problems. DML operations didn't lock, counts didn't lock and explain operations didn't lock (all, what was the case in 4.0.26) Concluding - it seems that the main change driver was mysql version. I've googled few hours, but haven't found anything relevant (may be beside information, that MyISAM at high traffic rate has problems with DML, but we actually didn't switch to InnoDB, so this is not the case). Any comments on this issue? Thanks in advance Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too less place for whole info in innodb status
Gleb Paharenko wrote: Hello. Maybe the output of innodb_monitor won't be truncated. Create the innodb_monitor table and check if you see the full output in the .err log. See: http://dev.mysql.com/doc/mysql/en/innodb-monitor.html this helps - thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too less place for whole info in innodb status
Ady Wicaksono wrote: You should try *SHOW INNODB STATUS \G;* Remigiusz Soko?owski wrote: Hi! problem is not critical, however it would be nice to get some workaround from time to time I check innodb status - the most interesting sections, I believe, are: FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS unfortunately they are at the bottom of whole report and that is problem, because in one of databases I manage lock and transaction info is so long, that other information is not displayed at all. Any advice? TIA Remigiusz that is, what I actually do - problem is in the returned report - it has probably some limited size here shortcut of what I get: *** 1. row *** Status: = 050831 15:51:14 INNODB MONITOR OUTPUT = Per second averages calculated from the last 46 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 970202675, signal count 398405600 --Thread 4063170811 has waited at mem0pool.c line 334 for 0.00 seconds the semaphore: Mutex at 0x8508b0c created file mem0pool.c line 205, lock var 0 waiters flag 0 wait is ending Mutex spin waits 3608862448, rounds 1320533442, OS waits 1902410973 RW-shared spins 276597184, OS waits 84173756; RW-excl spins 633297018, OS waits 54606698 LATEST DETECTED DEADLOCK 050822 13:00:02 *** (1) TRANSACTION: TRANSACTION 0 1759896686, ACTIVE 0 sec, process no 15919, OS thread id 3200827462 starting index read mysql tables in use 4, locked 4 LOCK WAIT 866 lock struct(s), heap size 60736, undo log entries 270 MySQL thread id 235920701, query id 2425092039 front-1.rozrywka.srv 10.0.4.1 user_cron Sending data /* Generowanie tabelki powiazan utwor_artysta skupiajacej w sobie dane z kilku tabel, zawierajce zmiany utworow z ostatnich 20 minut. Wykorzystywane do szybszego wyciagania informacji o danym utworze w calym serwisie. */ REPLACE INTO utwor_artysta (arNazwa, arLicencja, arZnany, gaNazwa, utId, utNaz *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 44322 n bits 128 index `PRIMARY` of table `wp_mp3/utwor_artysta` trx id 0 1759896686 lock_mode X locks rec but not gap waiting Record lock, heap no 52 PHYSICAL RECORD: n_fields 59; 1-byte offs FALSE; info bits 0 0: len 4; hex 8421; asc!;; 1: len 6; hex 68e24004; asc h @ ;; 2: len 7; hex 031ff33c5a; asc 735562634f6445; asc sUbcOdE;; 4: len 1; hex 03; asc ;; 5: len 1; hex 02; asc ;; 6: len 6; hex 5472616e6365; asc Trance;; 7: len 30; hex 31277374206772616465202d206561726c79206f6c642062726f6b656e20; asc 1'st grade - early old broken ;...(truncated); 8: len 0; hex ; asc ;; 9: len 0; hex ; asc ;; 10: len 1; hex 01; asc ;; 11: len 1; hex 01; asc ;; 12: len 1; hex 01; asc ;; 13: len 1; hex 01; asc ;; 14: len 1; hex 02; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: len 0; hex ; asc ;; 26: len 0; hex ; asc ;; 27: len 0; hex ; asc ;; 28: len 0; hex ; asc ;; 29: len 0; hex ; asc ;; 30: len 5; hex 31333a3531; asc 13:51;; 31: len 2; hex 00a0; asc ;; 32: len 1; hex ff; asc ;; 33: len 1; hex 00; asc ;; 34: len 1; hex 03; asc ;; 35: len 1; hex 02; asc ;; 36: len 0; hex ; asc ;; 37: len 3; hex 803f79; asc ?y;; 38: len 3; hex 0002bc; asc;; 39: len 3; hex 000268; asc h;; 40: len 3; hex 00027d; asc };; 41: len 5; hex 2020312e30; asc 1.0;; 42: len 3; hex 01; asc;; 43: len 2; hex ; asc ;; 44: len 8; hex 80001230bb10f1d6; asc0;; 45: len 8; hex 8000123c722153d5; asclen 2; hex 010e; asc ;; 48: len 2; hex 4535; asc E5;; 49: len 2; hex ; asc ;; 50: len 2; hex ; asc ;; 51: len 2; hex 04c4; asc ;; 52: len 8; hex 8000; asc ;; 53: len 2; hex ; asc ;; 54: SQL NULL, size 8 ; 55: SQL NULL, size 8 ; 56: len 1; hex 01; asc ;; 57: len 3; hex 80; asc;; 58: len 3; hex 80; asc;; *** (2) TRANSACTION: TRANSACTION 0 1759896728, ACTIVE 0 sec, process no 15916, OS thread id 3200815171 fetching rows, thread declared inside InnoDB 466 mysql tables in use 2, locked 2 32 lock struct(s), heap size 2496 MySQL thread id 235920640, query id 2425092110 front-1.rozrywka.srv 10.0.4.1 mp3 Sorting result INSERT INTO temp_sg_najnowsze_utwory SELECT utId, utNazwa, arId, arNazwa, gaNazwa, gaId, utMP3, utKeszSciagniecia, DATE_FORMAT(utCzasDodania,"%Y-%m-%d") as utCzas FROM utwor_artysta WHERE utStatus="zaakceptowany" AND utPublikacja_SG = 1 ORDER BY utCzasDodania DESC, arNazwa LIMIT 14 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 44322
too less place for whole info in innodb status
Hi! problem is not critical, however it would be nice to get some workaround from time to time I check innodb status - the most interesting sections, I believe, are: FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS unfortunately they are at the bottom of whole report and that is problem, because in one of databases I manage lock and transaction info is so long, that other information is not displayed at all. Any advice? TIA Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Qcache - how it works?
let's say that we have the following settings: Variable_name: query_cache_limit Value: 64kB Variable_name: query_cache_size Value: 4MB and status: | Qcache_queries_in_cache| 1679 | | Qcache_inserts| 2242534| | Qcache_hits | 1058592| | Qcache_lowmem_prunes | 135641 | | Qcache_not_cached| 2558808| | Qcache_free_memory | 1717208| | Qcache_free_blocks | 485| | Qcache_total_blocks | 3885 | I wonder why Qcache_lowmem_prunes grows, even though Qcache_free_memory is almost 2MB. I thought that queries are thrown from cache only in situation, when: 1. there were some changes in data of that query 2. there is no memory for new query and Qcache_free_memory should be close to 0 to achieve the state when queries are thrown away. Anybody has an explaination for such behaviour? Thanks in advance Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisam parameters in innodb db
Gleb Paharenko wrote: Hello. Use skip-innodb, this should prevent MySQL from InnoDB initialization. Remigiusz Soko$owski <[EMAIL PROTECTED]> wrote: Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? I see I was not clear enough - I use InnoDB and don't want to disable it - thee point is all my databases except mysql are of innoDB type. In such circumstances the question is if default myISAM settings affect performance of database - are they only maximal values dynamically allocated or inflexible parameters (what would mean that every connection is net_buffer + thread_stack + read_buffer + any_other_buffer of which only some are used and some are loss of memory)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam parameters in innodb db
Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? Regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade from v. 4.0.15 to v.4.0.24 (InnoDB)
Hi! last night we try to upgrade from 4.0.15 to 4.0.24. We thought, that it is possible without dumping data. We upgrade only server (clients were still 4.0.15). In changelogs in versions higher than 4.0.15 there were no info about some incompatible changes and those bound with InnoDB were connected with engine. Unfortunately after restarting mysql daemon didn't see some tables, there were problems with recognition of innoDB format. I wonder if anybody has some experience with similar upgrades? Is it possible to upgrade only server? Or do we need also dump data and load again? Any success stories? Best regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logs: is there a possibility to stop/start them without stopping server
Hi! I wonder if there is any possibility to switch log off on running server? Usually I have here general logs disabled due to performance and space saving reasons - but of course sometimes it is needed to find out full info about queries executed. It'd be fine to have possibility to run server with log and stopping/starting logging on demand. TIA Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
matt ryan wrote: Tobias Asplund wrote: <>On Tue, 7 Sep 2004, matt ryan wrote: I forgot, did you have multiple slaves on multiple machines? If so, do they have identical hardware/drivers? Multiple slaves on same machine, one works fine Do You tried to distribute replication to other machines? Is it option to You? Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
matt ryan wrote: The table is 9 gig, and the index is 8 gig unfortunately the primary unique key is almost every column, if I were to make it one using concat, it would be huge. I tried making those fields a hash, but it did not work, I had duplicate hashes for non duplicate records!! Matt If I well understood, You have in Your index almost all data, You have in Your table? Why not add field for unique key (auto increment if You want less work)? It reduces size of Your primary index and thus speed up working with it! Best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
