MySQL Forums - Data Warehouse

datawarehousing (1 reply)

Bodelère ASSOKPE — Fri, 03 May 2013 15:02:02 +0000

Hi everyone iam developping an application with php and mysql but i want to use oracle 11g for the datawarehouse can someone help me about how to proceed????

big data question (3 replies)

Vincent R&D — Sun, 31 Mar 2013 07:25:32 +0000

Hi,

I don't know if this is the right place to put this question but I guess this is
related to high availablity of data.

The problem is that I have a OLTP mysql database which has two very large tables among many.
The user is interested to get a report which is created from joining this two tables. These tables are getting bigger over time with rate of millions of records
per week.

Querying the tables through on-demand joins as a response to the user's request is
very unhealthy becasuse the tables are very big and the whole database hangs during the report.

I thought one solution would be to create a second database beside the first database. The second database has a view of joint tables of the first database which is filled with the source data from the first database in an interval. sounds like staging ....

I don't know what is a standard or best practice way of doing this...
What would you suggest?
I have also familiar with most of big data definitions and solutions such as partitioning, data warehouse, staging, data marts etc. but not having had a practical experience so far, I'm not that confidence to pick a solution ...

Your suggestions are appreciated.
Thanks,
Vincent.

16 Table Join Benchmarks for New Fast Data Warehouse Server Published (no replies)

Hiromichi Watari — Mon, 10 Dec 2012 19:21:02 +0000

16 table join benchmarks were ran on Parallel Universe* at Amazon Web Services.

Results are available at www.paralleluniverse-inc.com/benchmarks.shtml .

----------------------------------------------------------------------

*Parallel Universe is the industry's only SQL server with fast parallel query engine.

It is created by extending MySQL server architecture and runs on commodity server hardware.
Speed is achieved by processing tables in parallel utilizing multiple core/CPU of server hardware and scalable to large joins.

Because of fast query processing being available to data analysis, it is an ideal data warehouse server.
With Parallel Universe, you'll also be able to deploy less costly server hardware for the same query load/task.

Parallel Universe is released under the GPL license and fully compatible with MySQL and Percona servers.
Also available as part of Linux OS images at Amazon Web Services and www.GoGrid.com .

New Fast Data Warehouse Server Now Available at Amazon Web Services (no replies)

Hiromichi Watari — Tue, 13 Nov 2012 17:01:07 +0000

