"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 ...
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 ...
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='...
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.
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 ...
Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.
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 ...
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 ...
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.
(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 ...
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 ...
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://...
There is no auto cleanup of fragmentation on deleting table rows from an InnoDB table..
You have to defragment the table manually.
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 ...
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:
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
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.