Jon,
I do not know. Why not install a 64-bit Linux in your computer?
Regards,
Heikki
Oracle/Innobase
----- Alkuperäinen viesti -----
Lähettäjä: "Jon Frisby" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
<mysql@lists.mysql.com>
Lähetetty: Monday, October 17, 2005 8:46 PM
Aihe: RE: Non-linear degradation in bulk loads?
Actually, I believe we're running 32-bit, with bigmem... Does similar
behavior occur in such a scenario?
-JF
-----Original Message-----
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 16, 2005 2:23 AM
To: mysql@lists.mysql.com
Subject: Re: Non-linear degradation in bulk loads?
Jon,
your hardware/OS combination is quite new and unusual. It
might indeed be an OS problem. We observed from a 64-bit RHEL
4 that when the file cache of the OS grew bigger than 4 GB,
then the file I/O performance dropped to one tenth of the
normal. You would not expect that kind of behavior from a 64-bit OS.
When you see the slowdown, what does 'top' say about the OS
file cache size?
"
The database is our main sites database but we've
dramatically reduced the load on that machine over the past
couple months through careful optimization of our code. The
box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit
Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We
have 1GB allocated to the buffer pool, and our usual 1GB *
3 log files. 8 I/O threads.
Load on the box sits at around 6-7, with a large (>50%)
amount of time spent in wait state, but actual disk
throughput to our software RAID array (No longer on a SAN...)
is quite low -- 6-9k blocks/s out, 1-6k blocks/s in.
"
Regards,
Heikki
----- Original Message -----
From: ""Jon Frisby"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, October 14, 2005 10:39 PM
Subject: RE: Non-linear degradation in bulk loads?
> I've tried tweaking the structure of the schema to have,
for example, a =
> PRIMARY KEY index on email, no other indexes, and then
insert in sorted =
> order -- made no improvement whatsoever. Another clue that
leads me to =
> believe that this may be an OS issue: Starting a large cp
on the same =
> box (from a local filesystem other than the one the InnoDB
data pool was =
> on, to NFS) caused MySQL to become COMPLETELY backlogged
(we went from =
> ~15-20 connections at any given instant to 750 (our
max_connections =
> setting)).
>
> -JF
>
>
>> -----Original Message-----
>> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
>> Sent: Wednesday, October 12, 2005 8:15 AM
>> To: mysql@lists.mysql.com
>> Subject: Re: Non-linear degradation in bulk loads?
>>=20
>> Jon,
>>=20
>> hmm... maybe one of the indexes inevitably is in a random order.
>>=20
>> Please post a typical
>>=20
>> SHOW INNODB STATUS\G
>>=20
>> when the inserts happen slowly.
>>=20
>> What is your my.cnf like?
>>=20
>> Regards,
>>=20
>> Heikki
>> Innobase/Oracle
>>=20
>> ----- Alkuper=E4inen viesti -----
>> L=E4hett=E4j=E4: "Jon Frisby" <[EMAIL PROTECTED]>
>> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;=20
>> <mysql@lists.mysql.com>
>> L=E4hetetty: Wednesday, October 12, 2005 3:08 AM
>> Aihe: RE: Non-linear degradation in bulk loads?
>>=20
>>=20
>> > Two solutions: 1) sort the rows to be inserted on the
key 'email'=20
>> > before inserting.
>> >
>> > 2) Or:
>> >
>> > http://dev.mysql.com/doc/mysql/en/innodb-tuning.html
>> > "
>> > If you have UNIQUE constraints on secondary keys, starting=20
>> from MySQL
>> > 3.23.52 and 4.0.3, you can speed up table imports by
temporarily=20
>> > turning off the uniqueness checks during the import session:
>> > SET UNIQUE_CHECKS=3D0;
>> >
>> > For big tables, this saves a lot of disk I/O because
InnoDB can use=20
>> > its insert buffer to write secondary index records in a batch.
>> > "
>> >
>> > But make sure you do not have any duplicates in the rows!
>>=20
>> After sending my mail, I discovered SET UNIQUE_CHECKS=3D0, and=20
>> subsequent to that it also occurred to me to try putting the=20
>> data in in sorted order. Unfortunately, doing=20
>> UNIQUE_CHECKS=3D0 did not work, and even the combination of=20
>> both did not work. First chunk (3.4m rows) was ~1.5 minutes,=20
>> second was ~5 minutes...
>>=20
>> At this point I'm inclined to believe that there is something=20
>> very wrong with the disk subsystem because of this and other=20
>> problems (doing a large cp from the datapool filesystem to=20
>> another filesystem brought the database to a near-halt, among=20
>> other things).
>>=20
>> As a stop-gap solution, I created the table with no indexes,=20
>> and loaded all the data (loaded in linear time), and plan on=20
>> doing a CREATE UNIQUE INDEX on the table. Will this happen=20
>> in linear time, or near-linear time?
>>=20
>> *sigh*
>>=20
>> -JF=20
>>=20
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =20
>> http://lists.mysql.com/[EMAIL PROTECTED]
>>=20
>>=20
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
