Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL S-L-O-W when handling my 19 million row table

Posted on 2002-07-22
11
Medium Priority
?
2,539 Views
Last Modified: 2009-07-29
Dear group,

I am using MySQL 4.0.1-alpha on a i686 machine with RedHat 7.2. I'm afraid I don't have the exact machine specs available to me, but it's fast -- trust me! (SELECT BENCHMARK(1000000,1+1) = 0.1 seconds)

I've got a large 19 million items table, with 9 indices, and I need to run fairly complex queries (e.g.: one select from 2 tables, with a WHERE clause and an ORDER clause, etc...).

Currently, this is taking a HUGE amount of time (600 seconds), even though I've installed the fairly optimized my-huge.cnf configuration file. The longest step seems to be "copy to tmp table", which I presume is the SELECT portion.

Any help on speeding this query up would be highly appreciated.

Regards,
- Tonyboy
0
Comment
Question by:Tonyboy
  • 3
  • 2
  • 2
  • +4
11 Comments
 

Author Comment

by:Tonyboy
ID: 7170332
ADDENDUM:

When building the table, I executed the following commands:
ALTER TABLE large_table DISABLE KEYS
LOAD DATA INFILE 'large_file.bcp' INTO large_table

...20 hours later...

ALTER TABLE large_table ENABLE KEYS

...5-6 hours of "repair with keycache" later, the database was loaded.

Is it possible that my indexes were somehow not constructed properly? If it matters, I have made some modifications to the table since (UPDATE, DELETE, INSERT, etc...)

Thanks,
- Tonyboy
0
 
LVL 5

Expert Comment

by:kelfink
ID: 7173005
Can you describe the query ?

As far as the insert goes, it might be reasonable.  It's what, 1 million inserts in an hour?  It might make sense on your (speedy) platform.

So now you've got a query that takes 10 minutes.  Can we look at the structure of this schema... table and index definitions?  Can we see the query itself?

If you had to guess, what percentage of the data is your query going to be reading?  And, about how many rows do you expect to get back (that will determine the impact of the ORDER BY clause.)

Optimizing the table is a good idea, but IMO, mySQL shouldn't require it.  It should perform reasonably without it.

"copy to tmp table" might be the ORDER BY takign place, or it may be an indicator that your query uses more memory than you have on your machine (seems quite likely), thus, mysql has to use temp tables to store intermediate results.

If you have two tables in the query, make darn sure that both columns in the JOIN are indexed on their respective tables.
0
 

Author Comment

by:Tonyboy
ID: 7174582
Thanks for your input, kelfink. I'm bound by a non-disclosure contract, so I'll have to omit some of the specifics, but here's what the query looks like in rough terms:

SELECT *,substring(title,1,30) 'title_short' FROM books, publishers WHERE books.publisher = publishers.id AND author LIKE 'CLANCY%' AND (publishers.status < 50 OR publishers.status IS NULL) AND books.kind NOT LIKE 'NONFICTION%' AND books.kind NOT LIKE 'REFERENCE%' AND (books.availability != 0 OR books.availability IS NULL)
UNION
SELECT *,substring(title,1,30) 'title_short' FROM books, publishers WHERE books.publisher = publishers.id AND title LIKE 'CLANCY%' AND (publishers.status < 50 OR publishers.status IS NULL) AND books.kind NOT LIKE 'NONFICTION%' AND books.kind NOT LIKE 'REFERENCE%' AND (books.availability != 0 OR books.availability IS NULL)
ORDER BY artist,title_short,song,media,price

Note that the UNION statement might look awkward to search for books that contain (in this case) "CLANCY" at the beginning of the title, or at the beginning of the author field (which would contain matches in the form of "CLANCY, TOM"), but this is what we found to be fastest when using Sybase.


As far as result sets go, our biggest search results typically return 20 000 - 30 000 rows, or about 0.16% of the table.

We have the following indices which apply to the above query:

IN BOOKS:
title
author
publisher

IN PUBLISHERS:
id

I really hope this helps.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 8

Expert Comment

by:drittich
ID: 7175403
You need an index on kind.
0
 
LVL 5

Expert Comment

by:kelfink
ID: 7175673
I have to disagree with the index on kind.  You can experiment with it, of course, but it's not likely to help.

DBMS's routinely don't use indexes in the case of NOT LIKE, because it's akin to searching the dictionary for words which don't start with Q.  There's too many of them, and mySQL will probably ignore the index.