Parallel Universe* is now available as part of Linux OS images at Amazon Web Services (http://www.aws.amazon.com).

Launch Instance -> Classic Wizard -> Community AMIs -> type in "parallel universe" (All Images)
----------------------------------------------------------------------------------------------------------------------

*Parallel Universe is the industry's only SQL server with fast parallel query engine.

It is created by extending MySQL server architecture.
Speed is achieved by processing tables in parallel, utilizing multiple core/CPU of server hardware and scalable to large joins.

Because of fast query processing being available to data analysis, it is an ideal data warehouse server.
With Parallel Universe, you'll also be able to deploy less costly server hardware for the same query load/task.

Parallel Universe is released under the GPL license and fully compatible with MySQL and Percona servers.
Also available at www.GoGrid.com and www.paralleluniverse-inc.com .

New Fast Data Warehouse Server Now Available at GoGrid.com (no replies)

Hiromichi Watari — Mon, 24 Sep 2012 15:32:00 +0000

Parallel Universe* is now available as part of Linux OS images at www.GoGrid.com.
GoGrid.com is a dedicated/cloud server hosting site.

----------------------------------------------------------------------------------------------------------------------

*Parallel Universe is a new extension to MySQL server architecture, created to provide fast parallel query capability.
Speed is achieved by processing tables in parallel, utilizing multiple core/CPU of server hardware.

Because of fast query processing being available to data analysis, it is an ideal data warehouse server.
With Parallel Universe, you'll also be able to deploy less costly server hardware for the same query load/task.

Parallel Universe is released under the GPL license and fully compatible with MySQL and Percona servers.

Also available at www.paralleluniverse-inc.com

Size of my databse (no replies)

Thomas Laguerre — Thu, 13 Sep 2012 14:54:13 +0000

Hello everyone,

this is my first post on this forum. I have a tricky question that has probably been asked here before.
I would like to know what could be the size (in Octets or Bytes) of my database (using InnoDB engine with MySql) if I have to record 40 million of lines and 20 fields (attributes).

Best Regards,

Thomas

*** Sorry if I choose the wrong topic ***

New Fast Data Warehouse Server (4 replies)

Hiromichi Watari — Tue, 11 Sep 2012 16:46:03 +0000

Parallel Universe is a new extension to MySQL server architecture, created to provide fast parallel query capability.
Speed is achieved by processing tables in parallel, utilizing multiple core/CPU of server hardware.

Because of fast query processing being available to data analysis, it is an ideal data warehouse server.
With Parallel Universe, you'll also be able to deploy less costly server hardware for the same query load/task.

Parallel Universe is released under the GPL license and fully compatible with MySQL and
Percona servers.

Available at www.paralleluniverse-inc.com

Loading files automation using Load_data (1 reply)

Dhiraj Bhangale — Wed, 08 Aug 2012 05:07:13 +0000

HI everyone,

I want to load more than 100 CSV files in every week to database. For the same i am using LOAD DATA functionality of MYSQL.

To automate it. I am extracting file name & related path in csv file using scripting & loading to the table. Now i have all csv files names & path in my database table.

I tried to create a prepared statement for LOAD DATA, where i was concatenating file name & file path to LOAD DATA statement in procedure using cursor to dynamically execute it.

But here LOAD DATA is not allowed in procedures.

Can anyone please help me to do so????

Please suggest.

Thanking you in advance.

Regards,
Dhiraj

Need advice for table rotation (1 reply)

Pichai Iamtana-anan — Wed, 01 Aug 2012 03:54:38 +0000

I plan to design system for keep access log in my customer website and keep every events that happen so data increase very quick (someday about 2M in 1 day)
and cause it is a log record that no need for update or delete, just select and insert. And We wanna keep data just 1 year, older than will be removed but cleaning process will not run everyday, may 1 week, 2 week or month once.
I plan to active data just only newest 3 months and archive 9 older months, is case customer wanna review and compare old data.
So please suggest me.
1. Which storage engine that I should use, myisam, innodb or archive engine?
2. If I split data to daily table or monthly table, How is it performance?
3. If you have any good idea, please tell me.

Thanks for every answer.

Problem with granularity choosen (1 reply)

Hélio Hélio — Sat, 12 May 2012 01:54:39 +0000

Hi.

I have a fact table with a very small granularity, which is a daily snapshot of sales process on my company.

I have told my client I couldn´t handle too much detail because of the poor hardware given. But he insisted on that because he requires detailed data for some decisions.

So we agreed on this deal:

- To persist only the last day snapshot of each month in database;
- But update every day, erasing the previous days of the same month;

Example:

2012-05-03 replaces 2012-05-02
2012-05-04 replaces 2012-05-03
2012-05-05 replaces 2012-05-04
...
2012-05-31 replaces 2012-05-30, maintain 2012-05-31
start another month
2012-06-01
2012-06-02 replaces 2012-06-01
...so on

I realized that deleting large number of records takes too long time.
So I split on 2 tables, one with the last day of the month results, and the other the partially results, so I can perform TRUNCATE TABLE on this partial result table.

1st question: Is that the best solution?

Continuing...
Now I need to perform a UNION ALL with these 2 tables using view. Unfortunately, the performance drops considerably, because I can´t manage the indexes properly (USE INDEX, FORCE INDEX ...). I am not happy with that.

What should I do?

Proxy users (no replies)

Przemys?aw Budny — Mon, 23 Jan 2012 11:58:17 +0000

Hi,

http://dev.mysql.com/doc/refman/5.5/en/proxy-users.html

Did anybody use the a/m feature with mysql_native_password plugin?
I used the script from the site, replacing auth_plugin with mysql_native_password.
The commands have been executed succesfully however the feature "proxy users" seems not to work. I tried on 5.5.20 (windows) and 5.5.10-log (centos).
I used the same feature on Oracle 11g r1.

After logged in I received:




mysql> SELECT USER(), CURRENT_USER();


empl_external@localhost | empl_external@localhost

There is not proxy at all.

Memory Allocation Error Help ?? (1 reply)

Norm Bailey — Fri, 18 Nov 2011 16:10:29 +0000

I am having a memory problem when trying to update Cognos Query subjects in Framework manager running on MySql. I have a large MySql table that I have added a new column to and I am trying to update the table definition in Cognos Framework manager in order to pull the new version of the MySql table into Cognos. Smaller tables work fine but this larger table I am getting the error message shown below. I have 16G of memory for my Mysql server and am running the community version 5.1 of MySql... has anyone encountered this problem or have any suggestion as to what may be the cause ???

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[MySQL][ODBC 3.51 Driver][mysqld-5.1.49-community-log]MySQL client ran out of memory

sql error (no replies)

hasan ozerr — Thu, 25 Aug 2011 23:28:46 +0000

I got a mysql error on my site can you please tell why

http://www.mobilyainsaat.com/

Optimizing it even more (8 replies)

Pepe Pardo — Thu, 02 Jun 2011 08:13:12 +0000

Hi everyone,

We have a MySQL 5.5.11 x64 runnin on a Windows 2008 R2 x64, and we use it as a data warehosue.
So I optimize as max as I could but I think some more tweaking can be made.

Let's say that we have a sessions table, which is an INNODB one, it has been partitioned by mounth and subpartitioned by bot_id, which is the id of our bots.
It doesn't have Primary Key, but as it has an AUTO_INCREMENT column an index for that column has been set, but most of its columns are foreign key to dimensions (tables) without being set the constain, I mean, they are simple columns.

On the other hand we have for example the agent dimension table, which basically has the primary key and the agent name.

The SHOW TABLE STATUS for these tables are as follows:

Name Engine Ver Row_format Rows AvgR Data_lenght Max Index_lenght Data_free Auto_inc Create_time UpT ChT Collation ChSM CO Comment
'd_agents', 'InnoDB', '10', 'Compact', '1243425', '239', '297549824', '0', '228589568', '2001731584', '1262471', '2011-05-03 10:13:08', NULL, NULL, 'utf8_general_ci', NULL, '', ''
'f_sessions', 'InnoDB', '10', 'Compact', '39042058', '131', '5125947392', '0', '874774528', '1447251935232', '39233398', NULL, NULL, NULL, 'utf8_general_ci', NULL, 'partitioned', ''

So We usually run queries like this:
SELECT DISTINCT a.* FROM d_agents a INNER JOIN f_sessions s ON a.agent_id = s.agent_fk AND s.begin_timestamp >= '2010-01-01 00:00:00' AND s.begin_timestamp <= '2011-05-25 23:59:59' AND s.bot_fk IN (1,2,3,4,5,6,13);

It takes a lot of time and what concerns me is that it takes a lot of diskspace when running the query(+400 GBytes), even though all the database weights only 20 GBytes.

I changed the setting innodb_buffer_pool_size to 14G in order to help MySQL put the PrimaryKeys into memory.

Is there any way to omptimize even more this kind of queries?
If I set indexs for the fks in the session table, will they help the execution of the query?

Thanks all of us in advance.

Best Match from the left (1 reply)

goh juno — Wed, 18 May 2011 05:40:05 +0000

Suppose i have a list of numbers
2243432422
5645343425
4252524245
6756345234
7809080594

And i got number 56488888888.

I want the query to get 5645343425 which is the best match from the left. Is there a query that can do that ??

I tried with query below but no luck.

SELECT * FROM table
WHERE number LIKE CONCAT('56488888888', '%');

LOAD DATA INFILE with SET commad (1 reply)

chiko Ratnayake — Wed, 20 Apr 2011 20:29:25 +0000

Data mining in MySQL (no replies)

Ilyeop Yi — Fri, 15 Apr 2011 05:45:40 +0000

I search for data mining module tightly integrated with MySQL.
(like Oracle Data Mining integrate with Oracle Database, IBM Intelligent Miner integrated with IBM DB2...)

Does MySQL support data mining functionality itself?

Or, is there any data mining system tightly integrate with MySQL?

Data Warehouse In Web Development (2 replies)

saeed sp — Sat, 09 Apr 2011 10:00:59 +0000

Hi anybody.
First pleas let me know if I didn't post in correct forum.
I developed a website using php and MySQL.Now I want to show the amount of each record or each attribute and value of my columns where user browse my categories or search my sit.
For example please see this example http://www.amazon.com/s/ref=amb_link_83353631_23?ie=UTF8&node=565108&brand=lenovo&pf_rd_m=ATVPDKIKX0DER&pf_rd_s=left-1&pf_rd_r=006DKMQ9KADV5MQ1WBB9&pf_rd_t=101&pf_rd_p=1292034742&pf_rd_i=565108

there are another example,just brows some category or search some things:
http://cars.oodle.com/buick/dallas-area/

If you watch the left panel it showed how many record founded for each attribute.
I wondered how amazon or other site get this information rapidly! I think it may used Warehousing !

I wanna to know what is this technology and could I use it in MySQL fro my website?

Data Warehouse (no replies)

nikolas jones — Tue, 05 Apr 2011 05:30:46 +0000

I want more informations about this topic.
____________
Nikolas.
payroll solutions

Load Data with Binary File (1 reply)

Curtis Pastore — Sun, 13 Feb 2011 05:21:51 +0000

Hello All,

I'd like to use the speed of the load data statement to load in binary data into a table. I've done it with a character flat file (with determined/known character delimitations) however, does anyone know if it works with binary data also?
Thanks!

simple question about dimension table (1 reply)

Maron Vomr@ — Sat, 12 Feb 2011 22:58:18 +0000

Hi all ,

I am new to data warehouse world and need to know that I have a oltp structure where lets say there are 3 tables brand, category and product. Now in brand there is a field say name varchar(45) and in category there is also a field name varchar(45). So for OLTP it's different table and no problem.
But now when I am creating dimention table "product" and combining all these 3 tables in one Product dimension so how to incorporate that 'name' field as it will be duplicated and mysql wont allow so should I change the name to something meaningful or what is the dw concept for this.
And one more question. say in OLTP product table it has 100 rows and in category 5 and in brand 3. so how to populate the "product" dimension table by taking data from all these 3 tables.
Should I just run a query matching productID with category tables's product ID and then brandID with category tables brand ID and finally will get 100 rows in dimension table.

Thank you. hope I make myself clear.

Performance problem in InnoDB (3 replies)

Ganesh Ramasubramanian — Wed, 08 Dec 2010 08:46:55 +0000

I'm currently using Mysql 5.1.49 community server for the datawarehouse.
The performance of the server degrades quite dramatically for even a simple query.

The fact table contains 10 million rows and the dimension tables contain only around 40k records.
The query takes around 14 secs to execute.

I want to show the query results in the graph and the user cannot wait for 14 secs.
I want to show the graph in real time.
As the where clause can vary depending on the user selection, I cannot cache all the queries in the WS layer.

Is this the expected behavior ?

mysql> select date_key, time_key, sum(return_count)
from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and
fact.subnet_key = subnetDim.subnet_key AND
subnetDim.PIDType = 'LOCAL' and
b2bDim.customer_name = 'turktel'
group by fact.date_key , fact.time_key;

This query takes 13.26 secs

mysql> show table status like 'FACT_HOUR_SUBNET_TRACKER'\G
*************************** 1. row ***************************
Name: FACT_HOUR_SUBNET_TRACKER
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10224815
Avg_row_length: 58
Data_length: 602619904
Max_data_length: 0
Index_length: 447791104
Data_free: 1024909639680
Auto_increment: 10530643
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment: Stores the Subnet Tracker hourly data for 1 month
1 row in set (0.02 sec)

mysql> show create table FACT_HOUR_SUBNET_TRACKER;

| FACT_HOUR_SUBNET_TRACKER | CREATE TABLE `FACT_HOUR_SUBNET_TRACKER` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`creation_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`day_of_month` tinyint(4) NOT NULL DEFAULT '1',
`time_key` smallint(5) unsigned DEFAULT NULL,
`date_key` int(10) unsigned DEFAULT NULL,
`subnet_key` mediumint(8) unsigned DEFAULT NULL,
`b2bunit_key` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_local_seeders` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_unique_sessions` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_peers` mediumint(8) unsigned DEFAULT NULL,
`mbytes_uploaded` mediumint(8) unsigned DEFAULT NULL,
`mbytes_downloaded` mediumint(8) unsigned DEFAULT NULL,
`return_count` mediumint(8) unsigned DEFAULT NULL,
`receive_count` mediumint(8) unsigned DEFAULT NULL,
`local_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`peering_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`transit_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`local_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
`peering_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
`transit_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
PRIMARY KEY (`id`,`day_of_month`),
KEY `idx_FACT_HOUR_SUBNET_TRACKER_lookup` (`creation_time`,`subnet_key`,`time_key`,`date_key`,`b2bunit_key`)
) ENGINE=InnoDB AUTO_INCREMENT=10530643 DEFAULT CHARSET=latin1 COMMENT='Stores the Subnet Tracker hourly data for 1 month'
/*!50100 PARTITION BY LIST (day_of_month)
(PARTITION PDay1 VALUES IN (1) ENGINE = InnoDB,
PARTITION PDay2 VALUES IN (2) ENGINE = InnoDB,
PARTITION PDay3 VALUES IN (3) ENGINE = InnoDB,
PARTITION PDay4 VALUES IN (4) ENGINE = InnoDB,
PARTITION PDay5 VALUES IN (5) ENGINE = InnoDB,
PARTITION PDay6 VALUES IN (6) ENGINE = InnoDB,
PARTITION PDay7 VALUES IN (7) ENGINE = InnoDB,
PARTITION PDay8 VALUES IN (8) ENGINE = InnoDB,
PARTITION PDay9 VALUES IN (9) ENGINE = InnoDB,
PARTITION PDay10 VALUES IN (10) ENGINE = InnoDB,
PARTITION PDay11 VALUES IN (11) ENGINE = InnoDB,
PARTITION PDay12 VALUES IN (12) ENGINE = InnoDB,
PARTITION PDay13 VALUES IN (13) ENGINE = InnoDB,
PARTITION PDay14 VALUES IN (14) ENGINE = InnoDB,
PARTITION PDay15 VALUES IN (15) ENGINE = InnoDB,
PARTITION PDay16 VALUES IN (16) ENGINE = InnoDB,
PARTITION PDay17 VALUES IN (17) ENGINE = InnoDB,
PARTITION PDay18 VALUES IN (18) ENGINE = InnoDB,
PARTITION PDay19 VALUES IN (19) ENGINE = InnoDB,
PARTITION PDay20 VALUES IN (20) ENGINE = InnoDB,
PARTITION PDay21 VALUES IN (21) ENGINE = InnoDB,
PARTITION PDay22 VALUES IN (22) ENGINE = InnoDB,
PARTITION PDay23 VALUES IN (23) ENGINE = InnoDB,
PARTITION PDay24 VALUES IN (24) ENGINE = InnoDB,
PARTITION PDay25 VALUES IN (25) ENGINE = InnoDB,
PARTITION PDay26 VALUES IN (26) ENGINE = InnoDB,
PARTITION PDay27 VALUES IN (27) ENGINE = InnoDB,
PARTITION PDay28 VALUES IN (28) ENGINE = InnoDB,
PARTITION PDay29 VALUES IN (29) ENGINE = InnoDB,
PARTITION PDay30 VALUES IN (30) ENGINE = InnoDB,
PARTITION PDay31 VALUES IN (31) ENGINE = InnoDB) */ |

