Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm running LOCAL WAMPSERVER on a Quad Core 2.5 GHZ with 8 gb of RAM and when I'm running my custom script that read (a lot) in the Mysql DB, it'S pretty slow, a bit slower than when using a shared server.

When I pop the Task Manager, I see the CPU go from 3% to 30% while running the script, but not more.

So my question is why isnt it using at least 90% of it and giving me faster results?

I mean, a computer that run billions of operations/second should process some PHP/mysql script like... a lot faster.

Lighten me up!

(I've search all around and couldnt find the exact same problem...)

EDIT HOLY MACARONI. >> I hope this explanation helps other newbie like me GREATLY speed up their MYSQL database...

THANKS to gregmac & Eduardo Ivanec

I've been programming PHP/Mysql for quite some while now (self thought) and I can create some pretty crazy applications but I never got to create a REALLY big database that needed more than basic optimization knowledge (basically, if you build an application that runs fast, do what its supposed to and is good looking, why learn optimisation?).

But with this application (built for me, to ease my life), I have a 100meg db with "500 000" entries in one table and growing by a lot everyday. Basically, when I first learned MYSQL many years ago, I read quickly how it work and how to query and such but never got around "optimisation" thinking I would solve this when and IF need may be. So basically, I always only used the "INDEX" on the FIRST column of every table thinking that's what it was for.

When I read in gregmac comment about "If you are selecting data using a WHERE clause and there is no index on the field[...]" that quickly raised a flag and after some time at http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html, I learned THIS:

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

So I went in my db and set the INDEXes on the columns I was querying and BAM, 100x speed improvement! I hope this help other newbies of MYSQL like me!!

I know this might sound dumb not knowing this.... BUT, you know, when you're in website web, you have to know Javascript, HTML, CSS, PHP, MYSQL, servers settings, known bugs of all those scripts, tweaks, internet explorer etc.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

As Eduardo says, you're probably I/O bound. Databases often are. You can see this in the Resource Monitor in Windows.

There are several things you can do to fix it:

Physical drives

  • Faster drives: For a non-trivial database server, your off-the-shelf 7.2k RPM SATA drives just won't cut it. Go with 10 or 15K RPM SAS drives, or SSDs.
  • More drives: Put your database files on separate physical drives from your OS/swap file. You can split this up quite a bit, depending on the space you have.

As an example:

  • OS/applications: 2x 7.2 or 10k drives in RAID1
  • Transaction log: 2X 15k RPM in RAID1
  • Database files: 4x 15k RPM in RAID10

You always want to use RAID1 or 10 (or 5 or 6 - though in my experience, RAID10 usually performs a bit better - YMMV).

Database optimization

Poor read performance is often the result of improper indexing, and/or inefficient queries. It's hard to get into a deep discussion on it here, but I'd look for things like a select N+1 problem, and also look to see that indexes are being used appropriately.

If you are selecting data using a WHERE clause and there is no index on the field, you are asking the database to do a complete table scan every time, which is very slow. On the flip side, if you have too many indexes and many writes, your write performance (and consequently, I/O performance in general) will go down as the DB will be constantly rearranging and inserting to the indexes.

share|improve this answer
    
Thanks a lot for the tips :) –  Rock Mar 15 '12 at 17:06

You should post your data (benchmarks, statistics). But from what you're saying your script is probably I/O (as opposed to CPU) bound. Check for the I/O load in the task manager or using some other tool.

If this is the case see if you can cache some data and/or optimize your queries as to lighten the load on the I/O subsystem.

share|improve this answer
    
Also, use LAMP ;) –  bobert5064 Jun 2 '11 at 23:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.