Solved

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

Posted on 2002-07-22
11
2,507 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now