stored procedure
hi..i wanted to know if there is a possibility to debug stored procedures in mysql..engine type = InnoDB ...
innodb
How to deliberately lock a MySQL row such that even SELECT will return an error?
I'm trying to use MySQL row locking to basically emulate a MuteEx on a row. Lets say that my table has 2 columns, an id and a text field, and three entries (1,a) (2,b) and (3,c). SELECT * FROM table; would return these results. I can lock a specific row the normal way. START TRANSACTION; BEGIN; SELECT * FROM table WHERE id = '2' FOR UPD...
Reading reliably from a constantly appended-to table
I've got a web application, one of whose functions is which is constantly appending rows to a large table. The rows look approximately like this: id bigint not null primary key visited datetime not null ip_address # Other fields It's a tracking table, as you've probably guessed. The use of this table is completely append-only, that i...
a question on many-many relationships
I have the following tables items similars items_similars = pivot table items->similars has a many-many relationship the items_similars has the following field item_id similar_id if I am using InnoDB as my engine, do I need to create a relation between items.id and items_similars.id? or between similars.id and items_similars.id?...
Does MySQL record how often it uses indices?
I've got a table (InnoDB) with a fair number of indices. It would be great to know if one (or more) of these was never actually used. I don't care as much about the disk space, but insertion speed is sometimes an issue. Does MySQL record any statistics on how often it has used each index when running queries? ...
Speeding up conversion from MyISAM to InnoDB
I have a MySQL 1.5 GB MyISAM-table (1.0 GB data, 0.5 GB indexes) in production which I'm about to convert into InnoDB. Since the table is used in production I'd like to make the downtime as short as possible. My questions: What MySQL configuration options should be adjusted in order to speed up ALTER TABLE table_name ENGINE=InnoDB;? ...
Rebuild InnoDB Index ONLINE to prevent timeouts?
When I update a particularly large table, the update times out because the table is locked while the indexes rebuild. Is there any way to rebuild the index ONLINE (i.e. Oracle) so the update does not timeout? ...
MySQL query caching: limited to a maximum cache size of 128 MB?
My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together. Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server. query_cache_size is the maximum amount of data...
What is the default setting for innodb_data_file_path if it isn't specified in my.cnf?
What is the default setting for innodb_data_file_path if it isn't specified in my.cnf? When I try to execute the following command on a 12GB table full of pictures alter table `rails_production`.`pictures` change `data` `image_file_data` mediumblob NULL; I get the following error ERROR 1114 (HY000): The table '#sql-7fe4_12c9' is ful...
Bloated innodb datafile (ibdata1) and how to reclaim space?
I have about 12GB of data in my tables and my datafile, ibdata1, used to be about 12GBs in size. I then ran the following command alter table `rails_production`.`pictures` change `data` `image_file_data` mediumblob NULL While it was making a temporary copy of the table, I got the following error ERROR 1114 (HY000): The table '#sql-7f...
What is innodb_buffer_pool_size & innodb_log_file_size?
Can anyone tell me what is innodb_buffer_pool_size & innodb_log_file_size? What will happen after increasing it's sizes.. ...
What values are not stored on disk by InnoDB?
I have a pretty big table in InnoDB, and I want to minimize its size, within reason. For a few of the columns, the values are almost always the same in every row, so I want to try to take advantage of this. For example, if I have an int column (or datetime or varchar or whatever), would a null value in that column not actually be store...
MyISAM and InnoDB tables in one database
I have a database with about 30 tables and 5 tables of them is write-intensive. I'm considering Convert 5 write-intensive tables to use InnoDB engine and keep the rest on MyISAM engine Convert all tables to use InnoDB engine. I wonder which approach is better? To be more specific The reason I want to keep some table on MyISAM engi...
Rails MySQL query time confusion
Hi, I have a count calculation query which I am running thousands of times in my Rails app, once for each customer in the db. When I run the query in my MySQL client with query cache disabled the query takes last than 1ms. However, when I run my task from the Rails console with query output enabled I've noticed that after the first ...
InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
I'm currently working on creating an environment to test performance of an app; I'm testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we'll automatically prepare the database (load existing dumps) and instrument our test tools. I'm preparing to test the same data dump with MySQL and InnoDB, but ...
Keeping data plus index-data in memory - InnoDB vs. MyISAM
Assume a database consisting of 1 GB of data and 1 GB of index data. To minimize disk IO and hence maximize performance I want to allocate memory to MySQL so that the entire dataset including indexes can be kept in RAM (assume that the machine has RAM in abundance). The InnoDB parameter innodb_buffer_pool_size is used to specify the si...
How do I deal with this race condition in django?
This code is supposed to get or create an object and update it if necessary. The code is in production use on a website. In some cases - when the database is busy - it will throw the exception "DoesNotExist: MyObj matching query does not exist". # Model: class MyObj(models.Model): thing = models.ForeignKey(Thing) owner = model...
Force InnoDB to recheck foreign keys on a table/tables?
I have a set of InnoDB tables that I periodically need to maintain by removing some rows and inserting others. Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important. To insert the new rows without worrying about the order of the tables, I use: SET FOREIGN...
Lots of mysql Sleep processes
Hello, I am still having trouble with my mysql server. It seems that since i optimize it, the tables were growing and now sometimes is very slow again. I have no idea of how to optimize more. mySQL server has 48GB of RAM and mysqld is using about 8, most of the tables are innoDB. Site has about 2000 users online. I also run explain on...
What complications should I look out for when converting tables to InnoDB ?
I'm having trouble with a liferay installation that is suddenly (without any code modifications) failing with "dulplicate id" errors. We've identified that the issue may be solved by changing the mysql engine to InnoDB. I'm planning the change now, but i'm not sure what complications are likely in the process. I've checked for full te...