Split the myisam table into seperate tables. We will
call each table a bucket.
Create a MERGE table of all of them. For selecting the
data.
When inserting, use a hash function on your primary
key values to determine which bucket to insert into.
If you almost always select by primary key, then yo
--- matt ryan <[EMAIL PROTECTED]> wrote:
> >Do you ever delete from this table?
>
> Temp table is trunicated before the EBCDIC file is
> loaded
I meant the history table :)
> >Have you removed the unecessary duplicate key on
> the first column of your primary key?
>
> Have not touched the DIC in
Do you ever delete from this table?
Temp table is trunicated before the EBCDIC file is loaded
Have you removed the unecessary duplicate key on the first column of your primary key?
Have not touched the DIC index yet, I need a backup server to change
indexes, it would take the main server down for t
I don't see how using a multi value insert would be
any faster than the insert between the tables. It
would certainly be faster than one insert statement
per row, but I don't think it would be faster than
insert ... select ...
The only reason I suggested an extended syntax insert
earlier was beca
mysql -i < filename.sql
matt ryan wrote:
Lopez David E-r9374c wrote:
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename > filename.sql
This creates a file that inserts the records back into the same table
it also does not do an insert
Lopez David E-r9374c wrote:
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename > filename.sql
This creates a file that inserts the records back into the same table
it also does not do an insert ignore
I need the records to go into the
Lachlan Mulcahy wrote:
MySQL Version: 4.0.18
Server OS: windows 2000, or 2003
Memory 2 gig
CPU(s) dual 2.6-3ghz xeon 500-2mb cache (cpu load is low)
Disks (RAIDs and Independent disk speed/types) 8x72 gig 15,000 rpm scsi
II u320 raid 5 dell perc setup
--
MySQL General Mailing List
For list arch
I load all the data into a table with no keys
then I insert this data into a table with 225 million records, this
large table has the primary key, this is what takes a LONG time
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq
matt
>
> the issue is insert speed, I get 150k-1M records daily, of
> these, only
> 5-10 % are new records, I load the EBCDIC file into a temp table, and
> then do "insert ignore into historytable select * from temp table"
>
Since you have a temp table created (no keys I assume), use the comm
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename > filename.sql
This should create insert statements with many values in a single
insert. Then use the client program to insert them to you db.
mysql -u matt -p dbname < filename.sql
This
L2,5)),1,3) AS COL1,
SUBSTR (DISTINCT (RPAD(COL1,3) || RPAD(COL2,5)),4,5) AS COL2
FROM OLD_TABLE
matt ryan <[EMAIL PROTECTED]>
07/16/2004 07:43 AM
To:
cc: [EMAIL PROTECTED]
Subject: Re: Mysql growing pains, 4 days to create index on on
Donny Simonton wrote:
Matt,
I've been reading this thread for a while and at this point, I would say
that you would need to provide the table structures and queries that you are
running.
For example, we have one table that has 8 billion rows in it and it close to
100 gigs and we can hammer it all d
Matt,
I think I might have missed the start of this thread, as I can't seem to
find it.
Could you please post the following info (I don't mind if you just mail it
directly to me to save the list):
MySQL Version:
Server OS:
Server Hardware configuration:
- Memory
- CPU(s)
- Disks (RAIDs and In
> I've used it, with oracle, but oracles index
> searches are better, hit
> the best one first, then 2nd best, then 3rd, but I
> really dont want to
> go to oracle, it's too complicated for my tech's
Oracle rarely performs index merges, but it does have
the ability to do, which mysql lacks.
> Qu
s of everything.
Just my 2 cents.
Donny
> -Original Message-
> From: matt ryan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 15, 2004 9:32 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Mysql growing pains, 4 days to create index on one table!
>
> Tim Brody wrote:
>
&
Insert ignore doesn't insert the record if there is a
duplicate. It simply doesn't insert the row. Without
the IGNORE clause, the query would generate an error
insert of silenty ignoring the insert.
--- [EMAIL PROTECTED] wrote:
> That's the whole point. Eliminate your indexes and
> your load pro
a few suggestions...
Your slow inserts could be a concurrancy issue. If
lots of users are doing selects that could be
interfering with your inserts, especially if they use
a n odbc/jdbc app that locks the table for the entire
read. Jdbc reads do that when you tell it to "stream"
the contents of t
To: [EMAIL PROTECTED]
cc:
Subject: Re: Mysql growing pains, 4 days to create index on one table!
[EMAIL PROTECTED] wrote:
>You may want more indexes but you might be getting killed because you
already have too many.
>
>To test - try loading into a table without in
[EMAIL PROTECTED]
Subject: Re: Mysql growing pains, 4 days to create index on one table!
>
> You might be out of luck with MySQL ... sorry.
>
> You may need to switch to a database that has a parallel query
> facility. Then - every query becomes a massive table scan bu
You might be out of luck with MySQL ... sorry.
You may need to switch to a database that has a parallel query
facility. Then - every query becomes a massive table scan but gets
divided into multiple concurrent subqueries - and overall the job
finishes in a reasonable amount of time. The epitomy
- Original Message -
From: "matt ryan" <[EMAIL PROTECTED]>
> I need the DIC in the key to keep the record unique, I have thousands
> with everything identical except the DIC.
In your schema you had DIC in the PRIMARY KEY and an additional
(unnecessary?) KEY on DIC.
> I was confused on t
>Consider replicating to some slave servers and dividing reads among them.
I already replicate to slaves, and sites will do read only queries off
these slaves
99.9 % of the tables are read only anyway, the only tables we update or
insert into, are very very small and fast.
These big tables are
Tim Brody wrote:
You may find that the 'dic' KEY isn't necessary, as it's the first part of
your PRIMARY KEY.
I've found better performance for multi-column keys by putting the columns
in order of least variance first, e.g. for a list of dates:
1979-04-23
1979-07-15
1980-02-04
1980-06-04
You want a
I know it's naff but I've found it quicker to use "myisamchk" with row-sort
than it is to get the MySQL daemon to regenerate keys (and if you know
you're not changing the data file you can tell myisamchk not to copy the
data), unless I've missed something in the MySQL config ...
The only way I kno
Justin Swanhart wrote:
Indexes can generate vast amounts of random i/o.
Because of rotational latency, random i/o can really
slow you down, especially if you are using IDE or SATA
disks because they can't do tagged queueing like SCSI
disks can.
If you have the budget for it, I would consider
gett
Indexes can generate vast amounts of random i/o.
Because of rotational latency, random i/o can really
slow you down, especially if you are using IDE or SATA
disks because they can't do tagged queueing like SCSI
disks can.
If you have the budget for it, I would consider
getting some solid state di
[EMAIL PROTECTED] wrote:
You may want more indexes but you might be getting killed because you already have too
many.
To test - try loading into a table without indexes and see if it makes a difference.
At the very least - check to see if the primary index which starts with 'dic' can make
your sp
Egor Egorov wrote:
Are you running this under Microsoft Windows?
Yes, windows 2k and 2003, mysql-nt 4.0.16
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lopez David E-r9374c wrote:
matt
1) inserts using this format is much faster:
INSERT INTO table (col1, col2) VALUES (val1,val2), (val3,val4)
is much faster then single row insert. My experience is
2.5 hrs vs.. 36 hrs.
2) The PACK_KEYS=1 may be hurting you. I've never used it.
3) There may
Are you running this under Microsoft Windows?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL
You may want more indexes but you might be getting killed because you already have too
many.
To test - try loading into a table without indexes and see if it makes a difference.
At the very least - check to see if the primary index which starts with 'dic' can make
your special 'dic' index super
matt
1) inserts using this format is much faster:
INSERT INTO table (col1, col2) VALUES (val1,val2), (val3,val4)
is much faster then single row insert. My experience is
2.5 hrs vs.. 36 hrs.
2) The PACK_KEYS=1 may be hurting you. I've never used it.
3) There may be a cache somewhere
32 matches
Mail list logo