mysql> explain select date_key, time_key, sum(return_count)
from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and
fact.subnet_key = subnetDim.subnet_key AND
subnetDim.PIDType = 'LOCAL' and
b2bDim.customer_name = 'turktel'
group by fact.date_key , fact.time_key\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fact
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10265643
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: subnetDim
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: dwh.fact.subnet_key
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: b2bDim
type: eq_ref
possible_keys: PRIMARY,idx_B2BUnitDimension_lookup
key: PRIMARY
key_len: 4
ref: dwh.fact.b2bunit_key
rows: 1
Extra: Using where
3 rows in set (0.00 sec)

mysql> show variables like '%buffer%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| bulk_insert_buffer_size | 67108864 |
| innodb_buffer_pool_size | 10737418240 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 9998336 |
| key_buffer_size | 3221225472 |
| myisam_sort_buffer_size | 134217728 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 8388608 |
| sql_buffer_result | OFF |
+-------------------------+-------------+
12 rows in set (0.00 sec)

mysql> show variables like '%tmp%';
+-------------------+-------------+
| Variable_name | Value |
+-------------------+-------------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 10737418240 |
| tmpdir | /tmp |
+-------------------+-------------+
4 rows in set (0.00 sec)

mysql> show variables like '%heap%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| max_heap_table_size | 10737418240 |
+---------------------+-------------+

