Apparent Write caching slowing performance...

2003-03-14 Thread Jason West 
Hi,

I'm having some serious performance issues that are stemming from MySQL
writing data to tables. MySQL seems to cache writes, then write the
block all at once. The major issue I'm having with this is that it when
it does this it stops processing other requests until it's finished. I
have listed my variables and .cnf file below in hopes that someone
can point out what's causing this, or at least point me in the right
direction. (I've been Google-ing/reading/testing/experimenting for the
past 3 days to no avail).

This is all running on a dedicated server running Red Hat 8, with Dual
2.5GHz Xeons, 2GB ram, 50GB SCSI hard drive
  

Variables:


+-+
| Variable_name   |
Value  
   |
+-+
| back_log|
50 
   |
| basedir |
/usr/local/mysql/  
   |
| binlog_cache_size   |
32768  
   |
| bulk_insert_buffer_size |
8388608
   |
| character_set   |
latin1 
   |
| character_sets  | latin1 dec8 dos german1 hp8 koi8_ru
latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr
win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert   |
ON 
   |
| connect_timeout |
5  
   |
| convert_character_set  
|  
 |
| datadir |
/usr/local/mysql/var/  
   |
| delay_key_write |
ON 
   |
| delayed_insert_limit|
100
   |
| delayed_insert_timeout  |
300
   |
| delayed_queue_size  |
1000   
   |
| flush   |
OFF
   |
| flush_time  |
0  
   |
| ft_boolean_syntax   | +
-><()~*:""&|   
 |
| ft_min_word_len |
4  
   |
| ft_max_word_len |
254
   |
| ft_max_word_len_for_sort|
20 
   |
| ft_stopword_file|
(built-in) 
   |
| have_bdb

Creating a fixed length char field...

2003-03-10 Thread Jason West 
I have a question regarding fixed width char fields. How do you create
them in MySQL? When I create a table and define a field MyField
char[50], when I run the describe table, the field shows up as a
varchar.

Thanks,
-- 
Jason H. West
Software Engineer

Softek Software International, Inc.
813 Pavilion Ct.
McDonough, GA 30253
678-583-5718
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Searching on indexed char field...

2003-03-10 Thread Jason West 
Does anyone know of a way to speed up seek time when selecting based on
an indexed char field? I have a query that I'm executing ('select * from
Table where UserName = myname') and it takes approximately 7 seconds to
execute this 1000 times (UserName is char[50], and it is indexed). If I
change the query to search on UserID, an indexed integer field, the same
test runs in less than 1 second...

Any help is appreciated...
-- 
Jason H. West
Software Engineer

Softek Software International, Inc.
813 Pavilion Ct.
McDonough, GA 30253
678-583-5718
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Inserts slowing down as record count increases...

2003-03-10 Thread Jason West 
Hi,

I have a pretty significant problem when inserting records in to 2
tables (Parent/child). In the Parent table the records are 25 bytes, and
the child records are 80 bytes. The Parent table has an auto inc field
which I retrieve using 'mysql_insert_id()' to populate the ownerID in
the child table.

When the record count gets over a couple of million, the inserts slow
down to a crawl. I ran a test application over the weekend, and we
started off inserting roughly 1 million records in +- 20 minutes, but by
the time we'd inserted 9 million records it had taken over 10 hours!
We're running this on a dedicated server (Dual 2GHz Xeon procesors with
SCSI hard drive, 1 GB RAM),so I don't think the hardware is the
issue.It's running Red Hat 8 and MySQL 4.0, and we're using a slightly
modified version of the My-Large.cnf file.

Any help with this would be greatly appreciated.

Thanks,
-- 
Jason H. West
Software Engineer

Softek Software International, Inc.
813 Pavilion Ct.
McDonough, GA 30253
678-583-5718
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php