How to speed up Database Queries - Takes Hours

[TravelSite]

Hi,

I was wondering if there was any way to speed up database queries done in MySQL/PHP (standard Xampp configuration) by changing settings in php.ini, my laptop settings, etc - (i.e. anything other than improving the basic query in my php scripts, e.g. changing settings to use more RAM, etc).

The database I'm working with holds close to 20million records, and doing queries that updates all the records is taking most of the day to complete.

Thanks

[lammert]

Are you using Xampp for Linux, Windows, or another operating system?

Depending on the operating system you should try to find the bottleneck. It could be RAM, hard disk I/O or CPU cycles which are the root of the problem. In the next step you can then start to tune the right parameters.

In Windows the Task Manager and Resource Monitor may give you the information you need. In Linux you can use tools like top or iostat.

If your database has indexes and you use it in single-user mode, it may help to drop the indexes during the updates, perform the updates and then regenerate the indexes again. Of course you shouldn't do this if the database is used simultaneously by other clients for select statements, or when you use indexes to maintain data integrity through Unique or Foreign keys.

[TravelSite]

Sorry; its Windows Vista (32Bit) that I'm using.

Following your suggestion I looked at the RAM - the Task manager shows that "mysqld.exe" doesn't ever go above 50,000k (CPU seems okay - it uses roughly between 16% and 22% of the dual-core CPU).

Memory usage is sitting at around 1.5GB (out of the 3.5GB available to Vista) - so theres plenty more memory available to use.

In the "Resource Overview" CPU goes from 13% to 28% (and 71% to 87% "Manimum Frequency")

The Disk is 11 to 66 MB/sec and sits at 98% Highest Active Time.

The memory shows 0 to 14 Hard Faults/sec, 50% Used Physical Memory.

Does this mean MySQL is only using 50MB of memory, and that perhaps the hard disk write speed is being maxed out?

Thanks

[LifeinAsia]

In practice, I've found that it's better to break up major updates (or inserts) into chunks and loop through them. For example, instead of updating all 20 million records in one fell swoop, I'd add a BIT field called "Processed" to the table. Then grab 10,000 rows at a time, update those, wait a short delay, grab the next 10,000 unprocessed rows, rinse and repeat until finished.

Another issue- does every single row actually need to be updated? Or is there a way to restructure things so that you only update those rows that actually need to be updated?

Another possible issue is that each row has a lot of fields in it, but only one or 2 need updating every day. In that case, it may be more efficient to break the table into 2 tables.

Expanding on what lammert said, you should also revisit your indexes and see if you are over-indexing your table.

[rocknbil]

^ ^ ^ Right, and the reason is that indexed fields are faster for searching (selecting from) and slower for inserting/updating.

20 M records, I'd re-think and break them into smaller tables, but that's just me.

[lammert]

Based on these figures your problem is hard disk bound. With only 50MB currently in use by mysql.exe and 3.5GB total system memory it is worth trying to increase some cache buffer sizes. If you have PhpMyAdmin installed, the current use of internal MySQL resources can be found under Show MySQL runtime information right on the main PhpMyAdmin home page. Look at values which are colored red. These are considered to be critical and may give an indication where increasing cache sizes or other resources can help performance.

[brotherhood of LAN]

Outwith my.cnf changes (which can help immensely for any given situation), partitioning [dev.mysql.com] may help in your case.

I do something to LifeinAsia's idea of splitting the update into increments using a unique partition per increment. I have some tables similar in size to yours (ignoring row lenghts) and the process takes less than a minute.

[TravelSite]

Hi Everyone,

Thanks for the suggestions.

LifeinAsia - I'm currently breaking things up into chunks and looping through them. Have been using a varchar (10) field called "examined" to break things up (containing either NULL or Done) - but will switch to using a "bit" field instead thanks.

Yes - there are times when all the records need to be updated.

LifeinAsia / rocknbil - The dataset I'm working with requires mulitple seperate operations on them - I intend to break things into seperate tables when possible (using joins when needed), and have removed indexes that I don't need, thanks.

lammert - I couldn't see "Show MySQL runtime information" - but found the red values (I think) under "status" thanks.

Items highlighted in red include Slow_queries (2), Innodb_buffer_pool_reads (12), Handler_read_rnd (8), Handler_read_rnd_next (14 M), Created_tmp_disk_tables (65), Opened_tables (73 - though theres just the 2 "open tables").

To change the cache sizes I assume I can just change cache values in the php.ini file and restart Xampp?

- It would make sense that the hard disk could be a bottle neck, thanks - its a desktop replacement type laptop with a 1*500GB drive which I'd guess probably runs at 54,000.

[TravelSite]

brotherhood of LAN - I couldn't find my.cnf - there's a file called C:\xampp\mysql\bin\my.ini - is this the same thing?

[lammert]

Some XAMPP installations store the settings in a my.cnf file, others in my.ini. If you have only a my.ini file it is safe to change the settings in there. Most settings will only be effective after a restart of the MySQL server.

[TravelSite]

Found it thanks - my.ini.

Can anyone recommend which of its settings I should change? The following are currently set (along with some others that I don't think apply):

skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Thanks

[lammert]

Your main problem seems to be Handler_read_rnd_next with 14 million as counter value. This counter increments for every table-read in a full table scan caused by not properly indexed tables. The ideal way to decrease this high value is by optimizing your index in such a way that they match your queries better. If you have your slow query log turned on, the problematic query will show up there.

A quick test could be to increase the value of read_rnd_buffer_size. This is a temporary buffer used during full table scans to store a subset of the records and sort them. Increasing this buffer won't eliminate the inefficiency of the table scan procedure, but it may reduce disk I/O somewhat giving a better overall runtime.

On the other hand, 14 million record reads is about the size of your table so this high count could have just been caused by the large amount of records in your database.