# uname -a
Linux dataw1.kalixlab.be.alcatel-lucent.com 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

total used free shared buffers cached
Mem: 19 19 0 0 0 6
-/+ buffers/cache: 12 7
Swap: 1 0 1

Which data warehouse for MySQL can run on Solaris 10? (3 replies)

justin ux — Tue, 28 Sep 2010 17:54:45 +0000

Hello
I'm using mysql 5.0 and 5.1 based on Solaris 10 server, and there are about 500GB data to query ,I want to try data warehouse in my application.
But which data warehouse for MySQL can install and run on Solaris 10 (X86 or Space)? Please help to advice.

Thanks a lot
Justin

MySQL data warehouse? Newbie (2 replies)

Maximilian Seifert — Mon, 30 Aug 2010 10:56:11 +0000

Hello,

we have 2 MySQL databases of about 30 GB each (for 2 completely different products, the structure is very different). They are used as transactional databases.

The thing is we also want to do complex analytical queries on this data and even though it's "only" 30GB some queries are simply not possible at all. For example grouping a non-indexed table by 3 different columns at once and joining it with 2 other tables, all of which have several million rows, and without using a where condition. But we really need this data and we need it within < 1 second response time, so obviously we need a new solution. We didn't put indexes on the tables because it's actually a transactional DB and bad for OLTP performance (?), plus it probably wouldn't give us the sub-second response times we want either.

