The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 20, 2019, 11:35:01 AM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: mysql hogging resources upon startup  (Read 6539 times)
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« on: July 31, 2009, 10:12:15 PM »

any idea why?

for about 20 minutes after startup it just crawls.
Is it building the indexes or something?

any ideas what to do about it?

On my live server it causes a backlog of queries that just compounds the problem.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: August 01, 2009, 11:30:38 AM »

Is this server slaved or mastering with others? That's the only thing that initially comes to mind that would kick off when it restarts... how often are you clearing the master/slave files / event logs?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #2 on: August 01, 2009, 12:31:56 PM »

it is not. its standalone.

though now its going to be master-master, whenever my BH database ever finishes importing into the new server.... 3.5 hours and counting... oy.

Only thing I thought was maybe its the fulltext indexes being built?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: August 01, 2009, 02:37:07 PM »

Once fulltext is built, it is all done except for new C(R)UD. I am baffled and have never seen a big come up time... in fact, mine start up and are immediately ready, even in instances where I have many many databases or rows. Even on my oldest instance, a 3.4.3, with about 10MM cart-memos, multiple indicies and fulltext on a mind-numbingly slow server, it comes up and is done.

Is there some kind of "check everything when it starts up" flag happening or something?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
isthisthingon
Global Moderator
Lifer
*****
Offline Offline

Posts: 2879



View Profile
« Reply #4 on: August 01, 2009, 04:03:27 PM »

This is reaching a bit since I can't think of what might be happening.  In case you haven't already done so, I'd spend a little time reconfiguring your instance in various ways.  One issue that is not commonly considered is when too much RAM is assigned to your MySQL instance, and obviously too little will hit performance as well - but consistently.  Since this is at start up it got me thinking about what happens to the OS when it's being squeezed out of memory - it can take a long time RAM swapping before it completes what it needs to do during start up.  Also obviously, if anything else is hitting the hard drive(s) MySQL will take a corresponding performance hit. 

Another possibility is that somehow indexes are either getting corrupted or flagged for rebuilding.  If your diags on the box check out including RAM size/health, drive health (and enough room of course), then after reconfiguring the instance I'd try improving on your downtime cleanup procedures.  Not sure if this is a 24/7/365 high traffic server but in the lowest usage periods I'd make sure to schedule all possible cleanup I could (event log at least).  Re-indexing during off-peak hours while keeping the instance alive may help as well.

Good luck nuts!
Logged

I would love to change the world, but they won't give me the source code.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #5 on: August 01, 2009, 05:17:01 PM »

interesting. I gotta check the ram configs, which frankly are a bit baffling to me but ill figure it out. I have 8 gigs, and this box is mysql only. no apache even.

The databases come right up, and start processing queries, but the server load gets pegged high, and CPU% is about a solid 85% for mysqld, for about 40 minutes this time.

Aside from that everything runs great.

Currently I am still waiting on my import into my 2nd DB server. which is now on to hour# 7...
28gigs from a mysqldump. lordy this is slow as sin. wonder if if it will finish today?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: August 03, 2009, 05:04:23 PM »

If I were in your shoes, I'd either open the mysql command line and SHOW PROCESSLIST, or throw a quick Apache instance and phpMyAdmin up on it so that you can see what's going on. The process list will tell you what is busy.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #7 on: August 03, 2009, 06:27:50 PM »

i do.
heres the kicker...
NOTHING IS RUNNING in the process list.
Other than the normal queries that are running also when the server calms down.

it must be a config issue.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: August 03, 2009, 06:33:21 PM »

Wait... there are NO processes running in the mysqld, yet you show an 85% usage against your processors? Manohmah, wtf? That's just crazy talk.

You must have the "extra special" version of the d that I had compiled for, um, special instances. Pay no attention to your bandwidth usage in those first 30 minutes either. That SMTP-looking packet is nothing. Really.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #9 on: August 03, 2009, 08:25:43 PM »

lol. you can get to my SQL box, you can have the bandwidth Smiley

But yea, thats whats got me completely confused.
like right now my numbers are this after running for many hours and having settled down.
Code:
top - 20:17:42 up 1 day, 21:43,  1 user,  load average: 1.51, 1.01, 0.87
Tasks:  71 total,   1 running,  70 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.4%us,  0.1%sy,  0.0%ni, 76.4%id, 23.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   8259964k total,  8231900k used,    28064k free,   172524k buffers
Swap: 19800072k total,    45096k used, 19754976k free,  7677228k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4912 mysql     20   0  505m 138m 6676 S    7  1.7  77:46.07 mysqld
    1 root      20   0  4020  884  592 S    0  0.0   0:01.00 init

so, a whopping 7% CPU for mysql.
yet a load of 1.51 wtf?

Top makes my brain hurt.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #10 on: August 03, 2009, 08:49:07 PM »

Top sucketh. Question: if you throw a processor heavy task at it in the early morning of the processor, does MySQL bow out and let it go? Could it be some kind of idle process that's running because nothing else is?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: August 03, 2009, 09:07:18 PM »

I would think it would show in top then?

in the end I probably don't care. Shit is smoking fast anyway.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: August 03, 2009, 09:09:53 PM »

No, what I mean is if you write a little PHP app that tries to figure PI to 19 bazillion digits or something, and run that right after you've started the machine (ie., burden the processor with something of your own making while the MySQL process is Bogarting the cycles) does MySQL back off? My question is to if there's some kind of idle thing that MySQL is doing that would drop out if another process came in - that might give us a clue.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #13 on: August 03, 2009, 09:22:11 PM »

My DB servers are pure mysql. no apache or anything else for that matter.

not sure how to test it. guess a perl script?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #14 on: August 03, 2009, 09:45:17 PM »

That'd probably work... either Bomps or Dirk are your guy. The truth would seem to be that if everything is fasternshit then WGAF... but if you really want to know, that'd be the next place I'd try.

Bear in mind also that you could download PHP, configure, make and make install and it will be the same kind of thing that PERL is on the box - just an interpreter waiting to be called - it would never do anything except when you wanted something. I install PHP on my boxes as a matter of course, just so I have my favorite scripting language on the box, should I need it.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!