carlos's blog

Defragment to optimize MySQL tables

 

 

In MySQL, when you delete records from a table, space is reallocated automatically. It is as empty space and forming new attachments will advantage.

The problem is that if a table perform many DELETE operations, the physical space of the table will become increasingly fragmented and the performance is reduced.

In the MyISAM and InnoDB, OPTIMIZE TABLE command available to perform an optimization on any table that, among other things, performs an automatic defragmentation of the table.

It is highly recommended to use this command regularly especially on tables that are more statements of disposal of records.

As a precaution, keep in mind that during implementation, of course, the table is blocked. You have to remember when you are going to use with large tables and busy.

Supersimples The syntax is:

 

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

To make a fragmented table optimization can be selected to have free space, probably as a result of DELETE statements:

Improve MySQL performance by adjusting some parameters

 

MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.

Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.

I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)

The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.

The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.

Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.

 

Spectacular graphics that meets the key figures for Google

 

Spectacular graphics that meets the key figures for Google

 

To get an idea of the volume of business that moves Google infografia nothing better than that prepared in pingdom, which collected on the same graph one timeline with key milestones of the company, impressive numbers on the form, increase in revenues , figures on company employees, and the most significant data on other successful products such as Android, YouTube, Blogger, Gmail, Chrome, Orkut or Analytics.

A good source of inspiration for anyone eager to create a scorecard where we could see these evolve dynamically as so well chosen indicators.

Surely more than one person has clicked on an indicator in the chart to see if anything more;)..

Twitter will migrate from MySQL to Cassandra DB

 

Cassandra is an open source database distributed, is one of the noteworthy projects of the Apache Software Foundation and appears to be hitting hard. By being distributed enables high availability, fault tolerance and, above all, a highly scalable without loss of performance.

And were using companies that handle large amounts of data such as Rackspace, Digg, or Facebook, and the list is expanding.

The latest news, coming from a blog interview with Ryan King MyNoSQL is that Twitter is considering migrating their MySQL server clusters Cassandra DB.
 

This is a product presentation, which can be found in the same web Cassandra Project 

The first dashboard applications compatible with Apple iPad

 

As expected, the first BI tools adapted to the new iPad of Apple are appearing. Nothing like a dashboard application to exploit the Multitouch screen possibilities of this device.

Prelytis is the company that has developed Prelytis LiveDashBoard, the first Business Intelligence software compatible with the new Apple Tablet. This is a dashboarding tool, and 2.0 oriented, with collaborative features, and remarkable for their efforts in terms of adaptation to mobile devices.

SAP joins trend with a 2.0 product integrated with Google BPM Wave

 

SAP TechED presented in Vienna on Gravity product prototype, a BPM solution that works on the collaborative environment provided by Google Wave.

This product allows Business Process desing collaboratively, building communication facilities provided by the environment Google Wave. Obviously it works for web, and can also be used from mobile devices like an iPhone.

This video shows how to simulate a situation of merger of two companies in which they must redefine many business processess the highest level.

 

 

 

 

Easily export data from Oracle to flat file

 

A simple way to export data from a query, table, etc.. of an oracle database to a flat file is to use the SPOOL command in SQLPlus. This would not need to rely on visual aids, which are not always available or do not always work as we want. Also you can use the Oracle format functions in the same SELECT statement that generated the data already in the format we need.

If, for example, we want to retrieve some data from all records in a table of customers sorted by date of discharge, simply open a SQLPlus session and run this series of commands: 


SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\datos_de_clientes.txt
SQL> SELECT 'Cliente ' || CLI_NOMBRE || ', ' || CLI_NIF || '. Fecha alta: ' || TO_CHAR(CLI_FECHAALTA,'YYYY-MM-DD')
FROM TABLA_CLIENTES
ORDER BY CLI_FECHAALTA DESC;
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON

The first lines hide the headers that contain the field name, and do not concern us because we only want the data. Spool directs the output of data to the file 'datos_de_clientes.txt' on the C drive on the local machine.

The Time Dimension Structure and Loading script for SQLServer

 

 

In a Data Warehouse there are a number of common dimensions such as Geographic and Time. 

I leave here a script to create the Time Dimension table and a procedure to loading it between two dates:

 

/ * Destination database * /

use PAnalisys

Syndicate content