My question is how we should go about setting up a data warehouse. Our requirements are as follows:

- We would like to use a BI platform like MicroStrategy

- If possible, we'd like to have real-time synchronization between the data warehouse and the operational MySQL DB. If not possible, hourly synchronization. But it has to be reliable.

- The data warehouse should be synchronized with both (completely different) operational databases, plus some files that are generated by some scripts (web crawler stuff) - but those files can be updated like every day

- The data from the data warehouse actually has to be DISaggregated, like a table with 3m rows has to be turned into 20m rows and to be made MORE granular that way. Just imagine you have a table "slot_machine_displays" and it includes a "symbols" string column including the ids for the symbols that showed up. Well, we want to turn that into a table "symbols_that_showed_up", making it more granular. That's just an example, we're not a casino ;)

- It should not be a pain to set up. For example, if I have to think about in detail what aggregate tables I want to create, that would be a pain. It would be nice to have a data warehouse where I just push a button and it loads all the data in real-time from the operational databases, and all analytical queries, even the most complex ones that analyze data for an entire year across 5 dimensions are carried out within seconds.

-We're a company with 40 people and basically have no real budget to afford enterprise-level solutions. Anything >5000 EUR would probably be not possible.

I am COMPLETELY lost on this so maybe someone can explain to me in some detail of what we should explore in this situation.

I already looked at InfoBright but I don't know how it's supposed to synchronize with the operational database? ETL tools like Talend seem to be able to do that but the enterprise edition is ridiculously expensive.

Is there a really easy solution that I simply didn't realize? I mean isn't that the no.1 problem any small web company has? Having to analyze their MySQL data?

converting from InnoDB to MyISAM (2 replies)