The index on authors is probably sufficient to reduce the number of rows to basically what you'll have at the end.  So long as your query is using that index, it sounds OK.

Try this... take the ORDER BY off for a while.  I'm not certain, but the copy to tmp table might well be coming from that part.

If so, then you'll have to tune the system to use more RAM when ordering, and possibly skip the temp table.

Also, the UNION might be forcing the tmp table, and I've not used the new UNION feature yet (4.0)
0
 
LVL 8

Expert Comment

by:drittich
ID: 7175768
That is a good point, kelfink.  I can think of cases where using an index in NOT LIKE would be faster, but it is a sensible general assumption by the parser not to use an index with NOT LIKE.  I would be interested to know if it helps in this case, though.
0
 

Author Comment

by:Tonyboy
ID: 7181967
I don't think that the "copy to tmp table" stage has too much to do with the ORDER BY clause. There is also a separate "Sorting result" stage which I see being performed.

Here is a run-down of my variable settings in MySQL:

mysql> show variables;
+---------------------------------+------------------------------------------------------------------------------------------------------------------+
| Variable_name                   | Value                                                                                                            |
+---------------------------------+------------------------------------------------------------------------------------------------------------------+
| back_log                        | 50                                                                                                               |
| basedir                         | /                                                                                                                |
| binlog_cache_size               | 32768                                                                                                            |
| character_set                   | latin1                                                                                                           |
| character_sets                  | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp12|
| concurrent_insert               | ON                                                                                                               |
| connect_timeout                 | 5                                                                                                                |
| datadir                         | /var/lib/mysql/                                                                                                  |
| delay_key_write                 | ON                                                                                                               |
| delayed_insert_limit            | 100                                                                                                              |
| delayed_insert_timeout          | 300                                                                                                              |
| delayed_queue_size              | 1000                                                                                                             |
| flush                           | OFF                                                                                                              |
| flush_time                      | 0                                                                                                                |
| ft_min_word_len                 | 4                                                                                                                |
| ft_max_word_len                 | 254                                                                                                              |
| ft_max_word_len_for_sort        | 20                                                                                                               |
| ft_boolean_syntax               | + -><()~*:""&|                                                                                                   |
| have_bdb                        | NO                                                                                                               |
| have_innodb                     | YES                                                                                                              |
| have_isam                       | YES                                                                                                              |
| have_raid                       | NO                                                                                                               |
| have_symlink                    | YES                                                                                                              |
| have_openssl                    | NO                                                                                                               |
| init_file                       |                                                                                                                  |
| innodb_additional_mem_pool_size | 1048576                                                                                                          |
| innodb_buffer_pool_size         | 8388608                                                                                                          |
| innodb_data_file_path           | ibdata1:64M                                                                                                      |
| innodb_data_home_dir            |                                                                                                                  |
| innodb_file_io_threads          | 9                                                                                                                |
| innodb_force_recovery           | 0                                                                                                                |
| innodb_thread_concurrency       | 8                                                                                                                |
| innodb_flush_log_at_trx_commit  | OFF                                                                                                              |
| innodb_fast_shutdown            | OFF                                                                                                              |
| innodb_flush_method             |                                                                                                                  |
| innodb_lock_wait_timeout        | 1073741824                                                                                                       |
| innodb_log_arch_dir             |                                                                                                                  |
| innodb_log_archive              | OFF                                                                                                              |
| innodb_log_buffer_size          | 1048576                                                                                                          |
| innodb_log_file_size            | 5242880                                                                                                          |
| innodb_log_files_in_group       | 2                                                                                                                |
| innodb_log_group_home_dir       | -??:? 84?                                                                                                        |
| innodb_mirrored_log_groups      | 1                                                                                                                |
| interactive_timeout             | 28800                                                                                                            |
| join_buffer_size                | 131072                                                                                                           |
| key_buffer_size                 | 1677717504                                                                                                       |
| language                        | /usr/share/mysql/english/                                                                                        |
| large_files_support             | ON                                                                                                               |
| locked_in_memory                | OFF                                                                                                              |
| log                             | OFF                                                                                                              |
| log_update                      | OFF                                                                                                              |
| log_bin                         | OFF                                                                                                              |
| log_slave_updates               | OFF                                                                                                              |
| log_long_queries                | OFF                                                                                                              |
| long_query_time                 | 10                                                                                                               |
| low_priority_updates            | OFF                                                                                                              |
| lower_case_table_names          | 0                                                                                                                |
| max_allowed_packet              | 1047552                                                                                                          |
| max_binlog_cache_size           | 4294967295                                                                                                       |
| max_binlog_size                 | 1073741824                                                                                                       |
| max_connections                 | 100                                                                                                              |
| max_connect_errors              | 10                                                                                                               |
| max_delayed_threads             | 20                                                                                                               |
| max_heap_table_size             | 16777216                                                                                                         |
| max_join_size                   | 4294967295                                                                                                       |
| max_sort_length                 | 1024                                                                                                             |
| max_user_connections            | 0                                                                                                                |
| max_tmp_tables                  | 32                                                                                                               |
| max_write_lock_count            | 4294967295                                                                                                       |
| myisam_bulk_insert_tree_size    | 8388608                                                                                                          |
| myisam_max_extra_sort_file_size | 256                                                                                                              |
| myisam_max_sort_file_size       | 2047                                                                                                             |
| myisam_recover_options          | OFF                                                                                                              |
| myisam_sort_buffer_size         | 67108864                                                                                                         |
| net_buffer_length               | 16384                                                                                                            |
| net_read_timeout                | 30                                                                                                               |
| net_retry_count                 | 10                                                                                                               |
| net_write_timeout               | 60                                                                                                               |
| open_files_limit                | 0                                                                                                                |
| pid_file                        | /var/lib/mysql/mysql.pid                                                                                 |
| port                            | 3306                                                                                                             |
| protocol_version                | 10                                                                                                               |
| record_buffer                   | 104853504                                                                                                        |
| record_rnd_buffer               | 104853504                                                                                                        |
| rpl_recovery_rank               | 0                                                                                                                |
| query_buffer_size               | 0                                                                                                                |
| query_cache_limit               | 1048576                                                                                                          |
| query_cache_size                | 0                                                                                                                |
| query_cache_startup_type        | 1                                                                                                                |
| safe_show_database              | OFF                                                                                                              |
| server_id                       | 0                                                                                                                |
| slave_net_timeout               | 3600                                                                                                             |
| skip_locking                    | ON                                                                                                               |
| skip_networking                 | OFF                                                                                                              |
| skip_show_database              | OFF                                                                                                              |
| slow_launch_time                | 2                                                                                                                |
| socket                          | /var/lib/mysql/mysql.sock                                                                                        |
| sort_buffer                     | 10485752                                                                                                         |
| sql_mode                        | 0                                                                                                                |
| table_cache                     | 512                                                                                                              |
| table_type                      | MYISAM                                                                                                           |
| thread_cache_size               | 8                                                                                                                |
| thread_stack                    | 65536                                                                                                            |
| transaction_isolation           | READ-COMMITTED                                                                                                   |
| timezone                        | EDT                                                                                                              |
| tmp_table_size                  | 33554432                                                                                                         |
| tmpdir                          | /raid/tmp/                                                                                                       |
| version                         | 4.0.1-alpha                                                                                                      |
| wait_timeout                    | 28800                                                                                                            |
+---------------------------------+------------------------------------------------------------------------------------------------------------------+
109 rows in set (0.02 sec)

