Re: Low-end SATA vs. SCSI
On Fri, 12 Nov 2004, Fagyal Csongor wrote: Hi List, I am putting in a separate disk for our MySQL (4.1.7) server. I have some MyISAM, some InnoDB tables. Lots of reads, lots of writes (mostly atomic ones, insert/update one row), a few million rows per table, approx. 100-400 queries per second. What would you say is better (with respect to performance): a small SCSI disk (say 18G, 10kRPM) or a bigger SATA (say 120G, 7200RPM)? How about a 15kRPM SCSI disk? That's what I use and you can get them as large as 73GB. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How many rows?
This is my query select id, status from sale where user_id = 1 GROUP BY id; I need to know how many rows or how many entries this query returns. I tried to use count and sum. But the result is wrong. I want to be able to make select and know how many rows this query returns. Now, I use mysql_num_rows in php, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
Thanks for the advice Steven, I'll bear it in mind and do some reading. Graham -Original Message- From: Steven Roussey [mailto:[EMAIL PROTECTED] Sent: 13 November 2004 02:52 To: 'Graham Cossey' Cc: [EMAIL PROTECTED] Subject: RE: Help with query performance anomaly For production systems, I would never let the mysql optimizer guess a query plan when there are joins of big tables and you know exactly how it should behave. Once you think a query is finished, you should optimize it yourself. Use STRAIGHT_JOIN and USE INDEX as found here in the manual: http://dev.mysql.com/doc/mysql/en/JOIN.html STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order. http://dev.mysql.com/doc/mysql/en/SELECT.html The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scalability of MySQL - future plans?
Smart Software is the key to many problems hardware with 'standard' software cannot solve or handle. I believe that mySQL will, by year 2012, be able to handle it gracefully. It will be able to do so much more by then (easy and robust clustering / HA, for example) and even incorporate technologies and ideas that we (as in, people of our time) have not thought of as yet. It might even become the dominant database on the market.. 7 years is a century's worth of time in our world. It is the evolution of software. Regarding hardware, you should be certain technological advances would make it more than possible. Just look back at what was thought possible 7 years ago, and compare it with today's standards. On Fri, 12 Nov 2004 10:42:32 -0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Adequate data warehouse performance requires more than just hardware. 2 crucial make-or-break software features are partitioning and parallel query. On very large tables - accessing a large slice of the data via index is completely unfeasible. Table scan is the only option. Partitioning allows you to scan only the necessary segments instead of reading the whole table and rejecting massive numbers of rows. Parallel query breaks the job up so that multiple processes of the OS can participate and speed up the process. These features are an absolute necessity if we wanted to migrate our large databases from Oracle to MySQL. We are eager for MySQL to make them priority features. MySQL's market appeal would just explode. We will do our best to contribute to the effort if we can. I'd like to urge others who plan to use MySQL with large databases to consider doing the same. Thanks, Udi Heikki Tuuri [EMAIL PROTECTED] 11/12/2004 06:57 AM To: [EMAIL PROTECTED] cc: Subject:Re: scalability of MySQL - future plans? Jacek, - Original Message - From: Jacek Becla [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 12, 2004 2:30 AM Subject: scalability of MySQL - future plans? Hello, What are the plans regarding improving scalability of MySQL? We are currently trying to decide what technology/product to use for a large project that will generate ~600TB/year starting in 2012. Any pointers to related articles or hints how safe is to assume that MySQL will be able to handle petabyte-scale dataset in 8-10 years would be greatly appreciated. hmm... this mostly depends on hardware. With the innodb_file_per_table option, a single InnoDB table can be 64 TB in size, and you can have 4 billion such tables. With current PC hardware, the speed of a single CPU allows you to insert 10 000 rows per second, if the load is not disk-bound. Let us assume that a single row in 100 bytes. That makes 1 MB/s, which is 30 TB/year. CPU speed will probably double every 4 years or so. Thus, CPU speed will suffice if you use a multiprocessor. Normally, a database server has main memory at least 1 % of the data size. Is 6000 GB RAM realistic in 2012? Memory sizes will probably double every 2 to 3 years. If a high-end server today has 32 GB of RAM, in year 2012 it might have 512 GB of RAM. You will need a huge server. The worst problem is the disk seek time. If your tables have secondary indexes where the insertion order is random, a modern disk, in combination with the InnoDB insert buffer, can insert maybe 200 random records per second. That is 100 rows/s for a typical table. You are going to insert 200 000 rows/s. You may need a disk farm of 4000 physical disks. Such disk farms exist today, but they are expensive, and we have no experience how Linux performs on them. Probably by 2012, Linux is good enough, if not yet today. If you insert rows in large batches to tables smaller than your main memory, or if you insert in the prder of the primary key, and you do not have secondary keys, then there are no random accesses to disks, and you do not need a disk farm. A typical disk in 2012 may store 1 TB. Thus, you will need at least 600 disks anyway. How long does it take to build an index to a 64 TB table if you have 6 TB of memory? If the index completely fits in the memory, then this is sequential disk I/O. With today's high end disks, you can read 60 MB/s. Building an index with a single disk would take 2 weeks. In 2012, it might take only 3 days. Conclusion: MySQL/InnoDB is able to handle that workload of 600 TB/year in year 2012. But you will need a huge server which has 10 x the memory of a high-end server, and 600 - 4000 physical disk drives. The following link describes a system with 512 GB of memory, and 2000 disk drives: http://www.tpc.org/results/individual_results/IBM/IBM_690_040217_es.pdf The system costs 5.6 million US dollars. Best regards, Jacek Becla Stanford University Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions,
Re: Report Designer
On 12 Nov 2004, at 20:04, Ron Thomas wrote: What do most people use for a report designer for linux? I need to design a report similar to a phone book directory, ie, multi-column with page breaks when the first letter of the field changes. use perl; Something like DBI will talk to the MySQL database to pull out the raw name/address/number data. Something like PDF will build the pages for you and export it into something fixed and portable : http://www.enstimac.fr/Perl/perl5.6.1/site_perl/5.6.1/PDF.html Hope this helps, Andy -- Regards, Andy Davidson http://www.fotoserve.com/ Great quality prints, caanvas, posters, gifts from digital photos. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scalability of MySQL - future plans?
I hope that the original poster notes that none of the comments in this thread actually answer the question about how scaleable MySQL will be in 2012. No one has talked about that for one simple reason: no one knows. Frankly, I think the entire question was naive from the start. I can't think of any software developers who announce their plans for 8 or 10 years down the line. Software is a very competitive and ever-evolving thing. Changes in software, like increased scalability, are driven primarily by a few factors: a) Customer demand - if no customers are switching to other databases because they have greater scalability, MySQL has relatively little incentive to invest time and money on improving scalability unless perhaps they want to do it for prestige (bragging rights). b) Hardware innovation - the advent of new and faster devices and networks may make scalability easier to accomplish at a price that is attractive to customers. While we can guess at what hardware innovations are coming along by reading announcements about new research findings, some research in hardware is proprietary and is therefore not necessarily published. That means it is difficult to know about some new innovations until someone has released a device that uses the new innovation; then, there may be months or years of delay before competitors can acquire that innovation (or make their own version of it) and modify their software to use it. Even publicized research is not necessarily readily available to software developers: even if the new Fizzbin UltraHardDrive is very hot, it might be so expensive that very few people will ever buy one and therefore it isn't economic to write software for it because the installed base of these things will be tiny until the price of the device is cheaper. Or maybe the Fizzbin drive will be used by elite customers who will spare no expense to achieve scalability and MySQL will write a special version that addresses that hardware. As for customer demand, marketing gurus study this subject endlessly but don't really know what customers will do or want in the future; they can only offer guesses. MySQL has a to do list in the MySQL manual but the items in that list are mostly things that they plan to work on in the next year or two. I doubt many of the developers are thinking much about the shape of MySQL in 2012 yet. There may be an architect or chief developer of some kind who is fantasizing about major increases in scalability somewhere down the road but I'd bet that those plans are vague at best and certainly nothing that would be quantifiable. Even if they were, I doubt that MySQL would announce specific plans this far ahead of time because it would give their competitors too much information that the competitors might use against them. Besides, if they did make such an announcement, they would have to phrase it as either: 1) a solid committment, in which case they would probably *have* to live up to the promise for fear of legal action if they failed to do so. 2) a statement of intent that they could wiggle out of if the marketplace changed in such a way that it didn't make sense to achieve the stated scalability of the goals. The only type of announcement that a system planner should probably take seriously is the solid commitment; anything else is just vapourware and should be ignored. I doubt you will see many solid commitments for 8 years in the future, unless you consider platitudes like we will be the best as a solid commitment. I hope this doesn't come across as cynical. I have seen the software industry do tremendous things in the past 20 odd years. I'm expecting many more stunningly impressive innovations in the coming years. I just don't think anyone can predict with any precision what capabilities any given program, like MySQL, will have in 2012. Rhino - Original Message - From: Mark Papadakis [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, November 13, 2004 8:05 AM Subject: Re: scalability of MySQL - future plans? Smart Software is the key to many problems hardware with 'standard' software cannot solve or handle. I believe that mySQL will, by year 2012, be able to handle it gracefully. It will be able to do so much more by then (easy and robust clustering / HA, for example) and even incorporate technologies and ideas that we (as in, people of our time) have not thought of as yet. It might even become the dominant database on the market.. 7 years is a century's worth of time in our world. It is the evolution of software. Regarding hardware, you should be certain technological advances would make it more than possible. Just look back at what was thought possible 7 years ago, and compare it with today's standards. On Fri, 12 Nov 2004 10:42:32 -0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Adequate data warehouse performance requires more than just hardware. 2 crucial make-or-break software features are
Re: Problem with an insert query
GH wrote: I am trying to insert data in the ProgressNotes Table using the following query but it does not work. Can someone please assist? Thank You. [snip] 'Understandably, the consumers were upset. The Dean (SR) was kind enough to try and help explain to our members that there was a communications problem that caused the paperwork not to be completely in place before we started the program. LL and I got the members' phone numbers so that we can advise them if we are canceled next week. [snip] You've got a single apostrophe after members in the text above. If you want it to insert the apostrophe, it needs to be escaped or doubled up. ...and I got the members'' phone numbers so There may be others, but that was the only one I could find. You'll probably find that the other error relating to the select count(*)... may well disappear when you fix this. Hope this helps Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching a table and replacing all instances of a string with another
Have you read the page in the manual which documents the string functions http://dev.mysql.com/doc/mysql/en/String_functions.html? UPDATE your_table SET name_col = REPLACE(name_col, 'Peter', 'Paul'); Now, when you say, all fields in a table, do you mean every row of a particular column, or every row of all columns? If the latter, I expect you'll have to update them all separately, UPDATE your_table SET col1 = REPLACE(col1, 'Peter', 'Paul'), col2 = REPLACE(col2, 'Peter', 'Paul'), col3 = REPLACE(col3, 'Peter', 'Paul'), col4 = REPLACE(col4, 'Peter', 'Paul'), ... last_col = REPLACE(last_col, 'Peter', 'Paul'); or do as Kevin suggested: dump the table, search and replace, and reload (though I'd use sed instead of vi). Michael Kevin Spencer wrote: On Fri, 12 Nov 2004 22:12:29 -0500, Joshua Beall [EMAIL PROTECTED] wrote: Hi All, I would like to search through all fields in a table, and anytime a search string comes up, have it replace it with another string. By way of example, let's say I wanted to replace every occurence of 'Peter' with 'Paul' - can I do this purely with SQL? I know I could do it in PHP fairly easily, but I am wondering if there is a way I can just feed a query to MySQL that will take care of things. How big is the table? If it's not that big, dump it to a file via mysqldump, open the file in vi, do a global search and replace, re-load the table, and you're done. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax error
I've had this going over on the php-general list. Thought I would throw it out here . Running PHP 4.0.22 Keep getting this error - SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsSELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsWHERE VendorJobs.Industry = '2','3','4','5'Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '.Industry = '2','3','4','5'' at line 2 The first is the printout of my statement followed by the mysql_error . Here is my code. This is driving me nuts. Sorry $sql = SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; echo $sql; //if ($Ind) $sql .= WHERE VendorJobs.Industry = $s_Ind; As you can see above s_ind is an array , comma delimited. To me this all looks fine. to the parser, well ;) Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
On Sat, Nov 13, 2004 at 12:30:43PM -0800, Stuart Felenstein wrote: $sql = SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; echo $sql; //if ($Ind) $sql .= WHERE VendorJobs.Industry = $s_Ind; As you can see above s_ind is an array , comma delimited. To me this all looks fine. to the parser, well ;) You can't compare a column with a comma-delimited list of numbers like that, and you also want to make sure there is a space before the 'WHERE' keyword. You want: $sql .= WHERE VendorJobs.Industry IN ($s_Ind); -- Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: You can't compare a column with a comma-delimited list of numbers like that... What should the seperator be then ? Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
On Sat, Nov 13, 2004 at 12:46:12PM -0800, Stuart Felenstein wrote: --- Jim Winstead [EMAIL PROTECTED] wrote: You can't compare a column with a comma-delimited list of numbers like that... What should the seperator be then ? My point was that you can't compare a column with an array of numbers using the '=' operator. You have to use the IN operator, as in the line of code I posted: $sql .= WHERE VendorJobs.Industry IN ($s_Ind); (where $s_Ind is a comma-delimited list of numbers or quoted strings.) -- Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: My point was that you can't compare a column with an array of numbers using the '=' operator. You have to use the IN operator, as in the line of code I posted: Thank you Jim , it's working now! Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long update query does not replicate correctly (cont.)
Sasha Pachev [EMAIL PROTECTED] wrote: Check if you have any replication restricting rules on the slave. There might be a bug that incorrectly flags a query to be excluded. If that is the case, then try to re-write the rules to see if you can get around the bug. Yes. I have recently add the following line to the slave's my.cnf file: replicate-wild-do-table=abe_exp.% There are no more replication rules in that file. Both tables included in the multitable update were in this database (abe_exp). So this is a bug. -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.7: bug in FT search?
SELECT TYTUL FROM tKsidata WHERE MATCH (TYTUL) AGAINST (space) ORDER BY TYTUL TYTUL Shaping Space 2ed National Air Space Museum Light Space Free Space Architecture Does Economics Space Matter ? Economies of Signs Space Industry Space Competition Contribution of Economists of P Money Space Economy Improving Performance How to Manage White Space on Organisat State Space Modeling of Time Series Biogeography Introduction to Space Time and Life Cognition of Geographic Space (and so on) Already done optimize table, analyze table. Table is MyISAM, MySQL 4.1.7 Linux x86 standard-binary. Any ideas? This worked in 4.0 perfectly! -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concatinating Two Columns
I would like to know how I can make two columns out put as one? For Example I have +---+-+--+---+-+ | AttID | SessionDate | LastName | FirstName | Present | +---+-+--+---+-+ | 2 | 2004-10-30 | Smith | Dale | Yes | +---+-+--+---+-+ I would like to have it output as +---+-+--+-+ | AttID | SessionDate | Name| Present | +---+-+--+-+ | 2 | 2004-10-30 | Dale Smith | Yes | +---+-+--+---+-+ I have the following: mysql \s -- mysql Ver 12.22 Distrib 4.0.18, for mandrake-linux-gnu (i586) Server version: 4.0.18 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 2 days 8 hours 8 min 48 sec Threads: 2 Questions: 10654 Slow queries: 0 Opens: 123 Flush tables: 1 Open tables: 30 Queries per second avg: 0.053 -- Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concatinating Two Columns
SELECT AttID ,SessionDate , CONCAT( FirstName, , LastName ), Present FROM myTable - Original Message - From: GH [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 14, 2004 5:32 AM Subject: Concatinating Two Columns I would like to know how I can make two columns out put as one? For Example I have +---+-+--+---+-+ | AttID | SessionDate | LastName | FirstName | Present | +---+-+--+---+-+ | 2 | 2004-10-30 | Smith | Dale | Yes | +---+-+--+---+-+ I would like to have it output as +---+-+--+-+ | AttID | SessionDate | Name| Present | +---+-+--+-+ | 2 | 2004-10-30 | Dale Smith | Yes | +---+-+--+---+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
