MySQL Performance Issues

Hi,

I have a windows application that uses a MySQL database. I have installed XAMMP and have connected the two.

It takes FOREVER to add data and update data.

My computer has 24GB RAM. Are there any tweaking suggestions I can change in my mysql ini file?

key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"

innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[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
LVL 3
Computer GuyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Its seems that your machine is above average (24GB) so the first thing i would check is there is another reason that the performance is not the expected one.
My first move after the usual advice to check for virus and memtest is to create a virtual machine preferably to another hard drive than the one you use right now and check if this gives you acceptable performance...
Raymond van den BedumCommented:
Hi,

How large is the record set that you want to insert, and what is the size of your table you want that you wan't to update.

These kind of performance issues can have several cause's, if your looking at a very small table and record set i would first take a look if you don't have IO issues.

Please share your table structure and insert,update query to give a more in depth view the actions you want to preform.
Computer GuyAuthor Commented:
Actually, I am using Helium Music Manager (http://www.helium-music-manager.com/)

There are a few different Database types I can use and one of them in MySQL with the Premium Version.

When it scans my hard drive for songs (that part is quick) but adding the data (artist, title, path, etc) takes FOREVER!!!
Raymond van den BedumCommented:
As you use a music manager i asume were taking about a lot of data, All the settings metiont below are just an indication, it all depends on your database design.

MySQL is depending (defently in case of large record sets) on your disk IO.

I know there are some preformance improvements by setting the trx_commit

innodb_flush_log_at_trx_commit = 2

Other settings that can improve your prefomace.

innodb_file_per_table
innodb_buffer_pool_size = 2G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.