SEO

Hello everyone,

We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
MySQL with a mix of InnoDB and MyISAM tables.

We normally run at 1500 queries/per second and lately, the server will
all of a sudden lock up and we are forced to restart mysql.

This is not related to higher load, as it frequently happens during
our slowest load period (early AM). This is not related to any
significant code changes.

We have recently converted about 20 of our largest and most frequently
used tables from MyISAM to InnoDB. The size of our InnoDB tables
collectively is about 95GB.

I can provide my configuration if it is necessary, but was just
wondering if someone else might have experienced this and could
possibly point me in the right direction as to what might be causing
this.

Thanks,
Kevin
--
Kevin Old
kevinold@gmail.com

Hi,

Do you use MySQL with a NPTL or a linuxthread glibc configuration ?

Regards,
Jocelyn Fournier
www.mesdiscussions.net

> Hello everyone,
>
> We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> MySQL with a mix of InnoDB and MyISAM tables.
>
> We normally run at 1500 queries/per second and lately, the server will
> all of a sudden lock up and we are forced to restart mysql.
>
> This is not related to higher load, as it frequently happens during
> our slowest load period (early AM). This is not related to any
> significant code changes.
>
> We have recently converted about 20 of our largest and most frequently
> used tables from MyISAM to InnoDB. The size of our InnoDB tables
> collectively is about 95GB.
>
> I can provide my configuration if it is necessary, but was just
> wondering if someone else might have experienced this and could
> possibly point me in the right direction as to what might be causing
> this.
>
> Thanks,
> Kevin
> --
> Kevin Old
> kevinold@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
>
>

Another thought is - do you have backups running during this time?
Something that might be attempting to backup live the InnoDB files?
We had similar problems with MySQL and backup software a while ago,
though we used all-MyISAM.

Dan


On 12/7/06, joce@presence-pc.com <joce@presence-pc.com> wrote:
> Hi,
>
> Do you use MySQL with a NPTL or a linuxthread glibc configuration ?
>
> Regards,
> Jocelyn Fournier
> www.mesdiscussions.net
>
> > Hello everyone,
> >
> > We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> > MySQL with a mix of InnoDB and MyISAM tables.
> >
> > We normally run at 1500 queries/per second and lately, the server will
> > all of a sudden lock up and we are forced to restart mysql.
> >
> > This is not related to higher load, as it frequently happens during
> > our slowest load period (early AM). This is not related to any
> > significant code changes.
> >
> > We have recently converted about 20 of our largest and most frequently
> > used tables from MyISAM to InnoDB. The size of our InnoDB tables
> > collectively is about 95GB.
> >
> > I can provide my configuration if it is necessary, but was just
> > wondering if someone else might have experienced this and could
> > possibly point me in the right direction as to what might be causing
> > this.
> >
> > Thanks,
> > Kevin
> > --
> > Kevin Old
> > kevinold@gmail.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

RAM too, how are you on RAM?

Obviously 100GB+ probably isn't going to fit in cache, but the usage pattern
during slower periods might be causing killer thrashing.

On Thu, Dec 07, 2006 at 12:43:17PM -0600, Dan Buettner wrote:
> Another thought is - do you have backups running during this time?
> Something that might be attempting to backup live the InnoDB files?
> We had similar problems with MySQL and backup software a while ago,
> though we used all-MyISAM.
>
> Dan
>
>
> On 12/7/06, joce@presence-pc.com <joce@presence-pc.com> wrote:
> >Hi,
> >
> >Do you use MySQL with a NPTL or a linuxthread glibc configuration ?
> >
> >Regards,
> > Jocelyn Fournier
> > www.mesdiscussions.net
> >
> >> Hello everyone,
> >>
> >> We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> >> MySQL with a mix of InnoDB and MyISAM tables.
> >>
> >> We normally run at 1500 queries/per second and lately, the server will
> >> all of a sudden lock up and we are forced to restart mysql.
> >>
> >> This is not related to higher load, as it frequently happens during
> >> our slowest load period (early AM). This is not related to any
> >> significant code changes.
> >>
> >> We have recently converted about 20 of our largest and most frequently
> >> used tables from MyISAM to InnoDB. The size of our InnoDB tables
> >> collectively is about 95GB.
> >>
> >> I can provide my configuration if it is necessary, but was just
> >> wondering if someone else might have experienced this and could
> >> possibly point me in the right direction as to what might be causing
> >> this.
> >>
> >> Thanks,
> >> Kevin
> >> --
> >> Kevin Old
> >> kevinold@gmail.com
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
> >>
> >>
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mbac@netgraft.com

