Tag Info

New answers tagged

1

I just noticed that you are at the Windows Command Line. You are trying to launch MySQL. You cannot start MySQL because you are not an Administrator. Please Open CMD.EXE as an Administrator. Then, you can run net start MySQL56_1 mysqld --console According to the latest update, mysqld is running just fine. That's because it says mysqld: ready for ...


0

The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions. One of those conditions is when you also specify the --master-data option. In the source code, from mysql-5.6.19/client/mysqldump.c at line 5797: if ((opt_lock_all_tables || opt_master_data || ...


2

Assuming you have run innobackupex --apply-log successfully you either have a configuration problem (probably innodb_log_files_in_group is 1 in the original and 2 -or not set- in the restored server) or you lost ib_logfile1 somewhere in the process. InnoDB is ok, it is normal for it to fail when it detects a different configuration on disk than on the file. ...


1

The right way to get MySQL status regarding recovery is monitoring its error log. There are 3 phases on InnoDB recovery: Checking partially written pages (very fast) REDOing committed and uncommitted transactions written to the transaction log. This can take some time, but its time is IO bound (as fast as reading/writing to the transaction log). If you are ...


1

The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK;. It causes mysqldump to setup a repeatable read transaction for all tables being dumped. From your question, you stated that the mysqldump's SELECT for the db_external_notification table is holding up hundreds of INSERT command to that same table. Why is this happening ? ...


0

Answering to your MyISAm vs InnoDB question: You should definitively use InnoDB everywhere is possible. Only use MyISAM where you need it. Never tried but maybe it's ok for you to use MySQL 5.6 which allows you to use full text search with InnoDB. If you are using InnoDB only you can fit with your data in buffer pool (memory), which will speed up ...


0

I can't answer all of your questions, but I am in the middle of reading "High Performance SQL" (2013) by Baron Schwartz, Peter Zaitsev and Vadim Tkachenko (v. big hitters in the MySQL world), and they say "MySQL’s GIS support isn’t great, so most people don use it. The go-to solution for GIS in an open source RDBMS is PostGIS in PostgreSQL". Although I have ...


1

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data. Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities. Go into your command/terminal to open the MySQL utility, ...


0

You could try my solution to this issue - MySQLDumpSplitter. It takes a dump file and splits it into individual tables, which you may find convenient. My email is there should it not conform to your needs - I could take a look at any issues you may have in an effort to make the tool better. Disclaimer: I wrote it, and as you'll see from the Readme, it works ...


1

The main InnoDB data file - usually named ibdata - is essential for you MySQL to be able to understand your .ibd files. If you need to move data between servers using the binary files, you should stop MySQL cleanly and then move all data files, including the ibdata file(s), between directories. A more reliable mechanism of moving data between servers on ...


3

Here's a method you can use without dumping all your InnoDB tables. You will have to drop the foreign key constraints before you can alter the tables. You can generate the drop statements using the INFORMATION_SCHEMA this way: mysql> SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` DROP FOREIGN KEY `', constraint_name, '`;') AS ...


0

Not an expert, but what about this idea? https://rtcamp.com/tutorials/mysql/innodb-to-myisam/ Basically the data+SQLstructure is causing the problem. If you separate the data and the SQL maybe you can get around this issue. You can probably figure out how to filter out the tables you want if you don't want this done to the whole database.


1

Alan Storm wrote: At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables Indeed and may I just say that you have my deepest sympathies. This is a guess, so YMMV... but have you looked at your startup parameters for your mysqld daemon? In ...


1

If you want all tables to be MyISAM, here is a vicious way to make MySQL "suck it up and do the work" STEP #1 : mysqldump everything MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" MYSQLDUMP_OPTIONS="--routine --triggers --all-databases" mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql STEP #2 : Restart ...


0

SHOW ENGINE INNODB STATUS\G and see http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded increasing the lock timeout period could solve the issue. Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid. Just speculation.


1

InnoDB InnoDB can access multiple cores, but don't expect it to be that way out of the box. InnoDB must be configured to do so. Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian Sep 20, 2011 : Multi cores and MySQL Performance Sep 12, 2011 : Possible to make MySQL use more than one core? May 26, 2011 : About single threaded versus ...


2

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread. However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, ...


1

You should look at this. Mark Leith is a senior MySQL dev. manager. It's a way (MySQL are trying to get better...) that you can track some metrics - not per query like you want, but data access by table. Install the sys schema in your 5.6 instance and see what happens. Oh - yes - you were asking about InnodDB in 5.6 - you will have to enable ...


4

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run. Swapping should be a no-go for MySQL. Make sure to tune your ...


1

Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time. Additionally, MySQL may write to the mysql binary, general or slow log, ...


1

Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are the values you need to monitor for buffer reads and disk read for the Buffer Pool. What you are looking for is a cache miss rate of 1% or less based on this Innodb_buffer_pool_reads X 100 / Innodb_buffer_pool_read_requests I have written posts like this before Feb 04, 2014 : Tuning ...


1

This is a complex topic which is very interesting - I/O, caching - i.e. what's really happening? Percona always have good stuff about every aspect of MySQL performance - check here. Mark Leith (a senior MySQL devlelopment manager) is worth a look here. Be sure to follow the links to Baron Schwartz's and Brendan Gregg's stuff, both big hitters in the MySQL ...


1

The InnoDB dictionary contains information about all tables in your database(s). The dictionary is a set of InnoDB tables hidden from a user. Looks like a records about example_example/table1 is missing in the dictionary. I'd explore content of SYS_INDEXES and SYS_TABLES to see if the table is in it. https://twindb.com/how-to-recover-innodb-dictionary/


2

What you have seems fine. I would add the following Run this on the Master. SET GLOBAL innodb_fast_shutdown = 0; SET GLOBAL sync_binlog = 1; SET GLOBAL sync_master_info = 1; This will cause everything that has been uncommitted to be committed on shutdown. Then, it flushes the binlogs to disk. On the Slaves, run this SET GLOBAL innodb_fast_shutdown = ...


0

Testing with the current GA release MySQL 5.6.19 revealed that the issue is gone. Reading through the release notes of intermediate releases, I noted this particular note in 5.6.14 which I suspect to be related to our problem since it explicitly refers to subquery clauses which are in excessiveuse in our case: For some statements, memory leaks could ...


1

With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer. Increase it by setting it in ...


0

Refer to Johan's answer here, particularly the bit about covering indexes in InnoDB. My understanding of what he has written is that you should change your table definition to the following CREATE TABLE `t1` ( `pinc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `pinc_codigo` char(10) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`pinc_id`) ) ...


1

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats. The article you ...


1

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page. DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record. COMPRESSED is used for compressed tables. Hence its benefits.


0

The SHOW ENGINE INNODB STATUS \G command will show data from the last time the command was run till now - not sure what it will be the first time. I can't shut down the server here, but if you run the command rapidly twice in succession, you'll see the time since last run - set your mysql> pager less; mysql> show engine innodb status \G ...


2

InnoDB Architecture ANALYSIS Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table. You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped. Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ...


0

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables. #!/bin/bash USER="some_user" PASSWD="some_password" STAMP=$(date +"%d-%m-%y-%H:%M:%S") HOST="some_host" db="some_database" FILESTAMP=$(date +"%d%m%y") ...


0

This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options. I have written many posts about how and when to pick one storage engine over the other: Sep ...


2

this file ibdata1 also known as system tablespace, which consistent of undo log even if you have seperated tables into different files; from your process I found it is a really huge transaction, which will produce drampatic quantitiy of undo logs, till MySQL 5.5, undo log space can not be shrink or adjust automatically or manually. so there is a simple way ...



Top 50 recent answers are included