Innodb
I have a table with approx. 150,000,000 ++ rows. It should be approx
80byte for each row. Now I'm planning to use it with mysql/innodb.
Can you guys suggest anything about this.
I have many questions... Can Innodb work well with such tables?
Will queries (select/update) be slow?
What hardware do you suggest, cpu/ram?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Help On Innodb
I have read few articles on innodb and quite confuse about those stuff. So said that you need to do some start up setting on innod buffer pool and etc. Where can I like get this list of things which need to be done on innodb. I need some optimization tips for innodb.
Innodb
I've always managed dependencies accross tables with a 'link' table: Eg: Users belong to Groups so I'd have a User2Groups linking table to manage the connection. This works OK, but I have to manage the data integerity myself (if I delete a Group, I must also delete the record in the user2Groups table). I've recently heard of innodb and the CASCADE features - allowing the db to reatain referential integrity. Can someone please summarize the pros and cons of this - perhaps give reasons when to use and when not to use, and point me in the right direction for using innodb -
BDB Vs INNODB
Are there any big differences between using BDB and INNODB table types? I know they both support transactions and BDB is generally slower than INNODB. I'm talking more about the implementation rather than performance. For instance, I developed this application which at the time I was writing it was for INNODB tables. Should the same app work fine using the BDB table type.
3.23.x To 4.0.x INNODB
Can I take multiple 2GB files in /var/lib/innodb and the corresponding files in /var/lib/iblogs created with 3.32.55 (INNODB) and upgrade to 4.0.13 and let it use those existing files without a problem? Without dumping and re-importing? I'd only need to additionally run the mysql_privs_fix(?) script as part of a normal 3.23->4.0 upgrade? I did this as a test and it appeared to work but only on a test machine.. Want to make 100% certain before trying it on a production box.
64-Bit And INNODB
With an Opteron (or Itanium for that matter) using let's say the RedHat 9 or AS 3 betas available for those CPUs, how will MySQL/INNODB function? Specifically does the ability to allocate a bigger chunk of memory than 2GB just work "out of the box"? Can I set innodb_buffer_pool_size to 12GB for instance if I have 16GB in a box? Now then, along the same lines and not really MySQL oriented but someone here will likely know. I see lots of Opteron boards with 2 CPUs being advertised that have 4 DIMM slots "per CPU". Is it one large flat memory space or do they do SMP in some strange fashion where each CPU has access to it's own memory? Therefore not giving a flat 16GB memory space if 8 slots were populated with 2GB DIMMs? I hope not but the way they're being described it is somewhat confusing. Even more off-topic, anyone know of a good 1U or 2U Opteron or Itanium system with >8 DIMM slots? 64-bit is great but unless you can stuff a _lot_ of memory in it, a lot of the advantage is lost. I wish someone like SuperMicro would offer a high end Opteron SuperServer. I love their dual Xeon systems..
How To Set Up INNODB
I want MySQL to use INNODB tables.I created the tables using innodb as stated on mysql website but not having any luck. I am interested in maintaining referential integrity between primary and foreign keys..meaning.. "if i update the value in one table the other tables gets updated automatically too"..and if not wrong Mysql doesnt support this feature but innodb does. so how i can basically setup MySQL to use INNODB on linux.
InnoDB
I am struggling a bit to get going with mysql and my tutorial works through InnoDB tables without a clear explanation of the setup. To date I have modified the /etc/my.cnf file. mysqld starts without complaining. I have modified my.cnf to include: innodb_data_file_path = /data/mysqldata/innodb_data/innodata:10M:autoextend innodb_data_home_dir= It turns out that logged in as a mysql user if I "use innodta' it mounts as a the database. So am I correct in assuming that identifying the tablespace name in innodb_data_file_path is the equivalent of 'create database <databaseName> with a MyISAM table? Also once having 'use innodata' I can issue a create tables command and then see <tablename>.frm .MYD, and MYI listed in the innodb_data directory. However when <tblename> was created I could not use the 'engine = innodb; ' as the last line of the create table sql statement without getting an error. This suggests that the table that I made without 'engine=innodb' is just the usual MyISAM based table. Right? Wrong? Huh? I think the documentation is a little lacking in this area as are two other MySQL books that I purchased
Specifying INNODB
Is it a bug or a feature that with the conditions 1. INNODB is not configured in my.cnf 2. Table type is specified as INNODB that when you create the table there is no warning, and no syntax error, and the table type silently and Microsoft-like, defaults to MyISAM? Now, if you specify table type as TYPE=bullshit, you get a nice (and expected) syntax error.
Innodb
I'm using mysql 5.0.22 as packaged (and updated) with Ubuntu Dapper. I would like to use Innodb, but "show innodb status" says "ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined". However, there is no skip-innodb in anywhere in /etc/mysql/my.cnf. I found a file called /etc/mysql/debian.cnf which appears to be used as a defaults file for behind-the-scenese stuff. It doesn't mention skip-innodb. I've checked /etc/init.d/mysql and it has no reference to skip-innodb. I've googled the issue, and can't find anything. Am I missing something?
No InnoDB
OS Fedora Core 3 I have been trying to set InnoDB engine on mysql 4.0 and 5.0 as default with no joy so far. I have edited my.cnf file and put it in the datadir /usr/lib/. Everytime I create a table the engine is MyIsam.
Myisam Or Innodb
Is there a way to show what type of a storeage engine a table is using from the mysql monitor?
The Age-old MyISAM Vs InnoDB
Okay, someone help me out here. I want to see some numbers supporting the omnipresent claim that MyISAM tables are faster than InnoDB tables. I don't want to hear "MyISAM is the best because it's faster"; Google gave me over 100,000 links to pages that say that. I want to see numbers, results from benchmarking. I'm well aware of the difference in features between the two engines (transactions, referential integrity, full-text indexing, etc), I'm simply interested in seeing support for the claims of speed superiority the MyISAM crowd are always quick to shout. I'll start: http://www.mysqlperformanceblog.com/...hmarks-part-1/ This is the only set of data I could find comparing InnoDB and MyISAM, and shows in general InnoDB demonstrating superior performance over MyISAM. This test was done exclusively on SELECT statements, where MyISAM allegedly dominates InnoDB hands down. In my own (admittedly limited) testing, again solely on SELECT statements using a mixture of indexed and non-indexed columns in WHERE, ORDER BY, and GROUP BY columns, and even tossing in a few aggregate functions just for fun, InnoDB and MyISAM were virtually neck-in-neck, with InnoDB never eeking out more than a 20% lead and MyISAM never returning faster at all. (Average InnoDB lead: 2.8%, statistically insignificant and putting it well within the realm of possibility that my observation that InnoDB was always faster was nothing more than a fluke.)
InnoDB Or MyISAM?
I'm having a tough time on how to choose a table type in my database design. I am designing a multi-user web application and want to take advantage of both transactions and full text searching. However, as you know, I can only choose InnoDB for transactions or MyISAM for full text indexing/searching. How do you guys go about deciding which to choose from? I've thought about using different types in different tables, to suit my needs. However, all data is dependent on each other. This means if I set table A to InnoDB, and table B to MyISAM, future transactions wouldn't work properly if I had to delete/update to both tables at once.
Innodb Or MyISAM ?
I have been reading a lot about Innodb and MyISAM recently. MyISAM offers speed whereas Innodb offers reliability. MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations. The InnoDB which makes it fast for INSERTs, UPDATEs and DELETEs while MyISAM offer the best performance for SELECTs. so my doubt here which is best MyISAM or Innodb.Can I use Both engines in a single table and How Innodb handles foreign key constraints and how Myisam handles fulltext indexes.if we apply both in a single table.what is the sysntax for doing like this
Innodb Vs Myisam
hi every one i am working on a database for a website, and just want to know how faster will be myisam from innodb if you please can tell me in percentage if you want to look at the design of the database i had it on this
Using Transactions With InnoDB
Right now I am experiencing with transactions in MySQL using the InnoDB engine. I found a really great article which includes all about transactions: http://dev.mysql.com/books/mysqlpres...rial/ch10.html However, I still got a question and I couldn't find the answer exactly in this article. A simple example: UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; I just want both queries executed OR both not executed, so I should use a transaction. I thought the following would give me the right result, but it is not exactly what I want: START TRANSACTION ; UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; COMMIT ; If user 1 doesn't have 1000 dollar anymore, both queries shouldn't be executed. The same for when the field name in the first query is wrong spelled or doesn't exist, both queries shouldn't be executed. I read something about ROLLBACK in the mysql article on the mysql.com website and I think I can solve my problem with this one, but I don't know exactly how to use this. The example in the article says: START TRANSACTION ; UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; SELECT money FROM users WHERE id = 1 ; ROLLBACK ; But this one doesn't work for me. The ROLLBACK will always be executed, even when the user got enough money.
InnoDB Vs MyISAM
I wonder witch one is best? What is the differance? Witch one is faster in witch case? I hope someone can take littel time to answer me about these two database motors.
MyIsam DB To InnoDB...
How do I convert my existing database, which I'm using MySQL ver 4 for Windows and MyISAM table type to store the data, to the new format – InnoDB, without problem ( losing data...)
INNODB Crashing
Shortly ago our system hung, with all queries showing a status of 'statistics'. I was not able to cleanly shutdown and restart the database and had to kill the main process which was at 99% cpu. Now I cant restart, and am getting this dialog in my error file, can anyone please help!? 041014 10:57:57 mysqld started 041014 10:57:57 Warning: Asked for 196608 thread stack, but got 126976 041014 10:57:58 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 53 604876995 InnoDB: Doing recovery: scanned up to log sequence number 53 610119680 InnoDB: Doing recovery: scanned up to log sequence number 53 612616861 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1 row operations to undo InnoDB: Trx id counter is 0 972032512 041014 10:57:58 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 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 InnoDB: Error: trying to access page number 128 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041014 10:58:02InnoDB: Assertion failure in thread 4096 in file fil0fil.c line 1207 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. See section 6.1 of InnoDB: http://www.innodb.com/ibman.php about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=1044480 max_used_connections=0 max_connections=300 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 875341 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84152a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe488, stack_bottom=0x20386365, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x64207369 is invalid pointer thd->thread_id=1702047021 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. 041014 10:58:02 mysqld ended
Replication And InnoDB
I'd like to setup the following: Server A <-- Server B <-- Server C Data A some of Data A some of Data A Data B Data B Server A should be the master for server B. Server B should get not all but some of the data of server A. Additionally there should be other data stored on server B. Server B is slave for server A and master for server C. Server C simply should get all the data of server B. No problem with MyISAM, but with InnoDB some questions arise: The manual says, that one should shut down the master and copy the InnoDB datafiles, logs and .frm files. 1.) If I only need some data of server A on server B wouldn't it be overkill to copy all the InnoDB data? 2.) If I want to store other data on server B I can't mix up the InnoDB datafiles and logs for data A and B, can I?
Innodb Build
When I installed MySQL awhile backe I somehow neglected to allow Innodb. Now when I try to create an Inno table it says that I have to build with Innodb. Do I need to completely uninstall MySQL and start over? I ran through the administrator configuration program and thought that that would do it but it didn't.
InnoDB Can't Use System's RAM As Specified
I am coming across an annoying problem with a new InnoDB installation. On a 4Gig RAM box, Mysql doesn't seem to want to grab the RAM I am telling it to take. I would like for it to try and use around 3 Gigs for its innodb_buffer_pool_size, but it refuses to startup with anything greater than 512M. I bugged RedHat about it, and they had me change the shared memory allowance to 3Gigs, but nothing changed. I'd really like to get the pool size up so that the server doesn't have to go to disk so often. I will admit to being an InnoDB newbie, I am reading everything I can find though and have used the example my.cnf files to tweak it pretty decently. This memory issue is all that is really bugging me at the moment. System Specs: RedHat Enterprise ES 2.1 2.4.9-e27smp MySQL 4.0.14 Compaq/HP Proliant 300 Series Xeon Dual 2.8Ghz Pent4 4Gigs RAM 2 72Gig Scsi 320 drives Raid 1+0
InnoDB Corruption
My MySQL crashed this morning which lead me to look into my server to see what was up. Upon inspection it seems I may have made a mistake. Back on June 25th 2006 I upgraded MySQL from 4.1.12 to 4.1.20 and I copied over the data folder (or so I thought) and got it up and running and happy. However when I looked today it seems that I may have not copied over the log files! The following error shows up in the error log: 060808 10:48:44 InnoDB: Error: page 531 log sequence number 0 266909086 InnoDB: is in the future! Current system log sequence number 0 62488108. InnoDB: Your database may be corrupt. 060808 10:48:44 InnoDB: Error: page 532 log sequence number 0 266926873 InnoDB: is in the future! Current system log sequence number 0 62488108. InnoDB: Your database may be corrupt. a bunch of them! I have a feeling that it is because I failed to drop in the log files after the upgrade. What is the easiest way to remedy this situation? I do have the old log file. Is there anyway to append the two logs togehter?
Missing .myi And .myd Using INNODB
I just started this job and the previous IT Manager did not backup the mysql DB's -- It appears one crashed over the weekend and I have no idea where to begin. I do not want to do anything to make things worst. I did copy all of the .frm and lb* files to another directory for safe keeping. Can I recover from this? One of the lb files is huge, which leads me to believe it might be useful in a recovery.
InnoDb -v- MyISAM
I have three tables one of which is a ManyToMany table. tbl_Groups Group_ID | Group_Name tbl_Premises Premises_ID | Premises_Name m2m_GroupsPremises Group_ID | Premises_ID can the two tbl's be made with MyISAM and the m2m be made with innoDb or must they all be innoDB? I wonder if only those with a foreign key need to be built with InnoDb.
Innodb & Locks
My Applications, that uses ADO/VB^ to communicate with a MYSQL db with Innodb tabes need to create row level locks. I got this working by using the following code ( abridged from main application) .....
MyIsam -v- InnoDB
What is the view of the panel on this one? I suspect that there may be trade-offs but, I welcome your experience-based opinions.
InnoDB Vs. MyISAM
I'm making a database with about 20 tables, each once with many rows (ranging from 1000 to 100,000 from table to table) and it needs to be quick for updates and selects. I'm confident that I can arrange the tables and keys in a manner which provides me with good speed, however I cannot decide on which database to use. I read some documentation that said that InnoDB was best for a web server (where the database is hosted) for transactions. However each user only ever performs a single action at a time (although many users are online simultaniously). The only times I need to lock and tables or records are for backups and maintenance, which I will do inside the user code anyway. With that information can anyone help me to determine which would be the best database, InnoDB or MyISAM?
Fulltext For InnoDB?
I was wondering, does anyone know if there are any plans to add FULLTEXT indexes to the almighty InnoDB such that it catches up with the funky MyISAM in terms of things you can pass to ALTER TABLE?
InnoDB Vs MyISAM
I'm looking at what tables to use in my database and am thinking innodb will be better as it allows foreign keys however I was just wondering how much faster myISAM tables are? My table will probably have around 500,000 records... Any thoughts / advice?
MyISAM Vs. InnoDB
I'm designing an event registration web application and have created a database on our UNIX web hosting server using MySQL 4.1. I need to join several tables using a registration ID. My question: What is the best table design method to use - MyISAM or InnoDB?
Specifying INNODB Without Configuration
Is it a bug or a feature that with the conditions 1. INNODB is not configured in my.cnf 2. Table type is specified as INNODB that when you create the table there is no warning, and no syntax error, and the table type silently and Microsoft-like, defaults to MyISAM? Now, if you specify table type as TYPE=bullshit, you get a nice (and expected) syntax error.
MyIsam V Innodb
I am working with a test database. Originally the tables were all innodb. I have a number of queries involving and I had performed a certain amount of optimisation to ensure that the queries were using the correct indexes and were not performing a table scan. I then changed the table type to MyIsam because I wanted to speed up the application and I do not require that the database is transaction-safe. However when I ran the same EXPLAIN on the exact same queries as before, I noticed that the queries were going to take significantly longer to run, depsite the fact that they were still going to use the correct indexes. I have reviewed the documentation and cannot find anything of this nature. I was totally surprised because I expected MYISAM to speed up the queries.
Fulltext For InnoDB?
I was wondering, does anyone know if there are any plans to add FULLTEXT indexes to the almighty InnoDB such that it catches up with the funky MyISAM in terms of things you can pass to ALTER TABLE?
My.Ini - InnoDB Entries
I have been using MySQL in a Windows environment for about 6 months and have successfully written a couple of applications. Reading all and anything about MySQL I have been looking into the My.Ini entries, particularly those affecting InnoDB tables. Code:
Backup InnoDB
I just installed MySQL and I need to make a backup of my InnoDB tables but I don't want to purchase the tool to do it. Does anyone have a detailed list of how to do this.
MyISAM Vs. InnoDB
I've been having some problems with the .myi files, (the index files), of my MyISAM tables dissapearing. It can be fixed by restarting the server but I cannot figure out what is causing the problem. So, in looking for a solution I started reading about different table types and realized that I will not be able to use MyISAM tables for some transactional stuff that I am about to build. This led me to wonder - should I just change all my tables to InnoDB - I'll be able to do transactions and maybe I won't have problems with .myi files dissapearing! So, here's my dilemma - the MySQL manual is contradictory about why I should not use InnoDB tables - in one place it says it's slow because it's transactional, but in another it says it is the fastest disk-based engine in existence. Finally the real question: Is there any reason I should not switch to all InnoDB tables? Are they really super fast yet support both transactions and foreign key constraints?
Innodb Vs MyISAM?
Besides transaction capability, is there anything else about innodb tables that makes it superior to myISAM? I really don’t need transactions, but for example, I need superior reliability for index (too many index corruptions in the past). Is innodb better in that area, or any other areas?
Innodb Logfiles
Is it possible to manually delete the logfiles created in innodb tables and then do a 'touch logfilename' to recreate it?
Specifying INNODB Without Configuration
Is it a bug or a feature that with the conditions 1. INNODB is not configured in my.cnf 2. Table type is specified as INNODB that when you create the table there is no warning, and no syntax error, and the table type silently and Microsoft-like, defaults to MyISAM? Now, if you specify table type as TYPE=bullshit, you get a nice (and expected) syntax error.
InnoDB Vs MyISAM
Is there a matrix or chart that identifies the differences between InnoDB and MyISAM as well as highlighting any advantages and disadvantages?
Innodb Tables
I'm new to Unix and Mysql and I can't seem to get my innodb tables up and running. My.cnf file looks like this: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysql.pid I tried copying the portion of the my-medium.cnf that deals with innodb and putting it under the [mysqld] part but I got the can;t connect to socket error.
Mysqlhotcopy On InnoDB
Does mysqlhotcopy work with innoDB? I only get it to work with MyISAM tables... For innoDB only the structure is saved but not the data =20
InnoDB Can't Use System's RAM As Specified
I am coming across an annoying problem with a new InnoDB installation. On a 4Gig RAM box, Mysql doesn't seem to want to grab the RAM I am telling it to take. I would like for it to try and use around 3 Gigs for its innodb_buffer_pool_size, but it refuses to startup with anything greater than 512M. I bugged RedHat about it, and they had me change the shared memory allowance to 3Gigs, but nothing changed. I'd really like to get the pool size up so that the server doesn't have to go to disk so often. I will admit to being an InnoDB newbie, I am reading everything I can find though and have used the example my.cnf files to tweak it pretty decently. This memory issue is all that is really bugging me at the moment. Code:
Innodb Doc In Mysql.com
In mysql.com site on section 7.5.4.1 it says and I qoute "If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. " If I do this, will it break my database ?
Asking MySQL If InnoDB Is Available
Is it possible to 'ask' MySQL using SQL if InnoDB is available and usable? I am writing a GUI database app that has a 'create table' function, I want to give the user the option of creating an InnoDB database, but only if InnoDB is available, can this information be found out from SQL (I'm using JDBC)
Searching In Innodb
Does innodb support fulltext indexes and searching on those indexes in MySQL 4.0?
Using MyISAM And InnoDB
How I can know that my database uses MyISAM or InnoDB storage engine? What command can be issued to check this?
|
TRACKER
