Tag Info

New answers tagged

0

I think bit vector mapping may be a solution to optmize the performance of the cpu and you can easily utilize your cpu at 98-99% the processor get damaged for sometime and simply bit vector mapping will reduce the time consumption to load the page in frontend


0

Baron Schwartz, the author of that post, is one of the coauthors of High Performance MySQL, 3rd Edition, one of the best books out there regarding MySQL performance. While the argument of authority is not always a good one, I would like to remark that probably he knows what he is saying. While everything he says is correct -in my humble opinion-, you must ...


0

Since the column's data types have very short range, means that each column will have reduced selectivity. If you would have 1.000.000 rows, then filtering by age, for example, would still retrieve ~20.000 rows ( cosidering an age interval of 50 years ) So you need to create a composite index where you would add some of the most used columns, starting with ...


0

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially: The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the ...


-1

Your InnoDB tables are definitely have errors, but it seems to be that the reason why mysql can't start is lack of memory. It's from your error log: key_buffer_size=33554432 read_buffer_size=134217728 max_used_connections=0 max_threads=5000 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + ...


1

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE: If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a ...


0

Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row In that answer, I recommended the following Adding a third column to the table to represent a slot Select an arbitrary value for the number of slots Making a composite PRIMARY KEY hour boundary slot number Doing an INSERT with ON ...


1

You have a big problem for one reason: BTree indexes (which are the only format available for InnoDB) are highly inefficient for filtering on more than 1 range. There is one thing that you must understand: in general, using more than 1 index per table access is not possible/efficient (there are some cases where union_merge is faster, but that is an ...


0

If I have followed correctly, the two options is whether to have one or two intermediate table for the relationship between groups, user_types and resources. The answer, from a purely design point of view depends on whether that relationship is a strong or weak one. In other words, if groups_user_types (lets call it authorizations) is something that should ...


2

Answering one by one: Performance: yes, this is the main problem. Not only in resource for file descriptors, but also for the mysql open table cache and data dictionary, InnoDB checks on start and regenerating table statistics when opened. To be fair, these last ones (except InnoDB checks) will be found no matter the value of innodb_file_per_table, only ...


1

Setting ft_min_word_len only affects MyISAM. You need to set innodb_ft_min_token_size to 1 since the default is 3. Once you set innodb_ft_min_token_size to 1, go back and do ALTER TABLE addresses DROP INDEX address_index CREATE FULLTEXT INDEX address_index ON addresses(street); Give it a Try !!!


1

1) To get a single table restored and/or imported, use mysqldump. See the "how" links in my post here. 2) For a hot copy of InnoDB tables, use Perconas XtraBackup tool (BTW, the Percona website generally and their mysqlperformance blog are excellent MySQL resources). You can also replicate from InnoDB masters to MyISAM slaves and the other way round ...


0

You could try to do the following: SELECT rows_changed FROM information_schema.table_statistics WHERE table_schema = 'mydb' AND table_name='mytable'; This returns a number that increases with each table update, keeping track of it will allow to to detect change. Important note: the value is changed immediately after an UPDATE, not after COMMIT. So you ...


1

Login to mysql and run this SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size'; or this SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_log_file_size'; This will give you the size mysqld has. The default for MySQL 5.5 is 5M. Whatever number that turns up should be placed in my.cnf. If it is not set, then ...


0

There is a reason you cannot touch the innodb database. This is where Amazon stores the system tablespace file ibdata1 If you run SHOW GLOBAL VARIABLES LIKE 'innodb_data_home_dir'; you might see something like this /rdsdbdata/db/innodb It will point to this folder as the base of InnoDB. If you run SHOW GLOBAL VARIABLES LIKE ...


0

