MySQL ODBC Connector - will a cluster be faster than a heap-table?

Author

will a cluster be faster than a heap-table?

Jan Kirchhoff

2005-10-27, 5:27 pm

Hi,

I am currently using a replication setup on two servers with mysql
4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM, hardware SCSI-RAID).

I have a table that has lots of updates and selects. We converted this table
(along with other tables) from a myisam to a heap-table 6 months ago which
increased the performance a lot, but we are hitting the limits again - on the
master as well as on the slave.

We are only talking about 50-60 queries/second in peaks maybe 90 q/sec (which
means more selects, but not much more inserts), but the inserts are
bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON DUPLICATE KEY
UPDATE-Queries updating a total of around 50.000 rows/minute in the daytime,
We are getting locking-Problems with selects having to wait for 5 seconds or
sometimes even much longer.
We expect that the amount of insert will increase slowly while the selects
will get much more pretty soon. The selects are all optimized and respond
within 0.x or 0.0x seconds in a mysql-shell in case they are not locked by an
insert. It is weird that those inserts that usually only take 1-2 seconds
(never saw anything older in the processlist) now sometimes take 10 seconds or
more while more and more selects are waiting in the Locked-status. I saw this
behaviour 3 weeks ago for the first time and maybe 4 or 5 more times since then...

I am just wondering if a cluster-setup would help us speed up the system.
If I understand this right, it is no problem to mix NDB-tables an
memory/myisam-tables. I'd just have to install a cluster-enabled version of
mysqld and set up 2 or more NDB-Nodes, right? I could then alter the few
speed-critical tables to the NDB-storage-type and would not have to change any
SQL?
I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit-Ethernet and
an IDE-Harddisk, no expensive Server-Hardware? Or would I need at least 4
Nodes to have an speed-improvement?

I've been trying to find answers on this on mysql.com but was not successful.
There is no info about possibilities of mixing NDB- and myisam-tables and all
documentation on mysql-cluster focuses more on HA than on speed.... especially
speed of cluster-tables compared to traditional mysql-memory-tables...

thanks for any help on this!

Jan

in case this is important:
show table status:
********************
******* 25. row ********************
*******
Name: memtable_spr
Engine: HEAP
Version: 9
Row_format: Fixed
Rows: 777330
Avg_row_length: 294
Data_length: 234729984
Max_data_length: 856336152
Index_length: 52598232
Data_free: 294
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Brent Baisley

2005-10-27, 5:27 pm

Wow, it seems like you are going to extremes. To jump from myisam to
heap is a big step. Did you try using InnoDB? It would handle locking
issues much better since it doesn't lock the table. Heap tables can
be pretty dangerous since it's all in memory. If the machine crashes,
you'll lose the data.

Based on your information, you want to get the best disk I/O you can.
You won't get that out of a single "IDE" drive, even if it is one of
the latest SATA based with command queuing. I don't think you'll get
anything faster than heap tables and tons of RAM. But there is
certainly finite scalability because of the use of RAM. Clusters may
be the way to go for scalability, but I would work on getting your
data disk based for maximum scalability.

I would try InnoDB and maximize you disk setup. I don't know how many
disks you have in your RAID and if it's hardware or software based.
More disks will add speed by splitting the load across more disks.
Just keep in mind the limits of your SCSI card too. You may need to
add a card to split the load.

On Oct 27, 2005, at 1:57 PM, Jan Kirchhoff wrote:

> Hi,
>
> I am currently using a replication setup on two servers with mysql
> 4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM,
> hardware SCSI-RAID).
>
> I have a table that has lots of updates and selects. We converted
> this table
> (along with other tables) from a myisam to a heap-table 6 months
> ago which
> increased the performance a lot, but we are hitting the limits
> again - on the
> master as well as on the slave.
>
> We are only talking about 50-60 queries/second in peaks maybe 90 q/
> sec (which
> means more selects, but not much more inserts), but the inserts are
> bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON
> DUPLICATE KEY
> UPDATE-Queries updating a total of around 50.000 rows/minute in the
> daytime,
> We are getting locking-Problems with selects having to wait for 5
> seconds or
> sometimes even much longer.
> We expect that the amount of insert will increase slowly while the
> selects
> will get much more pretty soon. The selects are all optimized and
> respond
> within 0.x or 0.0x seconds in a mysql-shell in case they are not
> locked by an
> insert. It is weird that those inserts that usually only take 1-2
> seconds
> (never saw anything older in the processlist) now sometimes take 10
> seconds or
> more while more and more selects are waiting in the Locked-status.
> I saw this
> behaviour 3 weeks ago for the first time and maybe 4 or 5 more
> times since then...
>
> I am just wondering if a cluster-setup would help us speed up the
> system.
> If I understand this right, it is no problem to mix NDB-tables an
> memory/myisam-tables. I'd just have to install a cluster-enabled
> version of
> mysqld and set up 2 or more NDB-Nodes, right? I could then alter
> the few
> speed-critical tables to the NDB-storage-type and would not have to
> change any
> SQL?
> I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit-
> Ethernet and
> an IDE-Harddisk, no expensive Server-Hardware? Or would I need at
> least 4
> Nodes to have an speed-improvement?
>
> I've been trying to find answers on this on mysql.com but was not
> successful.
> There is no info about possibilities of mixing NDB- and myisam-
> tables and all
> documentation on mysql-cluster focuses more on HA than on speed....
> especially
> speed of cluster-tables compared to traditional mysql-memory-tables...
>
> thanks for any help on this!
>
> Jan
>
> in case this is important:
> show table status:
> ********************
******* 25. row ********************
*******
> Name: memtable_spr
> Engine: HEAP
> Version: 9
> Row_format: Fixed
> Rows: 777330
> Avg_row_length: 294
> Data_length: 234729984
> Max_data_length: 856336152
> Index_length: 52598232
> Data_free: 294
> Auto_increment: NULL
> Create_time: NULL
> Update_time: NULL
> Check_time: NULL
> Collation: latin1_swedish_ci
> Checksum: NULL
> Create_options:
> Comment:
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brent@landover
.com
>
>
>

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jan Kirchhoff

2005-10-28, 7:23 am

Hi Brent,

> Wow, it seems like you are going to extremes. To jump from myisam to
> heap is a big step. Did you try using InnoDB? It would handle locking
> issues much better since it doesn't lock the table. Heap tables can
> be pretty dangerous since it's all in memory. If the machine crashes,
> you'll lose the data.

I know that, but I do regular (cornjobs) backups to myisam-tables and I
can reconstruct the whole table from the machines that insert/update the
data.

> Based on your information, you want to get the best disk I/O you can.
> You won't get that out of a single "IDE" drive, even if it is one of
> the latest SATA based with command queuing. I don't think you'll get
> anything faster than heap tables and tons of RAM. But there is
> certainly finite scalability because of the use of RAM. Clusters may
> be the way to go for scalability, but I would work on getting your
> data disk based for maximum scalability.

For my case, scalability means more updates/second and more
selects/second. Not larger tables. At least not much larger, and this
table is using less than 300 MB of memory right now. So I see no point
in using anything disk-based. 2 years ago we started with myisam, then
changed to innodb, found out it wouldn't give any better performance in
our case and switched back to myisam since that makes the setup of
replications much easier. Then we changed it again 6 months ago and now
use memory-tables.

> I would try InnoDB and maximize you disk setup. I don't know how many
> disks you have in your RAID and if it's hardware or software based.
> More disks will add speed by splitting the load across more disks.
> Just keep in mind the limits of your SCSI card too. You may need to
> add a card to split the load.

These two systems have Hardware-RAID (SCSI storage controller: LSI Logic
/ Symbios Logic (formerly NCR) 53c1030 (rev 07)) width 2 disks in raid1
and the DB is myaybe 40 Gb of size. I have no performance trouble on any
other table.

Jan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links