shyam ahuja — Wed, 25 Aug 2010 15:02:57 +0000

Hi,

I am converting my tables from InnoDB to MyISAM using Alter table command. one table which was 23000 records took few seconds to convert. I have other table which is approximately 220 million records, alter command is running since 5 days now and not finished yet. Is this a right behavior? Is there way to find out how much data is converted and if the script is really running and not hung somewhere?

thanks,
shyam

datawarehouse ETL rolls back after 5 GB of data transfer (5 replies)

shyam ahuja — Fri, 21 May 2010 05:05:42 +0000

Hi,

I am running ETL which is Extracting Data from staging table and inserting into Datawarehouse DB. I have around 10 GB of data in one table. After transferring 5 GB of data it rolls back the transaction and deletes everything from table. Any feedback?

Here is ETL
--Procedure: new_etl1

--DROP PROCEDURE IF EXISTS new_etl1;

DELIMITER $$
CREATE PROCEDURE `new_etl1`()
BEGIN
DECLARE ins1 varchar(15) default '';
DECLARE ins2 varchar(15) default '';
DECLARE ins3 varchar(15) default '';
DECLARE ins4 varchar(15) default '';
DECLARE ins5 varchar(15) default '';
DECLARE ins6 varchar(15) default '';
DECLARE ins7 varchar(15) default '';
DECLARE ins8 varchar(15) default '';
DECLARE ins9 varchar(15) default '';
DECLARE ins10 varchar(15) default '';
DECLARE j int;
DECLARE dw_max_id int ;
DECLARE stg_max_id int ;
DECLARE dw_max_id_1 int ;
DECLARE stg_max_id_1 int ;
DECLARE log1 varchar(15) default'';
DECLARE l_log numeric ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
select concat
(
'Erorr Inserting Data into DWH Table ',
case
when ins1='' then 'Dw_log_presentation'
when ins2='' then 'Dw_log_smil_sequence'
when ins3='' then 'Dw_log_smil_context'
when ins4='' then 'Dw_theaterexhibitor '
when ins5='' then 'Dw_log_processor'
when ins6='' then 'Dw_log_day_control '
when ins7='' then 'Dw_Files '
when ins8='' then 'Dw_log_File'
when ins9='' then 'Dw_log_playlog'
when ins10='' then 'Dw_playlog'
END,
' Insertion Failed' );
SET AUTOCOMMIT=0;
START TRANSACTION;
select max(job_instance_no) into j from staging_db.job_details ;

insert into datawarehouse_db.dw_log_presentation
(Presentation_Id, Name)
select Presentation_Id,Name from staging_db.stg_log_presentation
where Presentation_Id not in
(select Presentation_Id from datawarehouse_db.dw_log_presentation);
SELECT ROW_COUNT() into log1;

if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_presentation','No Duplicate Entry Allowed');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_presentation','Completed');
end if;

SET ins1='Done';

insert into datawarehouse_db.dw_log_smil_sequence
(SEQUENCE_ID, NAME)
select SEQUENCE_ID, NAME from staging_db.stg_log_smil_sequence
where SEQUENCE_ID not in
(select SEQUENCE_ID from datawarehouse_db.dw_log_smil_sequence);

SELECT ROW_COUNT() into log1;

if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_smil_sequence ','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_smil_sequence','Completed');
end if;

SET ins2='Done';

insert into datawarehouse_db.dw_log_smil_context
(SMIL_ID, REF_ID)
select SMIL_ID, REF_ID from staging_db.stg_log_smil_context
where SMIL_ID not in
(select SMIL_ID from datawarehouse_db.dw_log_smil_context);

SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_smil_context','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_smil_context','Completed');
end if;
SET ins3='Done';
insert into datawarehouse_db.dw_theaterexhibitor
(PROCESSOR, THEATER, EXHIBITOR)
select PROCESSOR, THEATER, EXHIBITOR
from
staging_db.stg_theaterexhibitor
where PROCESSOR not in
(select PROCESSOR from datawarehouse_db.dw_theaterexhibitor );

SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_theaterexhibitor','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_theaterexhibitor','Completed');
end if;
SET ins4='Done';
insert into datawarehouse_db.dw_log_processor
(Processor_Id, Name)
select Processor_Id, Name
from
staging_db.stg_log_processor
where Processor_Id not in
(select Processor_Id from datawarehouse_db.dw_log_processor );
SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_processor','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_processor','Completed');
end if;
SET ins5='Done';
insert into datawarehouse_db.dw_log_day_control
(LDC_Id, Log_Date, Pending, Node_Id)
select LDC_Id, Log_Date, Pending, Node_Id
from
staging_db.stg_log_day_control
where LDC_Id not in
(select LDC_Id from datawarehouse_db.dw_log_day_control);
SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_day_control','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_day_control','Completed');
end if;
SET ins6='Done';
select max(Entry_Id) into dw_max_id from datawarehouse_db.dw_log_playlog ;
select max(Entry_Id) into stg_max_id from staging_db.stg_log_playlog ;
if dw_max_id is null then
select MIN(Entry_Id) into dw_max_id from staging_db.stg_log_playlog ;
End if;
if stg_max_id is null then
set stg_max_id=0;
End if;
set log1 = 0 ;
while dw_max_id < stg_max_id
DO
SET @s =CONCAT("insert into datawarehouse_db.dw_log_playlog
(Entry_Id, File_Id, Processor_Id, Sequence_Id, SMIL_Id, Play_Time)
select Entry_Id, File_Id, Processor_Id, Sequence_Id, SMIL_Id, Play_Time
from staging_db.stg_log_playlog
where stg_log_playlog.Entry_Id > " , dw_max_id, " and stg_log_playlog.Entry_Id <= " ,dw_max_id+10000);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
SELECT ROW_COUNT() into l_log;
if l_log is not null then
set log1 = log1 + l_log ;
end if ;
DEALLOCATE PREPARE stmt1;
SET dw_max_id=dw_max_id+10000;

END WHILE;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_playlog','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_playlog','Completed');
end if;
SET ins7='Done';
insert into datawarehouse_db.dw_files
(LONG_NAME, SHORT_NAME, MOVIE, STUDIO)
select LONG_NAME, SHORT_NAME, MOVIE, STUDIO from staging_db.stg_files
where LONG_NAME not in
(select LONG_NAME from datawarehouse_db.dw_files );
SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_files ','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_files ','Completed');
end if;
SET ins8='Done';
insert ignore into datawarehouse_db.dw_log_file
(File_Id, Presentation_Id, File_Name)
select File_Id, Presentation_Id, File_Name
from
staging_db.stg_log_file
where File_Id not in
(select File_Id from datawarehouse_db.dw_log_file);
SELECT ROW_COUNT() into log1;
if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_log_file ','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_log_file ','Completed');
end if;
SET ins9='Done';

select max(Entry_Id) into dw_max_id_1 from datawarehouse_db.dw_playlog;
select max(Entry_Id) into stg_max_id_1 from staging_db.stg_log_playlog;
if dw_max_id_1 is null then
select MIN(Entry_Id) into dw_max_id_1 from staging_db.stg_log_playlog ;
End if;
if stg_max_id_1 is null then
set stg_max_id_1=0;
End if;
set log1 = 0 ;
while dw_max_id_1 < stg_max_id_1
DO
SET @s =CONCAT("insert into datawarehouse_db.dw_playlog
(Entry_Id, Play_Time, File_Name, Processor_Name, THEATER, EXHIBITOR, Presentation_Name, Presentation_Id, File_id, processor_id, short_name, movie, studio, Node_id, Log_date )
SELECT stg_log_playlog.Entry_Id
,stg_log_playlog.Play_Time
, stg_log_file.File_Name
, stg_log_processor.Name
, stg_theaterexhibitor.THEATER
, stg_theaterexhibitor.EXHIBITOR
, stg_log_presentation.Name
,stg_log_presentation.Presentation_Id
,stg_log_file.File_Id
,stg_log_processor.Processor_Id
,stg_files.SHORT_NAME
,stg_files.MOVIE
,stg_files.STUDIO
,stg_log_day_control.Node_Id
,stg_log_day_control.Log_Date
FROM
((stg_log_file stg_log_file
INNER JOIN
stg_log_presentation stg_log_presentation
ON (stg_log_file.Presentation_Id = stg_log_presentation.Presentation_Id))
INNER JOIN
stg_log_playlog stg_log_playlog
ON (stg_log_playlog.File_Id = stg_log_file.File_Id))
INNER JOIN
stg_log_processor stg_log_processor
ON (stg_log_playlog.Processor_Id = stg_log_processor.Processor_Id)
left JOIN stg_theaterexhibitor stg_theaterexhibitor
ON (stg_log_processor.Name=stg_theaterexhibitor.PROCESSOR)
left JOIN stg_files stg_files
ON (stg_log_file.File_Name=stg_files.LONG_NAME)
left JOIN stg_log_day_control stg_log_day_control
ON (stg_log_file.File_Id=stg_log_day_control.LDC_Id)
where (stg_log_playlog.File_Id = stg_log_file.File_Id)
AND (stg_log_file.Presentation_Id = stg_log_presentation.Presentation_Id)
AND (stg_log_playlog.Processor_Id = stg_log_processor.Processor_Id)
AND stg_log_playlog.Entry_Id > " , dw_max_id_1, " and stg_log_playlog.Entry_Id <= " ,dw_max_id_1+10000);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
SELECT ROW_COUNT() into l_log;
if l_log is not null then
set log1 = log1 + l_log ;
end if ;
DEALLOCATE PREPARE stmt1;
SET dw_max_id_1=dw_max_id_1+10000;
END WHILE;

if log1 is null or log1=0
then
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_Status) values(j,log1,'Dw_playlog ','No Duplicate Entry Allowed ');
else
insert into staging_db.dw_logs(job_id,num_rows,Table_Name,Updated_status) values(j,log1,'Dw_playlog','Completed');
end if;

SET ins10='Done';

COMMIT;

END$$

DELIMITER ;

Stored procedure and connexion to another MySQL DB (2 replies)

discmat discmat — Sun, 04 Apr 2010 16:05:04 +0000

Hi,
I would like to retrieve some information from another MySQL database, modify data and load it in a MySQL DB.
To do it regularly, I'll use a stored procedure with an event.
So, is it possible to create a stored procedure in database 1 which will connect and get information from database 2 ? And what is the syntax to do it ?

With this method, I have many other questions :
* If the 2 databases are on the same server, can I have 2 versions of MySQL ? Because the database source use an older version of MySQL which does not support the Event Scheduler".
* Is my solution possible if the 2 databases are not on the same server ?

Thanks all,
I look forward

mysql or hadoop? (no replies)

sanjo cole — Mon, 08 Mar 2010 15:54:54 +0000

hi,

i'm working on a data warehouse and am deciding whether to use hadoop or mysql.

the dataset is currently likely to be no bigger than 40gb for the first year, then perhaps 80gb for the next year, and possibly 120gb the year after.

we want to be able to query all of the data at any point in the future - we aren't interested in throwing data away since we can't envisage how we might want to use it.

so, would mysql be the right choice? i don't need high availability since this will be a back-office application, and the number of different queries won't be problematic - if staff want to perform queries, they don't really need real-time results.

how large can mysql scale for this kind of thing? should i perhaps look at something like hadoop instead? i'm feeling it'd be overkill today, but how would mysql deal with a dataset of ~ 300gb?

any help would be appreciated.

thanks

Large Database Question (7 replies)

John McGiles — Sat, 13 Feb 2010 21:38:08 +0000

This is a continuation of post: http://forums.mysql.com/read.php?22,353250,353625#msg-353625

I'm sorry I wasn't clear. 3 to 4 GROUPS of inserts per minute after the database is initially populated. There could be 100s of rows to insert per minute for some of these tables. Also, after new rows are inserted, updates do have to be done, which if I'm interpreting correctly from your previous response would block any selects on a MyIsam table?

I am using an auto increment for one of the crucial tables in this project currently so I'm glad to hear that using this is extremely cheap.

Initially, when the database is first being populated, there will be thousands if not tens of thousands of rows to insert per minute, but during this process no one will need to access them except the program that initially inserted them.

Again, sorry, but I'm still working on an example we can use to discuss the design of this database.

Importing data to MySQL database (4 replies)

velayudham jayakumar — Sat, 06 Feb 2010 11:52:28 +0000

Hello,

I am working on MySQL database. My requirement is to import data from the following format to MySQL database. The data format is
"----[/DHS3dyn/account/TAXAYQEK.DAT : Ticket number 8/8/100]---------------------

00) TicketVersion = ED5.1 (01) CalledNumber = 00138313407

