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 ...
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
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 ...
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 ...
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 ...
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
269 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
178 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 ...
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 ...
3
votes
3answers
479 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?
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
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
...
1
vote
2answers
299 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
834 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
220 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
570 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
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 ...
4
votes
1answer
343 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
477 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
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
96 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 ...
2
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
786 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
424 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 ...
2
votes
1answer
551 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 ...
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
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 ...
3
votes
2answers
216 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
199 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
676 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
121 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
860 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
1k 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
174 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
467 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
695 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
250 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 ...
2
votes
1answer
2k 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 ...
1
vote
1answer
48 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
257 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
178 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
595 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
59 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 ...
0
votes
3answers
48 views
Using MySQL InnoDB as an Archive
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
0
votes
2answers
208 views
How to restore mysql dump file? [closed]
We have created a dump file (.sql) using the command mysqldump in mysql5.5.
Now we want to restore the same. We are using the command :-
-u[username] -p[pwd] [dbname] < [.sql file]
But we are not ...
0
votes
1answer
704 views
Connect Error (2054) - mysql_old_password issue still not solved
i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way:
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
...
