List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 2 2012 6:51pm
Subject:RE: problems with INNODB tables
View as plain text  
Some mixture.  Perhaps 35% of RAM for buffer_pool and 10% for key_buffer.  It depends on
which needs more caching.

Note:  The key_buffer does not need to be bigger than the total of all MyISAM indexes
(Index_length in SHOW TABLE STATUS, or size of .MYI files).  The buffer_pool does not
need to be bigger than the total of data+index for InnoDB files.

> -----Original Message-----
> From: Malka Cymbalista [mailto:malki.cymbalista@stripped]
> Sent: Wednesday, April 25, 2012 3:15 AM
> To: Rick James; Andrés Tello
> Cc: mysql@stripped; Shlomit Afgin; Ronen Hayun
> Subject: RE: problems with INNODB tables
> 
> Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory
> where it tells you to do one thing if using MYIASM tables and another
> if using INNODB tables.  We are using both. Any suggestions?
> Thanks for any help.
> 
> Malki Cymbalista
> Webmaster, Weizmann Institute of Science
> Malki.cymbalista@stripped
> 08-9343036
> 
> 
> -----Original Message-----
> From: Rick James [mailto:rjames@stripped]
> Sent: Monday, April 23, 2012 9:42 PM
> To: Andrés Tello; Malka Cymbalista
> Cc: mysql@stripped; Shlomit Afgin; Ronen Hayun
> Subject: RE: problems with INNODB tables
> 
> Check your memory usage according to
> http://mysql.rjweb.org/doc.php/memory
> 
> > -----Original Message-----
> > From: Andrés Tello [mailto:mr.criptos@stripped]
> > Sent: Monday, April 23, 2012 9:00 AM
> > To: Malka Cymbalista
> > Cc: mysql@stripped; Shlomit Afgin; Ronen Hayun
> > Subject: Re: problems with INNODB tables
> >
> > Weird, I use a lot Innodb, and no issue, I even kill bravely the
> mysql
> > process with pkill -9 -f mysql
> >
> > Y suppose the way drupal is being programed.
> > PHP open and closes database connections each time a webpage with db
> > access is issued.
> > When a php exceution ends and the apache webserver have fullfilled
> the
> > http request, again, php memory is freed and connections closed...
> > UNLESS:.. you are using a mem cached db connection, wich I doubt it
> > since drupal doens't requiere one, or using persistent connections,
> > again, I doubt it, because persistante database connections aren't
> > recommended to innodb tables...
> >
> > Mysql server by default can handles 100 conections, if you get to
> > thata limit you need to fine tune the number of connections allowed.
> >
> > show full processlist can give you a better idea of what is going on,
> > connections with the sleep status, are open connections with no
> > currently no transacctions...
> >
> > I never use script based stop, I always use mysqladmin -u root -p -h
> > localhost shutdown which properly tells mysql to flush tables and
> > terminate.
> >
> > I can almost bet that you are using Ubuntu... ubuntu had given me
> > sometimes very hard times because of the "edgy " code they use to
> use,
> > ext4 last version, and so on... what can you tell us about that?
> >
> > How much amount of memory you have?
> > How much concurrent apache/php users you have?
> > Can you provide more cuantitive data please? Hardware, php version,
> > distro, kernel...
> >
> > Cheers...
> >
> >
> >
> > To start, 100 process is quite a lot, something isn't fine. Each time
> >
> > On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista <
> > malki.cymbalista@stripped> wrote:
> >
> > > We are running MySQL version 5.0.45 on a Linux machine. Over the
> > > past few months we have been having several problems:
> > >
> > > 1.       Our mysql processes have increased the memory used from
> > about .3%
> > > per process to 8% per process
> > >
> > > 2.       We sometimes can have over 100 processes running which
> > brings the
> > > machine to its knees and we have to stop and start MySQL in order
> to
> > > kill all the processes. We think that maybe the processes are not
> > > finishing normally and are just hanging around.
> > >
> > > 3.       The machine is a web server and in the last few months we
> > are
> > > moving over to drupal 7 to build our sites and Drupal 7 requires
> > INNODB
> > > tables.   Sometimes, when we restart MySQL using the commands
> > > /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that
> > were
> > > built in drupal 7 do not come up.  In order for the INNODB tables
> to
> > > work, we have to stop mysql, rename the ibdata1 file, copy it back
> > > to
> > > ibdata1 and then restart mysql. Otherwise the INNODB tables are not
> > accessable.
> > >
> > >
> > >
> > > In the past all our tables were MYIASM.  Our problems started as we
> > > started using more and more INNODB tables. Is there anything
> special
> > > that has to be done to configure MySQL when using INNODB tables?
> > > We clearly have a problem  but we have no idea where to start
> > looking. Our
> > > error logs don't show anything.   If anyone has any suggestions, we
> > will be
> > > happy to hear them.
> > > We are considering hiring a consultant who is an expert in MySQL.
> We
> > > are in Israel and we are open to suggestions.
> > >
> > > Thanks for any help.
> > >
> > > Malki Cymbalista
> > > Webmaster, Weizmann Institute of Science
> > >
> >
> Malki.cymbalista@stripped<mailto:Malki.cymbalista@stripped
> > > >
> > > 08-9343036
> > >
> > >
Thread
problems with INNODB tablesMalka Cymbalista23 Apr
  • Re: problems with INNODB tablesAndrés Tello23 Apr
    • RE: problems with INNODB tablesRick James23 Apr
      • RE: problems with INNODB tablesMalka Cymbalista25 Apr
        • Re: problems with INNODB tablesAndrés Tello25 Apr
        • RE: problems with INNODB tablesRick James2 May