Re: error 29, file not found (errcode: 13)
Am 24.06.2014 07:08, schrieb thufir: On Mon, 23 Jun 2014 10:59:48 -0400, Scott Helms wrote: I generally drop them into /tmp for easy access and cleanup after the data load, but you can put them any place that the mysql daemon process has access to read. Huh, maybe the daemon doesn't have access to /tmp?... ERROR 29 (HY000): File '/tmp/make_year_model.csv' not found (Errcode: 13) then file not found is bullshit these days computers can distinguish between not exist and no permissions and should reflect that in the messages they give to humans signature.asc Description: OpenPGP digital signature
Optimizing InnoDB tables
Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
Hi Antonio, como esta? What's the mysql version you're running? Have you tried to ALTER TABLE x ENGINE=InnoDB? -- WB, MySQL Oracle ACE Em 24/06/2014, às 08:03, Antonio Fernández Pérez antoniofernan...@fabergroup.es escreveu: Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Optimizing InnoDB tables
Hi Wagner, I'm running MySQL Percona Server 5.5.30 64Bits. No, I don't have tried to execute ALTER TABLE (Analyze with InnoDB tables do that, or not?). Thanks in advance. Regards, Antonio.
Re: error 29, file not found (errcode: 13)
I think it's apparmor: thufir@dur:/tmp$ thufir@dur:/tmp$ nl /etc/apparmor.d/usr.sbin.mysqld 1 # vim:syntax=apparmor 2 # Last Modified: Tue Jun 19 17:37:30 2007 3 #include tunables/global 4 /usr/sbin/mysqld { 5#include abstractions/base 6#include abstractions/nameservice 7#include abstractions/user-tmp 8#include abstractions/mysql 9#include abstractions/winbind 10capability dac_override, 11capability sys_resource, 12capability setgid, 13capability setuid, 14network tcp, 15/etc/hosts.allow r, 16/etc/hosts.deny r, 17/etc/mysql/*.pem r, 18/etc/mysql/conf.d/ r, 19/etc/mysql/conf.d/* r, 20/etc/mysql/*.cnf r, 21/usr/lib/mysql/plugin/ r, 22/usr/lib/mysql/plugin/*.so* mr, 23/usr/sbin/mysqld mr, 24/usr/share/mysql/** r, 25/var/log/mysql.log rw, 26/var/log/mysql.err rw, 27/var/lib/mysql/ r, 28/var/lib/mysql/** rwk, 29/var/log/mysql/ r, 30/var/log/mysql/* rw, 31/var/run/mysqld/mysqld.pid rw, 32/var/run/mysqld/mysqld.sock w, 33/run/mysqld/mysqld.pid rw, 34/run/mysqld/mysqld.sock w, 35/sys/devices/system/cpu/ r, 36# Site-specific additions and overrides. See local/README for details. 37#include local/usr.sbin.mysqld 38 } thufir@dur:/tmp$ I need to grant access to /tmp somehow? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Optimizing InnoDB tables
Hello Antonio, On 6/24/2014 7:03 AM, Antonio Fernández Pérez wrote: Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio. It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Optimizing InnoDB tables
Am 24.06.2014 21:07, schrieb shawn l.green: It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table which is the most stupid default in case of innodb and only survivable without a lot of work for people who realize that *before* start operations and enable innodb_file_per_table from the very begin having defaults which can't be changed later without complete re-import of data and prevent from ever get disk space for long ago deleted data free is the most wrong thing a software developer can do signature.asc Description: OpenPGP digital signature
Re: Optimizing InnoDB tables
Hello Reindl, On 6/24/2014 3:29 PM, Reindl Harald wrote: Am 24.06.2014 21:07, schrieb shawn l.green: It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table which is the most stupid default in case of innodb and only survivable without a lot of work for people who realize that *before* start operations and enable innodb_file_per_table from the very begin having defaults which can't be changed later without complete re-import of data and prevent from ever get disk space for long ago deleted data free is the most wrong thing a software developer can do The tables can be moved from the common tablespace into their own tablespace at any time after the option is enabled. The space they once occupied within the primary tablespace will remain and it will be marked as 'available' for any general purpose (such as the UNDO log) The only way to shrink the primary tablespace is, as you correctly described, through a dump/restore of your data. This process to resize the primary tablespace (such as to shrink it) must be followed precisely or problems will result. http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql