Questions tagged [mysql-5.5]
MySQL version 5.5 - please also tag with mysql for search purposes.
181
questions
142
votes
5
answers
254k
views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/...
- 70k
29
votes
4
answers
17k
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 ...
- 4,289
55
votes
3
answers
73k
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,541
10
votes
1
answer
8k
views
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
my.cnf (5.5 and 5.1 is the same) :
back_log=200
max_connections=512
max_connect_errors=999999
key_buffer=512M
max_allowed_packet=8M
table_cache=512
sort_buffer=8M
read_buffer_size=8M
thread_cache=8
...
- 251
18
votes
4
answers
25k
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?
- 615
6
votes
2
answers
10k
views
Query runs a long time in some newer MySQL versions
I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
- 205
4
votes
3
answers
2k
views
mysql replication master 5.5 slave 5.1 error on create database
I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
- 155
32
votes
3
answers
66k
views
innodb_file_format Barracuda
I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda.
I was looking to play around with some compresses innodb ...
- 5,273
9
votes
3
answers
15k
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 ...
- 1,425
45
votes
2
answers
114k
views
InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup
I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,...
- 1,407
14
votes
2
answers
38k
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.
...
- 9,717
3
votes
1
answer
9k
views
MySQL service stops after trying to grant privileges to a user
I am using MySQL 5.6, I was trying to create a super user for specific database but I got
ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%'
Then I thought maybe there is already ...
- 195
84
votes
8
answers
255k
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 ...
- 1,367
7
votes
2
answers
5k
views
MySql autoincrement counter resetting after server restart [duplicate]
I need a way to keep auto-increment counter between mysql service restart.
As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
- 265
7
votes
1
answer
8k
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 ...
- 652
40
votes
1
answer
57k
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 ...
- 503
6
votes
2
answers
5k
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 ...
- 300
102
votes
3
answers
255k
views
Difference between On Delete Cascade & On Update Cascade in mysql
I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
- 1,131
18
votes
2
answers
3k
views
What can we do in MySQL 5.0 Replication to address bandwidth concerns?
I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
- 283
8
votes
2
answers
10k
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 (...
- 4,541
39
votes
2
answers
102k
views
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL? I have read the online documentation but not sure what the difference is.
- 631
33
votes
5
answers
99k
views
How to properly kill MySQL?
I have CentOS 64bit with CPanel installed and I use:
service mysql stop
It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of:
130303 17:42:38 [Warning] /...
Tiffany Walker
8
votes
2
answers
4k
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 ...
- 183
5
votes
1
answer
1k
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 ...
- 565
4
votes
2
answers
24k
views
MySQL Event does not run
i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
- 185
21
votes
3
answers
66k
views
What max_allowed_packet is big enough, and why do I need to change it?
I have MySQL (5.5) in master-slave setup and created another slave server.
I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the ...
- 471
9
votes
2
answers
34k
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,563
9
votes
3
answers
19k
views
InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)
This server is a virtual machine running CentOS 6.0, MySQL 5.5.21
There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 (HY000):...
5
votes
1
answer
10k
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 ...
- 565
2
votes
1
answer
4k
views
MySQL with high CPU and memory usage
here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%)...
- 183
1
vote
2
answers
1k
views
mysql master slave replication scenario
We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take ...
- 367
1
vote
1
answer
1k
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 ...
- 113
13
votes
4
answers
8k
views
MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preface
Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
- 233
10
votes
4
answers
26k
views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
- 285
6
votes
1
answer
7k
views
MySQL Slave Relay Logging but not logging Binary Log
I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
- 63
6
votes
4
answers
43k
views
MySQL using too much CPU
I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:
CentOS 5.9
24gb Ram
8 ...
- 197
6
votes
1
answer
4k
views
Replicate MySQL 5.0 Master to 5.5 Slave?
We're planning out a series of server/software upgrades and were curious.
We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and ...
- 759
5
votes
1
answer
20k
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 the ...
- 327
4
votes
2
answers
7k
views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
- 161
3
votes
1
answer
3k
views
How can I determine which Innodb table is being written?
I am running Mysql 5.5.
I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
- 191
2
votes
1
answer
573
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 ...
- 175
2
votes
1
answer
18k
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 ...
- 1,425
1
vote
1
answer
2k
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 ...
- 169
19
votes
6
answers
55k
views
mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
I am following this solution here https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
- 1,407
18
votes
2
answers
28k
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 ...
- 241
11
votes
1
answer
11k
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 ...
- 111
10
votes
2
answers
11k
views
Why are mysql password hashes internally saved with a star (asterisk)?
I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get
mysql> select * from mysql.user limit 1 \G
*************************** 1. row *********...
- 347
9
votes
2
answers
28k
views
Set sql_mode “blank” after upgrading to MySQL 5.6
After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting.
...
- 163
7
votes
2
answers
31k
views
MySQL: Row size too large (> 8126)
I have a table which has 300 columns in it. Each column is up to 255 bytes (there is a business justification for this).
When I try it create it using VARCHAR(255), I go past the limit for the ...
- 171
5
votes
3
answers
10k
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?
- 1,563