(02) ChargedNumber = 32370 (03) ChargedUserName = TEST PERF 202

(04) ChargedCostCenter = C42 (05) ChargedCompany =

(06) ChargedPartyNode = 202 (07) Subaddress =

(08) CallingNumber =

(09) CallType = PrivateNetworkOutgoingCallToPublicNetwork

(10) CostType = ISDNCircuitSwitchedCall (11) EndDateTime = 20090908 10:06:52

(12) ChargeUnits = 1 (13) CostInfo = 100

(14) Duration = 519 (15) trunkIdentity = 29

(16) TrunkGroupIdentity = 2 (17) TrunkNode = 202

(18) PersonalOrBusiness = Normal (19) AccessCode =

(20) SpecificChargeInfo = (21) BearerCapability = 3Audio

(22) HighLevelComp = Unspecified (23) DataVolume = 0

(24) UserToUserVolume = 0 (25) ExternFacilities =

(26) InternFacilities = BasicCall ARSService

(27) CallReference = 0 (28) SegmentsRate1 = 0

(29) SegmentsRate2 = 0 (30) SegmentsRate3 = 0

(31) ComType = Data (32) X25IncomingFlowRate = Unspecified

(33) X25OutgoingFlowRate = Unspecified (34) Carrier = 1

(35) InitialDialledNumber = 014001893 (36) WaitingDuration = 2

(37) EffectiveCallDuration = 7 (38) RedirectedCallIndicator = 1

(39) StartDateTime = 20090908 09:58:13 (40) ActingExtensionNumber = 65579

(41) CalledNumberNode = 9999 (42) CallingNumberNode = 9999

(43) InitialDialledNumberNode = 9999 (44) ActingExtensionNumberNode = 501

(45) TransitTrunkGroupIdentity = 43696 (46) NodeTimeOffset = 0"

Can you please suggest some methodology to import this kind of data.