Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?
View Complete Forum Thread with Replies
Related Forum Messages:
Loading Many Files
Say i have a database that has 3 varchar fields and then a blob field. I can easily fill the first 3 fields with a tab delimited file. However how can i load multiple files into the blob field. Say i have 700 records- i can't do it by typing out the command over and over again. Is there a way to write a file specifying all the files that need to be loaded and then feed it mysql like i can do with a tab delimited file? Or is there another way?
View Replies !
System Resets When Loading A Large Table
I have an application which is essentially 'read-only' - once the database is loaded it is only read from. So, I am trying to implement it with various indices and a table containg some large content in blob columns. I observe the max_allowed_packet setting and 'split' blobs too large among multiple rows. All is fine during testing. However, when I try a production load (which should load about 1.5G to the various tables) the system resets at or near the 1G mark leaving no logs/msgs (that I can find) telling me what went wrong. I have tried the following to no avail:
View Replies !
Loading Text Files
Im currently having problems loading a tab seprated file into mysql database. It loads the data but with each field only loads half the data i.e. txt file contains "hello how are you world" (for a field) SQL table contains "hello how" Not sure whats causing this as i've opened the same file in excel and it works perfectly (renamed it from .txt to .xls)
View Replies !
Loading Multiple Text Or CSV Files
I have a folder of approximately 6000 csv files. I would like to load them all into the same table. (i.e. what code do I use)? I really don't want to load them one at a time using the phpmyadmin import function, and I have been searching online for hours, but have not found the coded solution yet using mysqlimport or load data infile or similar.
View Replies !
Loading New Data By Replacing Files...
I regularly have to update the contents of my database which serves a a placeholder for the information of my website. What I do is that I work on my local PC where I have the same database installed, update this very one, and then try to update the database on the remote server by replacing the tables' files directly in the folder of my database. I chose this because I need to update all the information at once, as my web site is accesible all through the process of updating the database. Thing is the new data is not taken into account if I do not restart the WHOLE SERVER!! It is not enough to just restart mysql... How could I do this properly?
View Replies !
Loading Large Load Data Infile Without Bogging Table
We have a table with about 50million records with a couple indexes. We have some more data that needs to be added, another 30million or so records. The thing is, the table is in use and can't have more than an hour or so of down time. Loading the 30million records with load data infile will create a giant transaction that will take a few hours, plus the additional time to index the data. I could copy the table, add the additional records to that, index it, then rename the table to the old table name. Is there a better way? Or a way to keep the db functional while loading it? How can I copy the table without having the recreate the indexes it already has? I tried the load data infile but while it was going on all new quries/transactions said waiting on table. I also need to disable keys while loading so even if it is accessable, any queries take so long without the indexes that it's useless.
View Replies !
Help! Trouble Loading .txt Files Into MySQL Front
spent all night entering data into my 1st DB via "mySQL front" and this morning had to reinstall everything, so i lost the whole DB but i did export it as a .txt & html table file last night.. but now i can't seem to get it back :mad: here is what the exported .txt file looks like: id,artist,title,label,price,sound 1,Niko Bellotto ,Slam mode,La colecion,21.50,slam_mode.mp3 2,Oliver Ho,Listening to the voice inside,Meta,40.50,oliver.mp3 3,Numero Deux,The dinning rooms,Conesa,32.50,numero.mp3 4,Photek,Solaris,Omni,32.50,photek.mp3 5,Gianni Ferrio,Spring themes,Garden,32.50,spring.mp3 ..... more of the same.. (its for an online cd store (fake one) i created a table with the same fields and tried to "import .txt file" but all i get is the id field filled up? the other fields stay blank?
View Replies !
Loading Database From Multiple Text Files Each Containing A Partial List Of Columns
I'm kind of new to mysql. I would like to know if there is a way to use load infile or mysql import syntax to load one table with multiple files each containing a partial list of the table's columns: Table contains : ID, first name, last name, occupation, salary There are two files one containing the first three columns, the other the first field (ID) and last two fields. Is there a way to load them with just two load statements? Note: I know that they can be loaded into two tables and later joined where ID is equal, but the actual example is about 30 text files rather than two, which is a bit too much.
View Replies !
Large Files And Mysql5.0
I have some large files that I need to blob into the a table. I am using Innodb and added this line to my my.ini file [ max_allowed_packet=16M ]. I am using Hibernate, and have blob choosen as a type. What is the max size for a blob? I also see tht Hibernate has large blob, but nowhwere in docs do I see sizes for these objects. Also, if anyone is using hibernate, is there a way to choose largeblob as a mapping type?
View Replies !
Limits On Large Files?
I am developing a secure file upload/download site. My approach is to use an account driven system which allows for file upload/download. The files will be "absorbed" into the database directly, as opposed to leaving them on the web file structure. The file downloads will be by "releasing" from the database. My basic application operates currently, however, I am running into a limit problem. I cannot upload beyond 17 MB file. I am on WinXP, PHP v 4.3.10, MySQL v 4.1.8, and access to Apache v2.0.52 (port 80) and Abyss v 1.2.3.0 (port 8000). I have learned that these variables with upload influences are... PHP: file_uploads = on PHP: post_max_size = 30MB PHP: upload_max_filesize = 30MB MySQL: max_allowed_packet = 30MB
View Replies !
Upload Large Sql Files
Any suggestions for how to upload large data files (.sql). Our data is updated every few days, and we need to upload the data on our webserver. How we can do that. We are using PHP/Mysql on linux server.
View Replies !
Very Large Data-files - Performance?
I have to handle a large amount of Data (about 5GB) in one table. I want to extract the essence out of this table which should be something like 1MB and save it to a new table. Mostly used commands are GROUP, WHERE, BETWEEN, IN and LEFT JOIN. I use 3-4 temporary Tables. The script is ready and tested on a file of 60MB (750.000 datasets) and works fine (needs about 90seconds). What I want to know is: Can I expect the query to work through my big data within some ours, or will I fail with a standard PC? What can I do to optimize (Hardware/Code)? There is only one query to work through at one time.
View Replies !
Load Data Local Infile :: Large/huge Files
I've used 'load data local infile...' with success with small data files (data tab deliminated in a .txt file). But now I'm ready to tackle loading a MUCH larger file of data. This begs the question, what IS the best way to get large amounts of data loaded or inserted into a MySQL table? For instance, in the real world someone will probably hand you a file(s) with tons of data that you have to dump into a .txt file and massage with tabs, etc. to get the data loaded into MySQL. So how do most of you deal with this kind of situation? Thru programming, scripts, macros...? Massaging all that data into one coherent tab deliminated .txt file seems like a lot of work. I'm new to both db mgt. and MySQL. And I'm using MySQL server on a W2K/IIS box.
View Replies !
Loading Text Fields Into Tables
Let's say a tab delimited file like this: name is varchar (60) bio is TEXT data type name<tab>bio Jack<tab>name: My name is Jack car: I have a chevy car pet: My pet's named Fido Jane<tab>name: My name's Jane car: I have a VW car pet: I don't have a pet ..... How do I LOAD that into MYSQL ? I want name field and the a text bio field but how do I get it in a table assuming I have line breaks...I'm willing to globally edit the file if needed....
View Replies !
Loading Data In Tables With Foreign Keys
It's sample database contains tables with foreign keys. When I created the tables (with Type = InnoDB), I did so without creating the foreign keys and then went back and did "alter tables" to add them. That process seemed to work perfectly. However, with the foreign keys added, I CANNOT LOAD DATA INTO THE TABLES. I have used the MySQL Control Center's insert row feature, the load data file SQL command and the tedious SQL insert command to insert one table row at a time. Nothing works. I get error messages about "cannot create or modify a child". I have tried deleting the foreign keys. I found that the system will only allow me to delete them on some tables. On the tables where I can delete the foreigh keys, I can bulk load the data using the load file command but then, after data exists in the table, I cannot restore the foreign key.
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example: select myfunc(x,y,z,a,b,c) from events where energy>3; But as far as I know there is no way of defining your own functions in SQL
View Replies !
Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life: If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient? The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key. The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?
View Replies !
Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables. My question for performance, as this table grows should it eventually make a new table? If so here is my current select code select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1' Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?
View Replies !
Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....
View Replies !
Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is more efficient than storing th= e keys in a varchar field then within the second loop doing a where in (1,2= ,3,4) where the 1,2,3,4 are the stored category keys in the varchar field = rather than a where in (1), where 1 is the pirmaryID of the entry for insta= nce ?
View Replies !
Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really slowwwwww! What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size. The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table. But the question I have now, can unused large tables affect the perfomance of Mysql? I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems? as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
View Replies !
Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much out of the box setup, and am starting to suffer heavily with my larger tables. I have a table with 5,000,000+ rows that I have to search and do joins on. Although I have an index set up for it, the joined select will still take some 400+ seconds to return, which is obviously unacceptable. This is due to enormous HD access. Perhaps someone can help me with indexes here; I was under the impression that the index for the tables are cached in memory, and therefore permitted "instant" searchability, without having to retrieve data from each of the rows of the DB. Is there a startup parameter, or something in the mysql.ini file, that must be set to allow for this? I have the index configured properly, and have made sure that the query uses there parameters in the where clause in the same order that they appear in the index. Code:
View Replies !
Speed Of InnoDB DELETEs On Large Tables
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): [color=blue] > SELECT count(*)[/color] -> FROM UserSnap -> WHERE LogDate<now() - INTERVAL 750 DAY; +----------+ | count(*) | +----------+ | 308969 | +----------+ [color=blue] > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;[/color] That delete query takes hours to run. The structure of the table is: [color=blue] > desc UserSnap;[/color] +----------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------------------+-------+ | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | | | Period | tinyint(4) | | PRI | 0 | | | UserName | varchar(50) | | PRI | | | | RateType | varchar(50) | | PRI | default | | | Rate | float | YES | | NULL | | +----------+-------------+------+-----+---------------------+-------+ Any suggestions on why this is slow, and what to do about it?
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down. I have 4 important tables here (I'm only posting the columns which I use): `auth` UID mediumint(8) unsigned PRI NULL auto_increment username varchar(32) MUL clan_id mediumint(8) unsigned MUL 0 clan_abbrev varchar(7) MUL `clans` ID mediumint(8) unsigned PRI NULL auto_increment Abbrev varchar(7) MUL Name varchar(64) icon_id smallint(5) unsigned 0 `stats` UID int(10) unsigned PRI 0 kills mediumint(9) NULL `usernames` UID int(10) unsigned 0 ID int(10) unsigned PRI NULL auto_increment Name varchar(32) Uses mediumint(9) 0 `auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`. I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`. The result should be ordered by `Uses` (from `usernames`) -- highest to lowest. The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type). Here's what I managed to hammer out with my limited knowledge of SQL: ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `clans` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND clans.ID = auth.clan_id AND stats.UID = auth.UID ) UNION ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND auth.clan_id =0 AND stats.UID = auth.UID ) ORDER BY `Uses` DESC It works great, but the average query takes around 6s (these tables have several thousand entries in each).
View Replies !
Load Files Into Tables
I have an application that pulls a log file from our server, splits it apart and put the results in a table in a text file, and can upload the file onto any FTP or website. My question is can I use a remote location (like www.505pir.com/Files/Manicore/Stats.txt) in the Load Data command, or is it required to be on the server machine?
View Replies !
I Need To Put References To Files In Mysql Tables
I am using Mysql 3.28 What I see that in VARCHAR it holds maximum 255 chars. I need to be able to store them more than 2000 chars. But my data details are very long for each sequence number. So what I am doing is that put the data for each sequences into file1, file2, file3 for those data. So for an instance : seq_no (PK) SEQ_DETAILS (255) I would like to insert data as: 1, file1 2, file2 etc. How do I insert the files into Mysql table. I could do this MSAccess or SQL Server. But in Mysql I dont know how. And then How when I run a query to find particular seq_details, how it will pull the data out?
View Replies !
Recovering Tables Structure From Frm Files
I had to make a quick backup on my computer, so... I copied the entire data directory from the mysql folder (yeap, instead of using mysqldump, I know I know...), so, now I've installed everything, and have copied the data folder from the backup. Almost all databases are correct, but one. It has only .frm files in the folder, but I think it's correct because all the tables where in InnoDB. Now, every time I try to do something with those tables mysql says: #1017 - Can't find file: 'ca_tournaments' (errno: 2) (replace 'ca_tournaments' with every table name of the database...) What I need is the structure of the tables, not the data, but I don't know how to do it, so, what can I do?
View Replies !
Copy .frm Files To Db1 To Db2 To Create New Tables
i have .frm,.MYD,.MYI files on a local folder. I have installed mysql 5.0 command line client.i created a database called 'ABCD' and wish to use all these .frm , .MYD and .MYI files to create tables in this database.how do i do that? I have MySQL Maestro software which shows no way to create tables from this .frm files. I will have to use MySQL command line client. Could anyone please help me with this ...or suggest some other way by which i can create the tables.
View Replies !
Metadata Files Exist, But Tables Don't Show Up In The Client?
I have an NDB cluster set up, and in /var/lib/mysql/dbname/ there are all my .frm and .ndb files with all the table metadata. When I run the client, I can see the database but none of the tables show up. If I try to re-create a table, I get an "ERROR 1050 (42S01): Table 'account' already exists" error. If I try to drop the table, I get an "ERROR 1051 (42S02): Unknown table 'account'" error. I'm assuming it's safe for me to just delete all those files since they're just metadata, and then try re-creating the tables. (Not sure if this is an ndb-specific issue, and if I should have posted it that forum instead. It seems like it should be something easy so I figured I'd start here first.)
View Replies !
Can I Replace Corrupt MyISAM Tables By Just Copying Files From A Backup Directory?
I have a myisam table with about 1.9 million rows in it. This table has been badly indexed. An Atmysqlt to build a new index failed and the table (I suspect the myi file) has corrupted. Fortunately this occurred on a beta site. Unfortunately this is on the same server as the live site (no budget for a beta server) I can't shutdown the database server to atmysqlt a repair. Can I just copy the files back from the backup? How can I build this vitally needed index - every time a particular query is run it sends the server to its knees. It's run a lot
View Replies !
Backup :: .sql Files OR .myd .myi And .frm Files?
my host went down and so I got backups. Well, what I received is the raw data and stuff, not .sql files. For what should be simple .sql files instead I have craploads of .myd, .myi, and .frm files. How do I put all this stuff together to get a single sql file so I can upload it into mysql and get my sites working again?
View Replies !
Loading A New DB
I'm trying to populate a new copy of my database from the dump script taken from an overnight backup. It starts out OK but then errors with: ERROR 1060 (42S21) at line nnn: Duplicate column name 'id'. Taking a look at the script I can see that the previous couple of tables have loaded without problem but the offending CREATE TABLE looks like this: CREATE TABLE xxx (id int(11) NOT NULL auto_increment, <other columns...> ,PRIMARY KEY (id,id)) ... The id column is only defined once but is duplicated in the PRIMARY KEY definition - strange! Looking through the script I see this is the case on some of the other CREATE TABLE statements also
View Replies !
|
TRACKER
