Re: Normalization vs. Performance
At 2:11pm -0400 on Tue, 26 Aug 2008, John Smith wrote: > So how bad is this? The mentioned query will be the query which is used > the most in my application (yes, it is going to be a forum). > Should I break normalization and save the date of the root in each node row? My recommendation is no. Normalization is a Good Thing, and you'll be hard-pressed to convince me otherwise. In the long run, you (or following programmers) will thank yourself if you maintain a normalized core data model. Seriously. But speed and performance are the here-and-now issues while DB technology "catches up" to demands. Have you looked at temporary tables or materialized views? These might help you in this endeavor. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: recommended high availability setups
agador- did you look at replication implementations using master/slave configuration? http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-diffengines.html HTH/ Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 28 Aug 2008 13:38:37 -0700 > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: recommended high availability setups > > What's the recommended method for high-availability setups? > > I've currently got a master-master replication setup that I'm testing > but it doesn't quite seem as complete a solution as I had pictured. > > When a failed server comes back online you still have to manually add > it based off of the line numbers/binlog correct? And adding a new DB > requires reloading the MySQL server? > > I've been looking around at other technologies as well like linux-ha. > What are other people using for systems that have to be up 100% of the > time but aren't complete nightmares to manage? > > TIA! > > -Bryan > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > _ Get ideas on sharing photos from people like you. Find new ways to share. http://www.windowslive.com/explore/photogallery/posts?ocid=TXT_TAGLM_WL_Photo_Gallery_082008
Re: recommended high availability setups
In the last episode (Aug 28), Bryan Irvine said: > I saw that. That's what initially got me thinking that I might need > to look elsewhere for a different HA solution. > > The biggest thing is that the DB is for a hosting company. This > company could add databases for their clients upon a request and at > any point during the day. The current master-master that I've got set > up and working in my test environment requires the MySQL server be > restarted because you have to manually add the databases you want to > sync to the my.cnf file. Instead of specifying databases to replicate with the replicate-do-db option, why not specify the databases you /don't/ want to replicate with the replicate-ignore-db option? Then any newly-created databases will get replicated automatically. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommended high availability setups
I saw that. That's what initially got me thinking that I might need to look elsewhere for a different HA solution. The biggest thing is that the DB is for a hosting company. This company could add databases for their clients upon a request and at any point during the day. The current master-master that I've got set up and working in my test environment requires the MySQL server be restarted because you have to manually add the databases you want to sync to the my.cnf file. Then upon rebooting the 'slave/master' you have to manually add it back to the slave in order to receive it's updates again. I'm not sure if this is the best way or not. Do you use the drbd method? Are any of these things accounted for in that solution? -Bryan On Thu, Aug 28, 2008 at 2:06 PM, Jimmy Guerrero <[EMAIL PROTECTED]> wrote: > Hello, > > It isn't without its pros and cons (just like any other HA solution), but > you might want to check out DRBD. > > http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-drbd-heartbeat.html > > -- Jimmy > > Bryan Irvine wrote: >> >> What's the recommended method for high-availability setups? >> >> I've currently got a master-master replication setup that I'm testing >> but it doesn't quite seem as complete a solution as I had pictured. >> >> When a failed server comes back online you still have to manually add >> it based off of the line numbers/binlog correct? And adding a new DB >> requires reloading the MySQL server? >> >> I've been looking around at other technologies as well like linux-ha. >> What are other people using for systems that have to be up 100% of the >> time but aren't complete nightmares to manage? >> >> TIA! >> >> -Bryan >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommended high availability setups
I saw that. That's what initially got me thinking that I might need to look elsewhere for a different HA solution. The biggest thing is that the DB is for a hosting company. This company could add databases for their clients upon a request and at any point during the day. The current master-master that I've got set up and working in my test environment requires the MySQL server be restarted because you have to manually add the databases you want to sync to the my.cnf file. Then upon rebooting the 'slave/master' you have to manually add it back to the slave in order to receive it's updates again. I'm not sure if this is the best way or not. Do you use the drbd method? Are any of these things accounted for in that solution? -Bryan On Thu, Aug 28, 2008 at 2:06 PM, Jimmy Guerrero <[EMAIL PROTECTED]> wrote: > Hello, > > It isn't without its pros and cons (just like any other HA solution), but > you might want to check out DRBD. > > http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-drbd-heartbeat.html > > -- Jimmy > > Bryan Irvine wrote: >> >> What's the recommended method for high-availability setups? >> >> I've currently got a master-master replication setup that I'm testing >> but it doesn't quite seem as complete a solution as I had pictured. >> >> When a failed server comes back online you still have to manually add >> it based off of the line numbers/binlog correct? And adding a new DB >> requires reloading the MySQL server? >> >> I've been looking around at other technologies as well like linux-ha. >> What are other people using for systems that have to be up 100% of the >> time but aren't complete nightmares to manage? >> >> TIA! >> >> -Bryan >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommended high availability setups
Hello, It isn't without its pros and cons (just like any other HA solution), but you might want to check out DRBD. http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-drbd-heartbeat.html -- Jimmy Bryan Irvine wrote: What's the recommended method for high-availability setups? I've currently got a master-master replication setup that I'm testing but it doesn't quite seem as complete a solution as I had pictured. When a failed server comes back online you still have to manually add it based off of the line numbers/binlog correct? And adding a new DB requires reloading the MySQL server? I've been looking around at other technologies as well like linux-ha. What are other people using for systems that have to be up 100% of the time but aren't complete nightmares to manage? TIA! -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recommended high availability setups
What's the recommended method for high-availability setups? I've currently got a master-master replication setup that I'm testing but it doesn't quite seem as complete a solution as I had pictured. When a failed server comes back online you still have to manually add it based off of the line numbers/binlog correct? And adding a new DB requires reloading the MySQL server? I've been looking around at other technologies as well like linux-ha. What are other people using for systems that have to be up 100% of the time but aren't complete nightmares to manage? TIA! -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remove form feed characters from a text field
I've extracted text from approx 1600 pdf files using pdftotext.exe and inserted it into a table. Now I see there are form feed characters in the field, and I would suspect other special characters, also. I'm not having much luck trying to remove them. Any pointers appreciated. Thanks, David
Re: MyIsam Locking Questions
On Thu, Aug 28, 2008 at 1:14 PM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote:
> My point is that on my process lists there are no writes being done at that
> time only reads and actually only one read all other reads are locked as
> well as writes.
Sure, that's because the reads are in line behind the write that is
waiting for a lock. ("Your call will be answered in the order in
which it was received...") You can set the writes to low priority, or
use delayed writes, but you run the risk of starving the writes.
Frankly, those are hacky solutions compared to the InnoDB approach.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Locking Questions
My point is that on my process lists there are no writes being done at that time only reads and actually only one read all other reads are locked as well as writes. I've gone through every single one of the queries in my processlist at any given time when more than 500 process pile up and its always a select taking too long thats locking other selects that use the same table. Writes are locked as well which I understand but why the reads. I can provide you with all the process list at one point you will see there are no writes being done at that specific time. only one read that is locking all other reads. T Example. ---Locking Queries--- - 0 - - show full processlist domain_12263 - 50 - preparing - SELECT * FROM account.identity_data WHERE identity_id IN (SELECT identity_id FROM access_domainUsers WHERE deleted = 0 AND status = 0) ORDER BY kudos_current DESC LIMIT 0, 1 ---Locking Queries End --- DB - Time - Status - Query account - 50 - Locked - update identity_data set last_seen='1219936816', total_posts = total_posts + 1, last_post_time ='1219936816' where identity_id = '3875474' - 0 - - show full processlist domain_27046 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS id, rd.title, rd.created_at AS created_at, rd.body, rd.ip, rd.edit_count, rd.edited_on, rd.box_id, rd.avatar AS current_avatar, rd.current_postcount, adu.identity_id, adu.name, adu.user_domain, rd.hide AS hide, adu.avatar, adu.total_posts, adu.user_domain, adu2.name AS editor_name, adu2.user_domain AS editor_domain, rd.signature_text, rd.signature, adu.posts_padding AS posts_padding, adu.title AS cust_title, adu.auto_title AS auto_title, adu.title_post AS title_post, adu.title_member AS title_member, adu.title_kudos AS title_kudos, adu.title_admin AS title_admin, adu.title_mod AS title_mod, adu.custom_title AS custom_title, adu.user_type_id AS user_type_id, adu.show_signature AS show_signature, adu.show_avatar AS show_avatar FROM reply_data AS rd LEFT JOIN access_domainUsers AS adu ON adu.identity_id = rd.identity_id LEFT JOIN account.identity_data AS adu2 ON adu2.identity_id = rd.last_edited_by WHERE rd.lead_id = '12238' AND rd.deleted = 0 and hide = 0 ORDER BY rd.created_at ASC limit 0, 20 domain_11707 - 48 - Locked - select members_note.id, members_note.moderator, members_note.create_date, members_note.type, members_note.note, account.identity_data.name from members_note, account.identity_data where members_note.identity_id='994830' and members_note.moderator=account.identity_data.identity_id order by members_note.create_date desc limit 0, 3 domain_27031 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS id, rd.title, rd.created_at AS created_at, rd.body, rd.ip, rd.edit_count, rd.edited_on, rd.box_id, rd.avatar AS current_avatar, rd.current_postcount, adu.identity_id, adu.name, adu.user_domain, rd.hide AS hide, adu.avatar, adu.total_posts, adu.user_domain, adu2.name AS editor_name, adu2.user_domain AS editor_domain, rd.signature_text, rd.signature, adu.posts_padding AS posts_padding, adu.title AS cust_title, adu.auto_title AS auto_title, adu.title_post AS title_post, adu.title_member AS title_member, adu.title_kudos AS title_kudos, adu.title_admin AS title_admin, adu.title_mod AS title_mod, adu.custom_title AS custom_title, adu.user_type_id AS user_type_id, adu.show_signature AS show_signature, adu.show_avatar AS show_avatar FROM reply_data AS rd LEFT JOIN access_domainUsers AS adu ON adu.identity_id = rd.identity_id LEFT JOIN account.identity_data AS adu2 ON adu2.identity_id = rd.last_edited_by WHERE rd.lead_id = '7279' AND rd.deleted = 0 and hide = 0 ORDER BY rd.created_at ASC limit 0, 20 domain_27027 - 48 - Locked - SELECT id.name, id.user_domain, cb.* FROM chatbox_data AS cb, account.identity_data AS id WHERE cb.identity_id = id.identity_id ORDER BY created_at DESC limit 0, 25 domain_27578 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS id, rd.title, rd.created_at AS created_at, rd.body, rd.ip, rd.edit_count, rd.edited_on, rd.box_id, rd.avatar AS current_avatar, rd.current_postcount, adu.identity_id, adu.name, adu.user_domain, rd.hide AS hide, adu.avatar, adu.total_posts, adu.user_domain, adu2.name AS editor_name, adu2.user_domain AS editor_domain, rd.signature_text, rd.signature, adu.posts_padding AS posts_padding, adu.title AS cust_title, adu.auto_title AS auto_title, adu.ti
Re: MyIsam Locking Questions
On Thu, Aug 28, 2008 at 10:59 AM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote: > I understand that reads are locked by writes but nowhere does of mention > that reads also block reads. How could they not? You can't simultaneously read and write the same data -- the read would get half-written garbage. Read locks are shared, but write locks are exclusive, so they have to wait for reads to finish. You may find this section on locking helpful: http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html It also links to an explanation of concurrent inserts, which is a specific situation where MyISAM can handle reads and writes concurrently. For any application that has a significant percentage of writes or long-running SELECTs, you will get better concurrency from InnoDB with its MVCC approach. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Locking Questions
In the last episode (Aug 28), Jose Estuardo Avila said: > On Aug 28, 2008, at 5:48 AM, "Perrin Harkins" wrote: > > On Wed, Aug 27, 2008 at 8:21 PM, Jose Estuardo Avila wrote: > >> Hi, I've been trying to find information on how myisam handles > >> locks. I though myisam had locking only on writes and not on > >> reads. > > > > No, readers block writers. This true of any system that only has > > read and write locks to work with. The only reason they don't > > block in InnoDB tables is the MVCC system that lets readers use > > snapshots while writers modify the data. This is all in the MyISAM > > section of the docs. > > I understand that reads are locked by writes but nowhere does of > mention that reads also block reads. Boy queries y posted to the > list are selects. Are you sure there wasn't an UPDATE, INSERT, or DELETE somewhere in your process list? Readers and writers block each other, and when blocked, readers queue up behind writers. So an UPDATE query sent just after a long-running SELECT starts up will cause all other SELECTs to queue up behind it. You can force specific reads to queue up in front of writes with the HIGH_PRIORITY attribute, and you can force specific writes to queue up behind reads with the LOW_PRIORITY attribute. http://dev.mysql.com/doc/refman/5.1/en/locking-issues.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Locking Questions
I understand that reads are locked by writes but nowhere does of mention that reads also block reads. Boy queries y posted to the list are selects. Jose E. Avila(tachu) Yuku/Kickapps Sent from mobile device. On Aug 28, 2008, at 5:48 AM, "Perrin Harkins" <[EMAIL PROTECTED]> wrote: On Wed, Aug 27, 2008 at 8:21 PM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote: Hi, I've been trying to find information on how myisam handles locks. I though myisam had locking only on writes and not on reads. No, readers block writers. This true of any system that only has read and write locks to work with. The only reason they don't block in InnoDB tables is the MVCC system that lets readers use snapshots while writers modify the data. This is all in the MyISAM section of the docs. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex conditional statement during select
Try this: SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) >= '20080101' AND SUBSTRING(myDate,1,10) <= '20080131', 1, 0)) AS `January` FROM theTable GROUP BY theOther Problem was in parentheses Dusan Jay Blanchard napsal(a): SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) >= '20080101' AND SUBSTRING(myDate,1,10) <= '20080131'), 1, 0) AS `January` FROM theTable GROUP BY theOther Throws this error... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')), 1, 0) AS `January` Can I even do something like this during the SELECT. I tried a BETWEEN and while it did not throw errors it did not give back the expected data...I just got 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex conditional statement during select
SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) >= '20080101' AND SUBSTRING(myDate,1,10) <= '20080131'), 1, 0) AS `January` FROM theTable GROUP BY theOther Throws this error... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')), 1, 0) AS `January` Can I even do something like this during the SELECT. I tried a BETWEEN and while it did not throw errors it did not give back the expected data...I just got 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Locking Questions
On Wed, Aug 27, 2008 at 8:21 PM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote: > Hi, I've been trying to find information on how myisam handles locks. I > though myisam had locking only on writes and not on reads. No, readers block writers. This true of any system that only has read and write locks to work with. The only reason they don't block in InnoDB tables is the MVCC system that lets readers use snapshots while writers modify the data. This is all in the MyISAM section of the docs. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escape a dash in table name
>How do you escape a dash in a table name such as temp-08-08-28? Best not to use them at all. If you must for some odd reason, use backticks round the name. PB Dan O'Keefe wrote: How do you escape a dash in a table name such as temp-08-08-28? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escape a dash in table name
Using backticks `temp-08-08-28` Cheers, Ewen On Thu, Aug 28, 2008 at 1:01 PM, Dan O'Keefe <[EMAIL PROTECTED]> wrote: > How do you escape a dash in a table name such as temp-08-08-28? > > Thanks, > > Dan > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escape a dash in table name
Dan O'Keefe wrote: How do you escape a dash in a table name such as temp-08-08-28? Thanks, Dan How about `` ? hth, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
escape a dash in table name
How do you escape a dash in a table name such as temp-08-08-28? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
