Loading date takes a long time after replication

--------------090506080108080303050409
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

All,

I have a situation. We have a very large database(gigabytes). When we
load the data in to the table, it will be huge again (20 million rows).
So in order to get good backup we set up a replication(master-slave
setup). But now, because of this setup, loading into the table takes
hours which will be generally done in a few minutes otherwise.

Is there a workaround for this? We really don't want to wait for hours
for the table to be loaded. Should we abandon replication for this? Any
pointers ?

--
Thanks,
Sairam Krishnamurthy
+1 612 859 8161


--------------090506080108080303050409--

Re: Loading date takes a long time after replication

--00032555820223f34c0496bb83fc
Content-Type: text/plain; charset=ISO-8859-1

Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the same disk
as your data.

Put the binary logs on separate disks, and you'll notice a dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy wrote:

> All,
>
> I have a situation. We have a very large database(gigabytes). When we load
> the data in to the table, it will be huge again (20 million rows). So in
> order to get good backup we set up a replication(master-slave setup). But
> now, because of this setup, loading into the table takes hours which will be
> generally done in a few minutes otherwise.
>
> Is there a workaround for this? We really don't want to wait for hours for
> the table to be loaded. Should we abandon replication for this? Any pointers
> ?
>
> --
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00032555820223f34c0496bb83fc--

Re: Loading date takes a long time after replication

--00163683394aa0b5210496bb9c22
Content-Type: text/plain; charset=ISO-8859-1

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to SAN storage and
it was quite fast.

regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman wrote:

> Are you saying that mass inserts go much slower now that you've set up
> replication? In that case, I suspect you have your binlogs on the same disk
> as your data.
>
> Put the binary logs on separate disks, and you'll notice a dramatic
> increase
> in performance.
>
>
> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy > >wrote:
>
> > All,
> >
> > I have a situation. We have a very large database(gigabytes). When we
> load
> > the data in to the table, it will be huge again (20 million rows). So in
> > order to get good backup we set up a replication(master-slave setup). But
> > now, because of this setup, loading into the table takes hours which will
> be
> > generally done in a few minutes otherwise.
> >
> > Is there a workaround for this? We really don't want to wait for hours
> for
> > the table to be loaded. Should we abandon replication for this? Any
> pointers
> > ?
> >
> > --
> > Thanks,
> > Sairam Krishnamurthy
> > +1 612 859 8161
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--00163683394aa0b5210496bb9c22--

Re: Loading date takes a long time after replication

--------------030207050305080707050808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks Guys. Let me check these options and will get back with the
results. Thanks again.

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:
> Also, make sure your /tmp folder is on a separate and fast disk.
> We had similar issues and we moved /tmp folder from Local to SAN
> storage and it was quite fast.
>
> regards
> anandkl
>
> On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman > > wrote:
>
> Are you saying that mass inserts go much slower now that you've set up
> replication? In that case, I suspect you have your binlogs on the
> same disk
> as your data.
>
> Put the binary logs on separate disks, and you'll notice a
> dramatic increase
> in performance.
>
>
> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
> >wrote:
>
> > All,
> >
> > I have a situation. We have a very large database(gigabytes).
> When we load
> > the data in to the table, it will be huge again (20 million
> rows). So in
> > order to get good backup we set up a replication(master-slave
> setup). But
> > now, because of this setup, loading into the table takes hours
> which will be
> > generally done in a few minutes otherwise.
> >
> > Is there a workaround for this? We really don't want to wait for
> hours for
> > the table to be loaded. Should we abandon replication for this?
> Any pointers
> > ?
> >
> > --
> > Thanks,
> > Sairam Krishnamurthy
> > +1 612 859 8161
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>

--------------030207050305080707050808--

Re: Loading date takes a long time after replication

In infinite wisdom Sairam Krishnamurthy wrote:

> [1 ]
> All,
>
> I have a situation. We have a very large database(gigabytes). When we
> load the data in to the table, it will be huge again (20 million rows).
> So in order to get good backup we set up a replication(master-slave
> setup). But now, because of this setup, loading into the table takes
> hours which will be generally done in a few minutes otherwise.

