Brian,
wdmysql.exe is using 80MB of ram! I have 55000 records in the wx_data table. Can you make a version of wdmysql which does not load the records into a listview control - I hope that will reduce the memory usage.
/Ian
try a new 10.17o download
then you can tick , dont enable the table
then restart the wdmysql
Brian,
Only the zip file has revision o. But it does not have a new wdmysql in it.
/Ian
the zip version only contains the latest weatherd.exe and no other …exe files
so you need to download the latest full version 10.17o
it says 10.17n, but it actualy is 10.17o
believe me
Ok, got the new version. With the new setting, the memory usages goes from 15MB to 72MB and back down again approximately every minute. Any ideas why this could be? Are you fetching the entire wx_data record set into memory?
/Ian
must be just the way the component works
i cant stop that by the looks
Are you fetching the entire wx_data record set into memory? /Ian
Hmmm…that might account for why adding records to an server across the Internet starts to become incredibly slow once you get a fair number of records in the database and also why just adding a record every minute was consuming such a massive amount of bandwidth between my WD PC and the server (200kb/s when I eventually killed it off).
I’ll have to see if I can turn MySQL logging on and see if I can work out what happens each time a record is updated.
i dont have much control
you have to set the table, database to use., and then it connects to that
then
i just go:
append
chris, i have been updating the myql server you set me on your server ok , i have left it going…sometimes though i get a key violation (i am trying to trap that and then get the wdmysql to restart)
I’ve turned logging on in MySQL and can see the following queries from my WD system (running 10.17p that I’ve just downloaded). The log shows Date, time, ‘user session’, then the query description.
As you can see there’s an INSERT INTO (which is expected), immediately followed by a “Select * from wx_data ORDER BY station_id, date, time”. That means that after each record is uploaded, the WD client downloads every record from the database.
I’ve turned off the table update, so I’m not sure why all the data is being read back from the database. The odd thing is that I can see your INSERT INTO queries and they aren’t followed by the Select * query.
Query log shown below…
040923 23:20:32 110 Query INSERT INTO wx_data (station_id, date, time, average_windspeed, wind_direction, gust_windspeed, temperature, outdoor_humidity, barometer, daily_rainfall, monthly_rainfall, yearly_rainfall, rain_rate, max_rain_rate_curent_day, indoor_temperature, indoor_humidity, soil_temperature, forecast_icon, wmr968_extra_temperature, wmr968_extra_humidity, wmr968_extra_sensor_number, yesterday_rainfall, extra_temperature_sensor_2, extra_temperature_sensor_3, extra_temperature_sensor_4, extra_temperature_sensor_5, extra_temperature_sensor_6, extra_temperature_sensor_7, extra_humidity_2, extra_humidity_3, extra_humidity_4, hour, minute, second, station_name, dallas_1_wire_lightning_count, actual_solar_reading, day, month, wmr968_battery_level_1, wmr968_battery_level_2, wmr968_battery_level_3, wmr968_battery_level_4, wmr968_battery_level_5, wmr968_battery_level_6, wmr968_battery_level_7, current_windchill, current_humidex, max_daily_temperature, min_daily_temperature, icon_type, current_weather_desc, barometer_trend_last_hour, max_gust_current_day, dew_point_temperature, cloud_height, max_humidex, min_humidex, max_windchill, min_windchill, davis_vp_uv, max_heat_index, min_heat_index, heat_index, max_average_windspeed_day) VALUES (‘Blackpool-’, ‘2004-09-23’, ‘23:19:09’, 9.1, 322, 11.1, 12.6, 61, 1019, 3, 84, 577, 0, 0.2, 18.7, 62, 0, 1, 11.2, 62, 4, 5, 11.2, 15.6, 23, 18.7, -100, -100, 62, 67, 51, 23, 17, 59, ‘Blackpool-UK-23:17:59’, 0, 0, 23, 9, 100, 100, 100, 100, 2, 0, 0, 10.9, 12, 15.6, 12, 1, ‘Dry\Night_time’, 1, 21.8, 5.3, 3073.5, 18, 10.4, 15.3, 10, 0, 15.6, 12, 12.6, 14)
040923 23:20:33 110 Query Select * from wx_data ORDER BY station_id, date, time
which version of the wdmysql chris?
try vers 1.8
i just restarted my one after it lost connection and had a duplicate record error
PS. As my MySQL table currently contains >51000 rows of data (approx 24MB of data) it’s not surprising that there is a lot of bandwidth being used up between the server and my WD PC! each time the Select * query is used. I don’t think there is actually time to dump all the data back into WD before the next update is due!
I assume that the client software you’re using would probably allocate a buffer for the incoming data, so that might be where the memory is being consumed?
but why is doing a select query?
it should not be doing that, unless you are using the query function built into the wdmysql
and you say its not doing that for me?
which version of the wdmysql chris? try vers 1.8
I’m using v1.8. I don’t have a query defined and I can’t ever remember having used the query option as I have other tools which allow me to see the data.
I’ve got to go to work now, but if I get a chance at the weekend I’ll enable logging again and if you can force the duplicate error we might be able to see exactly what happened.
You’re generating Select *'s now. One INSERT INTO followed by one SELECT *. My bandwidth usage is high again today, so it looks like might be responsible for that.
040924 18:34:11 3 Query INSERT INTO wx_data (station_id, date, time, average_windspeed, wind_direction, gust_windspeed, temperature, outdoor_humidity, barometer, daily_rainfall, monthly_rainfall, yearly_rainfall, rain_rate, max_rain_rate_curent_day, indoor_temperature, indoor_humidity, soil_temperature, forecast_icon, wmr968_extra_temperature, wmr968_extra_humidity, wmr968_extra_sensor_number, yesterday_rainfall, extra_temperature_sensor_2, extra_temperature_sensor_3, extra_temperature_sensor_4, extra_temperature_sensor_5, extra_temperature_sensor_6, extra_temperature_sensor_7, extra_humidity_2, extra_humidity_3, extra_humidity_4, hour, minute, second, station_name, dallas_1_wire_lightning_count, actual_solar_reading, day, month, wmr968_battery_level_1, wmr968_battery_level_2, wmr968_battery_level_3, wmr968_battery_level_4, wmr968_battery_level_5, wmr968_battery_level_6, wmr968_battery_level_7, current_windchill, current_humidex, max_daily_temperature, min_daily_temperature, icon_type, current_weather_desc, barometer_trend_last_hour, max_gust_current_day, dew_point_temperature, cloud_height, max_humidex, min_humidex, max_windchill, min_windchill, davis_vp_uv, max_heat_index, min_heat_index, heat_index, max_average_windspeed_day) VALUES (‘Awhitu,Gra’, ‘2004-09-25’, ‘05:33:28’, 12.2, 199, 15.7, 10.1, 96, 1002.8, 27.9, 66.3, 931.7, 0.1, 0.8, 20.9, 54, 13.2, 2, 0, 0, 0, 0.3, 13.2, 20.7, 20.2, 0, 20.9, -129.6, 61, -100, -100, 5, 31, 58, ‘Awhitu,Grahams_Beach-05:31:58’, 0, 0, 25, 9, 100, 100, 100, 100, 100, 100, 100, 7.2, 11.1, 15.3, 10.1, 1, ‘Light_rain\Night_tim’, 0.1, 35.7, 9.4, 250.5, 18.4, 10.8, 14.9, 7.2, 0, 15.3, 10.1, 10.1, 27)
040924 18:34:12 3 Query Select * from wx_data ORDER BY station_id, date, time
Oops…I think I’ve messed up your data upload. I tried deleting all of my data from the table, but got the SQL wrong and deleted all the data. Your updates are still coming in, but not being processed into the table. I’m not sure why that is, but you might be better killing the connection and re-starting the uploads.
I’ve tried all the options I can (including Show/Hide in 17q) and I can’t find a way to stop WD sending the Select * even when the data table isn’t active.
Do you have any control over the table processing code? For example, are you able to add a ‘LIMIT 10’ to the SQL so that it only tries to return 10 records rather than all the records in the database.
Hmmm…there is actually a flaw in the table code, in that it does a :
Select * from wx_data ORDER BY station_id, date, time
This will return all the records in the table, and in case of the current test table you’ll end up getting records back from my station as well as your own. Assuming you can’t turn off the SQL to populate the table, can you change it to something like:
SELECT * from wx_data WHERE station_id = LOCAL_WD_STATION_ID ORDER BY date, time DESC LIMIT 10
Where LOCAL_WD_STATION_ID is the ID that WD is entering into records it’s uploading. This would only return records for a single station and also only return the last to records. You could also limit the download still further by specifying the columns you wanted rather than *
hi
i have looked at the code
there is only an append,then the field data is set, then a post is done
no select, no query, no search
strange!
note, i did create this page:
http://www.weather-display.com/windy/gb/indexmysql.php
but i had trouble…it would load too much data!
i need to have it only shows the last say 10 records
i will email you the raw file
so you can point my errors!
(i.e it needs to be changed a long your lines of the limit)
but, anyways, maybe someone has hijacked your mysql table??
maybe create a new one, dump the old one, and let me know the new username/password?
A few mods are required to your code to stop you getting too much data back:
To make sure you get the records back in date/time order change[code:1]$order=“date”; // Sort Order[/code:1]to
[code:1]$order=“date,time”; // Sort Order[/code:1]
Then change
[code:1]$query=“SELECT time,date,average_windspeed,wind_direction,gust_windspeed,temperature,outdoor_humidity,”.
“barometer,day FROM $table”." ORDER BY $order DESC";[/code:1]to
[code:1]$query=“SELECT time,date,average_windspeed,wind_direction,gust_windspeed,temperature,outdoor_humidity,”.
“barometer,day FROM $table”." ORDER BY $order DESC LIMIT $nlimit";[/code:1]
Adding LIMIT to the query means that you will only ever get up to $nlimit records returned, so you can remove:[code:1] if ($nrec>=$nlimit) break;[/code:1]
I don’t think that anyone has hijacked the database. The rogue SELECT always arrives immediately after the INSERT INTO using the same user session as the INSERT INTO used. If someone else had found out the password, they would have a different user session.
Also when there’s only you updating the database there is only one SELECT, but as soon as I start updating the database there are two (one ofr your session and one for mine). If I stop updating it drops back to one SELECT again. I can’t think how anyone could arrange to do that. Anyone monitoring the traffic into the server would need to be incredibly close to the server in network terms to see both your session from NZ and mine from UK.
Finally, if someone had hijacked the database, I’m sure they’d be doing more than just a SELECT every minute. The data they’d be getting isn’t that exciting and they’re not causing a denial of service.
I think the SELECT must be coming from the MySQL update code. Your code that updates the database isn’t adding it, but I wonder if the data display table sends a SELECT even if it’s turned off. Is it possible to create a cut down version of the update code that doesn’t have the display table or the query code. i.e. it just updates. It would be interesting to see if the SELECT still appeared then?
hi
thanks for the tips on the changes
i have only 20 records now
but i had to leave the break in there, otherwise the table would lock up the browser…
the other problem is the data is from the 1st of august now…so something is wrong./.
re the select:
ok, i will try scaling it back, etc, and experiment, and see if i can fix it …
as it definalty needs a fixing
i have made a small tweak
leet me know…