next »

[kurdt]

I have some spidering going on with A LOT of urls and I started to wonder why the hell script seems to run so slow. I found out that MySQL queries were taking A LOT of time to complete. Then I logged into phpmyadmin and checked some stats.

It gives me these numbers:
ø per hour
17.09 k
ø per minute
284.81
ø per second
4.75

Is it really that slow? I mean 5 queries per second. That's really slow when you need to check 20000 duplicates. Of course you could get all the rows into memory and check with PHP but when you have over 1000000 rows it starts to take a lot of memory.

How can I make this more efficient? Now it seems few parts of my script are causing long delays because checks they have to run for every processed url.

[nutballs]

Couple things.

What typeof machine is this running on?
Are other thing going that are eating power?
Are you SURE it's mysql bottlenecking? Low queries per second can be because you are sending only that many queries...

Indexes indexes indexes.

[kurdt]

Couple things.

What typeof machine is this running on?
Are other thing going that are eating power?
Are you SURE it's mysql bottlenecking? Low queries per second can be because you are sending only that many queries...

Indexes indexes indexes.

Ok, "system" is my home server. AMD Sempron 3200+ (1.8Ghz), 2GB RAM and 7200rpm harddrive. Basically it's normal Sempron era home computer that runs Ubuntu. When you look at top, mysqld eats about 30% CPU constantly and there's 5-6 php processes running each eating about 15% CPU. CPU is pretty much 93% used all the time. Scripts are really simple PHP scripts. Some basic for loops that check if value X exists already in certain table. I tested PHP speed just few mins ago with all the processes running that I mentioned above with simple for loop that adds 1 to $a with every round and I got 251298 per second as result. Of course this isn't very accurate benchmark but it tells me that speed of PHP or my script structure is not the problem. In theory I should be able to do 251298 SQL queries per second.

[nutballs]

sort of.

my point about mysql not being the bottleneck was not about your code quality, but possibly an external bottleneck. For example, you say its a spider, soooo, are you sure its actually spidering at a high speed? maybe the pipe is slow or has a lot of hops?

But overall, that TOP doesnt sound right.
each php is eating 15%??? thats harsh.
That makes me think that the PHP script is doing alot of the heavy lifting, and would be the bottleneck?
To give you a comparison
I run 120 queries per second average, but spike to about 300 every half hour due to a process.
These are not just select where type queries. These are Insert Ignore, fulltext queries, and of course some plain old Select Where.
I have 25MILLION slow queries in the past 14 days. I have slow set to 5 seconds... LOL

top looks like this pretty much constant:
top - 09:06:37 up 14 days, 23:07,  1 user,  load average: 1.14, 1.20, 1.04
Tasks:  70 total,   2 running,  68 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.3%sy,  0.0%ni, 79.2%id, 19.3%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   8259964k total,  8230916k used,    29048k free,   180284k buffers
Swap: 19800072k total,    45092k used, 19754980k free,  7626572k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4946 mysql     20   0  412m 145m 6632 S    4  1.8   2580:48 mysqld
    1 root      20   0  4020  880  592 S    0  0.0   0:01.14 init

machine is Dual Opteron Dual-core 2.8ghz with 8gb ram and a raid1 array, running ubuntu server.
So obviously quite a bit more power, but it seems disproportionate to me?

[perkiset]

Agree, 15%/php instance is wildly Bogart.

Points to look at:
* Indexes, like Nuts says
* smaller tighter scripts - if you have things that are repetitively DB, you might consider moving some of that logic into a stored procedure, since it will be way faster.
* Can you separate the machines that do spidering from PHP from the DB? If the TCP stack is crushed (spider and talking to MySQL) and the processor is mashed, then MySQL will have a hard time finding air.

I think something is run amok on you.