What part of the system is busy the most when you load the data - is it
the CPU or the disk or the network? Replication should not slow down
the speed of the insert, this might just be a red herring and the
problem might be somewhere else.

--
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.
-
Read the latest at my blog: "Humor in the bookstore"



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Loading date takes a long time after replication

--------------050200010702050805040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

I set up replication some days back. Because of some internal issues we
canceled the replication and reset the master to be a normal database
server. But after this "revert", the database is unusually slow.

It was slow because of the discussion below when replication was on. But
I wonder why it is slow now after the master has been reset. By reset I
mean, commenting out the master setup lines in the my.cnf file, removal
of logs and restarting the database.

Thanks in advance

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:
> Also, make sure your /tmp folder is on a separate and fast disk.
> We had similar issues and we moved /tmp folder from Local to SAN
> storage and it was quite fast.
>
> regards
> anandkl
>
> On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman > > wrote:
>
> Are you saying that mass inserts go much slower now that you've set up
> replication? In that case, I suspect you have your binlogs on the
> same disk
> as your data.
>
> Put the binary logs on separate disks, and you'll notice a
> dramatic increase
> in performance.
>
>
> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
> >wrote:
>
> > All,
> >
> > I have a situation. We have a very large database(gigabytes).
> When we load
> > the data in to the table, it will be huge again (20 million
> rows). So in
> > order to get good backup we set up a replication(master-slave
> setup). But
> > now, because of this setup, loading into the table takes hours
> which will be
> > generally done in a few minutes otherwise.
> >
> > Is there a workaround for this? We really don't want to wait for
> hours for
> > the table to be loaded. Should we abandon replication for this?
> Any pointers
> > ?
> >
> > --
> > Thanks,
> > Sairam Krishnamurthy
> > +1 612 859 8161
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>

--------------050200010702050805040902--

Re: Loading date takes a long time after replication

--20cf300258cc080ff80498ec6d3f
Content-Type: text/plain; charset=ISO-8859-1

What is the output of "show full processlist"

On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy wrote:

> Hi all,
>
> I set up replication some days back. Because of some internal issues we
> canceled the replication and reset the master to be a normal database
> server. But after this "revert", the database is unusually slow.
>
> It was slow because of the discussion below when replication was on. But I
> wonder why it is slow now after the master has been reset. By reset I mean,
> commenting out the master setup lines in the my.cnf file, removal of logs
> and restarting the database.
>
> Thanks in advance
>
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>
> On 12/06/2010 04:47 AM, Ananda Kumar wrote:
>
>> Also, make sure your /tmp folder is on a separate and fast disk.
>> We had similar issues and we moved /tmp folder from Local to SAN storage
>> and it was quite fast.
>>
>> regards
>> anandkl
>>
>> On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman >> vegivamp@tuxera.be>> wrote:
>>
>> Are you saying that mass inserts go much slower now that you've set up
>> replication? In that case, I suspect you have your binlogs on the
>> same disk
>> as your data.
>>
>> Put the binary logs on separate disks, and you'll notice a
>> dramatic increase
>> in performance.
>>
>>
>> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
>> >wrote:
>>
>>
>> > All,
>> >
>> > I have a situation. We have a very large database(gigabytes).
>> When we load
>> > the data in to the table, it will be huge again (20 million
>> rows). So in
>> > order to get good backup we set up a replication(master-slave
>> setup). But
>> > now, because of this setup, loading into the table takes hours
>> which will be
>> > generally done in a few minutes otherwise.
>> >
>> > Is there a workaround for this? We really don't want to wait for
>> hours for
>> > the table to be loaded. Should we abandon replication for this?
>> Any pointers
>> > ?
>> >
>> > --
>> > Thanks,
>> > Sairam Krishnamurthy
>> > +1 612 859 8161
>> >
>> >
>>
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>>
>>

--20cf300258cc080ff80498ec6d3f--

