New answers tagged

0

"Tablespaces" are for clumping tables into a single .ibd. The mysqldump options relating to that control whether to output the DDL statements such as CREATE TABLESPACE. mysqldump does not dump the contents of indexes, regardless of Engine. It does dump the definitions of the indexes. When reloading, the indexes will be recreated. Yes, MyISAM ...


1

Ok I feel really stupid now... Problem had nothing to do with MySQL Server settings etc. Problem was that in my service (that is communicating with MySQL) I'm using MySqlCommand Object (Service is coded using VB NET). This MySqlCommand object has a parameter CommandTimeout and its default value is 30. So that was the reason why executions longer than 30s ...


0

In case you want to check if a view has changed, you can check the most recent change among the tables involved in the view: select max(t.update_time) last_involved_table_update from view_table_usage vtu join tables t on (t.table_name=vtu.table_name and t.table_schema=vtu.table_schema) where vtu.view_name='name_of_view' and vtu.view_schema='...


1

You're worried about 3MB? er- don't. Even at 10x that, roughly your expected increase, it would be 30M. You aren't going to get saving of 30M of storage to counter any reliability failure. Use InnoDB.


0

For me, the problem was that I had previously enabled page compression on the table with: ALTER TABLE t1 COMPRESSION="zlib"; This prevented me from applying compressed row formatting: ALTER TABLE t1 ROW_FORMAT=COMPRESSED; .. resulting in the following error when I tried it: Error Code: 1031. Table storage engine for 't1' doesn't have this option. ...


1

Simply put, as the table (or index) grows, the performance approaches a minimum of one disk hit per lookup. This is because UUIDs (other than a version 1 UUID with the bits suitably shuffled) are effectively "random". That is whatever is in the buffer_pool cache is approaches being useless for the next lookup. You seem to have 3 such indexes; this ...


2

Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job. And: The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different ...


1

Most database systems won't even allow the syntax you wrote because it's a little non-sensical from the database engine's perspective. But if I understand you correctly, essentially you want the row with the MAX(date_res) for each sensor. The way to accomplish this might vary depending on which database system you're using, so you should always tag your ...


0

If you erased all the binary logs, the mysql-bin.index can also go. Do you still need binary logging ? You must restart MariaDB, and the mysqld process will recreate mysql-bin.index it for you. If you no longer want binary logging, you must comment out log-bin from the MariaDB config file. You still need to restart MariaDB.


0

(Rolando's answer addresses ways for that file to grow; I'll address the question of whether tables are in ibdata1 and you don't realize it.) Was this always set? innodb_file_per_table=ON -- I ask because it only applies to tables that were created after it is turned ON. Do SHOW TABLE STATUS in each directory. Observe Data_free. Some likely values you ...


1

The first thought that came to mind was "Wow !!! You must have rather big transactions." According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the main issues that cause ibdata1 to grow: Lots of Transactional Changes Very Long Transactions Lagging Purge Thread The undo logs inside ibdata1 will hold lots of ...


3

There are several ways to delete 'most' of a table. Perhaps the best is SET @@innodb_file_per_table = ON; -- if this is not already on (optional) CREATE TABLE new LIKE real; INSERT INTO new SELECT * FROM real WHERE ... -- the rows you want to _keep_; RENAME TABLE real TO old, new TO real; DROP TABLE old; More techiques: http://...


2

There is no auto cleanup of fragmentation on deleting table rows from an InnoDB table.. You have to defragment the table manually. METHOD #1 You can run the following OPTIMIZE TABLE table1; which will execute the following fotr you ALTER TABLE table1 ENGINE=InnoDB; ANALYZE TABLE table1; This might lock the table if you still have a lot of rows (this ...


1

I have written a few posts on loading index pages into the InnoDB Buffer Pool Feb 04, 2012 : MySQL warm procedure Nov 21, 2011 : Cache MySQL database in memory Here is an example of a SELECT that will generate all possible SELECTs you would want to run to read InnoDB Index Pages: SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb, ' ...


0

Use ENGINE=InnoDB, not MyISAM. Your attempts at changing settings were useless because you are not using InnoDB for the table in question. That can be specified as the default in my.cnf before creating tables. Else do it explicitly on CREATE TABLE. Also, don't blindly use 255, instead use sensible limits.


Top 50 recent answers are included