75,965 reputation
1176173
bio website
location Jersey City, NJ (USA)
age 50
visits member for 4 years, 1 month
seen 2 hours ago

2d
comment Large ibdata file in MySQL
+1 for a course of action around undo log growth
Mar
26
comment RAID10 vs. RAID5 for data files
Reminds me of my old post (dba.stackexchange.com/questions/12977/…) but for MySQL. +1 for you and adding SSD to the mix.
Mar
24
comment DB disk space requirements comparision between MySQL versions
Thank you for accepting :-)
Mar
24
comment Slow database drop in mysql
How many tables do you have in the one database ?
Mar
23
comment Percona Master-Master lag issues
Are all your writes to DB-01 ?
Mar
23
comment Percona Master-Master lag issues
Please post all the options in my.cnf for DB-02
Mar
22
comment Large ibdata file in MySQL
Here is an experiment you really need to do: 1) Get another DB Server (Dev or Test), 2) Install MySQL on it, 3) mysqldump the schema and load the schema-only dump into it (no data). 4) run ls -l /var/lib/mysql/ibdata1 | awk '{print $5}'. If the number is near 53687091200 (50G), then you can suspect the data dictionary. I would then be incredibly shocked.
Mar
22
comment Large ibdata file in MySQL
The data dictionary has absolutely nothing to do with ibdata1 growth. I have worked with a client that 800 databases (168 tables / database) with innodb_file_per_table. That's 134,400 tables. The ibdata1 file never surpassed 1G. If no more tables were added and you enable innodb_read_only (dev.mysql.com/doc/refman/5.6/en/…), ibdata1 would never grow because the rollback segments and undo space would never be used. Therefore, the data dictionary has never been, or ever will be, an issue when it comes to file size growth.
Mar
21
comment How to tell MySQL to ignore all indexes for a given query?
@RickJames For InnoDB, you are right in that aspect. The query should do a full pass through the PrimaryKey/Clustered Index anyway, even with my OPTION #1. Your approach should be the same, just more work sculpting the index names into the SELECT using information_schema.
Mar
18
comment Automatically purging binary logs
Please run these two commands: 1) head -1 mysql56_bin.index 2) tail -1 mysql56_bin.index. What is output from each command ?
Mar
18
comment No records in Mysql.func table even though there are stored functions in server
The point is this: 1) If you wrote the stored function using MySQL Stored Procedure Language, it goes into mysql.proc using CREATE FUNCTION. 2) If you wrote the stored function in C/C++ and placed that compiled code in some .so file, you must use CREATE AGGREGATE FUNCTION ... SONAME ... and the function name goes into mysql.func.
Mar
13
comment MySQL 5.6 Slave Stops Processing Binlog on Master Restart
Not referring to the up and down arrows. There should be a circle with a checkmark in it under the down arrow. If you see it, please click on it. If you do not see, then the new UI has it missing.
Mar
13
comment MySQL 5.6 Slave Stops Processing Binlog on Master Restart
If my answer was helpful, please click the checkmark on my answer to mark it accepted.
Mar
13
comment Big cpu caused by mysql
Please run SELECT VERSION(); and tell us what version of MySQL you are running ???
Mar
13
comment Big cpu caused by mysql
For #1, go to my old post dba.stackexchange.com/questions/8661/…, scroll down to the bold title UPDATE 2015-01-16 14:46 EST look for the first query entitled Total MySQL Data and Index Usage By Storage Engine. Copy and paste that query to run it. I post the results in the question, please.
Mar
13
comment Big cpu caused by mysql
Never mind on #2. I see 4G in the picture you sent (4194304k total)
Mar
13
comment Big cpu caused by mysql
1) Is your data all InnoDB? 2) How much RAM on the DBServer? 3) How much data for you have?
Mar
13
comment Is a 5+ column primary key bad for large (100 million+) table?
Does the table have other indexes besides the PRIMARY KEY ?
Mar
13
comment MySQL 5.6 Slave Stops Processing Binlog on Master Restart
1) Are you using multi-threaded replication ? 2) Are you dumping multiple databases ?
Mar
12
comment Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows]
If my answer was helpful, please mark it accepted by clicking on the checkmark in the upper left corner of my answer.