Re: Loading date takes a long time after replication

--------------030808030401020006090302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Apparently nothing. Just the "Show process list" query.

70100 | | localhost | | Query | 0 | NULL |
show full processlist

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 01/03/2011 01:51 AM, Ananda Kumar wrote:
> What is the output of "show full processlist"
>
> On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy
> > wrote:
>
> Hi all,
>
> I set up replication some days back. Because of some internal
> issues we canceled the replication and reset the master to be a
> normal database server. But after this "revert", the database is
> unusually slow.
>
> It was slow because of the discussion below when replication was
> on. But I wonder why it is slow now after the master has been
> reset. By reset I mean, commenting out the master setup lines in
> the my.cnf file, removal of logs and restarting the database.
>
> Thanks in advance
>
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>
> On 12/06/2010 04:47 AM, Ananda Kumar wrote:
>
> Also, make sure your /tmp folder is on a separate and fast disk.
> We had similar issues and we moved /tmp folder from Local to
> SAN storage and it was quite fast.
>
> regards
> anandkl
>
> On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman
>
> >> wrote:
>
> Are you saying that mass inserts go much slower now that
> you've set up
> replication? In that case, I suspect you have your binlogs
> on the
> same disk
> as your data.
>
> Put the binary logs on separate disks, and you'll notice a
> dramatic increase
> in performance.
>
>
> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
>
> >>wrote:
>
>
> > All,
> >
> > I have a situation. We have a very large database(gigabytes).
> When we load
> > the data in to the table, it will be huge again (20 million
> rows). So in
> > order to get good backup we set up a replication(master-slave
> setup). But
> > now, because of this setup, loading into the table takes hours
> which will be
> > generally done in a few minutes otherwise.
> >
> > Is there a workaround for this? We really don't want to wait for
> hours for
> > the table to be loaded. Should we abandon replication for this?
> Any pointers
> > ?
> >
> > --
> > Thanks,
> > Sairam Krishnamurthy
> > +1 612 859 8161
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>
>

--------------030808030401020006090302--

Re: Loading date takes a long time after replication

--------------040001000209070801000702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Any pointers on this issue ?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 01/03/2011 01:51 AM, Ananda Kumar wrote:
> What is the output of "show full processlist"
>
> On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy
> > wrote:
>
> Hi all,
>
> I set up replication some days back. Because of some internal
> issues we canceled the replication and reset the master to be a
> normal database server. But after this "revert", the database is
> unusually slow.
>
> It was slow because of the discussion below when replication was
> on. But I wonder why it is slow now after the master has been
> reset. By reset I mean, commenting out the master setup lines in
> the my.cnf file, removal of logs and restarting the database.
>
> Thanks in advance
>
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>
> On 12/06/2010 04:47 AM, Ananda Kumar wrote:
>
> Also, make sure your /tmp folder is on a separate and fast disk.
> We had similar issues and we moved /tmp folder from Local to
> SAN storage and it was quite fast.
>
> regards
> anandkl
>
> On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman
>
> >> wrote:
>
> Are you saying that mass inserts go much slower now that
> you've set up
> replication? In that case, I suspect you have your binlogs
> on the
> same disk
> as your data.
>
> Put the binary logs on separate disks, and you'll notice a
> dramatic increase
> in performance.
>
>
> On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
>
> >>wrote:
>
>
> > All,
> >
> > I have a situation. We have a very large database(gigabytes).
> When we load
> > the data in to the table, it will be huge again (20 million
> rows). So in
> > order to get good backup we set up a replication(master-slave
> setup). But
> > now, because of this setup, loading into the table takes hours
> which will be
> > generally done in a few minutes otherwise.
> >
> > Is there a workaround for this? We really don't want to wait for
> hours for
> > the table to be loaded. Should we abandon replication for this?
> Any pointers
> > ?
> >
> > --
> > Thanks,
> > Sairam Krishnamurthy
> > +1 612 859 8161
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>
>

--------------040001000209070801000702--