career advice - Excel Expert and MySQL SQL specialist
Hi I passed Oracle SQL Expert and working on MySQL 5.6 developer exam. I work 5% of my job with SQL but I am searching for a full time SQL job. http://shop.oreilly.com/product/0790145363466.do Is the Excel exam (Microsoft Office Specialist - MOS) useful for getting a junior SQL pro job? What do you think? Lukas
Re: i need advice on redundancy of mysql server.
Guys - thanks for the replys - do any of you guys are on odesk or elancer.com ?? thanks On Wed, Jun 13, 2012 at 7:28 PM, Rick James rja...@yahoo-inc.com wrote: I prefer: * Master-Master (dual master) but write to only one of them. (Writing to both can lead to duplicate keys, etc., unless you are very careful in your code.) * Have the two Masters geographically separate. (Think tornados, floods, earthquakes, etc) * Have Slave(s) hanging of each master -- (1) for read scaling, and (2) to avoid a major outage when one Master goes down and you need to take the other one down to clone it. Another thing to consider: Backing up via a LVM snapshot requires only a minute or so of downtime, regardless of dataset size. Percona's XtraBackup is also very good. I also agree that MyISAM in not best. But, caution, InnoDB's disk footprint is 2x=3x bigger than MyISAM's. You can Load Balance reads (among slaves and, optionally, masters); you cannot do writes. Any number of Apache servers can talk to MySQL. But watch out -- MaxClients should not be so large that it swamps max_connections. Load balancing: DNS is the simple way to load balance Apache. There are low-impact software solutions. There are hardware solutions. (This is what I am used to at work; it is severe overkill for most users.) Bottom line: There is no best or perfect solution. First decide what 'keeps you up at night'. -Original Message- From: Joey L [mailto:mjh2...@gmail.com] Sent: Monday, June 11, 2012 7:26 AM To: mysql@lists.mysql.com Subject: i need advice on redundancy of mysql server. I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
- Original Message - From: Joey L mjh2...@gmail.com It sounds like you are all consultants. Hehe. I'm not :-p A lot are, though, because the combined technical knowledge on this list draws in consultants looking for stuff, and having experienced consultants on the list in turn heightens the combined technical knowledge again. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Does really Master-Master replication provide load balancing feature? since, each node need to replicate to other node, and MySQL replication still a is single threaded replication , it mean there is only single replication thread sql_thread for DML queries. eg. There is two node with master master replication - Master -1 Master 2. *app1 --read/write* --- *Master 1 --* single thread*-- Master 2* --- *read/write -- app1* Its just a *high availability* not a load balancing*.* Thanks, On Wed, Jun 13, 2012 at 2:33 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Joey L mjh2...@gmail.com It sounds like you are all consultants. Hehe. I'm not :-p A lot are, though, because the combined technical knowledge on this list draws in consultants looking for stuff, and having experienced consultants on the list in turn heightens the combined technical knowledge again. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: i need advice on redundancy of mysql server.
I prefer: * Master-Master (dual master) but write to only one of them. (Writing to both can lead to duplicate keys, etc., unless you are very careful in your code.) * Have the two Masters geographically separate. (Think tornados, floods, earthquakes, etc) * Have Slave(s) hanging of each master -- (1) for read scaling, and (2) to avoid a major outage when one Master goes down and you need to take the other one down to clone it. Another thing to consider: Backing up via a LVM snapshot requires only a minute or so of downtime, regardless of dataset size. Percona's XtraBackup is also very good. I also agree that MyISAM in not best. But, caution, InnoDB's disk footprint is 2x=3x bigger than MyISAM's. You can Load Balance reads (among slaves and, optionally, masters); you cannot do writes. Any number of Apache servers can talk to MySQL. But watch out -- MaxClients should not be so large that it swamps max_connections. Load balancing: DNS is the simple way to load balance Apache. There are low-impact software solutions. There are hardware solutions. (This is what I am used to at work; it is severe overkill for most users.) Bottom line: There is no best or perfect solution. First decide what 'keeps you up at night'. -Original Message- From: Joey L [mailto:mjh2...@gmail.com] Sent: Monday, June 11, 2012 7:26 AM To: mysql@lists.mysql.com Subject: i need advice on redundancy of mysql server. I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
It sounds like you are all consultants. On Mon, Jun 11, 2012 at 4:46 PM, Baron Schwartz ba...@xaprb.com wrote: Yeah -- that was an unintentional omission. There are solo consultants like Ronald Bradford too. On Mon, Jun 11, 2012 at 3:14 PM, Andrew Moore eroomy...@gmail.com wrote: Not forgetting Pythian, Baron ;) On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz ba...@xaprb.com wrote: Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Joey, I can not afford any downtime and the data is realtime. This is a very good reason for asking help to consultants. If you ask What is the best method for this setup? master-master or master-slave? then the simple answer is master-slave, for any mysql setup, that is the only safe mysql replication setup. If you mean Master-Master Active/Passive I still consider that Master-Slave. While is very well advisable to stay away from master-master active/active, unless you really know what you are doing and you have the development aware, very skilled, and listening to you*. So, go for master-slave, but I would suggest to read online mysql manuals and some great books, because if you post here to ask every single step to set it up, you are basically asking someone else to do the job for you, for free ;) Cheers Claudio 2012/6/12 Joey L mjh2...@gmail.com It sounds like you are all consultants. On Mon, Jun 11, 2012 at 4:46 PM, Baron Schwartz ba...@xaprb.com wrote: Yeah -- that was an unintentional omission. There are solo consultants like Ronald Bradford too. On Mon, Jun 11, 2012 at 3:14 PM, Andrew Moore eroomy...@gmail.com wrote: Not forgetting Pythian, Baron ;) On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz ba...@xaprb.com wrote: Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
i need advice on redundancy of mysql server.
I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
when u say redudency. Do u just want replication like master-slave, which will be active-passive or Master-master which be active-active. master-slave, will work just a DR, when ur current master fails you can failover the slave, with NO LOAD balancing. Master-master allows load balancing. On Mon, Jun 11, 2012 at 7:56 PM, Joey L mjh2...@gmail.com wrote: I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
On 6/11/2012 10:36 AM, Ananda Kumar wrote: ... Master-master allows load balancing. Why do people keep replication rings as if they are the best possible configuration? A master-slave relationship also permits load balancing and is easier to maintain and recover in the event of a node failure. Any MySQL replication topology cannot, in a generic sense, allow load balancing. Careful precautions must be maintained in your load balancer and in your application code to avoid data collisions (adding or modifying the same PK on the same table on two or more nodes at the same time). For continuous uptime, you do need redundancy. For the 'limited downtime' scenario that the customer proposed, this includes geographical redundancy as well as physical duplication and should also include sharding his data so that the loss of one sharded set (due to extreme disaster or comms failure) does not knock all of his users offline at the same time. realtime data is also a variable definition. For hydrologic monitoring data 'realtime' could mean within the last 15 minutes. For some applications (such as telecommunication) 'realtime' is measured in microseconds. It may be that the customer's requirements can tolerate a normal variance in duplication time provided by MySQL's native replication. If not, then rewriting (notice I did not say porting) their application to use MySQL Cluster may be the way to meet their realtime requirements. Yes, master-master replication can be useful (in an active-passive setup) for rapid failover and recovery if you take the appropriate precautions. It is not recommended for the newest administrators because recovery can become complicated. One should really understand basic, top-down master-slave replication before attempting to create a replication ring. Active-Active (dual master) configuration is even more complicated and is suited only for specific application purposes. This is definitely an advanced technique and requires careful planning and engineering to perform properly. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
I understand ..I am looking for load balancing - something that i do not have to worry about if one server goes down - the other server will be up and running by itself and i can bring back the other server later on when i have time. On Mon, Jun 11, 2012 at 10:36 AM, Ananda Kumar anan...@gmail.com wrote: when u say redudency. Do u just want replication like master-slave, which will be active-passive or Master-master which be active-active. master-slave, will work just a DR, when ur current master fails you can failover the slave, with NO LOAD balancing. Master-master allows load balancing. On Mon, Jun 11, 2012 at 7:56 PM, Joey L mjh2...@gmail.com wrote: I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
That's not a description of 'load balancing'; it is a high availability solution you're looking for. On Mon, Jun 11, 2012 at 4:43 PM, Joey L mjh2...@gmail.com wrote: I understand ..I am looking for load balancing - something that i do not have to worry about if one server goes down - the other server will be up and running by itself and i can bring back the other server later on when i have time. On Mon, Jun 11, 2012 at 10:36 AM, Ananda Kumar anan...@gmail.com wrote: when u say redudency. Do u just want replication like master-slave, which will be active-passive or Master-master which be active-active. master-slave, will work just a DR, when ur current master fails you can failover the slave, with NO LOAD balancing. Master-master allows load balancing. On Mon, Jun 11, 2012 at 7:56 PM, Joey L mjh2...@gmail.com wrote: I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
You listed a lot of things - but no solution - i am looking for master - master configuration. Any tools you have used ? Anything concrete you can offer? thanks On Mon, Jun 11, 2012 at 11:39 AM, Shawn Green shawn.l.gr...@oracle.com wrote: On 6/11/2012 10:36 AM, Ananda Kumar wrote: ... Master-master allows load balancing. Why do people keep replication rings as if they are the best possible configuration? A master-slave relationship also permits load balancing and is easier to maintain and recover in the event of a node failure. Any MySQL replication topology cannot, in a generic sense, allow load balancing. Careful precautions must be maintained in your load balancer and in your application code to avoid data collisions (adding or modifying the same PK on the same table on two or more nodes at the same time). For continuous uptime, you do need redundancy. For the 'limited downtime' scenario that the customer proposed, this includes geographical redundancy as well as physical duplication and should also include sharding his data so that the loss of one sharded set (due to extreme disaster or comms failure) does not knock all of his users offline at the same time. realtime data is also a variable definition. For hydrologic monitoring data 'realtime' could mean within the last 15 minutes. For some applications (such as telecommunication) 'realtime' is measured in microseconds. It may be that the customer's requirements can tolerate a normal variance in duplication time provided by MySQL's native replication. If not, then rewriting (notice I did not say porting) their application to use MySQL Cluster may be the way to meet their realtime requirements. Yes, master-master replication can be useful (in an active-passive setup) for rapid failover and recovery if you take the appropriate precautions. It is not recommended for the newest administrators because recovery can become complicated. One should really understand basic, top-down master-slave replication before attempting to create a replication ring. Active-Active (dual master) configuration is even more complicated and is suited only for specific application purposes. This is definitely an advanced technique and requires careful planning and engineering to perform properly. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Sorry new to this part - but I am looking for both. I have setup similar configuration using other technologies. I was asking the group for recommendations - concrete ones ? Can you offer up any ? On Mon, Jun 11, 2012 at 11:54 AM, Andrew Moore eroomy...@gmail.com wrote: That's not a description of 'load balancing'; it is a high availability solution you're looking for. On Mon, Jun 11, 2012 at 4:43 PM, Joey L mjh2...@gmail.com wrote: I understand ..I am looking for load balancing - something that i do not have to worry about if one server goes down - the other server will be up and running by itself and i can bring back the other server later on when i have time. On Mon, Jun 11, 2012 at 10:36 AM, Ananda Kumar anan...@gmail.com wrote: when u say redudency. Do u just want replication like master-slave, which will be active-passive or Master-master which be active-active. master-slave, will work just a DR, when ur current master fails you can failover the slave, with NO LOAD balancing. Master-master allows load balancing. On Mon, Jun 11, 2012 at 7:56 PM, Joey L mjh2...@gmail.com wrote: I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
On 6/11/2012 12:02 PM, Joey L wrote: You listed a lot of things - but no solution - i am looking for master - master configuration. Any tools you have used ? Anything concrete you can offer? There is no one-size-fits-all approach to the problem you are attempting to solve. However, there are some documented solutions in the manual for you to review Let's start with the basics: http://dev.mysql.com/doc/refman/5.5/en/faqs-replication.html http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-scaleout.html http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html http://dev.mysql.com/doc/refman/5.5/en/replication-problems.html We also document some more exotic configurations: http://dev.mysql.com/doc/refman/5.5/en/ha-overview.html Which one you opt for depends on many factors that we cannot determine for you. Warmest wishes, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Not forgetting Pythian http://www.pythian.com, Baron ;) On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz ba...@xaprb.com wrote: Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Yeah -- that was an unintentional omission. There are solo consultants like Ronald Bradford too. On Mon, Jun 11, 2012 at 3:14 PM, Andrew Moore eroomy...@gmail.com wrote: Not forgetting Pythian, Baron ;) On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz ba...@xaprb.com wrote: Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design advice for hotel availability program
Verify the regulation that a Hotel should submit, read the part of the occupation reports, information gathering, ask management for their currents report... Remember the data output should came from the data stored... On Mon, Feb 20, 2012 at 4:42 PM, Chaim Rieger li...@up-south.com wrote: On 2/17/2012 4:01 AM, Tompkins Neil wrote: Hi, I'm in the process of starting to design a hotel booking system that will eventually consist of over 10,000 hotels all with different room types, rates and availability for different dates. My question is does anyone have any experience with regards the best way to store the daily rates. Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Thanks for any help and input. There is already more than one package out there that does what you are trying to do, so why not take a look at how they do the db stuff. also keep in mind that you are working two reservations at once for each entry a guest requests an asett (the room) a guest then promises to pay a certain amount for an asset an asset can be tied to a variety or prices, but only one at a time, unless certain conditions are met. so no its not easy to do, but it has been done. http://drupal.org/project/uc_**hotel http://drupal.org/project/uc_hotel (ps. no i do not do drupal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design advice for hotel availability program
Where are your domain experts? You *are* consulting with them, no? If you don't know the answers, and don't have access to domain experts to help you, I would design for the most general case, and factor out exceptions as they prove to be so. Pre-optimization for exceptions almost always turns out to be a bad choice. From: Tompkins Neil neil.tompk...@googlemail.com Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Everything we think we know about the world is a model... None of these is or ever will be the real world. -- Donella H. Meadows Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design advice for hotel availability program
Hi,
Nobody answers because this is a very wide question about software
engineering,
Trust me, It seems like a simple question but it is not.
The only advice I can give you is to try to imagine all possible
scenarios/use-cases before starting the design.
Cheers
Claudio
2012/2/20 Jan Steinman j...@bytesmiths.com
Where are your domain experts? You *are* consulting with them, no?
If you don't know the answers, and don't have access to domain experts to
help you, I would design for the most general case, and factor out
exceptions as they prove to be so. Pre-optimization for exceptions almost
always turns out to be a bad choice.
From: Tompkins Neil neil.tompk...@googlemail.com
Am
I best using the following pattern
(1) Default rates/rooms stored in a generic table
(2) Any exceptions/changes/closed days to the daily rates are store in
a separate table.
(3) Any special offer exceptions are stored as a rule
All, should I consider that for any hotel, for any room, for any day I
have
a record in a huge single table ???
Everything we think we know about the world is a model... None of these is
or ever will be the real world. -- Donella H. Meadows
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: Design advice for hotel availability program
Hi I am trying to get some background information with regards the design. I have a fair idea but would like advice from anyone that has previously worked on similar projects. On 20 Feb 2012, at 17:22, Jan Steinman j...@bytesmiths.com wrote: Where are your domain experts? You *are* consulting with them, no? If you don't know the answers, and don't have access to domain experts to help you, I would design for the most general case, and factor out exceptions as they prove to be so. Pre-optimization for exceptions almost always turns out to be a bad choice. From: Tompkins Neil neil.tompk...@googlemail.com Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Everything we think we know about the world is a model... None of these is or ever will be the real world. -- Donella H. Meadows Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design advice for hotel availability program
On 2/17/2012 4:01 AM, Tompkins Neil wrote: Hi, I'm in the process of starting to design a hotel booking system that will eventually consist of over 10,000 hotels all with different room types, rates and availability for different dates. My question is does anyone have any experience with regards the best way to store the daily rates. Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Thanks for any help and input. There is already more than one package out there that does what you are trying to do, so why not take a look at how they do the db stuff. also keep in mind that you are working two reservations at once for each entry a guest requests an asett (the room) a guest then promises to pay a certain amount for an asset an asset can be tied to a variety or prices, but only one at a time, unless certain conditions are met. so no its not easy to do, but it has been done. http://drupal.org/project/uc_hotel (ps. no i do not do drupal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Design advice for hotel availability program
Hi, I'm in the process of starting to design a hotel booking system that will eventually consist of over 10,000 hotels all with different room types, rates and availability for different dates. My question is does anyone have any experience with regards the best way to store the daily rates. Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Thanks for any help and input. Best, Neil
How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...
I'm running into some deadlocks issues. I have this structure accounting |---movements To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardware :(, so I need to find a software solution My approach was to create a balance field inside accounting, but I'm running into deadlocks, because accounting has a tree structure based upon accounting.id and accounting.parentid. Btw, I can't use triggers or store procedure due insert management reason here. Out of discussion. So, to have all the accounts in balance, I do a drill down to get all paretns, grandparent from an account... So I think, I have race conditions and multiversioning issues. I read about innodb locking and decided to use lock in share mode... But I'm runing with some deadlocks: TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id 140061201196816 starting index read mysql tables in use 1, locked 1 LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s) MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264994 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc ;; 2: len 7; hex 3b0b18; asc ; ;; 3: len 4; hex 0194; asc ;; 4: len 1; hex 02; asc ;; 5: len 20; hex 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8fb56d; asc m;; 11: len 1; hex 00; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7086e1; asc ;; *** (2) TRANSACTION: TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id 140061201999632 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4360 lock struct(s), heap size 456688, 579126 row lock(s) MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264995 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc ;; 2: len 7; hex 3c1b0a; asc ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8faa7d; asc };; 11: len 1; hex 01; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 78f72f3efa; asc / ;; Any clues about how to avoid this?
Re: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...
The smoothest way to avoid deadlocks, is to ensure that all your sessions lock their tables in exactly the same order. From your explanation, that might not be as easy as one would expect, though. If you can't create triggers, is it acceptable to have delayed updates on the totals? Your idea was good, but the classic way to go about that is materialized views - in this case also known as aggregate tables. You simply run a cron job every hour or whatever that drops and recreates a (temporary) table holding all the summaries you'll ever need. Sure, the build job is heavy - but it only runs once every so often; and building an aggregate of ten subset is not as heavy as running the aggregate query for every subset separately, so there's a definite win. If you can live with 24h old data, just run the aggregate build somewhere during the slow hours. - Original Message - From: Andrés Tello mr.crip...@gmail.com To: Mailing-List mysql mysql@lists.mysql.com Sent: Wednesday, 20 April, 2011 9:15:09 PM Subject: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination... I'm running into some deadlocks issues. I have this structure accounting To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardware :(, so I need to find a software solution My approach was to create a balance field inside accounting, but I'm running into deadlocks, because accounting has a tree structure based upon accounting.id and accounting.parentid. Btw, I can't use triggers or store procedure due insert management reason here. Out of discussion. So, to have all the accounts in balance, I do a drill down to get all paretns, grandparent from an account... So I think, I have race conditions and multiversioning issues. I read about innodb locking and decided to use lock in share mode... But I'm runing with some deadlocks: TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id 140061201196816 starting index read mysql tables in use 1, locked 1 LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s) MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264994 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc ;; 2: len 7; hex 3b0b18; asc ; ;; 3: len 4; hex 0194; asc ;; 4: len 1; hex 02; asc ;; 5: len 20; hex 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8fb56d; asc m;; 11: len 1; hex 00; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7086e1; asc ;; *** (2) TRANSACTION: TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id 140061201999632 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4360 lock struct(s), heap size 456688, 579126 row lock(s) MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264995 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc ;; 2: len 7; hex 3c1b0a; asc ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8faa7d; asc };; 11: len 1; hex 01; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 78f72f3efa; asc / ;; Any clues about how to avoid this? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
On 10/8/2010 3:31 PM, Neil Tompkins wrote: Hi Shawn Thanks for your response. In your experience do you think I should still retain the data used to generate the computed totals ? Or just compute the totals and disregard the data used ? In my experience, the details matter. Also in my experience, as soon as you designate some bit of data as useless it will somehow become critical that you find it again. You should probably keep that lowest-level detail data somewhere safe even if you never plan to need it for direct statistics reporting after you use it to generate the first level or two of time-based summary tables. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
Hi Neil, On 10/5/2010 5:07 AM, Tompkins Neil wrote: Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Many databases designed for rapid, time-based reporting do exactly as you propose: build a table just to hold the aggregate of a time-interval of values. Here's a rough example. Let's say that you run a web site and you want to track your traffic levels. Every second you may have thousands of hits, every hour hundreds of thousands of hits, and by the end of the week you may have hundreds of millions of individual data points to report on. To compute monthly stats, you are looking at a huge volume (billions) of data points unless you start aggregating. Lets say you build tables like: stats_hour, stats_day, stats_week, and stats_month. Every hour, you would take the last hour's worth of traffic and condense those values into the stats_hour table. At the end of the day, you take the previous 24 entries from stats_hour and compute a stats_day entry. Each level up aggregates the data from the level below. Does that give you an idea about how other people may have solved a similar problem? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
Hi Shawn Thanks for your response. In your experience do you think I should still retain the data used to generate the computed totals ? Or just compute the totals and disregard the data used ? Regards Neil On 8 Oct 2010, at 19:46, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hi Neil, On 10/5/2010 5:07 AM, Tompkins Neil wrote: Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Many databases designed for rapid, time-based reporting do exactly as you propose: build a table just to hold the aggregate of a time- interval of values. Here's a rough example. Let's say that you run a web site and you want to track your traffic levels. Every second you may have thousands of hits, every hour hundreds of thousands of hits, and by the end of the week you may have hundreds of millions of individual data points to report on. To compute monthly stats, you are looking at a huge volume (billions) of data points unless you start aggregating. Lets say you build tables like: stats_hour, stats_day, stats_week, and stats_month. Every hour, you would take the last hour's worth of traffic and condense those values into the stats_hour table. At the end of the day, you take the previous 24 entries from stats_hour and compute a stats_day entry. Each level up aggregates the data from the level below. Does that give you an idea about how other people may have solved a similar problem? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Design advice
Wonder if anyone can help me ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 5, 2010 at 10:07 AM Subject: Design advice To: [MySQL] mysql@lists.mysql.com Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Thanks, Neil
Design advice
Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Thanks, Neil
RE: Table advice.
Another trick is to reverse the domain when you store it. For example instead of storing www.facebook.com, store it as com.facebook.www. That way you write a query like: WHERE domain LIKE 'com.facebook%' This will use the index, since you're not using a wildcard at the beginning of the string, and the results will include all subdomains for the given domain you're looking for. Regards, Gavin Towey -Original Message- From: Alexander Kolesen [mailto:kolese...@mail.by] Sent: Saturday, August 01, 2009 11:10 AM To: mysql@lists.mysql.com Subject: Re: Table advice. Hello. Your query performs a full table scan, because if you match text with '%...' wildcard, MySQL can't using index. Try to use external full-text search engines like Sphinx (http://www.sphinxsearch.com/) or Lucene (http://lucene.apache.org). I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn VARCHAR(255), domainVARCHAR(63), tld VARCHAR(63), actionVARCHAR(4), request TEXT, referrer TEXT, clientVARCHAR(255), INDEX eid (eid), INDEX timestamp (timestamp), INDEX ip (ip), INDEX fqdn (fqdn), INDEX domain (domain), INDEX tld (tld) ); The is no real logic behind the indexes, the table was hobbled together looking at examples. Currently I am trying queries on about 300 million records and the results are pretty crappy. for example, a query like this: select domain,count(domain) as count from event where domain like '%facebook%' group by domain order by count desc; takes about 5 minutes to complete. Most of the queries will be like that above but probably with additional filters like date constraints or IP constraints or a mixture of both. I can also see searches through the requests for filetypes etc. Any suggestions or comments would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kolese...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table advice.
I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn VARCHAR(255), domainVARCHAR(63), tld VARCHAR(63), actionVARCHAR(4), request TEXT, referrer TEXT, clientVARCHAR(255), INDEX eid (eid), INDEX timestamp (timestamp), INDEX ip (ip), INDEX fqdn (fqdn), INDEX domain (domain), INDEX tld (tld) ); The is no real logic behind the indexes, the table was hobbled together looking at examples. Currently I am trying queries on about 300 million records and the results are pretty crappy. for example, a query like this: select domain,count(domain) as count from event where domain like '%facebook%' group by domain order by count desc; takes about 5 minutes to complete. Most of the queries will be like that above but probably with additional filters like date constraints or IP constraints or a mixture of both. I can also see searches through the requests for filetypes etc. Any suggestions or comments would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table advice.
Hello. Your query performs a full table scan, because if you match text with '%...' wildcard, MySQL can't using index. Try to use external full-text search engines like Sphinx (http://www.sphinxsearch.com/) or Lucene (http://lucene.apache.org). I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn VARCHAR(255), domainVARCHAR(63), tld VARCHAR(63), actionVARCHAR(4), request TEXT, referrer TEXT, clientVARCHAR(255), INDEX eid (eid), INDEX timestamp (timestamp), INDEX ip (ip), INDEX fqdn (fqdn), INDEX domain (domain), INDEX tld (tld) ); The is no real logic behind the indexes, the table was hobbled together looking at examples. Currently I am trying queries on about 300 million records and the results are pretty crappy. for example, a query like this: select domain,count(domain) as count from event where domain like '%facebook%' group by domain order by count desc; takes about 5 minutes to complete. Most of the queries will be like that above but probably with additional filters like date constraints or IP constraints or a mixture of both. I can also see searches through the requests for filetypes etc. Any suggestions or comments would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kolese...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
I've been reading High Performance MySQL today and got some great tips from that which will help a lot. Yes it is a good book. I hope you have the 2nd edition. I do, I should have read this years ago (well.. the 1st edition then at least). So many caveats to using indexes. So why not have 2 tables: Cases_Active for the cases currently open, and Cases_Closed. Reporting across open and closed, but as you state I could be using UNION for this. Reporting is not expected to be fast any way. You can use a Memory table but they don't work with Merge tables. I would only consider this if the table gets updates every second or two and that flushes the cache. I'm not sure how much money you're willing to throw at this project, but I know of some additional hardware that can squeeze out more speed. Well.. I could just throw some more RAM at it. But ideally, I would have a sound setup first before considering mindlessly adding resources (however tempting it is). Thanks for your tips. I'll be looking further into splitting the tables. Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
mo...@fastmail.fm (mos) writes: At 08:06 PM 7/12/2009, Morten wrote: If you can get rid of the DateTime and switch to just Date it may speed up the indexes. While not as pretty it's more compact to convert timestamp values into an bigint. For example: seconds since epoch. If you know the ranges to put in the query then store them this way and thus save on some storage, and therefore improve performance. May be worth considering? ... These queries which involve easily indexable fields (status_id, assignee_id, company_id) and multiple conditions on different ranges are what's difficult. The table is about 2.500.000 records and grows at a daily rate of about 50.000 records (that number is growing though). Once an action has been closed, it gets status closed and is no longer of interest. 70% of the records in the table will be status closed. As mentioned if you are not interested in closed queries get rid of them. put them in another table. That reduces the number of rows and hence the query time. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need advice on a good setup for generic queries
Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
Morten, Perhaps you could also add how many rows are in the table, how many rows are added each day, what are the column types, and what do the search queries look like? Mike At 11:39 AM 7/12/2009, Morten wrote: Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
Mike, you're right - sorry. I've been reading High Performance MySQL today and got some great tips from that which will help a lot. I think the fundamental challenge now, is that the table contains a lot of timestamps, and querying against these involves multiple range queries which makes indexing hard. The actions table has the following columns (of relevance to the example): status_id assignee_id company_id created_at assigned_at opened_at updated_at verified_at due_at solved_at closed_at Queries could be: Show all actions which are assigned to Tom, were created in October and solved in November Show all open actions which were opened before August, do not have an assignee and were verified last week These queries which involve easily indexable fields (status_id, assignee_id, company_id) and multiple conditions on different ranges are what's difficult. The table is about 2.500.000 records and grows at a daily rate of about 50.000 records (that number is growing though). Once an action has been closed, it gets status closed and is no longer of interest. 70% of the records in the table will be status closed. I think what I'm looking for now, is some way to encode the different date values into a single column which can be indexed and the value of which gets calculated and updated by a background job. This will cost some precision, but I hope that can be done. Otherwise I'm back to considering alternative index/query-mechanisms. Does my problem make a little more sense now? Thanks. Morten Let's say I would like to see all actions that were created in october and solved in november. On Jul 12, 2009, at 3:54 PM, mos wrote: Morten, Perhaps you could also add how many rows are in the table, how many rows are added each day, what are the column types, and what do the search queries look like? Mike At 11:39 AM 7/12/2009, Morten wrote: Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my.li...@mac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
At 08:06 PM 7/12/2009, Morten wrote: Mike, you're right - sorry. I've been reading High Performance MySQL today and got some great tips from that which will help a lot. Yes it is a good book. I hope you have the 2nd edition. I think the fundamental challenge now, is that the table contains a lot of timestamps, and querying against these involves multiple range queries which makes indexing hard. If you can get rid of the DateTime and switch to just Date it may speed up the indexes. The actions table has the following columns (of relevance to the example): status_id assignee_id company_id created_at assigned_at opened_at updated_at verified_at due_at solved_at closed_at Queries could be: Show all actions which are assigned to Tom, were created in October and solved in November Show all open actions which were opened before August, do not have an assignee and were verified last week These queries which involve easily indexable fields (status_id, assignee_id, company_id) and multiple conditions on different ranges are what's difficult. The table is about 2.500.000 records and grows at a daily rate of about 50.000 records (that number is growing though). Once an action has been closed, it gets status closed and is no longer of interest. 70% of the records in the table will be status closed. So why not have 2 tables: Cases_Active for the cases currently open, and Cases_Closed. This will greatly reduce the number of rows in the table and the depth of the index tree. Your application will select the appropriate table if the question mentions Active or Closed cases. You can also define a Merge table Cases_All that will logically combine both tables so you could do a query on both closed and active cases if you need to, or just do a Union on the two tables via 2 separate queries. You didn't mention how many queries per second you need to handle. A couple hundred queries per second can be handled by MyISAM because it has excellent query cache that can be tweaked. You can also load the indexes into memory if needed. The main problem as you mentioned is trying to index all of the date fields. I don't think this is necessary. I noticed in 5.1 MySQL (because it was rushed out the door) does not always use the correct index and I often have to force it to use the proper index with Force Index. So you need to use the Explain on your slow queries (make sure you log them) and find out which index they are using. You can use a Memory table but they don't work with Merge tables. I would only consider this if the table gets updates every second or two and that flushes the cache. I'm not sure how much money you're willing to throw at this project, but I know of some additional hardware that can squeeze out more speed. I think what I'm looking for now, is some way to encode the different date values into a single column which can be indexed and the value of which gets calculated and updated by a background job. This will cost some precision, but I hope that can be done. Otherwise I'm back to considering alternative index/query-mechanisms. Does my problem make a little more sense now? Thanks. Yup! :-) Mike Morten Let's say I would like to see all actions that were created in october and solved in november. On Jul 12, 2009, at 3:54 PM, mos wrote: Morten, Perhaps you could also add how many rows are in the table, how many rows are added each day, what are the column types, and what do the search queries look like? Mike At 11:39 AM 7/12/2009, Morten wrote: Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To
Configuration and improvement advice.
I have recently become responsible for a LAMP site which has a decent MySQL install (v5.0.24a). The database is around 40GB with a single master to single slave replication scheme, although all activity goes to the master at this time, with the exception of backups which are taken from the slave. I have several tables which are fairly large, one has 120 million records, and I need to migrate these tables to InnoDB from MyISAM to reduce the number of table locks that occur on a daily basis which bring down the site's performance. What is the best way to perform this migration? Should I simply take an outage and alter table to set the engine type to InnoDB, or should I rename the table, and select into a new table? What are the upper limits of MySQL performance in terms of data set size using MyISAM vs InnoDB? TIA, -- Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Configuration and improvement advice.
[EMAIL PROTECTED] (Josh Miller) writes: I have recently become responsible for a LAMP site which has a decent MySQL install (v5.0.24a). The database is around 40GB with a single master to single slave replication scheme, although all activity goes to the master at this time, with the exception of backups which are taken from the slave. I have several tables which are fairly large, one has 120 million records, and I need to migrate these tables to InnoDB from MyISAM to reduce the number of table locks that occur on a daily basis which bring down the site's performance. What is the best way to perform this migration? Should I simply take an outage and alter table to set the engine type to InnoDB, or should I rename the table, and select into a new table? It depends on this table usage. If you can, then the ideal situation might be to create the new table with a temporary name and fill it in the background, and finally update for any changed values during the process. This process might be time-consuming for 120,000,000 rows but may work. If you use replication beware of the delays that may arise from doing this in anything but small enough chunks. What are the upper limits of MySQL performance in terms of data set size using MyISAM vs InnoDB? Be careful: the InnoDB footprint of this table may be much larger than your existing MyISAM footprint. I've seen issues with this especially as you'll be needing to adjust the the innodb_buffer_pool_size and key_buffer values during this process. Thus you _may_ suffer a performance problem, not because of the engine change but because of the increased memory requirements. Consider also the use of innodb_file_per_table which makes the resulting files easier to manage. A different solution might be to make a new slave, convert the table(s) on the slave to InnoDB, finally promoting it to be the new master. You'd also need to rebuild your existing slave. This avoids downtime to the site except for the master switchover period. It also gives you time to tweak all values while doing the conversion from MyISAM to InnoDB. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
make sure to FLUSH PRIVILAGES; - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Monday, 14 July, 2008 10:59:35 AM Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary
Re: ***SPAM*** RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
grant all on *.* to root@'%.def.com'; /* The percent sign is your wildcard character. */ flush privileges; I don't think you need to flush privileges as of 5.0. I still do just to be sure. Curtis [EMAIL PROTECTED] wrote: Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED
Re: Looking for a more efficient way to achieve the same result - Found solution but would still like some advice :)
Richard a écrit : Richard a écrit : Hello, I'm in the process of programming a customer area with a list of subscriptions : Reference | Title | Type | Date of first subscription | Expires Each item in this list will have a link to it's details with will show a list like this : Subscribed on : date of first subscription Renewed on : date of first renewal Renewed on : date of second renewal Renewed on : date of third renewal Expires on : date when expires. At the moment I have got three mysql tables : -- 1) Products Reference | Title | Type 2) Subscriptions number(autoincrement) | reference | date_begin 3) subscriptions details number(autoincrement) | subscription_number | length(number of months) -- To get the first table I would : List subscriptions For each subscription get list of lengths which I would add together and then calculate expire date by : Total lengths + date_begin Which gives me the expire date. However all this seems alot of queries and resources for such a small list and as I have not started the programming yet and have not created the tables either, I thought I might ask your advice to see if you think this is the best way, or if you could think of a better way of achieving the same result. Maybe there is a way to get the expire date with mysql, or maybe I should rethink my tables? I hope that everything is clear, and thanks in advance :) Hi I might have found a better solution. I could have one table for the latest details and another containing the history. I would still have two tables, but only one query when a customer wishes to view his or her subscriptions and only one query when he or her views the detail. What would the best way to copy an entry from one table to another and then change the entry value ? What is the best way to do this using a minimum of queries? To copy an entry from one table to another do you have to read the value with one query and then insert the value to the other table with a second query or does mysql (5.0) have a function to copy data from one table to another. Thanks in advance. Hi me again, just to say I think I've found the solution : INSERT INTO TABLE2 SELECT * FROM TABLE1 Do I have to do two queries : INSERT INTO SUB_HISTORY SELECT * FROM SUB WHERE num = '$subnumber'; and UPDATE SUB SET end_date = '$newdate' WHERE num= '$subnumber'; Or is it possible to do both queries in one ? Thanks :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need advice on how to design tables for recurring events
On 10/10/07, Ramsey, Robert L [EMAIL PROTECTED] wrote: I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. (...) The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Well that doesn't seem to resource intensive to me, however it depends on what you are displaying later. Knowing the amount of times the class meet would be counting the Mondays (+Wednesdays+Fridays) between start_datetime and stop_datetime, but that shouldn't be too bad. What you should never do though is putting different values into the same field -- it defeats the purpose of a relational database. '1,3,5' is therefore a no-go... You ought to make a column for each day of the week and set it 0 for no class and 1 for class, or something along the lines of that. That will also make it insanely easy to retrieve all classes that meet on any given day: SELECT * FROM classes WHERE monday = 1 AND start_datetime = NOW() AND stop_datetime = NOW(); Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need advice on how to design tables for recurring events
I've been working with Drupal for some time, and there is a module that allows you to create event-based content with a repeating schedule. The schema that it uses may be of some help to you. CREATE TABLE IF NOT EXISTS event_repeat ( rid int(10) unsigned NOT NULL default '0', repeat_data longtext NOT NULL, repeat_RRULE longtext NOT NULL, repeat_COUNT_remaining int(4) NOT NULL default '-1', repeat_start int(10) unsigned NOT NULL default '0', repeat_end int(10) unsigned NOT NULL default '0', repeat_last_rendered int(10) unsigned NOT NULL default '0', PRIMARY KEY (rid) ); CREATE TABLE IF NOT EXISTS event ( rid int(10) unsigned NOT NULL default '0', nid int(10) unsigned NOT NULL default '0', repeat_edited int(2) unsigned NOT NULL default '0', UNIQUE KEY nid (nid), KEY rid (rid) ); CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( day_stamp char(15) NOT NULL default '', date_stamp int(10) unsigned NOT NULL default '0', day_of_week char(2) NOT NULL default '', day_in_month char(3) NOT NULL default '', day_in_month_R char(4) NOT NULL default '', month_day char(2) NOT NULL default '', month_day_R char(3) NOT NULL default '', month char(2) NOT NULL default '', year_day char(3) NOT NULL default '', year_day_R char(4) NOT NULL default '', week_number char(2) NOT NULL default '', week_number_R char(3) NOT NULL default '', PRIMARY KEY (date_stamp), KEY day_of_week (day_of_week), KEY day_in_month (day_in_month), KEY day_in_month_R (day_in_month_R), KEY month_day (month_day), KEY month_day_R (month_day_R), KEY month (month), KEY year_day (year_day), KEY year_day_R (year_day_R), KEY week_number (week_number), KEY week_number_R (week_number_R) ); HTH -Erich- Frederic Wenzel wrote: On 10/10/07, Ramsey, Robert L [EMAIL PROTECTED] wrote: I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. (...) The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Well that doesn't seem to resource intensive to me, however it depends on what you are displaying later. Knowing the amount of times the class meet would be counting the Mondays (+Wednesdays+Fridays) between start_datetime and stop_datetime, but that shouldn't be too bad. What you should never do though is putting different values into the same field -- it defeats the purpose of a relational database. '1,3,5' is therefore a no-go... You ought to make a column for each day of the week and set it 0 for no class and 1 for class, or something along the lines of that. That will also make it insanely easy to retrieve all classes that meet on any given day: SELECT * FROM classes WHERE monday = 1 AND start_datetime = NOW() AND stop_datetime = NOW(); Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need advice on how to design tables for recurring events
This and Frederic Wenzel's suggestions were exactly what I was looking for. Thanks! Bob -Original Message- From: Erich C. Beyrent [mailto:[EMAIL PROTECTED] Sent: Thursday, October 11, 2007 6:10 AM To: mysql@lists.mysql.com Subject: Re: need advice on how to design tables for recurring events I've been working with Drupal for some time, and there is a module that allows you to create event-based content with a repeating schedule. The schema that it uses may be of some help to you. CREATE TABLE IF NOT EXISTS event_repeat ( rid int(10) unsigned NOT NULL default '0', repeat_data longtext NOT NULL, repeat_RRULE longtext NOT NULL, repeat_COUNT_remaining int(4) NOT NULL default '-1', repeat_start int(10) unsigned NOT NULL default '0', repeat_end int(10) unsigned NOT NULL default '0', repeat_last_rendered int(10) unsigned NOT NULL default '0', PRIMARY KEY (rid) ); CREATE TABLE IF NOT EXISTS event ( rid int(10) unsigned NOT NULL default '0', nid int(10) unsigned NOT NULL default '0', repeat_edited int(2) unsigned NOT NULL default '0', UNIQUE KEY nid (nid), KEY rid (rid) ); CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( day_stamp char(15) NOT NULL default '', date_stamp int(10) unsigned NOT NULL default '0', day_of_week char(2) NOT NULL default '', day_in_month char(3) NOT NULL default '', day_in_month_R char(4) NOT NULL default '', month_day char(2) NOT NULL default '', month_day_R char(3) NOT NULL default '', month char(2) NOT NULL default '', year_day char(3) NOT NULL default '', year_day_R char(4) NOT NULL default '', week_number char(2) NOT NULL default '', week_number_R char(3) NOT NULL default '', PRIMARY KEY (date_stamp), KEY day_of_week (day_of_week), KEY day_in_month (day_in_month), KEY day_in_month_R (day_in_month_R), KEY month_day (month_day), KEY month_day_R (month_day_R), KEY month (month), KEY year_day (year_day), KEY year_day_R (year_day_R), KEY week_number (week_number), KEY week_number_R (week_number_R) ); HTH -Erich- Frederic Wenzel wrote: On 10/10/07, Ramsey, Robert L [EMAIL PROTECTED] wrote: I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. (...) The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Well that doesn't seem to resource intensive to me, however it depends on what you are displaying later. Knowing the amount of times the class meet would be counting the Mondays (+Wednesdays+Fridays) between start_datetime and stop_datetime, but that shouldn't be too bad. What you should never do though is putting different values into the same field -- it defeats the purpose of a relational database. '1,3,5' is therefore a no-go... You ought to make a column for each day of the week and set it 0 for no class and 1 for class, or something along the lines of that. That will also make it insanely easy to retrieve all classes that meet on any given day: SELECT * FROM classes WHERE monday = 1 AND start_datetime = NOW() AND stop_datetime = NOW(); Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=robert- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need advice on how to design tables for recurring events
Hi, I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. For example, let's say the following: Class-A Mon,Wed,Fri 8-9 from August 20th-December 5th Class-B Mon,Wed,Fri 9:10-10:10 from August 20th-December 5th Class-C Tues,Thurs 10:30-12 from August 20th-December 5th Class-D Wed,Thurs,Friday 1-2 from August 20th-December 5th Class-E Tues,Thurs 8-9 from September 15th-January 15th Event-F Thursday, November 8th, 12:30-3 I could have 1 table that had Name, Start_time, Stop_time and have one entry for every single class and event. That is a simple way to design the table, but it seems like there's lots of needless duplication. There are going to be 50+ entries for Class-A, one for each day it meets. It seems like there ought to be a way to use something like the ical recurring event format to set things up so that there's 1 entry for each event and that includes the recurrence. The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Any suggestions for the best way to handle this? Thanks, Bob ~~~ Many things went on at Unseen University and, regrettably, teaching had to be one of them. The faculty had long ago confronted this fact and had perfected various devices for avoiding it. But this was perfectly all right because, to be fair, so had the students. Terry Pratchett Interesting Times ~~~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
This one time, at band camp, [EMAIL PROTECTED] wrote: I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. According to your method of storing binary data in BLOB fields, rather than LONGBLOBs you would have over 11,000 rows for a single 700MB iso. I am not sure how MySQL handles blobs internally but I would assume it is like most other databases and adds some sort of delimiter to the data and has to seek to it to distinguish columns. This means a 700Mb file stored in 64k chunks would need over 11,000 file seeks per row read. LONGBLOBs are the way forward here I feel. A single table containing the relevant metadata along with the binary data. There is no need for any normalization as this is a one-to-one relationship. having over 11,000 rows for a single file I dunno, I would like to see some benchmarks on this. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
I would love to see an implementation with 1 row for large data that works well. The main issues I had were that mysql has a default max packet size limit (I think it used to be like 16MB (mysql 3.23) - 1GB Mysql 4 - Not sure v5. Alot of people don't have control over those settings in their environment. If you try to do a single insert/query larger than that, the query would fail. Also it was causing the webserver to buffer a large amount of data. Here's some numbers to tickle your fancy.. I downlaoded a file from the ftp gateway to a storage implementation I've done (production). FTP CLIENT -- NETWORK FRONTEND VLAN (100FDX) -- FTP SERVER (JAVA) FRONTEND -- NETWORK BACKEND VLAN (100FDX) -- MYSQL STORAGE NODE I grabbed an approximately 230MB file from a different box (on the same frontend vlan) in 40 seconds, at a speed of about 5800K/sec .. Also I logged into the storage node to see how mysql was doing: 5179 mysql 15 0 12528 4644 2108 S10.8 0.5 0:04 mysqld It was bouncing between 9% and 19% during the transfer and the machine is a 2 X P3/933. There's very little chance a real internet user can download at that type of speed, so a single mysql storage node can handle a large amount of concurrent users/activity. [EMAIL PROTECTED]:~$ ftp ftp.dbfs Connected to ftp.dbfs 220 DBFS. Name (ftp.dbfs): XX 331 User name okay, need password for XX Password: 230 User logged in, proceed Remote system type is UNIX. ftp bin 200 Command TYPE okay ftp cd XX/filez/apps 200 Directory changed to /XXx/filez/apps ftp get Visio2K3.exe local: Visio2K3.exe remote: Visio2K3.exe 200 Command PORT okay 150 File status okay; about to open data connection 226 Closing data connection 235075910 bytes received in 39.9 secs (5.8e+03 Kbytes/sec) ftp Rules for testing: - it must be a double jump (as in real world implemenation) - you must push the data between the webserver and mysql server over a tcp/ip network - you must download the data from the webserver from another box via tcp/ip (no loopback/local socket connections - does not clearly represent a real scalable solution) Looking forward to some numbers. On Wed, 25 Apr 2007, Kevin Waterson wrote: This one time, at band camp, [EMAIL PROTECTED] wrote: I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. According to your method of storing binary data in BLOB fields, rather than LONGBLOBs you would have over 11,000 rows for a single 700MB iso. I am not sure how MySQL handles blobs internally but I would assume it is like most other databases and adds some sort of delimiter to the data and has to seek to it to distinguish columns. This means a 700Mb file stored in 64k chunks would need over 11,000 file seeks per row read. LONGBLOBs are the way forward here I feel. A single table containing the relevant metadata along with the binary data. There is no need for any normalization as this is a one-to-one relationship. having over 11,000 rows for a single file I dunno, I would like to see some benchmarks on this. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- 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: advice for blob tables?
I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. On Sat, 21 Apr 2007, Kevin Waterson wrote: This one time, at band camp, Michael Higgins [EMAIL PROTECTED] wrote: So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- 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: advice for blob tables?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. Just wanted to thank you all for your input thus far. I'm proceeding with caution, but with heightened confidence that I'll be moving forward with a bit of a clue. Cheers, -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: advice for blob tables?
-Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, April 20, 2007 3:16 PM To: Michael Higgins Cc: mysql@lists.mysql.com Subject: Re: advice for blob tables? At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. My company offers an archive service for our customers, and we do exactly this. The metadata is stored in a known location, include and index to a table which points to the database/table where the blob is. One such pointer is marked active so it receives all new documents. This allows us to roll over the databases at a certain size, or move them around as needed. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Hi Michael, You are not the only one with this problem. We work in the print/ publishing sector and have many customers that need this type of functionality as well. So this is not going to help you right away, but we have consulted with MySQL and are starting an initiative for the implementation of BLOB streaming for MySQL. In fact, the first announcement will go out on Monday, but you can already checkout some of the details of our plans at a web-site we have setup for the new project: http://www.blobstreaming.org I will also be holding a BoF at the MySQL Users Conference to discuss the project (http://www.mysqlconf.com/cs/mysqluc2007/view/e_sess/14125). We are hoping to get as many people from the community involved as possible (both potential users and developers). So if you are there, please join us! Best regards, Paul Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
advice for blob tables?
Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Michael, here's what I can tell you - Some people will advise you against storing documents in tables - claim it makes the data harder to serve up, etc. I agree it's quite simple to set up a folder structure with your files on the filesystem and point Apache or IIS to it while tracking metadata in MySQL, but there may well be other destinations/uses for your documents where actually having them in BLOBs would be quite handy. There's no technical reason you can't do it, obviously, and I worked for several years at a midsize newspaper where we stored literally everything in gigantic Sybase databases. Story, ad, page layouts, postscript graphics files, etc. Everything. And by and large it worked quite well. Nearly 1TB by the time I left, and a colleague at another newspaper had near 3TB. A big plus was the centralized workflow and tracking it allowed, but that will depend largely on the quality of the application software you have. At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. You could do this with the MERGE engine in MySQL; that has MyISAM tables underneath, with a view of sorts that presents all the underlying tables as one. You could also do it by having multiple DocumentTable001 tables structured identically, with another table to track the document tables as well as the current insert path. This is obviously more complex but doable. MyISAM is not transactional; InnoDB is, but doesn't offer MERGE. InnoDB can be configured to store one file per table in the latest versions of MySQL, and I'd recommend you go that route. Having the data split across multiple table files (in MyISAM or InnoDB) will allow you to check, optimize, and on bad days recover, your data in a more incremental fashion than a single large table. It would also potentially allow you to distribute the data across multiple physical storage devices for improved speed - and while that may not be a concern up front, some day it likely will be if you intend to store things for long. You could even distribute data across multiple database servers or clusters if you structured it properly. You could also take advantage of MySQL's compressed table type for archival data, which would save disk space and potentially improve read speed if your data compresses well. Anyway, hope this helps. Let me know if I can answer any other questions about such a setup. Dan On 4/20/07, Michael Higgins [EMAIL PROTECTED] wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Here's a good php implementation, you can implement the concept in any language you like: http://www.dreamwerx.net/phpforum/?id=1 On Fri, 20 Apr 2007, Michael Higgins wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- 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: advice for blob tables?
This one time, at band camp, Michael Higgins [EMAIL PROTECTED] wrote: So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log table advice
Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Thanks, Yong. Yong Lee Developer [EMAIL PROTECTED] http://www.eqo.com/ direct: +1.604.273.8173 x113 mobile:+1.604.418.4470 fax: +1.604.273.8172 web:www.EQO.com http://www.eqo.com/ EQO ID: yonglee
Re: log table advice
Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Well, just a few thoughts... - with MyISAM, delayed insert and REPLACE proved to be very useful for me - for a very intensive logging application (1000 hits per second) I found it better to keep the hits in textfiles (on ramdisk), and periodically (in every minute or so) process them and feed them to MySQL, using a bunch of speedup techniques OTOH I am talking about preprocessed logfiles. Storing logs as-is in a database seems an overkill for me. I would just use standard textfiles with regular bzipping for that. - Fagzal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log table advice
On 2/6/07, Yong Lee [EMAIL PROTECTED] wrote: Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Use of the ARCHIVE engine in conjunction with a partitioning scheme works wonders for logging. -- -jp If at first you don't succeed, you are obviously not Chuck Norris.
Advice on multilingual databases?
I'd like some advice on setting up databases that contain entries for the same item in more than one language. For instance, here's what I currently do for a table that contains the same topics translated into English and Arabic: CREATE TABLE `TOPIC` ( `TopicID` int(11) NOT NULL auto_increment, `Topic-en` text NOT NULL, `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL, `ParentTopicID` int(11) NOT NULL default '0', PRIMARY KEY (`TopicID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based hierarchies' AUTO_INCREMENT=76 ; In this table, 'Topic-ar' is the Arabic translation of the English 'Topic-en.' If this were required to also be in Spanish and French, I'd add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above. I'm wondering if there are any other ways to store and access multilingual data. Can anyone suggest other ways they've dealt with this task, and the pros and cons of their approach compared to mine? Thank you in advance for your advice and suggestions. -Kevin Kevin Zembower Internet Services Group manager Center for Communication Programs Bloomberg School of Public Health Johns Hopkins University 111 Market Place, Suite 310 Baltimore, Maryland 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice on multilingual databases?
Here is my suggestion, but like every other thing I post here I urge you to take it with a grain of salt. Set up the following tables (described in rough terms, not in SQL): topic_index topic_id autonumber ... Whatever else you need to keep track of that identifies a topic topic_detail topic_id long integer primary index topic_language indexed (perhaps) parent_topic_id indexed topic_language_text (utf-8, collate utf8_unicode_ci) ... Whatever else you need that is specific to this language / topic combo Now, whenever you add a topic you make an entry for a new topic, you make a record in topic_index that identifies the topic uniquely. Make corresponding entries in topic_detail using the topic_id that was just assigned to topic_index's topic_id: there will be one such record for each language. Now you can locate the topic itself in the topic_index table and quickly pull all of the corresponding records for the different languages from topic_detail. You can also easily find all topics that have entries in a particular language, or that do not have entries in a particular language, etc. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Zembower, Kevin [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 9:36 AM To: mysql@lists.mysql.com Subject: Advice on multilingual databases? I'd like some advice on setting up databases that contain entries for the same item in more than one language. For instance, here's what I currently do for a table that contains the same topics translated into English and Arabic: CREATE TABLE `TOPIC` ( `TopicID` int(11) NOT NULL auto_increment, `Topic-en` text NOT NULL, `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL, `ParentTopicID` int(11) NOT NULL default '0', PRIMARY KEY (`TopicID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based hierarchies' AUTO_INCREMENT=76 ; In this table, 'Topic-ar' is the Arabic translation of the English 'Topic-en.' If this were required to also be in Spanish and French, I'd add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above. I'm wondering if there are any other ways to store and access multilingual data. Can anyone suggest other ways they've dealt with this task, and the pros and cons of their approach compared to mine? Thank you in advance for your advice and suggestions. -Kevin Kevin Zembower Internet Services Group manager Center for Communication Programs Bloomberg School of Public Health Johns Hopkins University 111 Market Place, Suite 310 Baltimore, Maryland 21202 410-659-6139 -- 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]
MySQL tuning advice
Hello, I am looking for some advice regarding the tuning of a dedicated MySQL database server. The machine in questions is a Dell 2850 with dual Xeon procs and 4GB of memory running FreeBSD 6.1. Its sole purpose in life is to run the MySQL 5.0.x database engine serving out databases to multiple Apache servers. I started with the my-large.cnf file distributed with the MySQL port on FreeBSD 6.1. I made the following changes: set max_allowed_packet = 2M set-variable=max_connections=500 Now the questions: 1. What would be the recommended max_user_connections for the Dell 2850? 2. What is the practical maximum value that max_user_connections could be set to? I have been looking around for documentation regarding a max value for max_user connections and found the following: http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html What documentation do others use for tuning MySQL? My goal is to convert our PHP and Perl applications we run on the Apache web servers to use persistent database connections to the dedicated MySQL server. -- Greg Pelle System Administrator Domain-it! Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL tuning advice
I highly recommend Jeremy Zawodny's High Performance MySQL from O'Reilly. Dan On 9/12/06, Gregory T Pelle [EMAIL PROTECTED] wrote: Hello, I am looking for some advice regarding the tuning of a dedicated MySQL database server. The machine in questions is a Dell 2850 with dual Xeon procs and 4GB of memory running FreeBSD 6.1. Its sole purpose in life is to run the MySQL 5.0.x database engine serving out databases to multiple Apache servers. I started with the my-large.cnf file distributed with the MySQL port on FreeBSD 6.1. I made the following changes: set max_allowed_packet = 2M set-variable=max_connections=500 Now the questions: 1. What would be the recommended max_user_connections for the Dell 2850? 2. What is the practical maximum value that max_user_connections could be set to? I have been looking around for documentation regarding a max value for max_user connections and found the following: http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html What documentation do others use for tuning MySQL? My goal is to convert our PHP and Perl applications we run on the Apache web servers to use persistent database connections to the dedicated MySQL server. -- Greg Pelle System Administrator Domain-it! Inc. -- 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: MySQL tuning advice
Really you need to look at what your server is doing, the traffic patterns, then you can tune. There is a lot you can determine by just looking at the output of SHOW STATUS and SHOW VARIABLES. Threads_created number high and growing? Increase the thread_cache_size variable. Opened_tables number keeps growing? Increase the table_cache variable Those are very simple examples and there is a lot more you can tell, including how your indexes are doing. Read up on the status and variables, there is information on ratios and lots of other good stuff to help you change basic things. Careful with persistent connections and PHP, you can easily trip over your own connections. - Original Message - From: Gregory T Pelle [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, September 12, 2006 12:50 PM Subject: MySQL tuning advice Hello, I am looking for some advice regarding the tuning of a dedicated MySQL database server. The machine in questions is a Dell 2850 with dual Xeon procs and 4GB of memory running FreeBSD 6.1. Its sole purpose in life is to run the MySQL 5.0.x database engine serving out databases to multiple Apache servers. I started with the my-large.cnf file distributed with the MySQL port on FreeBSD 6.1. I made the following changes: set max_allowed_packet = 2M set-variable=max_connections=500 Now the questions: 1. What would be the recommended max_user_connections for the Dell 2850? 2. What is the practical maximum value that max_user_connections could be set to? I have been looking around for documentation regarding a max value for max_user connections and found the following: http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html What documentation do others use for tuning MySQL? My goal is to convert our PHP and Perl applications we run on the Apache web servers to use persistent database connections to the dedicated MySQL server. -- Greg Pelle System Administrator Domain-it! Inc. -- 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]
Advice on replication
Hello everybody!! I've a problem synchronizing a master mysql to a slave: When my master make a rotate in binlog my slave can follow up it and catch. What should I do ? -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz. http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication advice
I've 3 machines that I use with mysql. One is used for BI consulting and other two for operational consulting. Which one is indicated to be an master server ? Is there possible, with 2 nics in machines, use one for replication and other for receive clients consulting ? Thanks. -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Navegue com o Yahoo! Acesso Grátis, assista aos jogos do Brasil na Copa e ganhe prêmios de hora em hora! http://br.yahoo.com/artilheirodacopa/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for advice on how to store and query some data
--- Russell Horn [EMAIL PROTECTED] wrote: Hi, I'm storing data against a bunch of people and want to track how it changes. So, I have a person table where everyone has a person ID and a results table a bit like this: | personID | classification | date | | 1| 0 | 2005-11-10 | | 2| 3 | 2005-11-10 | | 3| 1 | 2005-11-10 | | 4| 0 | 2005-11-10 | | 1| 3 | 2005-12-01 | | 4| 2 | 2005-12-03 | | 1| 2 | 2005-12-23 | | 5| 1 | 2006-01-03 | | 2| 2 | 2006-12-03 | This lets me see how things change as a pattern, for example comparing a a SELECT classification WHERE DATE '2006-01-01' GROUP BY classification and comparing it to: SELECT classification WHERE DATE = '2006-01-01' AND DATE = '2006-01-31' GROUP BY classification But is there a way I can select every personID's most recently expressed preference? I hope this makes sense - in the table above, person 1 had a classification of 0 at 10th November, but this changed to 2 on 23rd Decembner. Can I write a query to select personID once together with their latest preference, or indeed their preference as expressed at a specific point in time? Thanks as ever for any suggestions. Russell. Yet another variety of the FAQ - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html In this case, what you are trying to maximize is the date per person_id. It should be fairly easy for you to convert the examples to match your situation. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for advice on how to store and query some data
Russell - This should basically work for selecting the most recently selected preference... SELECT DISTINCT personID, classification FROM results_table ORDER BY date DESC On 4/10/06, Russell Horn [EMAIL PROTECTED] wrote: Hi, I'm storing data against a bunch of people and want to track how it changes. So, I have a person table where everyone has a person ID and a results table a bit like this: | personID | classification | date | | 1| 0 | 2005-11-10 | | 2| 3 | 2005-11-10 | | 3| 1 | 2005-11-10 | | 4| 0 | 2005-11-10 | | 1| 3 | 2005-12-01 | | 4| 2 | 2005-12-03 | | 1| 2 | 2005-12-23 | | 5| 1 | 2006-01-03 | | 2| 2 | 2006-12-03 | This lets me see how things change as a pattern, for example comparing a a SELECT classification WHERE DATE '2006-01-01' GROUP BY classification and comparing it to: SELECT classification WHERE DATE = '2006-01-01' AND DATE = '2006-01-31' GROUP BY classification But is there a way I can select every personID's most recently expressed preference? I hope this makes sense - in the table above, person 1 had a classification of 0 at 10th November, but this changed to 2 on 23rd Decembner. Can I write a query to select personID once together with their latest preference, or indeed their preference as expressed at a specific point in time? Thanks as ever for any suggestions. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Looking for advice on how to store and query some data
Hi, I'm storing data against a bunch of people and want to track how it changes. So, I have a person table where everyone has a person ID and a results table a bit like this: | personID | classification | date | | 1| 0 | 2005-11-10 | | 2| 3 | 2005-11-10 | | 3| 1 | 2005-11-10 | | 4| 0 | 2005-11-10 | | 1| 3 | 2005-12-01 | | 4| 2 | 2005-12-03 | | 1| 2 | 2005-12-23 | | 5| 1 | 2006-01-03 | | 2| 2 | 2006-12-03 | This lets me see how things change as a pattern, for example comparing a a SELECT classification WHERE DATE '2006-01-01' GROUP BY classification and comparing it to: SELECT classification WHERE DATE = '2006-01-01' AND DATE = '2006-01-31' GROUP BY classification But is there a way I can select every personID's most recently expressed preference? I hope this makes sense - in the table above, person 1 had a classification of 0 at 10th November, but this changed to 2 on 23rd Decembner. Can I write a query to select personID once together with their latest preference, or indeed their preference as expressed at a specific point in time? Thanks as ever for any suggestions. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam
Let's try to characterize the load and sizing. If the posts are mostly text. 100 forums X 100,000 posts X 2,000 bytes per post = 20GB large but not huge We have people reading the posts. Even speed readers will take minutes to read the new posts. Maybe 1 in 10 - 100 readers will do a new post. A person writing a new post will take at least 5 minutes to type the 2000 bytes. So, while this site might have 100's to 1000's of concurrent users, the load profile on the database is much lower than a dynamic database driven web site where users are constantly searching/linking and the time spent on a specific page is seconds. I suspect if you watch some typical forum activity and build a crude database interaction model, you will find that even with 1000's of connected users the database server will need to run less than 100 relatively simple selects per second and many of them will be served by the query cache. This is a very rushed analysis with lots of assumptions, but if close then I think you are looking at at most a pair of dual dual-core 4GB systems running Master Slave replication. -Original Message- From: J. Pow [mailto:[EMAIL PROTECTED] On Behalf Of jay Sent: Monday, February 27, 2006 6:06 PM To: Philip Hallstrom Cc: mysql@lists.mysql.com Subject: [SPAM] - Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam Hi Philip, thanks for the reply. Single master + many read only slaves would only solve the problem of handling many many concurrent read accesses, by distributing the load across all slaves. However, I guess the real problem, is that the writes would still need to be performed across ALL databases, and the DB would be HUGE, would it not? Lets say I host 100 forums, with 100k posts each, every write would need to be replicated to as many slaves as I have. Thanks! Jay Philip Hallstrom wrote: Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- 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]
MYSQL: Unlimited Forums, Scalablity Issues, advice please?
Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. I dont think putting EVERYTHING on one single DB would be a good idea, considering that a single forum could grow huge, and have a million posts. I am thinking that the solution would possibily involve like a fixed amount of forums per db/server, and then drop in a new server everytime that limit is reached. Regarding point 3, where a user has a dashboard that allows him to view all subscribed posts across ALL forums, an obvious solution would be to have a user-centric table that also stores copies of all his subscribed post IDs (including last viewed time stamp etc), eliminating the need to query across all separate DBs. Am I heading in the right direction? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Appreciate any advice! Sprock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please?
Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please?
Hi Philip, thanks for the reply. Single master + many read only slaves would only solve the problem of handling many many concurrent read accesses, by distributing the load across all slaves. However, I guess the real problem, is that the writes would still need to be performed across ALL databases, and the DB would be HUGE, would it not? Lets say I host 100 forums, with 100k posts each, every write would need to be replicated to as many slaves as I have. Thanks! Jay Philip Hallstrom wrote: Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade advice
Jon Miller wrote: Jon, I'm currently running MySQL on RedHat 7.2 and now I'm in the processing of finishing testing a new server which has SUSE Linux Enterprise Server 9. My question is my database currently resides on RH7.2 and MySQL-server-4.0.13-0. I want to install the latest version on the SUSE system but I believe this to be Version: 4.0.18 Release: 32.20 (this is with Service Pack 3 from Novell). How do I do a backup of the existing and restore to the current? Are there any changes that may cause my apps to have a fit? Seeing that you're moving from 4.0.x to a 4.0.x, its highly unlikely that any of yours apps should have a fit. You might fancy reading: http://dev.mysql.com/doc/refman/5.0/en/disaster-prevention.html (our backup and recovery guide) If you fancy performing an upgrade to a newer version, do pay http://dev.mysql.com/doc/refman/4.1/en/upgrade.html a visit as well. Hope this helps -- Colin Charles, Community Engineer MySQL AB, Kuala Lumpur, Malaysia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade advice
I'm currently running MySQL on RedHat 7.2 and now I'm in the processing of finishing testing a new server which has SUSE Linux Enterprise Server 9. My question is my database currently resides on RH7.2 and MySQL-server-4.0.13-0. I want to install the latest version on the SUSE system but I believe this to be Version: 4.0.18 Release: 32.20 (this is with Service Pack 3 from Novell). How do I do a backup of the existing and restore to the current? Are there any changes that may cause my apps to have a fit? Thanks Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geographical advice
Ben Clewett wrote: Hi, I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. I have been reading the Geographic Spacial extensions to MySQL. Which enable me to store the coordinates in a far more useful form. But do not seem to offer me the type of index I need. This must be a common problem, is there any person who can help me? Thanks in advance, Ben Clewett. I wish mysql had the ability to automatically convert points to different coordinate systems then get the distance like PostGIS, but it doesn't. Anyway, I believe this will get you distance in miles: SELECT( DEGREES( ACOS( SIN(RADIANS( latitude1 )) * SIN(RADIANS( latitude2 )) + COS(RADIANS( latitude1 )) * COS(RADIANS( latitude2 )) * COS(RADIANS( longitude1 - longitude2 )) ) * 60 * 1.1515 ) ) AS distance where latitude1, latitude1, longitude2, longitude2 are columns Just SORT that by distance. You can also do HAVING distance 20 for example, to cut down on the number of points returned. -- http://www.douglassdavis.com
Geographical advice
Hi, I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. I have been reading the Geographic Spacial extensions to MySQL. Which enable me to store the coordinates in a far more useful form. But do not seem to offer me the type of index I need. This must be a common problem, is there any person who can help me? Thanks in advance, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geographical advice
If you visualise your search area as a circle around your 'target' coordinates, then you can eliminate many of the irrelevant rows by search for coordinates that fall within a square surrounding that circle. So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06, Ben Clewett wrote: I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Geographical advice
James is right. I use this method on a table with a combined index on 50 million rows and it's almost instantaneous. Performance was vastly improved after I did an alter table order by x Andy -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: 10 January 2006 14:27 To: Ben Clewett Cc: mysql@lists.mysql.com Subject: Re: Geographical advice If you visualise your search area as a circle around your 'target' coordinates, then you can eliminate many of the irrelevant rows by search for coordinates that fall within a square surrounding that circle. So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06, Ben Clewett wrote: I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. -- 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: Geographical advice
Sorry, subject corruption, my last email should have had this subject, not 'temporary table issue'... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advice Required
I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice Required
What are specifications of your DB. How much of data you have. How mant transactions you will be getting daily. Without these details it will be difficult to answer ur question. But for a centralised database server, I guess 1G RAM and 2CPU will be a good configuration to start with. sujay -Original Message- From: Vinayak Mahadevan [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 9:25 AM To: mysql@lists.mysql.com Subject: Advice Required I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- 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: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. However, since MySQL is freely available, why not just download it, install it on your development machine, and run a few tests. The only real measurement of performance is actual tests: predictions often err, both high and low. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
[EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 11:28:51: [EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 That sounds reasonably competent hardware. But it is not the number of connections that matters, it is the number and complexity of queries. One connection can generate a massive query which will lock out others; idle connections consume a small amount of memory but no other resources. I can only suggest you try to set up a representative test load and see if the performance is adequate for you. If performance is not adequate, after having examined your slow queries carefully and checked your indexing, extra ram is the first hardware upgrade to do. However, I think newcomers to MySQL are frequently surprised by its performance once properly indexed; don't spend money on extra ram until you have tried out a real (or simulated) test. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Phone Number Column Advice
Hello, I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Anyone have advice or know of best-practice on this subject? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Phone Number Column Advice
Hi, I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Anyone have advice or know of best-practice on this subject? Well, there was a long thread on this last week. Anyway, given that a phone-number IS NOT an integer (eg: leading zeros), it simply cannot be stored into a numerical column. Also, these days, you might have character-based phone-numbers: 0800-AOLUSA. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Phone Number Column Advice
Hello. I posted a similar question a while back and received a bunch of responses. It depends on how you plan to use the numbers. If you plan to use them in calculations, then storing them as INTs is best. Also, breaking a number into separate components makes sense if you plan to use these components as qualifiers in WHERE clauses. I decided to store my numbers as VARCHARS after considering these issues and to allow for maximum flexibility although this is an inefficient use of memory. HTH Asad On Fri, 29 Jul 2005, Scott Purcell wrote: Hello, I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Anyone have advice or know of best-practice on this subject? Thanks, Scott -- 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: Phone Number Column Advice
Scott Purcell wrote: I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Parts of some phone numbers begin with '0', which you can't represent as an INT, so use CHAR. PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.7/60 - Release Date: 7/28/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote: This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1 Wow! Really? This part amazes me. So the MySQL importing process seems to do the converting of the special characters into the newly-defined encoding format? (from latin1 to utf-8 in my case) See - we do webhosting for clients around the world, and right now our default-encoded MySQL 4.0 databases have Swedish and Hebrew characters in them. I'm concerned that if I dumped them as latin1/default in 4.0, but then imported as utf-8 in 4.1 that the non-ASCII characters would get imported as the wrong encoding. (Assuming, yes, that I would set our new 4.1 databases to do ALL utf-8 in the /etc/my.cnf and gladly take the small performance/size hit.) Oh, and one more thing, the conversion worked for us in our Hong Kong boards where they have a lot of Japanese speakers, the Chinese speakers of course, and a lot of English messages, and of course even all Chinese messages with email addresses in regular text... so yes, not only did it work for us, it worked for us with a multitude of different character sets in the very same table (even in the same column). Gotsta love utf8. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote: This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1 Wow! Really? This part amazes me. So the MySQL importing process seems to do the converting of the special characters into the newly-defined encoding format? (from latin1 to utf-8 in my case) See - we do webhosting for clients around the world, and right now our default-encoded MySQL 4.0 databases have Swedish and Hebrew characters in them. I'm concerned that if I dumped them as latin1/default in 4.0, but then imported as utf-8 in 4.1 that the non-ASCII characters would get imported as the wrong encoding. (Assuming, yes, that I would set our new 4.1 databases to do ALL utf-8 in the /etc/my.cnf and gladly take the small performance/size hit.) OK, this is where we are at, and what it does for us... I can't speak to where you are at and what it will do for you, however, we have this: 4.0 data everything is encoded by MySQL as Latin1. However the web app and the JDBC both speak UTF8, so the data being given to the database is UTF8 data which the database then stores in it's Latin1 table. When the JDBC extracts the data from 4.0 and gives it to the web app it displays as we would expect in the language it was entered... Was very cool and worked great for us. Then we went to 4.1... just changed the Binary, expecting things to work the way they did... wrong. Suddenly all our databases with strong non latin1 character sets were in trouble. Our Chinese boards were a mess, as were our German boards... even our Australian board which should have been fine was a mess because one of the areas was titled Australia Café! and the word was in there a lot. Took about three months and many hours with MYSQL folks to figure it out. The upshot however was if you went about the conversion by doing a dump and an import, and made sure to tell the import to treat the specific columns as UTF8, everything arrived in fine shape. (There was one set of data loss because it turned out one of the Chinese boards was set to Big8 and the JDBC was set to UTF8 and the database Latin1... Get's to a point where there's only so much encoding it can take) That said. the reality is if your application is anything like most the majority of the columns are for the application to work with, very few actually deal with text that needs to be encoded. The rest are time stamps, id#s, references to help you track the data... there's likely not much if any benefit to having them UTF8 encoded. For us we have 80 databases with almost that many tables with many columns.. figuring out one by one which way is up on everything represents a challenge. We're going to go back and do it now. But if I had one or two databases, I'd start by leaving the database the default (Latin1) and doing the database create and importing the .sql files (which creates the tables), then go through and run ALTER TABLE a few times and set the specific columns to UTF8... do the import the same as in my script... Latin1 text encodes in UTF8 to... Latin1 text, so you shouldn't have any problem, but I'm guessing here of course. Dump your data to disk and bring it up on another server, try different things and fidn out what works best for you before you do the real import. It SHOULD be pretty painless if you manage the import and use the mysql tools with the appropriate flag set for character sets on the way in... the only thing that will be painful about the process is if you just upgrade the binaries and expect the thing to work the way it did before, then you'll have some pain :-) Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
Hello. If all your data has the same encoding it shouldn't be a problem. Just check that character_set_server and character_set_database variables have 'utf8' value for each database in which you're going to import data. Than invokes mysql with --default-character-set = 'encoding_of_your_data'. All tables should be created with default utf8 charset and data will be converted to utf8 during the import. See: http://dev.mysql.com/doc/mysql/en/charset-map.html Miles Keaton [EMAIL PROTECTED] wrote: I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice about this? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice about this? Hi! We have been going through this process since January... the learning curve was steep and the resources hard to find. At the end of the day it's quite simple unless there are weird things already in your database... here's the process we use: On the original server using 4.0.n server and tools to match we run this: mysqldump --tab=/var/tmp/database database Then we move the directory /var/tmp/database to /var/tmp on the new server with 4.1 running... note this has the 4.1.n server AND the 4.1.n tools (such as mysql, mysqldump, mysqlimport and so on). Also note we have this line (amongst others) in our 4.1 my.cnf file: [mysqld] default-character-set=utf8 The upshot of this is that by default all new databases and tables will automatically create themselves with utf8 as the default character set unless told otherwise. For us this was important because we have MANY databases with MANY tables with MANY columns, and going through and setting the character set for each and every database/table/column was prohibitively expensive. HOWEVER... give some thought to this, the reality is for our setup there are maybe two or three columns in two or three tables in each database that really need to be UTF8 - most of the data doesn't need to be encoded this way, and there are some overheads to having everything encoded in utf8. But in the interests of time and quick conversion, we did it this way, we are reassessing it and may change things before we convert the rest of the databases. If you do decide to do this you may want to setup your mysql database using latin1 before setting the default for everything on the server to utf8... things like username/ hostname/password with 16 character varchar column type when converted to utf8 allows 16 bytes, and not 16 characters, and since utf8 allows multibyte characters you may only get 5 characters in your usernames etc... so there are little gotchas to setting the default character set for the whole server to utf8, and if you do, configure the mysql database separately on it's own. OK, having set the default character set for everything on the server to utf8 we go ahead and import our data into the server using this sequence of commands (we use a shell script, so that's what you get here). Call the shell script by giving it the database name as a flag (eg ./import database) - watch for differences in line breaks caused by email clients here, there are three lines of commands after setting DB=$1. #!/bin/sh # # LiveWorld's MySQL Import Script # Use for converting 4.0 databases to 4.1 UTF8 databases # Suitable for LiveWorld Servers only, use at your own risk # DB=$1 mysql --socket=/tmp/mysql.sock -e CREATE DATABASE $DB; cat /var/tmp/$DB/*sql | mysql --socket=/tmp/mysql.sock $DB mysqlimport --default-character-set=utf8 --socket=/tmp/mysql.sock $DB /var/tmp/$DB/*txt If you choose (probably wisely) not to set the default character set for the server to utf8 you can achieve the same result by making the first execution line of the above script to look like this: mysql --socket=/tmp/mysql.sock -e CREATE DATABASE $DB default character set utf8; Which will still have the affect of making your newly imported database use utf8 everywhere This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1, we don't loose anything along the way. Just again though I need to restate.. things like: username varchar(75) binary NOT NULL default '' take on a new meaning under utf8, it's no longer 75 characters, but 75 bytes, and utf8 encoded data takes more bytes. As well as potential data issues where you expect something to be 8 characters and it's really 24 bytes so having a varchar(8) may break new data inserts. There are also disk space issues that come out of this, and of course if you triple your disk usage there may also be new performance issues. Our recommendation is to do the import as above (this way you are sure to get your utf8 data in to the database the right way) and then go through (by script potentially) and convert the tables and columns that don't really need to be utf8 back to latin1... which is what we are looking at doing. Hope this has been a little helpful :-) Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote: This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1 Wow! Really? This part amazes me. So the MySQL importing process seems to do the converting of the special characters into the newly-defined encoding format? (from latin1 to utf-8 in my case) See - we do webhosting for clients around the world, and right now our default-encoded MySQL 4.0 databases have Swedish and Hebrew characters in them. I'm concerned that if I dumped them as latin1/default in 4.0, but then imported as utf-8 in 4.1 that the non-ASCII characters would get imported as the wrong encoding. (Assuming, yes, that I would set our new 4.1 databases to do ALL utf-8 in the /etc/my.cnf and gladly take the small performance/size hit.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
