MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
9
votes
3answers
10k views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
8
votes
1answer
818 views
MySQL table with 100,000 records queried often
I have a single database of about 100 tables to store various kinds of information.
The most important table is our order table which is used to store customers orders and is over 100000 records as ...
7
votes
1answer
2k views
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then ...
6
votes
2answers
785 views
How do Application Like Yelp Retrieve distance information from data base efficiently
For example, say I have a table:
Business(BusinessID, Lattitude, Longitude)
All are indexed of course. Also there are 1 million records
Say I want to find businesses closest to 106,5, for example, ...
6
votes
1answer
204 views
Why does Oracle keep working on MySQL 5.1
2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62
It looks like there are should be fundamental ...
6
votes
1answer
1k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
5
votes
2answers
196 views
Less restrictive query return less result due to simple removing one additional constraint
Look at this query
SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
...
5
votes
1answer
3k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
I'm a beginner system administrator for a small virtual server. The main function of the server is to run the open-source Moodle course management system software, written in PHP. It relies on a ...
5
votes
2answers
171 views
Is there a reason/benefit I am not aware of for this kind setup?
I now manage a MySQL database that was designed and created by another.
I am confused about a certain setup that the original developer created..
He did not use auto increments for table IDs. ...
5
votes
2answers
1k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
5
votes
2answers
726 views
how to enable temporal database capability in mysql.?
How to enable the temporal database compatibility in mysql database?
Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
5
votes
3answers
1k views
Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
I need to design database to store information contained in millions of log files generated by devices on trial on network.
I have designed 5 different tables to store log file information. But, as ...
5
votes
1answer
210 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
5
votes
0answers
210 views
Migrating MySQL to new server
We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B).
We have a new MySQL 5.5 instance on ...
5
votes
1answer
170 views
MySQL varchar(255) limitation & Anomaly
I have defined a column in my MySQL database as :
ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
However, when I enter text ...
4
votes
3answers
6k views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
4
votes
2answers
101 views
DELETE LOW_PRIORITY row visibility
I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
4
votes
1answer
63 views
Will these queries return the same results?
I have this simple query:
SELECT
friend_id
FROM
default_friend
WHERE
user_id = 1
And the results are below:
+-----------+
| friend_id |
+-----------+
| 2250 |
| 4901 |
| ...
4
votes
3answers
3k views
How to change a MySQL previous slave to be a master and remove slave status information?
I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.
What I can't seem to do is to remove ...
4
votes
1answer
341 views
will replication from 5.5.20 to 5.0.XX server work?
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?
Because I remember when I transferred ...
4
votes
2answers
1k views
Scaling Percona datacenters: setup and replication
Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.
We have already scaled alot of our application stack: we offloaded ...
4
votes
2answers
5k views
How do you remove fragmentation from InnoDB tables?
I have a Database having number of tables.
I want to delete some records from the tables say the no of records are more than 20K or 50K.
The All the Tables are InnoDB. And file_per_table is off.
...
4
votes
2answers
4k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
4
votes
2answers
8k views
Grant table privileges to a user connecting from any host
I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
4
votes
1answer
789 views
Setting up MySQL Cluster on Two Systems
I am attempting to setup a MySQL Cluster.
I have the folllowing setup
one management node
two data nodes
another machine on which two more nodes reside
Below is the config.ini file:
[ndbd ...
3
votes
3answers
473 views
How costly is opening and closing of a DB connection?
How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL
... when the DB software is on localhost?
... when the DB software is on another machine?
3
votes
3answers
85 views
Issue after moving the ib_logfile1 and ib_logfile0 files
I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
3
votes
2answers
3k views
What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?
I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)
I ...
3
votes
2answers
215 views
How to split a string to and get info from another table in mysql
I have a system to assign score for each sentence stored in a column:
TABLE 1: word_scores
word score
this 3
is 4
a 5
test 1
another 0
sentence 8
TABLE 2: ...
3
votes
1answer
55 views
where varcharcol = 0 returns all rows
Suppose I have a table like this:
create table mytable(cola int primary key, colb varchar(10));
Let's put some data in it:
insert into mytable values(1, 'abc');
insert into mytable values(2, ...
3
votes
1answer
1k views
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between ...
3
votes
2answers
692 views
MySQL replication to slave having VPN connection to Master
I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
3answers
269 views
InnoDB insertion faster
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
3
votes
1answer
381 views
Enabling “log_queries_not_using_indexes” disables “long_query_time”?
I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far ...
3
votes
3answers
461 views
What are the limits on the amount of slaves per master?
I currently have a Master with 2 slaves, all running MySql 5.5.
What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?
3
votes
1answer
3k views
How to setup replication(Master/slave) in MySQL 5.5.20?
I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine.
I tried following this tutorial (I used ...
3
votes
1answer
676 views
MySQL 5.5 InnoDB = OFF?
We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine.
However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
3
votes
2answers
557 views
Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?
My Hardware Spec
Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 ...
3
votes
4answers
245 views
Stress Test to Saturate Memory for MySQL InnoDB
I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
3
votes
1answer
83 views
Backup and off-site restore setup
I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one.
Our current situation is ~100 GB of InnoDB and MySAM data in ...
3
votes
2answers
463 views
Is it bad to create many mysql temporary tables simultaneously?
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
1answer
124 views
Get profiles values where message.sender_user_id = profile.user_id
I have this tables:
default_messages
CREATE TABLE IF NOT EXISTS `default_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`message` text ...
3
votes
3answers
3k views
How to Add Federated engine after installing Mysql
I have Mysql 5.5.18
and upon show engines:
show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine ...
3
votes
1answer
85 views
How this simple Stored Procedure ended up in Deadlock
Thanks for all and this forum is really helpful.
After few minutes of system running we get “Try restarting the transaction.”
And its end up in deadlock.
We use MySQL 5.5.28 and the Java code ...
3
votes
1answer
122 views
Query not using index when it should
I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table?
...
3
votes
2answers
151 views
How to concatenate a column after x rows?
I have a table with columns
object varchar(255),
frequency tinyint(3) unsigned,
I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as
.. ...
3
votes
1answer
67 views
how to add removed partition?
This is the structure of my table:
-- Table: t_keyword_conversion_ga
-- Create Table:
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT ...
3
votes
1answer
827 views
Slow insert performance in mysql innodb table for solr indexing
I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.
...
3
votes
1answer
423 views
Keeping MySQL staging database on same server updated from production
I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
3
votes
1answer
670 views
What does the base64 BINLOG statements in mysqlbinlog output mean?
I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0.
When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
