Re: mysql load balancing
2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. I hope that helps. Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Spatial extensions
Hi Gavin,
OK, I finally tracked down the source of the trouble... My CREATE TABLE query
included a regular KEY index instead of SPATIAL KEY -- that's why the
MBRCONTAINS wasn't working... Works now!!
Thanks for your help.
On 2009-12-22, at 2:42 AM, Gavin Towey wrote:
How did you populate the point column? Should be something like:
UPDATE places SET point= =GeomFromText(CONCAT(Point(,longitude,
,latitude,)));
You also should have a spatial index:
ALTER TABLE places MODIFY coordinates point NOT NULL, add spatial index
(coordinates);
You can check the data with the ASTEXT() function:
SELECT longitude, latitude, ASTEXT(coordinates) FROM places;
Hope this helps
-Gavin Towey
From: René Fournier [mailto:m...@renefournier.com]
Sent: Saturday, December 19, 2009 12:42 AM
To: Gavin Towey
Cc: mysql
Subject: Re: Spatial extensions
Thanks Gavin.
I've got part your query working... sort of. Something I can't figure out is
how to use MBRContains on a table with a POINT column. For example, this
works:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), GeomFromText('Point(49 -114)')) limit 10;
++--+
| id | astext(coordinates) |
++--+
| 1 | POINT(49.00701238 -110.00507933) |
| 2 | POINT(49.01415809 -110.01615511) |
| 3 | POINT(49.01424023 -110.00508075) |
| 4 | POINT(48.99978158 -110.01617366) |
| 5 | POINT(48.99978996 -110.00507794) |
| 6 | POINT(49.00683419 -110.02751996) |
| 7 | POINT(49.01402057 -110.03861578) |
| 8 | POINT(49.01407281 -110.02750442) |
| 9 | POINT(48.99974667 -110.0386263) |
| 10 | POINT(48.9997718 -110.0275421) |
++--+
10 rows in set (0.00 sec)
But when I try to use the table's POINT column, nothing is returned:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), (coordinates)) limit 10;
Empty set (0.00 sec)
What am I missing? For clarity, here's the table schema:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`),
KEY `coord` (`coordinates`(25))
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
On 2009-12-18, at 2:21 AM, Gavin Towey wrote:
Not only is it 5.1, but there's a special branch that has improved GIS
functions not found in the regular MySQL. I'm not sure if/when they're
planning on rolling them back into mysql:
http://downloads.mysql.com/forge/mysql-5.1.35-gis/
If it's not possible to use that version, then you can still implement a
Distance function yourself as a stored procedure or UDF. Just google for
mysql+haversine or something similar.
The important part though is the MBRContains, which does an efficient box
cull and uses the spatial index. Oops, I forgot to change a couple
occurances of line_segment to coordinates line_segment was just the
column name I was using in my original query.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Thursday, December 17, 2009 8:54 AM
To: Gavin Towey
Cc: mysql
Subject: Re: Spatial extensions
Awesome, this is what I was trying to find, as you succinctly wrote it. I
*really* appreciate getting pointed in the right direction, since I haven't
found a lot of MySQL's GIS tutorials directed at what I'm trying to do.
Still, a couple questions, the Distance() function you included, that must
require 5.1 or higher right? 5.0.88 on my box throws an error:
Function places.Distance does not exist
Also, where does line_segment come from in the below query?
Thanks.
...Rene
On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
Yes, spatial indexes are very fast:
Query would be something like:
SET @center = GeomFromText('POINT(37.372241 -122.021671)');
SET @radius = 0.005;
SET @bbox = GeomFromText(CONCAT('POLYGON((',
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))')
);
select id, astext(coordinates), Distance(@center,line_segment) as dist
FROM places where MBRContains(@bbox, line_segment) order by dist limit 10;
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Wednesday, December 16, 2009 4:32 PM
To: mysql
Subject: Spatial extensions
I have table with 2 million rows of geographic points
sql tuning
Hi All, I have below sql, the sql is quite big. select this_.listingprog_id as listingp1_490_3_, this_.marketplace_id as marketpl2_490_3_, this_.company_id as company3_490_3_, this_.listing_id as listing4_490_3_, this_.marketplace_listing_id as marketpl5_490_3_, this_.processing_flag as processing6_490_3_, this_.comments as comments490_3_, this_.successfully_submitted as successf8_490_3_, this_.date_added as date9_490_3_, this_.date_modified as date10_490_3_, this_.scheduled_end_time as scheduled11_490_3_, this_.lock_expiry_time as lock12_490_3_, this_.retries as retries490_3_, this_.resubmit_processing as resubmit14_490_3_, this_.bin_enabled as bin15_490_3_, this_.ebay_fixed_price_yn as ebay16_490_3_, listing1_.company_id as company1_485_0_, listing1_.listing_id as listing2_485_0_, listing1_.shipping_snapshot_id as shipping3_485_0_, listing1_.payment_snapshot_id as payment4_485_0_, listing1_.tax_snapshot_id as tax5_485_0_, listing1_.business_snapshot_id as business6_485_0_, listing1_.ebay_market_snapshot_id as ebay7_485_0_, listing1_.item_id as item8_485_0_, listing1_.ebay_ad_id as ebay9_485_0_, listing1_.marketplace_id as marketp10_485_0_, listing1_.marketplace_listing_id as marketp11_485_0_, listing1_.title as title485_0_, listing1_.sub_title as sub13_485_0_, listing1_.status as status485_0_, listing1_.date_added as date15_485_0_, listing1_.date_modified as date16_485_0_, listing1_.scheduled_start_time as scheduled17_485_0_, listing1_.scheduled_end_time as scheduled18_485_0_, listing1_.actual_start_time as actual19_485_0_, listing1_.actual_end_time as actual20_485_0_, listing1_.ad_schedule_queue_id as ad21_485_0_, listing1_.launch_profile_id as launch22_485_0_, listing1_.category_id as category23_485_0_, listing1_.category_id2 as category24_485_0_, listing1_.ebay_stores_category_id as ebay25_485_0_, listing1_.ebay_stores_category_id2 as ebay26_485_0_, listing1_.duration as duration485_0_, listing1_.quantity as quantity485_0_, listing1_.live_quantity as live29_485_0_, listing1_.revoke_quantity as revoke30_485_0_, listing1_.ebay_fixed_price_yn as ebay31_485_0_, listing1_.min_bid asmin32_485_0_, listing1_.bin_price as bin33_485_0_, listing1_.reserve_price as reserve34_485_0_, listing1_.current_price as current35_485_0_, listing1_.number_of_bids as number36_485_0_, listing1_.height as height485_0_, listing1_.weight as weight485_0_, listing1_.width as width485_0_, listing1_.depth as depth485_0_, listing1_.dimension_units as dimension41_485_0_, listing1_.weight_unit as weight42_485_0_, listing1_.package_size as package43_485_0_, listing1_.picture_url as picture44_485_0_, listing1_.gallery_url as gallery45_485_0_, listing1_.title_image_number as title46_485_0_, listing1_.gallery_image_type as gallery47_485_0_, listing1_.counter_type as counter48_485_0_, listing1_.counter_value as counter49_485_0_, listing1_.honesty_counter_mode as honesty50_485_0_, listing1_.honesty_counter_border as honesty51_485_0_, listing1_.deluxe_align as deluxe52_485_0_, listing1_.deluxe_theme as deluxe53_485_0_, listing1_.prev_marketplace_listing_id as prev54_485_0_, listing1_.relist_status as relist55_485_0_, listing1_.relist_offset as relist56_485_0_, listing1_.imported_from_mkt as imported57_485_0_, listing1_.email_winner as email58_485_0_, listing1_.checkout_redirect as checkout59_485_0_, listing1_.listing_type as listing60_485_0_, listing1_.run_postsale as run61_485_0_, listing1_.prefill_product_id as prefill62_485_0_, listing1_.stock_photo_yn as stock63_485_0_, listing1_.prefill_info_yn as prefill64_485_0_, listing1_.stock_photo_gallery_yn as stock65_485_0_, listing1_.prefill_attr_str as prefill66_485_0_, listing1_.stock_photo_url as stock67_485_0_, listing1_.prefill_details_url as prefill68_485_0_, listing1_.best_offer_enable_yn as best69_485_0_, listing1_.best_offer_option as best70_485_0_, listing1_.best_offer_decline_value as best71_485_0_, listing1_.fvf_fee as fvf72_485_0_, listing1_.listing_fees as listing73_485_0_, listing1_.purchase_cost as purchase74_485_0_, listing1_.iea_id as iea75_485_0_, listing1_.sco_listing as sco76_485_0_, listing1_.parts_acc as parts77_485_0_, listing1_.relaunched as relaunched485_0_, listing1_.cid as cid485_0_, delayedsub4_.company_id as company1_541_1_, delayedsub4_.listing_id as listing2_541_1_, delayedsub4_.submission_id as submission3_541_1_, delayedsub4_.marketplace_id as marketpl4_541_1_, delayedsub4_.attempt_number as attempt5_541_1_, delayedsub4_.submit_time as submit6_541_1_, delayedsub4_.window_start_time as window7_541_1_, delayedsub4_.window_end_time as window8_541_1_, delayedsub4_.processing_flag as processing9_541_1_, delayedsub4_.lock_expiry_time as lock10_541_1_, delayedsub4_.successfully_submitted as success11_541_1_, delayedsub4_.comments as comments541_1_, delayedsub4_.date_added as date13_541_1_, delayedsub4_.date_modified as date14_541_1_, delayedsub4_.sequence_id as sequence15_541_1_, delayedsub4_.archive_yn as archive16_541_1_, delayedsub4_.ad_schedule_queue_id as
Error in running trigger
Hi, I have created the below trigger from root user with definer: CREATE definer=`roo...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
is it root or root1 user. Also is this trigger executed by root user or some other user. On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`roo...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
Hi Ranjan, User doesn't have sufficient privileges. root access required. Thanks, Krishna On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`*root...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
please run this and lets us know the output show grants on root1; On Tue, Dec 22, 2009 at 5:50 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Ranjan, User doesn't have sufficient privileges. root access required. Thanks, Krishna On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`*root...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: mysql load balancing
Hi, El día 22 de diciembre de 2009 10:14, Jaime Crespo Rincón jcre...@warp.es escribió: 2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). See you. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Free online training class on MySQL backups and recovery.
Hey everyone, I just posted new blog post about an online training class I will be doing January the 13th. This class will cover backups, recovery and disaster planning. It is completely free with no strings attached. If you are interested, take a look at the blog post here: http://www.paragon-cs.com/wordpress/?p=358 It contains all the details. There is a limited number of seats available so you need to respond soon. Looking forward to seeing you there! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
UPDATE and simultaneous SELECT ... similar to RETURNING?
All, There was a feature of another DB that I have grown extremely accustomed to and would like to find the equivalent in MySQL: UPDATE mytable SET mycolumn = mycolumn + 1 WHERE mykey = 'dante' RETURNING mycolumn; The magic of this statement is in the RETURNING clause. RETURNING causes every update statement to become a select statement also where the rows affected by the update can also be returned. This works for multiple rows or just one and is how I have been able to do in 1 step what otherwise seems to require many. In MySQL, I have found this so far: UPDATE mytable SET mycolumn = @mycolumn := mycolumn + 1 WHERE mykey = 'dante'; SELECT @mycolumn; This provides the same solution as the query above, but it has to be performed in 2 steps and it won't work for multiple rows since the @mycolumn variable will be overwritten for each matched row in the WHERE clause. Does anyone have suggestions on a MySQL pattern that might achieve what I'm after? Any word on whether the RETURNING syntax might be added to the supported SQL syntax some time in the future? Dante -- D. Dante Lorenso