--
Michael Bacarella <mbac@netgraft.com>

1-646-641-8662 (cell)

545 Eighth Avenue * Suite 401
New York, NY 10018

http://michael.bacarella.com/
http://netgraft.com/

No backups (like innodb-backup) running during these times.

We have 16GB of RAM and are currently using about 8GB of it.

We think we might have narrowed it down to a few hellish queries that
are hitting a few tables that we recently converted to InnoDB from
MyISAM. We're gonna convert them back to MyISAM tonight and see if
that fixes it.

Thanks for your help,
Kevin

On 12/7/06, Michael Bacarella <mbac@netgraft.com> wrote:
> RAM too, how are you on RAM?
>
> Obviously 100GB+ probably isn't going to fit in cache, but the usage pattern
> during slower periods might be causing killer thrashing.
>
> On Thu, Dec 07, 2006 at 12:43:17PM -0600, Dan Buettner wrote:
> > Another thought is - do you have backups running during this time?
> > Something that might be attempting to backup live the InnoDB files?
> > We had similar problems with MySQL and backup software a while ago,
> > though we used all-MyISAM.
> >
> > Dan
> >
> >
> > On 12/7/06, joce@presence-pc.com <joce@presence-pc.com> wrote:
> > >Hi,
> > >
> > >Do you use MySQL with a NPTL or a linuxthread glibc configuration ?
> > >
> > >Regards,
> > > Jocelyn Fournier
> > > www.mesdiscussions.net
> > >
> > >> Hello everyone,
> > >>
> > >> We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> > >> MySQL with a mix of InnoDB and MyISAM tables.
> > >>
> > >> We normally run at 1500 queries/per second and lately, the server will
> > >> all of a sudden lock up and we are forced to restart mysql.
> > >>
> > >> This is not related to higher load, as it frequently happens during
> > >> our slowest load period (early AM). This is not related to any
> > >> significant code changes.
> > >>
> > >> We have recently converted about 20 of our largest and most frequently
> > >> used tables from MyISAM to InnoDB. The size of our InnoDB tables
> > >> collectively is about 95GB.
> > >>
> > >> I can provide my configuration if it is necessary, but was just
> > >> wondering if someone else might have experienced this and could
> > >> possibly point me in the right direction as to what might be causing
> > >> this.
> > >>
> > >> Thanks,
> > >> Kevin
> > >> --
> > >> Kevin Old
> > >> kevinold@gmail.com
> > >>
> > >> --
> > >> MySQL General Mailing List
> > >> For list archives: http://lists.mysql.com/mysql
> > >> To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
> > >>
> > >>
> > >
> > >
> > >--
> > >MySQL General Mailing List
> > >For list archives: http://lists.mysql.com/mysql
> > >To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
> > >
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mbac@netgraft.com
>
> --
> Michael Bacarella <mbac@netgraft.com>
>
> 1-646-641-8662 (cell)
>
> 545 Eighth Avenue * Suite 401
> New York, NY 10018
>
> http://michael.bacarella.com/
> http://netgraft.com/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=kevinold@gmail.com
>
>


--
Kevin Old
kevinold@gmail.com

Kevin Old wrote:
> Hello everyone,
>
> We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> MySQL with a mix of InnoDB and MyISAM tables.
>
> We normally run at 1500 queries/per second and lately, the server will
> all of a sudden lock up and we are forced to restart mysql.

That isn't a crash.

Can you still connect to the db? If so output of 'show full
processlist\G', etc would be useful.

If it is truly locked up then running mytop in a background shell
somewhere might be a good idea. You would know what queries were
running when it locked up.

ds

Hi,

How much memory do you have on your system ? (the current setting in your
my.cnf could eat *a lot* of memory)

Thanks,
Jocelyn Fournier
www.mesdiscussions.net

> On 12/7/06, David Sparks <dave@ca.sophos.com> wrote:
>> Kevin Old wrote:
>> > Hello everyone,
>> >
>> > We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
>> > MySQL with a mix of InnoDB and MyISAM tables.
>> >
>> > We normally run at 1500 queries/per second and lately, the server will
>> > all of a sudden lock up and we are forced to restart mysql.
>>
>> That isn't a crash.
>>
>> Can you still connect to the db? If so output of 'show full
>> processlist\G', etc would be useful.
>
>
> Sorry, I shouldn't have worded it as a crash. Basically what happens
> is that queries start stacking up and if we don't start killing
> queries, we have to restart mysql before it crashes/restarts itself.
>
> We have spotted a couple of queries that we think might be causing the
> problems, but even after adjusting the queries and the tables
> (converting from InnoDB to MyISAM) we're still seeing the spike in
> queries (not due to actual load).
>
> Here are links to our my.cnf and the output of "show innodb status" and of
> "show variables":
>
> http://kold.homelinux.com/bj/my.cnf
> http://kold.homelinux.com/bj/innodbstatus.txt
> http://kold.homelinux.com/bj/mysqlstats.txt
>
> Maybe some of our settings could be causing the problem.
>
> Thanks for any help,
> Kevin
> --
> Kevin Old
> kevinold@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
>
>

