0
votes
0answers
7 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
0
votes
0answers
24 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
16 views

Importing Data with Single .ibd file

I have one ".ibd" file with me. I have configured my local Database server with FILE_PER_TABLE option. i have two question here. When i was trying to restore/import the IBD file, the engine expect ...
0
votes
1answer
23 views

MySQL my.cnf: innodb_log_file_size is missing

How to set innodb_log_file_size to MySQL database if there is no innodb_log_file_size in my.cnf configuration file? I'd like to backup via Percona Xtrabackup and the backup crashes at this point ...
2
votes
2answers
48 views

How do you update a large MySQL table that's on a server?

I have an application on a web server. The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this ...
1
vote
2answers
56 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 ...
3
votes
1answer
126 views

MySQL RDS Instance eating up memory and swapping

I have an issue with memory on a RDS instance which is of db.m2.4xlarge class. Below are the specifications of the RDS instance Instance Class: db.m2.4xlarge ( 68.4 GB of RAM ) Engine: ...
0
votes
1answer
28 views

Is it possible to restore a database snapshot from a 64 bit system to a 32 bit one?

I would like to perform a restoration test on a snapshot backup of my MariaDB database using a test server running on a 32-bit OS. My production server is a 64-bit system. I would like to know if the ...
0
votes
1answer
28 views

How can I tell when InnoDB recovery is complete/check its progress?

I'm writing a process to bring up a new instance from our nightly database snapshots, and I want to watch its progress and perform some actions once the database is up. When mysql first comes up, it ...
1
vote
2answers
73 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
2
votes
2answers
120 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
1
vote
1answer
53 views

Safely changing innodb_buffer_pool_size on MySQL replication master?

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown. What is the ...
0
votes
1answer
88 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
0
votes
0answers
22 views

How are performances with InnoDB auto-extending data file

Our innoDB tablespace is configured like this: innodb_data_file_path = ibdata00:40G;ibdata01:40G;ibdata02:100M:autoextend If I take a look to the directory: [root@xxx innodb]$ du -hs ...
0
votes
0answers
20 views

How can I determine the performance (rows/second) for a certain query on one of my tables?

I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records. The table has around 10 million rows. ...
0
votes
2answers
93 views

Buffer pool above 90% utilized causes MySQL to crash

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner? EDIT I'm using Rolando's script to identify how full the innodb_buffer_pool is. SELECT ...
1
vote
2answers
47 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?
1
vote
1answer
206 views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
3answers
109 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
1
vote
2answers
268 views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
0
votes
0answers
23 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
1
vote
2answers
85 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
0
votes
1answer
47 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
2
votes
2answers
34 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
3
votes
2answers
269 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
0
votes
1answer
30 views

Check whether MySQL Innodb Rows are compressed

After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?
0
votes
0answers
60 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
0answers
64 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
1
vote
1answer
294 views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
1
vote
1answer
206 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 - ...
1
vote
1answer
1k views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` ...
2
votes
2answers
98 views

How to store logs correctly?

(This is somewhat a follow up on my last question) I am using MySQL 5.5 with innodb. My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request ...
5
votes
1answer
5k 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. ...
1
vote
1answer
576 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
3answers
543 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
0answers
39 views

Is there any way to set server-wide and schema-wide default row formats for InnoDB tables in MySQL?

Is there a way to set server-wide and database-wide default row formats? Currently it seems that I must specify ROW_FORMAT in CREATE TABLE or ALTER TABLE statements. Am I reading the documentation ...
1
vote
0answers
468 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
0
votes
0answers
28 views

Accessing Database from Slave server

I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost. ...
5
votes
1answer
672 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
1
vote
0answers
334 views

Incorrect information in .frm file after a power outage?

CentOS release 6.3 (Final) x86_64 mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 SHOW SLAVE STATUS\G: Slave_IO_Running: Yes Slave_SQL_Running: No ...
1
vote
0answers
418 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
2
votes
1answer
2k views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
3
votes
1answer
268 views

MySQL / Fusion IO Configuration Question

I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement. Current Config [mysqld] user=mysql datadir=/var/lib/mysql ...
1
vote
3answers
782 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
2
votes
2answers
3k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
2
votes
2answers
6k 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 ...
0
votes
2answers
285 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
1
vote
1answer
893 views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
0
votes
1answer
96 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
1
vote
2answers
496 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...