Once again, I hope this helps. Keep in mind that our machine has 2 Gigs of RAM.
0
 
LVL 4

Expert Comment

by:marko020397
ID: 7206820
You have to have the fields that are connected in the query "books.publisher = publishers.id" of the exactly the same type and indeces on those fields. I presume you have that.
0
 
LVL 2

Expert Comment

by:holdenweb
ID: 7393149
Seems to me you might save yourself at least some time by using a logical OR in a single query rather than trying to unite two separate queries. Is there a particular reason why

SELECT *,substring(title,1,30) 'title_short' FROM books, publishers WHERE books.publisher = publishers.id AND (author LIKE 'CLANCY%' OR title LIKE 'CLANCY%') AND (publishers.status < 50 OR publishers.status IS NULL) AND books.kind NOT LIKE 'NONFICTION%' AND books.kind NOT LIKE 'REFERENCE%' AND (books.availability != 0 OR books.availability IS NULL)

This might at least halve your query time. It seems likely that the tmp table is being used to hold the output from the first query so it can perform the union after finishing the second.
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9804146
Tonyboy,
No comment has been added lately (387 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: PAQ/No Refund

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

Squeebee
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 9842743
PAQed - no points refunded (of 300)

GhostMod
Community Support Moderator
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 12 hours left to enroll

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question