On 12/7/06, David Sparks <dave@ca.sophos.com> wrote:
> Kevin Old wrote:
> > Hello everyone,
> >
> > We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> > MySQL with a mix of InnoDB and MyISAM tables.
> >
> > We normally run at 1500 queries/per second and lately, the server will
> > all of a sudden lock up and we are forced to restart mysql.
>
> That isn't a crash.
>
> Can you still connect to the db? If so output of 'show full
> processlist\G', etc would be useful.


Sorry, I shouldn't have worded it as a crash. Basically what happens
is that queries start stacking up and if we don't start killing
queries, we have to restart mysql before it crashes/restarts itself.

We have spotted a couple of queries that we think might be causing the
problems, but even after adjusting the queries and the tables
(converting from InnoDB to MyISAM) we're still seeing the spike in
queries (not due to actual load).

Here are links to our my.cnf and the output of "show innodb status" and of
"show variables":

http://kold.homelinux.com/bj/my.cnf
http://kold.homelinux.com/bj/innodbstatus.txt
http://kold.homelinux.com/bj/mysqlstats.txt

Maybe some of our settings could be causing the problem.

Thanks for any help,
Kevin
--
Kevin Old
kevinold@gmail.com

We have 16GB total, but are only using 8GB (according to mysql and our dbadmin).

Kevin

On 12/8/06, joce@presence-pc.com <joce@presence-pc.com> wrote:
> Hi,
>
> How much memory do you have on your system ? (the current setting in your
> my.cnf could eat *a lot* of memory)
>
> Thanks,
> Jocelyn Fournier
> www.mesdiscussions.net
>
> > On 12/7/06, David Sparks <dave@ca.sophos.com> wrote:
> >> Kevin Old wrote:
> >> > Hello everyone,
> >> >
> >> > We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of
> >> > MySQL with a mix of InnoDB and MyISAM tables.
> >> >
> >> > We normally run at 1500 queries/per second and lately, the server will
> >> > all of a sudden lock up and we are forced to restart mysql.
> >>
> >> That isn't a crash.
> >>
> >> Can you still connect to the db? If so output of 'show full
> >> processlist\G', etc would be useful.
> >
> >
> > Sorry, I shouldn't have worded it as a crash. Basically what happens
> > is that queries start stacking up and if we don't start killing
> > queries, we have to restart mysql before it crashes/restarts itself.
> >
> > We have spotted a couple of queries that we think might be causing the
> > problems, but even after adjusting the queries and the tables
> > (converting from InnoDB to MyISAM) we're still seeing the spike in
> > queries (not due to actual load).
> >
> > Here are links to our my.cnf and the output of "show innodb status" and of
> > "show variables":
> >
> > http://kold.homelinux.com/bj/my.cnf
> > http://kold.homelinux.com/bj/innodbstatus.txt
> > http://kold.homelinux.com/bj/mysqlstats.txt
> >
> > Maybe some of our settings could be causing the problem.
> >
> > Thanks for any help,
> > Kevin
> > --
> > Kevin Old
> > kevinold@gmail.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=joc@presence-pc.com
> >
> >
>
>


--
Kevin Old
kevinold@gmail.com

joce@presence-pc.com wrote:
> Hi,
>
> How much memory do you have on your system ? (the current setting in your
> my.cnf could eat *a lot* of memory)
>
min_memory_needed = global_buffers + (thread_buffers * max_connections)

thread_buffers
---------------------------+---------
sort_buffer_size | 12M
myisam_sort_buffer_size | 64M
read_buffer_size | 12M
join_buffer_size | 12M
read_rnd_buffer_size | 12M
---------------------------+----------
TOTAL | 112M

global_buffers
---------------------------+----------
key_buffer_size | 2048M

So worst case we're talking 33 and a bit Gig, but late night worst case scenario? maybe a 1000 connections?
So something like 15G, that's not that bad. I'd run mtop as someone suggested and see if some query is hammering it, maybe some other process on the machine is hogging or going IO bound?

Regards,
Phil