I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read). The task in question will be doing over 1 million database...
Here is an example of what I've got going on: CREATE TABLE Parent (id BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB; CREATE TABLE Child (id BIGINT NOT NULL, parentid BIGINT NOT NULL, PRIMARY KEY (id), KEY (parentid), CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB; CREAT...
I like InnoDB's safety, consistency, and self-checking. But I need MyISAM's speed and light weight. How can I make MyISAM less prone to corruption due to crashes, bad data, etc.? It takes forever to go through a check (either CHECK TABLE or myisamchk). I'm not asking for transactional security -- that's what InnoDB is for. But I do w...
How to enable inno-db support on installed instance of MySql? I have installed mysql-5.0.67-win32. 'InnoDB' is 'DISABLED' when executing 'show engines'. According to documentation MySql is compiled with support of inno-db (From doc: A value of DISABLED occurs either because the server was started with an option that disables the engine,...
I have to restore a database that has been inadvertently DROPped in MySQL 5.0. From checking the backup files, I only seem to have .FRM files to hold the database data. Can anyone advise whether this is all I need to perform a database restore/import from the backup, or are there other files I should have to hand to complete this? ...
I would like to create a copy of a database with approximately 40 InnoDB tables and around 1.5GB of data with mysqldump and MySQL 5.1. What are the best parameters (ie: --single-transaction) that will result in the quickest dump and load of the data? As well, when loading the data into the second DB, is it quicker to: 1) pipe the resu...
I am running a mysql server. I would like to somehow make sure that the whole DB is loaded into the ram as I heard it would be alot faster. Is this true? and how do I vertify it? ...
mysql5.0 with a pair of databases "A" and "B", both with large innodb tables. "drop database A;" freezes database "B" for a couple minutes. Nothing is using "A" at that point, so why is this such an intensive operation? Bonus points: Given that we use "A", upload data into "B", and then switch to using "B", how can we do this faster? Dr...
One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there. What should I look at or consider before doing this? Can I just change the engine, or should the data be prepared for it? ...
We (apparently) had poorly executed of our Solaris MySQL database engine last night. At least some of the InnoDB tables are corrupted, with timestamp out of order errors in the transaction log, and a specific error about the index being corrupted. We know about the tools available for MyISAM table repairs, but cannot find anything for I...
Hi all, Does anyone know how much memory MyISAM and innoDB use? How does their memory usages compare when dealing with small tables vs. when dealing with bigger tables (up to 32 GB)? I know innoDB is heavier than MyISAM, but just how much more? Any help would be appreciated. Thanks, jb ...
What are the differences between MyISAM and Inno DB types in MySql? ...
Hi, since fews days Mysql server on Windows does not success on closing itself. In the mysql error log I find multiple instance of these lines: InnoDB: Operating system error number 32 in a file operation. InnoDB: The error means that another program is using InnoDB's files. InnoDB: This might be a backup or antivirus software or ano...
So, I'd like to be able to set the max log file size to 64M, but after doing so with innodb_log_file_size=64M MySQL starts OK, but nothing seems to work properly. EDIT: and by properly I mean not at all. Setting other InnoDB variables aren't causing any problems. How should I go about troubleshooting this one? ...
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables. My argument, as detailed here, is that the performanc...
Here's my table: CREATE TABLE `alums_alumphoto` ( `id` int(11) NOT NULL auto_increment, `alum_id` int(11) NOT NULL, `photo_id` int(11) default NULL, `media_id` int(11) default NULL, `updated` datetime NOT NULL, PRIMARY KEY (`id`), KEY `alums_alumphoto_alum_id` (`alum_id`), KEY `alums_alumphoto_photo_id...
When using phpMyAdmin or the MySQL GUI Tools, whenever I create an InnoDB table, it adds a comment to the table like this: InnoDB free: 9216 kB What does this mean? What's it used for? ...
I've created a table in MySQL: CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT, type ENUM('rate','report','submit','edit','delete') NOT NULL, Q_id int NOT NULL, U_id int NOT NULL, date DATE NOT NULL, time TIME NOT NULL, rate tinyint(1), PRIMARY KEY (A_id), CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id), CON...
It seems like I will be needing transaction with MySQL and I have no idea how should I manage transactions in Mysql with mixed InnoDB/MyISAM tables, It all seems like a huge mess. You might ask why would I ever want to mix the tables together... the anwer is PERFORMANCE. as many developers have noticed, InnoDB tables generally have bad ...
I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this? ...