Solved

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

Posted on 2002-07-22
11
2,514 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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