I've been doing a lot of prep work for some MySQL benchmarks on an important system that's slated to have a fairly high transaction volume and a lot of records.

However, I loaded the test data and played a round a bit (raw disk I/O benchmarks) before writing the real SQL code that's going to run. Along the way I realized I needed to make a few minor schema changes.

Ugh.

Running an ALTER TABLE command on a table of 60 million rows (60+GB of data) isn't exactly a trivial operation, even if you are doing it on a 12 disk RAID 0+1 array. Twice so far.

I'm used to making cheaper mistakes, at least in terms of time spent waiting for the fix to finish.

Next time around, it'll look more like this:

  1. create schema
  2. write data generator
  3. write benchmarking plan
  4. write benchmarking code
  5. load data
  6. run tests

For some dumb reason, I did #5 just after #2. Then when it came to #3 and #4 I realized "whoops... something is not quite right."

I'll blame too much multi-tasking.

Posted by jzawodn at March 22, 2004 10:15 PM

Reader Comments
# Dan said:

Dear Jeremy:

I have an overload mysql machine, And I want tunning it.

It have 256 database , 256 * 256 table.
each table have 20000+ rows. And max
client connection is over 1000/s.

My machine hardware is : 2 Xeon + 2G RAM
36G RAID5

my my.cnf setup :

skip-locking
key_buffer=384M
max_allowed_packet=1M
max_connections=1000
max_connect_errors=100
table_cache=2000
join_buffer=2M
record_buffer=1M
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
thread_cache=100
thread_concurrency=8
query_cache_size=64M
query_cache_limit=1M

Mysql version : 4.1.1-alpha

But the mysqld eat about 900M Memory,
And system load is over 30...No idle time.
No high iowait

Could you give me any advice? Or tell me
Such machine can afford how many concurrent
client?


on March 24, 2004 07:54 AM
# Justin said:

It might help if you tell why you have 256 databases each with 256 tables?

20,000 records for a table is pretty low.

on March 24, 2004 02:34 PM
# Dan said:

My application use md5sum as an uniq id for
an database cluster, I just use the first
4 char of md5sum as the database cluster method
2 char for database name, 2 char for table name
like:
db_29
db_2a -- tbl_2a3c/tbl_2a3d ....
...

So I have 256 database * 256 table now, An stupid
choice, But it is easy to move the database to
different machine.And every client know connect which machine to get the infomachine form his
md5sum uuid.

Now not every table have 20000+ rows,
The rows is from 1000+ to 200000+

Thank you very much for your help!

on March 24, 2004 07:53 PM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.