Re: how things get messed up
Just like to mention that http://www.blobstreaming.org was created to solve this problem in MySQL. The Launchpad project is here: https://launchpad.net/pbxt On Feb 16, 2010, at 3:23 PM, Johan De Meersman wrote: On Mon, Feb 15, 2010 at 10:53 AM, Martijn Tonies wrote: databases are made for storing data - it saves you on both database and PHP requests, as (from a web point of view) you can't return the image data inside your HTML - it requires a second HTTP call. Filesystem image serving, Doesn't an image always required additional http calls from the tag? Yes, that's what I'm saying. However, there's a significant difference between an HTTP call that only needs to pump a file on to the network, and an HTTP call that needs to start a PHP process that then in turn needs to connect to the DB, which then needs to parse and execute a query. Offloading static files (images, css, whatnot) to a separate server that runs a lightweight, threaded httpd without PHP and whatnot compiled in, also means that you'll need less hardware to serve the same amount of requests - our PHP machines typically run 400 apache processes, but a static server on the same hardware could easily serve a few thousand threads - suddenly it's not memory/cpu but disk/network bandwidth that becomes your bottleneck. If you do go for BLOBs, though, for god's sake keep them in a separate table, lest you fragment your datafiles. Split records are a disaster for performance. I agree -for MySQL-, but this is a MySQL limitation!! Not a DBMS one. True. I think (but am too lazy to verify) that even in MySQL this is mainly an issue with MyISAM, not InnoDB. Given how MyISAM is the default engine, though, I thought it worthwile to mention. Separate LOB storage still leaves the overhead of query parsing and other generic DBMS stuff which isn't there on a filesystem, though, not to mention that each image requests takes up a database connection for as long as it takes to transfer the (potentially huge) data. Don't forget that in a well-tuned database, network transfer is often a significant part of your total connection lifetime for select statements. -- 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
mysql and tomcat
Hi, In our deployment, mysql needs to be started before tomcat automatically after server restart. If you know how this order can be specified, please share. We use tomcat6 on Linux tyu-linux 2.6.18-128.2.1.el5 #1 SMP Tue Jul 14 06:36:37 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux Thanks
Information_schema permission error
I am getting this error - mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES. I am using 5.1.45. I don't understand what is causing this problem. Does anyone have a clue? TIA, Zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: count children nodes
David, I need count the messages don'tread in a thread. Have a look at the edge list examples at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - David Arroyo Menendez wrote: Hello, I've the next table structure: CREATE TABLE tx_cc20_mensajes ( uid int(11) NOT NULL auto_increment, pid int(11) DEFAULT '0' NOT NULL, tstamp int(11) DEFAULT '0' NOT NULL, crdate int(11) DEFAULT '0' NOT NULL, cruser_id int(11) DEFAULT '0' NOT NULL, deleted tinyint(4) DEFAULT '0' NOT NULL, hidden tinyint(4) DEFAULT '0' NOT NULL, remitente int(11) DEFAULT '0' NOT NULL, destinatario int(11) DEFAULT '0' NOT NULL, padre int(11) DEFAULT '0' NOT NULL, mensaje text, leido tinyint(3) DEFAULT '0' NOT NULL, PRIMARY KEY (uid), KEY parent (pid) ); Where padre is the id of the parent message. I need count the messages don't read in a thread. How can I do it? With $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and (padre=".$est_row['uid']." or uid=".$est_row['uid'].")"; I am counting only the first level, but I need count the rest of children messages. What is the query? Thanks! No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 07:35:00
RE: count children nodes
Trees can be complex in SQL; these articles will give some different ideas to handle it: http://hashmysql.org/index.php?title=Trees_and_hierarchical_data_in_SQL http://dev.mysql.com/tech-resources/articles/hierarchical-data.htm Regards, Gavin Towey -Original Message- From: David Arroyo Menendez [mailto:david.arr...@bbvaglobalnet.com] Sent: Tuesday, February 16, 2010 8:27 AM To: mysql@lists.mysql.com Subject: count children nodes Hello, I've the next table structure: CREATE TABLE tx_cc20_mensajes ( uid int(11) NOT NULL auto_increment, pid int(11) DEFAULT '0' NOT NULL, tstamp int(11) DEFAULT '0' NOT NULL, crdate int(11) DEFAULT '0' NOT NULL, cruser_id int(11) DEFAULT '0' NOT NULL, deleted tinyint(4) DEFAULT '0' NOT NULL, hidden tinyint(4) DEFAULT '0' NOT NULL, remitente int(11) DEFAULT '0' NOT NULL, destinatario int(11) DEFAULT '0' NOT NULL, padre int(11) DEFAULT '0' NOT NULL, mensaje text, leido tinyint(3) DEFAULT '0' NOT NULL, PRIMARY KEY (uid), KEY parent (pid) ); Where padre is the id of the parent message. I need count the messages don't read in a thread. How can I do it? With $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and (padre=".$est_row['uid']." or uid=".$est_row['uid'].")"; I am counting only the first level, but I need count the rest of children messages. What is the query? Thanks! This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
count children nodes
Hello, I've the next table structure: CREATE TABLE tx_cc20_mensajes ( uid int(11) NOT NULL auto_increment, pid int(11) DEFAULT '0' NOT NULL, tstamp int(11) DEFAULT '0' NOT NULL, crdate int(11) DEFAULT '0' NOT NULL, cruser_id int(11) DEFAULT '0' NOT NULL, deleted tinyint(4) DEFAULT '0' NOT NULL, hidden tinyint(4) DEFAULT '0' NOT NULL, remitente int(11) DEFAULT '0' NOT NULL, destinatario int(11) DEFAULT '0' NOT NULL, padre int(11) DEFAULT '0' NOT NULL, mensaje text, leido tinyint(3) DEFAULT '0' NOT NULL, PRIMARY KEY (uid), KEY parent (pid) ); Where padre is the id of the parent message. I need count the messages don't read in a thread. How can I do it? With $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and (padre=".$est_row['uid']." or uid=".$est_row['uid'].")"; I am counting only the first level, but I need count the rest of children messages. What is the query? Thanks!
RE: how things get messed up
i agree with jerry put date/timestamps on each record..(that way you know when the record was created/modified) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > From: jschwa...@the-infoshop.com > To: vikkiatb...@yahoo.in; vegiv...@tuxera.be > CC: mysql@lists.mysql.com > Subject: RE: how things get messed up > Date: Tue, 16 Feb 2010 11:02:22 -0500 > > >-Original Message- > >From: Vikram A [mailto:vikkiatb...@yahoo.in] > >Sent: Friday, February 12, 2010 4:13 AM > >To: Johan De Meersman > >Cc: MY SQL Mailing list > >Subject: Re: how things get messed up > > > >Sir, > > > >Thanks for your suggestion, > >I will go for blob storage, because our application will maintain the data on > >yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not > >face > >such kind of performance issue in our application. > > > [JS] It sounds like you are planning to have one table per year. Regardless > of > where you put your blobs, I think that is a bad idea from a design > standpoint. > It will make it harder to find historical information. > > If your database is relatively small, then I'd just keep everything in one > table. If it is big, then roll data that is five years old into an archive > table. That will give you only two places, and an easy-to-follow rule to tell > you where to look. > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > _ Hotmail: Powerful Free email with security by Microsoft. http://clk.atdmt.com/GBL/go/201469230/direct/01/
RE: how things get messed up
>-Original Message- >From: Vikram A [mailto:vikkiatb...@yahoo.in] >Sent: Friday, February 12, 2010 4:13 AM >To: Johan De Meersman >Cc: MY SQL Mailing list >Subject: Re: how things get messed up > >Sir, > >Thanks for your suggestion, >I will go for blob storage, because our application will maintain the data on >yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not >face >such kind of performance issue in our application. > [JS] It sounds like you are planning to have one table per year. Regardless of where you put your blobs, I think that is a bad idea from a design standpoint. It will make it harder to find historical information. If your database is relatively small, then I'd just keep everything in one table. If it is big, then roll data that is five years old into an archive table. That will give you only two places, and an easy-to-follow rule to tell you where to look. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.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: how things get messed up
>-Original Message- >From: Ann W. Harrison [mailto:a...@mysql.com] >Sent: Monday, February 15, 2010 12:12 PM >To: Martijn Tonies >Cc: mysql@lists.mysql.com >Subject: Re: how things get messed up > >There are lots of ways to screw up storage. > [JS] As the one who started this topic, I can't agree more. I was away for several days, but I must insert that I'm not a novice (although I haven't looked at the internals of a database for several years). Some of the things that can affect data transfer between the client and the physical storage are: - Seek time of the mechanics - Internal buffering in the physical drives - Delayed writing at the device level - Ability, or lack thereof, of the drives and/or controllers to chain commands - Ability, or lack thereof, of the drives and/or controllers to reorder chained commands - Raid level - Buss speed - Raw vs. file system storage - Fragmentation of the file allocation on the disks - Ability of the file system to do anticipatory reads - File system buffer strategies - File system buffer size/number - Database engine buffer strategies - Database engine buffer size/number - Fragmentation of the tables within the data file (if applicable) - Fragmentation of the data within the tables - Design of the database/tables - Ability of the engine to optimize queries - Ability of the programmer to write sane queries - Use, design, and optimization of stored procedures - Speed of the pipe between the client and the database host - Efficiency of the database access library - Language in which the client application is written (compiled vs. interpreted being the big differentiator) - Efficiency of the client's handling of the retrieved data - Efficiency of the presentation layer - Speed of the pipe between the client and the user - ROI on optimizing any or all of the above - Management meddling A profiler that could integrate all of the above would be a nifty tool, wouldn't it? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >Cheers, > >Ann > > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.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: how things get messed up
On Mon, Feb 15, 2010 at 6:11 PM, Ann W. Harrison wrote: > And here's a comment from a very experienced MySQL DBA > > http://sheeri.com/archives/39 Not so much a comment as her starting up the same discussion we're having :-) If you have the kind of needs where replicating NFS servers won't hold, go for distributed redundant storage, also known as cluster filesystems. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Enabling Slow query log in Mysql 5.0
Machiel Richards wrote: Hi All I hope that someone can assist me with this. We have a client with a production MySQL database running MySQL 5.0. Their slow query counts have skyrocketed over the last week and I found that their slow query logs are not enabled. However when trying to configure this I get the following message: mysql> set global log_slow_queries=ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable mysql> I did the same thing many times before on other databases but this specific one gives me this message. Can anyone perhaps give me some insight as to why i'm getting this and how to enable it (preferably without having to restart the database seeing it is a high availability production system)? You can do what you described in version 5.1 but not in 5.0 . For 5.0 that variable is not dynamic. That means you cannot change it while the system is running: http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-slow-queries One way around this is to setup the machine with the Slow Query Log enabled but to use a very large value of --long-query-time to essentially ignore every query. Then, when you want to capture slow queries, you reset --long-query-time to a reasonable value. Unfortunately, this requires a restart to initialize. After that you can adjust the --long-query-time to throttle the contents of the log. http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_long_query_time -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, 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: how things get messed up
On Mon, Feb 15, 2010 at 10:53 AM, Martijn Tonies wrote: > databases are made for storing data - it saves you on both database and PHP >> requests, as (from a web point of view) you can't return the image data >> inside your HTML - it requires a second HTTP call. Filesystem image >> serving, >> > > Doesn't an image always required additional http calls from the tag? Yes, that's what I'm saying. However, there's a significant difference between an HTTP call that only needs to pump a file on to the network, and an HTTP call that needs to start a PHP process that then in turn needs to connect to the DB, which then needs to parse and execute a query. Offloading static files (images, css, whatnot) to a separate server that runs a lightweight, threaded httpd without PHP and whatnot compiled in, also means that you'll need less hardware to serve the same amount of requests - our PHP machines typically run 400 apache processes, but a static server on the same hardware could easily serve a few thousand threads - suddenly it's not memory/cpu but disk/network bandwidth that becomes your bottleneck. > If you do go for BLOBs, though, for god's sake keep them in a separate >> table, lest you fragment your datafiles. Split records are a disaster for >> performance. >> > > I agree -for MySQL-, but this is a MySQL limitation!! Not a DBMS one. True. I think (but am too lazy to verify) that even in MySQL this is mainly an issue with MyISAM, not InnoDB. Given how MyISAM is the default engine, though, I thought it worthwile to mention. Separate LOB storage still leaves the overhead of query parsing and other generic DBMS stuff which isn't there on a filesystem, though, not to mention that each image requests takes up a database connection for as long as it takes to transfer the (potentially huge) data. Don't forget that in a well-tuned database, network transfer is often a significant part of your total connection lifetime for select statements. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
My SQl Master -Slave Setup
Dear Sirs, I would like to have the MYSQL master and slave on my application setup. I am using windows vista and mysql 5.1.x what are the needed things for this master salve setup? So that, I can proceed further with manuals available. Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: Enabling Slow query log in Mysql 5.0
Hi Machiel, The below link will help you. mk-query-digesthttp://www.xaprb.com/blog/category/maatkit/ Regards, Krishna On Tue, Feb 16, 2010 at 12:51 PM, Machiel Richards wrote: > Hi All > > > >I hope that someone can assist me with this. > > > >We have a client with a production MySQL database running > MySQL 5.0. > > > >Their slow query counts have skyrocketed over the last week > and I found that their slow query logs are not enabled. > > > >However when trying to configure this I get the following > message: > > > > mysql> set global log_slow_queries=ON; > > ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable > > mysql> > > > >I did the same thing many times before on other databases > but this specific one gives me this message. > > > >Can anyone perhaps give me some insight as to why i'm > getting this and how to enable it (preferably without having to restart the > database seeing it is a high availability production system)? > > > >Help is much appreciated. > > > > Regards > >
