Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport...

I am getting really lousy performances, for example :

0.293437004089 seconds for 

CREATE TABLE blockip ( 
id INT(11) NOT NULL AUTO_INCREMENT, 
ip VARCHAR(15) NOT NULL DEFAULT '', 
username VARCHAR(80) NOT NULL DEFAULT '', 
time INT(10) NOT NULL DEFAULT '0', PRIMARY KEY (id) ) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci TYPE=MyISAM;

On another machine, nearby I get 0.0016529560089111 seconds for the same query.

The trouble seems to be somewhat related to the disk : disk usage goes really high when I start creating the 70 tables in my database.

Any ideas where to start for a clue ?

share|improve this question
Unfortunately this has nothing to do with the slowlines of the create but it will be an issue once the table is filled, do you have any specific reason for using VARCHAR for ip (and username) and not using datetime or timestamp for time? – ddeimeke Jan 11 '11 at 9:25
I don't really care about the VARCHAR (for ip and username) : it could be anything else. For "time" however, I really wanted to store timestamp. This project was started about 8 years ago and the "INT (10)" just remained there... I don't remember seeing "TIMESTAMP" in the Mysql 3.23 version ;-) – perrick Jan 11 '11 at 10:16
Where are the tables being stored? Is it on the root partition where there's contention, or an external disk where you might have other types of contention on the bus? – Joe Jan 11 '11 at 12:31
It's on the root partition. – perrick Jan 11 '11 at 12:38
What Mac is this - a MacBook? Their disks aren't as fast as desktops unless you tick that option in the Apple Store. – Gaius Jan 11 '11 at 13:48
show 1 more comment

closed as off topic by Jeff Atwood♦ Jan 29 '11 at 1:21

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

1 Answer

up vote 2 down vote

Based on the data for the performance of the two drives you mentioned:

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHV2060BHPL

versus

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHY2160BH

It looks like the MacBook should be much faster. However, this being a laptop versus a stationary computer, I'm willing to bet you use the MacBook a lot more than the MacMini. Therefore I'm going to suggest that it's likely drive contention in many ways, both in finding open areas on the disk contiguous to create the space as well as other programs on the computer running at the same time. I would really question if you're planning on running the database on this laptop and using the performance metrics from the test runs for anything besides baseline benchmarking. Because when you goto run this code on the server somewhere else it's going to have much better performance.

Until you can eliminate all other factors and declare the two boxes to be as identical as possible can we say why the macmini is faster than the macbook. This becomes an issue for SuperUser not dba.SE

share|improve this answer
I'm using both machines for development purposes (not in production). The trouble starts since I do test-first programming : having to waiting about 10 seconds for each test run is a real pain. Anyway, I'll check-out SuperUser for "drive contention" related problems. – perrick Jan 15 '11 at 12:39
Well, I changed the disk and it was not enough : I still get the same delay. Any other clue ? I'm really wondering what is going on... – perrick Jan 18 '11 at 13:02
I wasn't suggesting that you really change the disk, I was saying that there are other factors competing here that can't accurately be modeled by a simple "works here but not there". I would really need a list of every application that's ever been installed on both boxes, frequency of usage of each box, activity during testing ... in short, I would need to sit in front of the box to debug this one, because I would know what to eliminate on the fly. Sounds like you need a dedicated test host. – jcolebrand♦ Jan 18 '11 at 15:31

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