Tag Info

Hot answers tagged

3

First, a query or (update) statement with a condition like WHERE posts < comments that compares 2 columns cannot effectively use your indexes so it will probably have to do a full table scan. It might be better if you had a composite index, (posts, comments) or the other way around, but it would still need to do a full index scan. If the rows to be ...


3

We were seeing the same issue on two servers on versions 5.6.12 and 5.6.16 running on Windows, with a pair of slaves each. We were stumped, like you, for almost two months. Solution: set global binlog_order_commits = 0; See https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_order_commits for details of the variable. ...


3

In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format. In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example, InnoDB: Page may be an index page where index id ...


2

It makes a lot of sense to me. The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses. Look at the first query select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ...


2

The EXPLAIN plan for SELECT * and SELECT id, which you said were identical, would be separated by the number of rows accessed. How are the rows being accessed ? Through the index_things_on_active_and_date index. The SIMPLE in the EXPLAIN means it is a scan. In both cases, it is an index scan based on active=1 and date < '2015-07-11 00:00:00' How does ...


2

The root cause is the storage engine. InnoDB does not update the information_schema.tables.table_rows column. I have old posts that explain this further Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...? May 16, 2012 : Why doesn't InnoDB store the row count? Mar 03, 2014 : The most efficient method to obtain ...


2

You may need to tune your writes for InnoDB and possibly your ext4 volume ASPECT #1 I noticed you have innodb_write_io_threads set to 4 (default value). You need to increase that so dirty pages can get flushed to their respective .ibd files more robustly. Please set it 16. ASPECT #2 That pause to perform write might be do to ext4. Why ? You have ...


2

Root Cause foreign_key_checks is not a global option you can preset at startup. Why ? When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart ft_boolean_syntax says Variable Scope Global ; Dynamic Variable Yes innodb_doublewrite says ...


1

As per documentation your error is: Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition ...


1

Yes, that would be a good idea. I have mentioned a FaceBook Engineer's Blog about doing that, along with putting the DoubleWrite Buffer and Tablespace Files in other places, in my other posts Feb 24, 2012 : Tuning dedicated Percona Server with XtraDB for OLTP (Also See Switching Gears TO RAID) Feb 06, 2014 : MySQL on SSD - what are the disadvantages? Jan ...


1

Same scenario here. I have been unable to find a definition of Lock_time in the slow queries log, but this post provides a definition consistent with the situation: the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. I found ...


1

First have a look at the InnoDB Architecture (from Percona CTO Vadim Tkachenko) Your problem is very simple. You have a broken data dictionary entry. Inside the system tablespace file ibdata1 is a list of tablespace ids. There is an entry for appointments.business_users. Deleting the .frm and .ibd of appointments.business_users does not remove its ...


1

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked. Both transactions are locking space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE The DB Connections 12287 and 12279 ran one INSERT each Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE ...


1

For it worth : I"ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes. The optimize reduced the table sizes by 90% and improved the system performance by more than twice !


1

Here's some explanation of which index you could use and when: First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes ...


1

Thanks to Rolando, his answer works fine. I also created a stored procedure to get that information, i'll post it if it's useful for somebody: DROP PROCEDURE IF EXISTS mysql.getRowCount; DELIMITER // CREATE PROCEDURE mysql.getRowCount(DBNAME VARCHAR(30)) BEGIN DECLARE NO_DATA int default 0; DECLARE TBNAME varchar(30); DECLARE TBS ...


1

Given the above table description, my first order of business would be to get rid of the duplicate index for three reasons: it would slow down inserts needlessly bloat the table to about triple the amount on index info would render either the PRIMARY KEY or the code index useless in EXPLAIN plans NOTE : A duplicate index is an index that has the same ...


1

I am not totally convinced MySQL is running out of memory. Here is why: ASPECT #1 When you use mysqltuner.pl, it calculates the total usage in the worst case scenario. It does it by adding 3 figures together key_buffer_size innodb_buffer_pool_size thread_memory : max_connections * the sum of sort_buffer_size join_buffer_size read_buffer_size ...


1

Backing up a step -- to answer the question behind the question: How many rows in the table? PARTITIONing is rarely of any benefit on tables with fewer than a million rows. If your goal is to make use of multiple drives, RAID striping is better and any manual spreading of data between drives.


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers later, things will get confusing fast. A nice set of instructions in a stored procedure or client side procedure can get across the business logic more clearly than a bunch of hidden logic in the database. There are also limitations ...


1

Yes you can use innodb_file_per_table = 1 for your slaves. Additionally take below points in your consideration If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding innodb_file_per_table = 1 on slave in my.cnf Please have a look at How to set Master Slave ...



Only top voted, non community-wiki answers of a minimum length are eligible