Re: MyISAM vs InnoDB ----- Incorrect key file for table error
Praveen KS [EMAIL PROTECTED] writes: In a table of 20,000 records I am frequented with this error: Error 1034: Incorrect key file for table: ''; try to repair it Frequency of this error: Three or four times a week. I am logging the data it was trying to insert or update. After I, repair table tablename if, I try to insert the same data.. its accepts without getting corrupted. Your tables are getting corrupted. You might have a software of hardware error. Since you are running an ALPHA release my guess is that you have a software problem. Upgrade mysql to a production release and see if that takes care of your problem. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb crashes during heavy usage with exceeded memory error
pointer thd-thread_id=25038128 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Memory status: Non-mmapped space allocated from system: 31660016 Number of free chunks: 236 Number of fastbin blocks:5 Number of mmapped regions: 22 Space in mmapped regions:1275957248 Maximum total allocated space: 0 Space available in freed fastbin blocks: 168 Total allocated space: 25461120 Total free space:6198896 Top-most, releasable space: 16552 Estimated memory (with thread stack):1319610352 Number of processes running now: 0 050704 18:38:23 mysqld restarted 050704 18:38:23 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 050704 18:38:23 [ERROR] Do you already have another mysqld server running on port: 3306 ? 050704 18:38:23 [ERROR] Aborting 050704 18:38:23 [Note] /usr/libexec/mysqld: Shutdown complete 050704 18:38:23 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb crash on failed read disk
Ady Wicaksono [EMAIL PROTECTED] writes: Dear All I use RedHat 9 with 2,5 Gbyte RAM, Intel(R) Xeon(TM) CPU 2.80GHz (Hyperthread), filesystem ext3 standar linux journaling filesystem. Today my DB is crash :(, here is the log. Could it be that our stacks and your heaps might have mixed? If your threads allocate to much memory and you have a lot of them this might corrupt your database. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1 cluster
Michael Gale [EMAIL PROTECTED] writes: Anyone running mysql 4.1 with cluster support ? Try the Mysql cluster list. Is it sort of production ready ? The 4.1 release is labeled BETA. See http://en.wikipedia.org/wiki/Development_stage -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatically optimizing a table - how should I so this?
Joshua Beall [EMAIL PROTECTED] writes: Daniel Kasak [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is there any particular reason why you think the table will need optimizing, or do you just want everything to be super-optimized? Because when I pull up phpMyAdmin, and it says there is 3,768 bytes of overhead, I just feel *dirty*! Overhead, after all, is a Very Bad Thing! Yeah. And doing a full table optimization after updating one single row does not at all add any overhead. Not at all. :P -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query status
Filip Rachunek [EMAIL PROTECTED] writes: Hello, can somebody tell me what the status Writing to net returned by show processlist exactly mean? I've tried to find it in MySQL documentation but either it's not there or I missed it. I am asking because this status is usually shown when my J2EE application [using Connector/J 3.0.9] starts to eat 99% of CPU time and then gets jammed. Are you sure you don't have any errors in your SQL? Maybe your query returns a karthesian product - and this might kill your J2EE application. Check out your slow-log - the query is probably there. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Jon Hancock [EMAIL PROTECTED] writes: When you issue this null ALTER TABLE, is the entire table locked during the build? The table is read-only during the build. i.e. Is the only way to defragment to effectively take the table offline during the rebuild? Well. Not quite offline, but almost. Is there a method to estimate time to do this rebuild? I usually go with rows * 1/5000 seconds. But it varies with hardware and table complexity. My tables are not very complex. thanks, Jon - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:17 PM Subject: Re: innodb and fragmentation Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] 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. -- Per Andreas Buer -- 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] -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] 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. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb and fragmentation
Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
Jackson, Jackson Miller [EMAIL PROTECTED] writes: I have 4 SCSI drives currently. Well, is you want Redundancy you don't have a choice. Mirror them. 2x 2 drives. You might want to put OS and write-ahead-log on one and InnoDB/MyISAM-data on the other. I would like to have 1 drive run the OS, 1 drive to be the MySQL data directory and 1 drive to be InnoDB (possibly raw partition). Why do you want to use both backends? MyISAM and InnoDB have their own index-cache (key_buffer and innodb_buffer_pool), so you might be better off with just one of them. What is the best way for me to configure RAID? Here is the kind of load I am talking about: Uptime: 1749850 Threads: 44 Questions: 1266402021 Slow queries: 16923 Opens: 162177 Flush tables: 1 Open tables: 64 Queries per second avg: 723.720 These figures are useless. 723q/s is nothing if the layout is simple or the dataset is small or if these are only selects. I've seen quite old servers do 7000q/s with little or no tuning. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
[EMAIL PROTECTED] (Lefevre, Steven) writes: I say go with RAID 5, on a controller card. .. You get better performance than mirroring or regular drive, because the data is spread out over your drives. It's not as good as disk striping, though. Ehh. Wrong. That is not how it works. If you have RAID5 with 4 disks, as we have here, one single write() will have the following effect. 1. The controller will have to read the whole stripe off the array. 3 reads from 3 diffrent discs. 2. Calculate the new checksum for the stripe. 3. Write the modified block back to the disk where it was changed 4. Updated the checksum This works Ok for multimedia and file storage, where you write()-call might be the size of a stripe or bigger. Then you can skip phase 1) on the list above. Ask any DBA; they will all tell you to never_ use RAID 5 for databases with dynamic content. Just don't. As for performance, seek times tend to be higher on a RAID5 array then on a mirror. The only thing which is good with RAID5 is read througput - which might be important for full table scans, but not much else. So, all in all, RAID 5 gives fault tolerance and better performance. Why do you think people use RAID1? You can have the OS do the RAID, but that puts a lot of burden on the processor and OS. CPU is almost never an issue anymore - not for database servers, anyway. The increase in CPU-usage is seldom noticable. I've seen software raid (on Linux 2.4) outrun $2000+ RAID-cards. CPUs are many times faster than the puny i960 or Strongarm CPU which are put on RAID controllers. I recommend getting a RAID card, and not a cheap one, either. Plan on spending ~$500. If you get one. Get one with a battery and write-back cache. They will give you kick-ass performance for those pesky fsync's. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone had a chance to try an Opteron yet?
Greetings, Lenz Grimmer [EMAIL PROTECTED] writes: On Tue, 24 Jun 2003, David Griffiths wrote: A 64-bit CPU won't have the 4-gig memory limit that a 32-bit processor will; even worse, Linux is apparently limited to about a 2-gig process. It depends - there is a BIGMEM patch from Andrea Arcangeli that raised that limit to 3.5 GB on 32bit systems. I think the patch is in the mainline kernel as well by now. There is still a problem with malloc. Malloc takes a unsigned int but the msb it discarded to safeguard against problems with signing. So, with Arcangelis kernel-patch mysqld can safely grow up to 3.5GB but no single buffer can grow beyound 2GB - as each buffer is allocated in one go (at least innodb_buffer_pool - which is the one I care about). I have not tried to remove limittation this due to lack of time and a proper test rig. Maybe someone at MySQL might give it a go? You probably have better torture chambers too. ;) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not allowed to connect to this mysql server
[EMAIL PROTECTED] writes: Hi I am using a .Net project to connect to a mysql server. I have a winXP (IIS installed) machine as a network client to our mysql server hold on a linux machine. I have mydobc installed. I made a connection in .Net. But when I try to connect to mysql I get an error message saying: 'My ip' is not allowed to connect to this mysql server What can I do? Check out this list: http://www.mysql.com/doc/en/Access_denied.html Most likely the answer is: If you get the following error when you try to connect from a different host than the one on which the MySQL server is running, then there is no row in the user table that matches that host: Host ... is not allowed to connect to this MySQL server You can fix this by using the command-line tool mysql (on the server host!) to add a row to the user, db, or host table for the user/hostname combination from which you are trying to connect and then execute mysqladmin flush-privileges. If you are not running MySQL Version 3.22 and you don't know the IP number or hostname of the machine from which you are connecting, you should put an entry with '%' as the Host column value in the user table and restart mysqld with the --log option on the server machine. After trying to connect from the client machine, the information in the MySQL log will indicate how you really did connect. (Then replace the '%' in the user table entry with the actual hostname that shows up in the log. Otherwise, you'll have a system that is insecure.) Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using. In this case you should either upgrade your OS/glibc or download the source MySQL version and compile this yourself. A source RPM is normally trivial to compile and install, so this isn't a big problem. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql4 query cache pruning unnecessarily?
LS [EMAIL PROTECTED] writes: Hi- Can anyone tell me why mysql4 would prune queries from the cache if the Qcache_free_memory is still very big? I'm confused why we get so many Qcache_lowmem_prunes: Mysql will prune queries if the tables are updated, maybe this is what you are experiencing? -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL for our production reporting data warehouse
Sonia Ghadially [EMAIL PROTECTED] writes: Dear Sir/Madam: My company is thinking of using MySQL for our production reporting data warehouse. Could you please tell me: 1) What is the latest release of MySQL, and what is the latest stable release of MySQL? Go to http://www.mysql.com/downloads/index.html. You will always find an updated answer here. 2) On the MySQL website, what is the difference between the following (releases?) http://www.mysql.com/doc/en/News.html MySQL 3.23.39 debug (what does debug mean?) From WordNet (r) 1.7.1 (July 2002) [wn]: debug v : locate and correct errors in a computer program code; debug this program The debug versions of mysql have information which makes debugging easier. MySQL 3.22.29 debug MySQL 4.0.6 gamma (what does gamma mean?) almost stable MySQL 4.1.0 alpha standard (what does alpha standard mean?) An alpha release is the first release that might actually work and be useful in some way (development, etc). Alpha releases are very seldom stable, though. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
malloc'ing 2GB+ of memory in mysql
Hi The Mysql binary distribution for IA32-linux is statically linked with glibc. glibc malloc limits memory allocations to 2GB, which means that a buffer in mysql can't grow beyond 2GB. This is due to some paranoia in glibc malloc - they don't rely on the size to be an unsigned int - which limits the size to 2^31 on any 32-bit platform. Has anyone tried to remove this limit in glibc malloc or linking Mysql with another malloc implementation? -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: malloc'ing 2GB+ of memory in mysql
Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I remember someone also reporting a problem that glibc or Linux does not allow creation of new threads if one has allocated = 2 GB user memory. I think there are problems in where the OS places the excutable, thread stacks, etc. We are running a mysql-server which has a innodb_buffer_pool_size of 2040 MB, 128MB Query Cache, 16MB key_buffer and is running ~ 250 threads. We are way past 2GB and running very stable. As our dataset has grown we need to increase the size of the innodb_buffer_pool_size. But malloc fails when innodb_buffer_pool_size 2GB. The comments in glibc (malloc/malloc.c) explains this as predicted behavior. We are considering to remove the paranoia checks from glibc and to see whether we are able to malloc more than 2GB in one go. But I believe someone must have done this before... (?) So it is uncharted territory. :( .. We are saving up cash for an Opteron now. :) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql/innodb and the 2GB limit
On Monday 02 June 2003 04:32 am, Kieran Kelleher wrote: What platform and hardware spec are you using? Linux (2.4.19 with the rmap vm) on Intel x86 (32 bit) with 3GB of memory. Mysql won't start if I set the InnoDB buffer above 2048MB. -Original Message- From: Per Andreas Buer [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 1:48 PM To: [EMAIL PROTECTED] Subject: mysql/innodb and the 2GB limit Hi. Lately, I've been banging against the glibc 2GB malloc limit - what a headache. :/ I believe this is not a mysql-specific problem, so a might be a bit off topic here. Does anyone know of a way to work around this limit? Does Redhat Advanced Server also have this problem? Or Suse Enterprise? -- Per Andreas Buer mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- There are only 10 different kinds of people in the world, those who understand binary, and those who don't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql/innodb and the 2GB limit
Hi. Lately, I've been banging against the glibc 2GB malloc limit - what a headache. :/ I believe this is not a mysql-specific problem, so a might be a bit off topic here. Does anyone know of a way to work around this limit? Does Redhat Advanced Server also have this problem? Or Suse Enterprise? -- Per Andreas Buer mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Loading a database into RAM
Steve Quezadas [EMAIL PROTECTED] writes: I have a mySQL database that is about 240 megabytes. I am loading it on a Linux server with 2 gigs of RAM. I would like to have the whole table reside in memory to save time from disk access. Is there any way to load the tables into RAM on startup? I am thinking about creating a heap table, but I need the heap table to be lodaed when mysql gets loaded. Is there anyway to do this? Or perhaps it is best to put the table in a RAM disk or something? Anyone know a generally recommended solution? If your server has a lot of memory all your tables will reside in cache. A fast an K.I.S.S.-compliant solution. :) -- Per Andreas Buer - 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
POSIX Asynchronous I/O in InnoDB?
Hi. I see InnoDB uses Asynchronous IO on Windows NT but not on Linux. There are patches, made by SGI, which enables asynchronous IO on Linux 2.4 (http://oss.sgi.com/projects/kaio/). Are there any plans to support native asynchronous IO in InnoDB? Quoted from the project site: Preliminary experience with KAIO have shown over 35% improvement in database performance tests. Unit tests (which only perform I/O) using KAIO and Raw I/O have been successful in achieving 93% saturation with 12 disks hung off 2 X 40 MB/s Ultra-Wide SCSI channels. We believe that these encouraging results are a direct result of implementing a significant part of KAIO in the kernel using split-phase I/O while avoiding or minimizing the use of any globally contented locks. -- Per Andreas Buer sql,query,queries,smallint - 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 keeps crashing due to out-of-memory errors.
Hi Heikki. Heikki Tuuri [EMAIL PROTECTED] writes: Why does InnoDB allocate so much memory in your case? If you follow the memory allocation with innodb_monitor, does the allocation grow steadily over days? Then it could be a memory leak in InnoDB. This might be the case. I started MySQL with a very big additional_mem_pool (400M) as you suggested. The server ran very stable for 12 hours. At first, memory allocation was like this: Total memory allocated 1499024287; in additional pool allocated 1205632 Total memory allocated 1499024287; in additional pool allocated 1651072 Total memory allocated 1499024287; in additional pool allocated 1815808 Total memory allocated 1499024287; in additional pool allocated 1887488 Total memory allocated 1499024287; in additional pool allocated 1962880 Total memory allocated 1499024287; in additional pool allocated 2005504 Total memory allocated 1499024287; in additional pool allocated 2029056 Total memory allocated 1499024287; in additional pool allocated 2060288 Total memory allocated 1499024287; in additional pool allocated 2068352 Total memory allocated 1499024287; in additional pool allocated 2106496 12 hours later: Total memory allocated 1499024287; in additional pool allocated 4044928 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4046208 Total memory allocated 1499024287; in additional pool allocated 4038912 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4048000 Total memory allocated 1499024287; in additional pool allocated 4046464 Total memory allocated 1499024287; in additional pool allocated 4046080 Total memory allocated 1499024287; in additional pool allocated 4043264 Total memory allocated 1499024287; in additional pool allocated 4046080 Total memory allocated 1499024287; in additional pool allocated 4049024 Total memory allocated 1499024287; in additional pool allocated 4059008 Total memory allocated 1499024287; in additional pool allocated 4067200 Total memory allocated 1499024287; in additional pool allocated 4063744 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4061952 (The reason the server crashed was due to many threads - we had approx 350 threads and we hit the 2GB barrier with a bang). 1) Monitor the memory consumption with innodb_monitor. Is there a symptom of a memory leak? I will get back to you on this one ~ friday when I have more data. 2) Try a smaller buffer pool or bigger. Does the crash always happen when InnoDB has allocated about 1400 MB of memory? 3) Try setting innodb_additional_mem_pool much bigger, say, 400 MB. Does memory allocation still spill over from it? No. It runs very stable with a much bigger innodb_additional_mem_pool. Looks the problem is linked with increasing the size of the innodb_additional_mem_pool. Greetings, -- Per Andreas Buer - 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 keeps crashing due to out-of-memory errors.
Heikki Tuuri [EMAIL PROTECTED] writes: good. I have also modified 3.23.52 so that it will generate a seg fault when it runs out of memory. That way we will get a stack trace on Linux. The error occered again. Now I se that I only pick 10 frames of the stack (curse me for cut'n pasteing). If you need a complete trace let me know. Oh, by the way. The error does not occur if I use less memory. 020615 21:45:28 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 2851686576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1413577223 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. Obtained 10 stack frames. /usr/sbin/mysqld(ut_malloc_low+0xb8) [0x823ba28] /usr/sbin/mysqld(ut_malloc+0x13) [0x823c37b] /usr/sbin/mysqld(mem_area_alloc+0xf9) [0x823a601] /usr/sbin/mysqld(mem_heap_create_block+0x63) [0x82397d3] /usr/sbin/mysqld(dtuple_convert_big_rec+0x1b6) [0x8235fde] /usr/sbin/mysqld(btr_cur_optimistic_insert+0x3e1) [0x8198bdd] /usr/sbin/mysqld(row_ins_index_entry_low+0x674) [0x81614b4] /usr/sbin/mysqld [0x816175e] /usr/sbin/mysqld(row_ins+0x1cc) [0x8161950] /usr/sbin/mysqld(row_ins_step+0xf7) [0x8161a83] Another question: As the server has some limitation (io kapasity) I am running with innodb_flush_log_at_trx_commit set to 0. Would it be unwise to try to sync the logs when we hit this kind of error? Does syncing of logs require mallocing of memory? What do you mean by syncing logs? Syncing the log-files, or journal. This only makes sense if one runs with innodb_flush_log_at_trx_commit=0. This is really not an important issue. If one requires durability one should buy adequate hardware. :/ -- Per Andreas Buer - 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 keeps crashing due to out-of-memory errors.
Hi Heikki. Heikki Tuuri [EMAIL PROTECTED] writes: Oh, by the way. The error does not occur if I use less memory. please show us your complete my.cnf. # This will be passed to all mysql clients [client] #password = my_password port= 3306 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 set-variable= table_cache=500 set-variable= max_connections=800 # the highest number of threads recorded is approx. 270 set-variable= max_connect_errors=10 # # Replication log-bin server-id=1 #log= /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking # # The skip-networkin option will no longer be set via debconf menu. # You have to manually change it if you want networking i.e. the server # listening on port 3306. The default is disable - for security reasons. # skip-networking set-variable= key_buffer=16M set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= max_allowed_packet=16M set-variable= thread_stack=256K # # Here you can see queries with especially long duration log-slow-queries= /var/log/mysql/mysql-slow.log # The following can be used as easy to replay backup logs or for replication #server-id = 1 #log-bin= /var/log/mysql/mysql-bin.log #binlog-do-db = include_database_name #binlog-ignore-db = include_database_name # skip-innodb # InnoDB innodb_data_home_dir=/var/lib/mysql/innodb/ innodb_data_file_path=ibdata00:1G;ibdata01:1G;ibdata02:1G;ibdata03:1G;ibdata04:1 G;ibdata05:1G;ibdata06:1G;ibdata07:1G;ibdata08:1G;ibdata09:1G;ibdata10:1G;ibdata 11:1G;ibdata12:1G;ibdata13:1G;ibdata14:1G;ibdata15:1G;ibdata16:1G;ibdata17:1G;ib data18:1G;ibdata19:1G innodb_log_group_home_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_files_in_group=3 innodb_log_arch_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M set-variable = innodb_buffer_pool_size=950M set-variable = innodb_additional_mem_pool_size=128M # innodb_flush_method=O_DSYNC innodb_flush_log_at_trx_commit=0 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] set-variable= key_buffer=16M Could it be that the mysqld memory usage may approach 2 GB? In that case glibc will start allocating memory over thread stacks, which will cause all kinds of crashes and errors. Hmm. I thought the heap could grow up to 3GB on Linux 2.4 (with one gigabyte for stack). Is the memory space split 2/2? This is bad news for me - I need as much memory as I can get. Note that each thread using sorting will use sort_buffer much memory, and each thread using a MyISAM table scan will use record_buffer much memory. Do you have many queries doing sorting simultaneously? Hmm. So Innobase tables don't use the record_buffer? But it does use the sort buffer, rigth? This is new to me - the documentation is a bit thin here - maybe you could add a section on how to configure a pure InnoDB MySQL-server? InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 2851686576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1413577223 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. Obtained 10 stack frames. /usr/sbin/mysqld(ut_malloc_low+0xb8) [0x823ba28] /usr/sbin/mysqld(ut_malloc+0x13) [0x823c37b] /usr/sbin/mysqld(mem_area_alloc+0xf9) [0x823a601] /usr/sbin/mysqld(mem_heap_create_block+0x63) [0x82397d3] /usr/sbin/mysqld(dtuple_convert_big_rec+0x1b6) [0x8235fde] /usr/sbin/mysqld(btr_cur_optimistic_insert+0x3e1) [0x8198bdd] /usr/sbin/mysqld(row_ins_index_entry_low+0x674) [0x81614b4] /usr/sbin/mysqld [0x816175e] /usr/sbin/mysqld(row_ins+0x1cc) [0x8161950] /usr/sbin/mysqld(row_ins_step+0xf7) [0x8161a83] The trace above is sensible. Do you have a BLOB or TEXT column in the table where it is doing an insert? No. Its mostly varchars. If not, then the index entry tuple which InnoDB has just constructed has probably garbage in its fields, and that is why InnoDB believes it is a 'big record'. Hmm. So, if the heap has grown to much - glibc is writing garbage all over the stack. Is there any way to tell how much memory MySQL has allocated? -- Per Andreas Buer
Re: innodb keeps crashing due to out-of-memory errors.
Hi Heikki, thanks for replying so swiftly. Heikki Tuuri [EMAIL PROTECTED] writes: this is probably a real bug. It is trying to allocate 1.5 GB of memory in one shot, which does not make sense. More probably the argument to the allocator is garbage. What MySQL version you are running? mysqld Ver 3.23.49 for pc-linux-gnu on i686 MySQL is installed from a Debian Package (I am running Debian GNU/Linux 3.0) - package version 3.23.49-8. Can you compile MySQL yourself with CFLAGS=-g -O3 ./configure --with-innodb and run it inside gdb? Put a breakpoint to the code where it prints the error message, so that we get the stack trace with gdb bt full Hmm. This is a production machine and I have a very limited window to tinker around with things. The crash occures when the server is really busy - I have so far failed to provoke this to happen. I've modified innobase/ut/ut0mem.c to give a nice stack-trace when the error occures, and recompiled with symbols. Hopefully (uhh ;) it will crash again monday and I will have a nice stack-trace to show you. Another question: As the server has some limitation (io kapasity) I am running with innodb_flush_log_at_trx_commit set to 0. Would it be unwise to try to sync the logs when we hit this kind of error? Does syncing of logs require mallocing of memory? For completeness; here is my innodb configuration: # InnoDB innodb_data_home_dir=/var/lib/mysql/innodb/ innodb_data_file_path=ibdata00:1G;ibdata01:1G;ibdata02:1G;ibdata03:1G;ibdata04:1G;ibdata05:1G;ibdata06:1G;ibdata07:1G;ibdata08:1G;ibdata09:1G;ibdata10:1G;ibdata11:1G;ibdata12:1G;ibdata13:1G;ibdata14:1G;ibdata15:1G;ibdata16:1G;ibdata17:1G;ibdata18:1G;ibdata19:1G innodb_log_group_home_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_files_in_group=3 innodb_log_arch_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=96M set-variable = innodb_buffer_pool_size=950M set-variable = innodb_additional_mem_pool_size=64M innodb_flush_method=O_DSYNC innodb_flush_log_at_trx_commit=0 -- Per Andreas Buer -- Per Andreas Buer - 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
innodb keeps crashing due to out-of-memory errors.
Hi. I have a MySQL-server running Innodb. We have installed ~ 1.7GB of memory in the server. In spite of this MySQL keeps crashing due to out-of-memory errors. The server is a dual i686 running kernel Linux 2.4.18 (no patches - 4GB RAM supported). I've enabled overcommit_memory (/proc/sys/vm/overcommit_memory). When the system is crashing memory may look a bit like this: ~# free -m total used free sharedbuffers cached Mem: 1761 1748 12 0 11926 -/+ buffers/cache:810951 Swap: 2059 6 2052 The message is: 020613 17:06:31 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 1529313000 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1455520263 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. Any clues? -- Per Andreas Buer - 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
how to tell if innodb has enough memory
Hi. I've got an database-server with 768MB RAM running MySQL/InnoDB. There is quite a lot of I/O activity - about 7Mbytes/s. I am not quite sure why there is so much I/O - maybe the indexes won't fit into memory? Is there a way to tell how innodb is spending available memory? Or even better - a small guide telling you how to find the appropriate memory configuration for innodb. -- Per Andreas Buer - 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: random order by id
Jule [EMAIL PROTECTED] writes: Hey guys, i have a db and a table with id and questions now i want these questions to be listed in a random order, is there a way to format my SQL query or do i need some PHP work to? SELECT foo FROM bar ORDER BY rand(); Just to pick one: SELECT foo FROM bar ORDER BY rand() LIMIT 1; -- Per Andreas Buer - 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
(innodb) transations waiting in lock_mode AUTO-INC waiting
Hi. I have quite i buzy MySQL-server running MySQL 3.23.49. The Database has ~100 tables. One of them, here referred to as table_0, is quite buzy. Turning on the Innodb Monitor gives me the following clue. A lot of transations are listed like this: ---TRANSACTION 0 1029552346, OS thread id 155184269 setting auto-inc lock, active, lock wait, has 1 lock struct(s) MySQL thread id 151538, query id 1612637 xxx.xxx.com nn.nn.nn.nn database update insert into table_0 (attribute, attribute, attribute) values ( .. --TRX IS WAITING FOR THE LOCK: TABLE LOCK table database/table_0 trx id 0 1029552346 lock_mode AUTO-INC waiting I am not quite sure what this means - my guess would be that there are problems getting a lock on the auto-increment counter in table_0. If so - what could be the cause of this? -- Per Andreas Buer - 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
