473,238 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

Mysql large table SLOW (3 gig table, 3 days to reindex)

I REALLY dont want to switch to oracle :( but I cant get these tables
working any faster.
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
backplanes (new ones will have dual channel)

All have 2 gig of ram, but I've never seen mysql use more than 300mb
of ram.

The servers handle huge loads, each day there are 30 1-2 gig files
loaded into large tables, total mysql data size is 96 gig, the large
tables are 2-6 gig.

Inserts are done on dupe key ignore, this takes hours on the large
files, it barely keeps up with input files.

At the bottom of this post I've got the mysql.ini config lines, any
sugestions are welcome, I'm already beyond the mysql "huge" sample
they used to include in the program.

Sample table that I load is as follows.

each day I get 40 % new records on the text file, the input file is
normally 20mb, once a week I get one that's 1-2 gig, these take all
day to load.

I need more multiple column indexes, as some querys return millions of
rows that must be scanned, but the index size already exceeds the
table size, and the combinations I need would result in an myi that's
5x larger than the data itself.

Here's an example of the speed problem, the index was corrupt so I
dropped all and recreated, rather than a myisam repair. I think 3 days
is a little excessive for a table that's only 3.428 gig, index is
2.729 gig. I cant remove the primary key, as it keeps duplicates out
of the system, the input files are from old database's, we use mysql
to store the data for the web frontend, mostly done in ASP, most
queries take less than a second, unforuntatly we have big queries that
take way more than the IIS timeout setting all the time, but no way
around it, I cant add more indexes without making it even slower :(

mysql> alter table hood_stat add primary key
(dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add
index `niin` (`niin`), add index `stor` (`stor`), add index `dic`
(`dic`), add index `ctasc` (`ctasc`);

Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds
Records: 45449534 Duplicates: 0 Warnings: 0
CREATE TABLE `hood_stat` (
`dic` char(3) NOT NULL default '',
`fr_ric` char(3) NOT NULL default '',
`niin` char(11) NOT NULL default '',
`ui` char(2) NOT NULL default '',
`qty` char(5) NOT NULL default '',
`don` char(14) NOT NULL default '',
`suf` char(1) NOT NULL default '',
`dte_txn` char(5) NOT NULL default '',
`ship_to` char(3) NOT NULL default '',
`sta` char(2) NOT NULL default '',
`lst_sos` char(3) NOT NULL default '',
`esd` char(4) NOT NULL default '',
`stor` char(3) NOT NULL default '',
`d_t` char(4) NOT NULL default '',
`ctasc` char(10) NOT NULL default '',
PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ),
KEY `don` (`don`),
KEY `niin` (`niin`),
KEY `stor` (`stor`),
KEY `dic` (`dic`),
KEY `ctasc` (`ctasc`)
) TYPE=MyISAM MAX_ROWS=1000000000 PACK_KEYS=1

skip-locking
set-variable= key_buffer=512M
set-variable=join_buffer=512M
set-variable= max_allowed_packet=256M
set-variable= table_cache=512
set-variable= sort_buffer=256M
set-variable=tmp_table_size=400M
set-variable= record_buffer=256M
set-variable= thread_cache=8
set-variable=myisam_sort_buffer_size=256M
myisam-recover=BACKUP,FORCE
set-variable=read_buffer_size=2M
set-variable=interactive_timeout=7200
set-variable=wait_timeout=7200
log-bin
server-id=1
replicate-do-db=finlog
set-variable=open-files-limit=500
set-variable=table-cache=400
Jul 20 '05 #1
6 22482
I recommend two things, which should fix your problems.

(a) you need to keep the index table in ram, and you have to disable
it from being written to the disk. Your problem is wit the index.
So do a delayed key write=ALL
http://dev.mysql.com/doc/mysql/en/MyISAM_start.html

(b) Then the key_buffer needs to be as big as your indecis combined.

(c) maybe not necessary: the daily inserts/deletes to be rewritten to
be done in bulk. i.e. for inserts, insert multiple lines with a
single command.

I don’t think Oracle is the answer (still the same laws of physics).
Keeping the indecis in ram and keeping them there is IMHO the answer.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-large-...ict124453.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=414920
Jul 20 '05 #2
the largest table i work with is about 4 gig, and it takes about 3 to 4
hours to do a repair or analyze. It has about the same number of keys
as yours, but not the hugh primary key you have. So 3 days seems too
much indeed. I'm running on a Duron 800 with 1Gig ram, and 10Krpm raid
0. I think your key_buffer could be much larger.... other than that i'm
not knowledgable enough...

Matt Liverance wrote:
I REALLY dont want to switch to oracle :( but I cant get these tables
working any faster.
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
backplanes (new ones will have dual channel)

All have 2 gig of ram, but I've never seen mysql use more than 300mb
of ram.

The servers handle huge loads, each day there are 30 1-2 gig files
loaded into large tables, total mysql data size is 96 gig, the large
tables are 2-6 gig.

Inserts are done on dupe key ignore, this takes hours on the large
files, it barely keeps up with input files.

At the bottom of this post I've got the mysql.ini config lines, any
sugestions are welcome, I'm already beyond the mysql "huge" sample
they used to include in the program.

Sample table that I load is as follows.

each day I get 40 % new records on the text file, the input file is
normally 20mb, once a week I get one that's 1-2 gig, these take all
day to load.

I need more multiple column indexes, as some querys return millions of
rows that must be scanned, but the index size already exceeds the
table size, and the combinations I need would result in an myi that's
5x larger than the data itself.

Here's an example of the speed problem, the index was corrupt so I
dropped all and recreated, rather than a myisam repair. I think 3 days
is a little excessive for a table that's only 3.428 gig, index is
2.729 gig. I cant remove the primary key, as it keeps duplicates out
of the system, the input files are from old database's, we use mysql
to store the data for the web frontend, mostly done in ASP, most
queries take less than a second, unforuntatly we have big queries that
take way more than the IIS timeout setting all the time, but no way
around it, I cant add more indexes without making it even slower :(

mysql> alter table hood_stat add primary key
(dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add
index `niin` (`niin`), add index `stor` (`stor`), add index `dic`
(`dic`), add index `ctasc` (`ctasc`);

Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds
Records: 45449534 Duplicates: 0 Warnings: 0
CREATE TABLE `hood_stat` (
`dic` char(3) NOT NULL default '',
`fr_ric` char(3) NOT NULL default '',
`niin` char(11) NOT NULL default '',
`ui` char(2) NOT NULL default '',
`qty` char(5) NOT NULL default '',
`don` char(14) NOT NULL default '',
`suf` char(1) NOT NULL default '',
`dte_txn` char(5) NOT NULL default '',
`ship_to` char(3) NOT NULL default '',
`sta` char(2) NOT NULL default '',
`lst_sos` char(3) NOT NULL default '',
`esd` char(4) NOT NULL default '',
`stor` char(3) NOT NULL default '',
`d_t` char(4) NOT NULL default '',
`ctasc` char(10) NOT NULL default '',
PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ),
KEY `don` (`don`),
KEY `niin` (`niin`),
KEY `stor` (`stor`),
KEY `dic` (`dic`),
KEY `ctasc` (`ctasc`)
) TYPE=MyISAM MAX_ROWS=1000000000 PACK_KEYS=1

skip-locking
set-variable= key_buffer=512M
set-variable=join_buffer=512M
set-variable= max_allowed_packet=256M
set-variable= table_cache=512
set-variable= sort_buffer=256M
set-variable=tmp_table_size=400M
set-variable= record_buffer=256M
set-variable= thread_cache=8
set-variable=myisam_sort_buffer_size=256M
myisam-recover=BACKUP,FORCE
set-variable=read_buffer_size=2M
set-variable=interactive_timeout=7200
set-variable=wait_timeout=7200
log-bin
server-id=1
replicate-do-db=finlog
set-variable=open-files-limit=500
set-variable=table-cache=400

Jul 20 '05 #3
> So do a delayed key write=ALL

Then the key_buffer needs to be as big as your indecis combined.

I made both changes, running the same create index that took 3 days on
our test server.

I noticed when it writes out the table at first, it writes it in 65536
kb chunks, I have the myisam sort buffer, and sort buffer & read
buffer at the max it would let me, 67104768 bits, is there another
setting that would bump this up to a higher level?

Not sure if that would even help though
Key buffer is 2 gig, that's all the ram I've got, and some of these
indexes get up to 4 gig in size

mysql> show variables like '%key%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| delay_key_write | ALL |
| key_buffer_size | 1572864000 |
| max_seeks_for_key | 4294967295 |
+-------------------+------------+
3 rows in set (0.00 sec)

mysql> show variables like '%buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 536866816 |
| key_buffer_size | 1572864000 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| read_buffer_size | 67104768 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 838860800 |
+-------------------------+------------+
10 rows in set (0.00 sec)
Jul 20 '05 #4
You have a monster db, and it would be hard to test whatever solution
is proposed, including mine.

May I suggest that you set up a test machine with say 1 gig of ram,
and then test with a scaled down db. You should still be able to see
how much of a difference difference solutions make.

Once you have confidence, then you can spring for another 2-3 gig of
ram. But I can see that you would not change the set up now (neither
would I) before knowing if these changes make a difference.

You cannot partially cache the key, as you indicated. The entire key
(and better all the keys) need to be in ram.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-large-...ict124453.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=415870
Jul 20 '05 #5
Bumped up a few settings

delay_key_write=ALL
key_buffer_size=1000M
read_buffer_size=512M
record_buffer=512M

seems to help a little, but not much (test box has 1 gig of ram)

I cant tell if it's mysql that's the problem, or the hardware, Here's
a screenshot of the disk IO, if I copy a file while mysql is doing the
build index, the io shoots way up, which tells me, mysql is NOT maxing
out the drives, and it's also not maxing out the memory.

Unless it's doing lots and lots of seeks on the drive, which is harder
to test using perfmon, it's a 1x6 or 1x8 backplane, I forget, u320
drives, perc 3 raid 5 setup

http://www.geekopolis.com/pics/diskio.jpg
Jul 20 '05 #6
Also another quick thing:

I have noticed that if the tables are not optimized, they could be
10’s of times slower. So tables with frequent deletes need to be
optimized. Of course in your case, that can take days!!

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-large-...ict124453.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=416073
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: CoOL! . | last post by:
Hello, I found the key to solve this problem in: http://darkstar.ist.utl.pt/mysql/doc/en/InnoDB_foreign_key_constraints.html You'll probably need an INDEX for that new foreign key you are...
0
by: Elinor Lev | last post by:
I'm trying to build an astronomical database with mysql. The goal is to list all the stars in an image and enter them into a master-table of stars. The first step is to see that in the data-list...
1
by: Noah Bawdy | last post by:
This should be a <duh> but I can't find the answer. For example: SELECT Company_Defaults.CompanyID, Company.CompanyID FROM Company_Defaults Left Join Company on Company_Defaults.CompanyID =...
4
by: Matt | last post by:
Hi all, We recently upsized two Microsoft Access Databases to SQL. We're using an ADP (2002) as the front end. All the conversion issues have been resolved, except for one: Whenever we...
0
by: Symphony | last post by:
Hi, all: In our web application(vb.net), we are using Table, Table Row, Table Cell web controls dymanically load data, we define two columns, left column likes header, list data titles(eg....
21
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
3
by: steve | last post by:
Hello, I am having a problem with an index controlled partitioned table. I was altering a table using SQL. I was trying to add a partition to a table to change it from index controlled to...
10
by: pbd22 | last post by:
Hi. Like the title says - how do i do this? I was given the following example: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A' The above statement threw the following error:
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.