MySQL has some special features when making a connection from a remote client that if we do not know can complicate access to a MySQL database from a different machine that hosts the database.
With other databases such as Oracle or SQL Server, once that no firewall or anything like that prevents us from the client machine access to the server, using normally data acces from a database user we can 'enter'.
With MySQL, although access to the port, usually 3306, is open, the database can be configured to shut out external connections, and the result is the same as if the port was closed by a firewall:
telnet mysql.dataprix.es 3306 Trying 184.108.40.206... telnet: connect to address 220.127.116.11: Connection refused telnet: Unable to connect to remote host
If you get this result should consult the file /etc/my.cnf, and checking for bind-address variable or skip-networking.
If skip-networking is and is not discussed, edit the file and delete it, or make a comment to have no effect and allow external connections:
The first aim will do so through an ADO.NET Destination and MySQL provider for the connection. If we do well to insert the data directly, to create the target, select the connection and then select the table appears an error like the following check everything with the preview or try and do the assignments...
In MySQL there is a parameter that enables logging of queries that take more than x seconds to execute calls Slow Queries. Basically what it does is put them in a log file and then check with mysqldumpslow. This mechanism may be useful to improve the overall performance of the database if we reduce the value of a progressive manner to the execution times of "leave" before declaring a query such as "slow" or slow query...
en leave a small post on how to mount an automatic notification via mail that tells us what is the status of our database. For example we can manage it a bit before starting our work day and before we leave or while on vacation (jaja. ..). This example is about a MySQL 5.x running on Debian. Steps as follows...
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:
It has the following characteristics:
- Operating System: Ubuntu 8.10
- BI Platform: Pentaho 2.0
- DBMS: MySQL
This demo also has the following modules:
- Pentaho Data Integration: Through this tool we have designed the transformations and jobs that extract, transform and load the data to populate the Data Warehouse. (Select option 'No catalog')
- Mondrian Workbench: Through this tool we have created multidimensional schemes.
- Pentaho Metadata Editor: Through this tool we've created the Business Model.
- Design Studio: Through this tool we have developed the xactions.
- Pentaho Report Design: Through this tool we have created an ad hoc report that requests input parameters.
These modules can be accessed through the taskbar side:
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.
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.
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
This post is based on this one of il-masacratore:Time: Dimension structure and loading script for SQLServer.
As il-masacratore says usually there are a number of dimensions that are common to all DW. The Time dimension is one of them.