MySQL performance on Windows

[kc64]

Hi all.

I have been using LV2010SP1 with MySQL to implement data acquisition systems for hundreds of channels.  I typically run the MySQL instance on the same machine as the LV app and I love the synergy this combo provides.  I interface LV with the database using Mike Porter's excellent ADO database toolkit.  (I can't find a link to it presently but I found it on the forums.)

I have a LV process to collect the data into a table.  I have another LV process to mine a small subset of channels for plotting on a chart.

I have run into a few limitations with my implementation.  When the number of records in the table grew to >250K, the process of querying the table began to grow large.  I now see query times longer than 50 seconds while I'm trying to refresh the chart every 10 seconds.  This obviously won't work.

The current machine I am using is a Toshiba Satellite with a quad core i3, 4GB RAM, 500GB HDD, and Windows 7.  When I examine the task manager in Windows, it shows most of the processor time spent idle.  When I look at the MySQL Workbench performance charts, they seem to indicate a low level of activity.  I'm not sure what to do to boost the performance of this setup.  Have any of you expeienced similar problems?

Thanks.

[Matt_W1]

 I use sqlite (with a LV interface I wrote) so if the problem isn't related to some quirk with mysql, I might be able to point you in the right direction.

 Is the query slow when you don't have the collection process running? (maybe some kind of contention between the reader and writer).

 Is it still slow if you break the query up into smaller queries? (I'm not sure what the ADO toolkit offers but limit and offset should work although they probably aren't the ideal method, this might indicate some memory handling issues)

 Are you sure that the query is the slow part, and not display code or something else? (easiest to check by just running the query by itself, be sure to try combining this with breaking up the query)

 What does the slow query and database schema look like? (maybe you need to add an index or adjust the table structures)

 Does it become gradually slower or suddenly jump from fast to slow?