MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
9
votes
3answers
11k 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 ...
4
votes
2answers
6k 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
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
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 ...
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 ...
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 ...
1
vote
1answer
294 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
6
votes
1answer
2k 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 ...
2
votes
1answer
182 views
mysql emits BINLOG rows even though binlog_format=STATEMENT
We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)
According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
3
votes
3answers
534 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?
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 ...
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 ...
5
votes
2answers
201 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
...
1
vote
2answers
310 views
Any problem will all InnoDB and one MyISAM table?
I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
9
votes
1answer
929 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 ...
2
votes
1answer
851 views
MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?
When reading online about innodb_log_file_size, it says
The combined size of the log files must be less than 4GB
So what happens if it needs to be larger?
In books and online, it says to set ...
2
votes
1answer
241 views
Configured MySQL replication but its not working
I have configured MySQL master and slave replication setup on 5.5.x.
But for some reason data is not getting replicated properly. What might be the reason?
At Master:
/etc/my.cnf
[mysqld]
...
2
votes
2answers
629 views
Can we assign mysql Key_buffer_size value for particular table?
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:
SET GLOBAL myisam_table.key_buffer_size = 4194304;
CACHE INDEX test.myisam_table ...
5
votes
1answer
4k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
4
votes
1answer
3k views
What can cause TRUNCATE TABLE to take a really long time?
I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves).
I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
4
votes
1answer
365 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 ...
3
votes
2answers
559 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 ...
1
vote
2answers
62 views
Tinyint structure is confusing
I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
1
vote
1answer
197 views
Database Design - Creating Multiple databases to avoid the headache of limit on table size
I need to design database to store information contained in millions of log files generated by devices on trial on network.
My first approach was storing all the information in tables contained in a ...
1
vote
1answer
2k views
Fixing MySQL errors recorded in error logs
Below is the extract of the most common lines of error being recorded in error logs on my PC:
110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled.
110905 16:16:54 InnoDB: The InnoDB memory heap is ...
0
votes
1answer
99 views
Create a mysql slave database from master database
I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
3
votes
3answers
4k views
mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
6
votes
2answers
817 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, ...
3
votes
1answer
433 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 ...
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
2answers
9k 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 ...
3
votes
2answers
245 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
209 views
How to Update same table on deletion in MYSQL?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
Following is query I used to create table:
CREATE TABLE IF NOT EXISTS `Employee` ...
3
votes
1answer
714 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 ...
2
votes
1answer
131 views
Restriction on self referencing on insert in MYSQL?
How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself).
mysql> SELECT * FROM Employee;
...
2
votes
1answer
873 views
How to upgrade MySQL to a New version..?
I have installed mysql using the package management system.I am using ubuntu machine.
After that i have very well configured the server for InnoDB and other server related parameters.
I am using the ...
1
vote
2answers
2k views
MySQL Error Reading Communication Packets
In MySQL error logs, I see these quite few a warnings like these:
120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
5
votes
2answers
177 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. ...
3
votes
1answer
1k views
Deleting bin logs in replication environment
I have a question about deleting binary logs in the replication environment:
We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
3
votes
3answers
514 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
2answers
758 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 ...
2
votes
2answers
293 views
How to GROUP_CONCAT between given rows?
I have a table as
id diary
1 breakfast
2 walk
3 start
4 office works
5 office projects
6 end
7 taxi
8 start
9 preparing for meeting
10 doing the meeting
11 end
12 night
I want ...
1
vote
1answer
28 views
Is there a way to find the least recently used tables in a schema?
Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
1
vote
1answer
253 views
Should I increase max_connections in AWS RDS t1-micro for MySQL?
I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
1
vote
1answer
60 views
MySQL localhost vs Amazon RDS instance
I am seeing unexpected behavior with some MySQL performance.
When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on ...
1
vote
5answers
395 views
How to properly kill MySQL?
I have CentOS 64bit with CPanel installed and I use
service mysql stop
However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
1
vote
1answer
103 views
Several values from different tables
I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
1
vote
1answer
617 views
I am getting an error while trying to download binary for mysql 5.5.11
I am trying to upgrade MySQL 5.1 to 5.5.11 on Ubuntu 10.10. Everytime I try the wget command I get this error:
wget ...
0
votes
1answer
114 views
In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table
On Windows, I upgraded from 5.1 to 5.5 no problem.
Copied my 5.1 data folder into my 5.5 instance
Started mysqld skipping grants
Ran mysql_upgrade
All good, but going from 5.5 to 5.6:
Copied ...
0
votes
2answers
64 views
Breaking Semisynchronous Replication in MySQL 5.5
I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7.
My application is running and updating the database of the master server and same is being updated in the ...
