Tag Info

New answers tagged

0

It used to be that a high transaction rate in InnoDB would stall periodically. The TPS would drop to zero for a few seconds, and there was nothing that anyone could do about it. Percona came along, figured out what was causing it and put out a version of MySQL that fixed the problem. (It had something to do with the dirty pages in the buffer_pool getting ...


0

RAID-5 controller with a battery-backed write cache is excellent. Writes appear (to MySQL) to be instantaneous. When comparing RAID-5 to -10, you need to decide whether you are comparing N drives to N drives or comparing M GB to M GB. N drives: RAID-10 scatters its reads across N drives; So does RAID-5. Writes hit 2 drives in either case, but the ...


0

Thanks @mysql_user and @oNare for your answers and I do it: Fields terminated by: , Fields enclosed by: " Lines terminated by: \r\n When I tried to do that with MySQL Workbench 6.3 and Table Data Import Wizard, I couldn't believe it because it is so slow. And I tried to do it with HeidiSQL and is faster than MySQL Workbench but no enough. My CSV is a big ...


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 ...


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

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 ...


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. ...


0

When you have disable innodb, dont' forget to remove all innodb-related tables from Dbs. For example "mysql" database has 5 of them: root@ustimen.co$ mysql mysql -e 'show table status' | grep Unknown | awk '{print $1}' innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info


0

To answer your storing NULL question: Storing NULL Values / Performance When setting up a historical model like such, you really need to pay attention to what the historical data will be used for...as in, will I need this data for historical reporting, trivial lookups, metrics / analysis, corporate data compliance (the list goes on)? If there is no need ...


0

I would be a little concerned. When you run COMMIT; you should get this mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> If the prompt has not returned yet, run this SHOW PROCESSLIST; just to see if any queries are processing. Notwithstanding, MySQL could be still processing things. How ? Look at the InnoDB Architecture (Picture made ...


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 ...


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 ...


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 ...


0

Rewrite the query as: select * from media me where performed_by='worker_id' and exists ( select 1 from media_resources_used au where au.media_id=me.media_id ); Can you please check if this is running any better?


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 ...


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 ...


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 ...


0

Although foreign key names can be anything, it's actually a good practice to follow the convention of putting the table name first. The most important reason for this is that foreign key names must be globally unique (contrarily to index names). So, by following this convention, foreign key names only have to be unique within each table. Personally, I use ...


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 ...


0

Other alternative's are: SELECT DISTINCT l.a, l.b FROM links l LEFT JOIN t_i ON t_i.i_id IN (l.a, l.b) LEFT JOIN t_o ON t_o.o_id IN (l.a, l.b) WHERE COALESCE(t_i.i_id, t_o.o_id) IS NOT NULL; SELECT l.a, l.b FROM links l WHERE EXISTS ( SELECT 1 FROM t_i WHERE t_i.i_id IN (l.a, l.b) UNION ALL SELECT 1 FROM t_o WHERE ...


0

As I understand you want to check each combination of a/b and i_id/o_id then simple rewrite to join is like this: SELECT a, b FROM links JOIN t_i ON (a = i_id OR b = i_id) WHERE id = x UNION SELECT a, b FROM links JOIN t_o ON (a = o_id OR b = o_id) WHERE id = x But for good index usage I suggest splitting it once more. It is even longer ...


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 ...


0

If you are using InnoDB FULLTEXT indexes, queries will often hang in the "FULLTEXT initialization" state if you are querying against a table that has a large number of deleted rows. In InnoDB's FULLTEXT implementation, deleted rows are not pruned until a subsequent OPTIMIZE operation is run against the affected table. See: ...


0

InnoDB: Unable to lock ./ibdata1, error: 11 Perhaps another copy of mysqld is still running? Only run it as a service.


0

10 GB of ram -- nothing is threatening to hit this. open_files_limit=10000 -- Try 2000 query_cache_size=256M -- No more than 50M default-storage-engine=MyISAM -- Unless you already have some InnoDB tables, this means that innodb_buffer_pool_size may as well be 0 -- you aren't using it. So, I will guess one of this is the case: 32-bit OS 32-bit build of ...


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 ...


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 ...


0

I'm guessing a bit on what your query is supposed to do. I assume you are looking for all users that share 5 or more items with a certain user: SELECT a.userid FROM ratings AS a WHERE EXISTS ( SELECT 1 FROM ratings AS b WHERE b.userid = :user AND a.userid <> b.userid AND a.showid = b.showid ) GROUP BY a.userid HAVING ...


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 ...



Top 50 recent answers are included