Defragmentation of MySQL tables, how many times have I to do it?

Hi everybody, Once I have done the defragmentation of MySQL tables, mysql-tunner.pl suggests me do it again. Is this correct? I think that the idea is that in the result of the script there are not fragmented tables ... Any ideas? Thank you very much. Best regards, Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

Hi Reindl, Thanks for your reply. So, is it not necessary (not recommended) to defragment tables if I have a lot of write operations (writing or deleting)? Best regards, Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

Ok. Thank you very much. Best regards, Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

Hello Manuel, Exactly. My objective doing performance MySQL it isn't to free disk space (it isn't the problem). I would like to get a good performance using cache and setting good values to configuration variables. I will defrag tables once per month. Best regards, Antonio.

Doubt with stored procedures

Hi everybody, I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. Thanks

Re: Doubt with stored procedures

Hi everybody, Firstly thanks for your reply. I'm using MySQL only in one server (I have only one), without replication and without cluster configuration. I have defined the stored procedure as a normal user, not as a root. And the call is make as a normal user also. Occurs with MySQL 5.1.49 on

Doubt with sored procedures - II

I have discovered that in MySQL client program I can define definer and sql security. This is the detail. Thanks everybody. Best regards, Antonio.

Re: LOAD DATA in replication

If I don't mistake, there are some parameters to make that you are saying. Check statement-based-replication and row-based-replication. I think that this could help you. Regards, Antonio.

Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions

Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not

Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions

Hi Lukas, In that case, such as Adarsh has said, you can configure until 70% of your RAM for innodb_buffer_pool_size. In your case, with 3GB RAM, I suggest you to configure until 2GB for MySQL: Minimal for MyISAM (Maybe 32MB), and the rest for InnoDB. Your problem will be loading data. Maybe your

Big innodb tables, how can I work with them?

​ ​ ​Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can

Advices for work with big tables

Hi, I write to the list because I need your advices. I'm working with a database with some tables that have a lot of rows, for example I have a table with 8GB of data. How can I do to have a fluid job with this table? My server works with disk cabin and I think that sharding and partitioning

Re: Big innodb tables, how can I work with them?

​Hi, Thanks for your replies. In our case, we can't implement NOSQL solution. Thats requires modify/check all our application and all services (Including FreeRADIUS that I'm not sure if it's compatible). Andrew, I have heard about people that has a lot of data, more than me. I know that MySQL

Re: Order column in the second table

​Hi Lay, If I don't mistake, you can't eliminate Using temporary and Using filesort because you are using an order by. Try the explain again removing order by and check the output. When you use an order by, MySQL needs to use filesort and spends some time sorting the result set. Also, create a

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

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: Optimizing InnoDB tables

​Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio. ​

Problem with INSERT INTO and UPDATE queries

​Hi list, I have some problems with INSERT INTO and UPDATE queries on a big table. Let me put the code and explain it ... I have copied the create code of the table. This table has more than 1500 rows. ​Create Table: CREATE TABLE `radacct` ( `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,

Re: Optimizing InnoDB tables

​Hi Andre, Thanks for your reply. I have checked the link and my configuration. Innodb_file_per_table is enabled and in data directory appears a set of files by each table. Any ideas? Thanks in advance. Regards, Antonio.​

Re: Optimizing InnoDB tables

​Hi Reindl, Thanks for your attention. Following the previous mail, I have checked my MySQL's configuration and innodb_file_per_table is enabled so, I think that this parameter not affects directly to fragmented tables in InnoDB (In this case). I would like to know, if is possible, why after

Re: Optimizing InnoDB tables

​Hi Johan, Thanks for your reply. Theorically the fragmented tables not offer the best performance to the InnoDB engine, that's correct or not? I don't know if is a problem or not, is a doubt/question for me. I'm not sure if is an atypical behaviour. Thanks in advance. Regards, Antonio.​

Doubts tuning MySQL Percona Server 5.5

Hi list, I have some doubts adjusting some MySQL variables. ​ I have checked MySQL status and maybe I should to increase some variables ... For example: InnoDB log waits is 103; innodb_log_buffer_size is 8M -- Maybe the next best value could be 16M? Table cache hit rate is 12%; Open_tables is

Re: Doubts tuning MySQL Percona Server 5.5

​Hi, I was checking MySQL performance ... Sometimes my database could be working slow. I have some queries that spend 9-10 seconds updating some columns by primary key. I'm not sure if is a data base problem ... Moreover, I have checked tuning scripts and appear these variables. Good sentence!