Hmm, regardless of whether it's safe to delete the innodb schema or not, RDS won't let you do it anyway (tested on a temporary test instance): DROP DATABASE `innodb`; Error dropping database (can't rmdir './innodb/', errno: 17)


0

Another answer that I can think of, if you have enough space on disk, is to do the pre-processing outside of the database. You can use a large-file comparison tool that do not require the files to be on memory (or even program your own quick script, I do not see it too complicated) and then generate a patch-like syntax, so you can, from that, generate a ...


1

Just to bring InnoDB some contention relief, you could play with innodb_purge_threads. Before MySQL 5.6, the Master Thread did all the page flushing. In MySQL 5.6, a separate thread can handle it. The default value for innodb_purge_threads in MySQL 5.5 was 0 with a maximum of 1. In MySQL 5.6, the default is 1 with a maximum of 32. What does setting ...


1

Given the clarifications on your comment, I would recommend 2 options: Use LOAD DATA INFILE IGNORE to load the data directly from the filesystem. This will insert new domains and not touch the "old" ones, but it will not delete the ones that are removed. On the bright side, it will reduce IO a lot. Go for your approach: use LOAD DATA INFILE on a new table, ...


4

Let me maybe try and describe the historical problem with log flushing and how adaptive flushing works: The redo logs are a ring buffer design. They are only ever written to (never read from in normal operation) and provide in crash recovery. I like to describe a ring buffer as similar to the tread of a tank. InnoDB will not be able to overwrite log file ...


1

Hi as per your requirement try below query with out dynamic query SELECT Table_name AS TablesInDatabase ,table_rows AS NumberOfRows FROM information_schema.tables WHERE Table_schema=DATABASE();


1

I have a very aggressive approach using brute force Dynamic SQL SET group_concat_max_len = 1024 * 1024 * 100; SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database, COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;') INTO @sql FROM (SELECT table_schema db,table_name tb FROM ...


0

I addressed this issue 1.5 years ago : innodb_file_format Barracuda Just from reading the documentation, I was able to figure out mathematically that the smaller you make the key_block_size, the less extra space you need to add to the InnoDB Buffer Pool. Here is the chart I made up in that earlier post You have a DB Server with a 8G Buffer Pool You ran ...


1

Sample in Windows I ran the following on my Laptop in MySQL 5.6.15 mysql> use test Database changed mysql> drop table if exists nums_composite; Query OK, 0 rows affected (0.70 sec) mysql> create table nums_composite ( -> id int, happened datetime not null, -> primary key (id, happened) -> ) engine=InnoDB -> partition ...


4

A change in the 5.6.20 release notes: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation. As a ...


2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database). I dropped all indexes but the PRIMARY KEY, and inserted this new index: ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index ...


1

You can go to the OS and drop the file because the data dictionary in the system tablespace (ibdata1) does not know of the .ibd file's existence any more. I wrote a post 2 months ago about deleting temporary .ibd files : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave. It applies in your case as well. Give it a Try !!!


1

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game. I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring): Total memory allocated 26217103360; in additional pool ...


4

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them ...


1

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case. Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log. ...


-1

Your tables do have several indexes to update, did you remember to run a optimize table from time to time? /usr/bin/mysqlcheck -o --auto-repair MyDatabaseName This will optimize all tables and indexes in the Database MyDatabaseName. Depending on how your data looks this might help with INSERT and SELECT speed. I guess since you have tried everything you ...


1

That is 100% normal: what you are seeing is the background threads performing the following operations: Flushing dirty pages from the buffer pool into the tablespace files. You can see that is happening on your case due to the difference between your LSN and the latest checkpoint on the SHOW ENGINE INNODB STATUS. Merging non-unique secondary indexes ...


0

If you are using InnoDB with the option innodb_file_per_table option off (by default on MySQL 5.1 and before), you need to stop the server after the dump (make sure you are not using innodb_fast_shutdown = 2), then delete at least the ibdata1, ib_logfile0, ib_logfile1 and your database directory and finally restart MySQL and import only the final truncated ...


0

I've had the exact same problem only having the files as backup. What i did to solve it was to copy the database files into /var/lib/mysql/yourdb and the ibdata1 which is placed in /var/lib/mysql. I was then able to verify that i could access the tables mysql -u root -p dbname and the querying some of the tables that were previous corrupted. I made a ...


-1

After an upgrade you need to run "mysql_upgrade -u root" and this will handle all the system table updates.


2

I would choose Option 2 If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up. Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant. With ...


1

These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted. If you execute: UPDATE parent SET id = -1 WHERE id = 1; And there is at least one record on child with parent_id = 1, 1) will fail; in cases 2) and 3), all records with parent_id = 1 are updated to parent_id = -1. ...


2

1) means that if the parent is deleted, the child is also deleted (not a good idea IMHO - you should keep track of all data that's ever been in a database), although this can be done using triggers. You could also SET NULL. 2) means that if the parent primary key is changed, the child value will also change to reflect that - again IMHO, not a great idea. If ...


0

Per the Cold Backup section of the MySQL Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html), you need ibdata1, ib_logfiles. Obviously, this requires that you stop the server and flush all caches to disk.


3

Please tell me you are not (and not even thinking) of using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-( It's not very clear from your post (correct me if I'm ...


5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


1

Removing foreign keys does not damage data because you are doing DDL to the indexes. Once you do that, data integrity (even for existing data) going down the road needs its integrity tested. EXAMPLE create table parent ( id int not null auto_increment, ... primary key (id) ); create table child ( id int not null auto_increment, fk_id ...


1

Actually I guess the answer is in your boss's words: 3.He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them. FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, ...


4

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


4

With innodb_flush_log_at_trx_commit = 0 there is not a guaranteed data loss (such an option would be nonsense), but a non-guarantee that data will not be lost in the last innodb_flush_log_at_timeout seconds (between fsyncs). To test how much data you are losing, you need to write several times per second and then kill mysqld just after some of those writes ...


0

The extra predicate wont mess up the indexing. Most likely your index will be evaluated first and any rows that are found will be evaluated against the predicate cityid = 100. On the other hand, assuming your index is defined as (usergroupid, birthdate). If you remove usergroupid = 54 from your query, the index won't do any good and you will have to ...



Top 50 recent answers are included