Re[2]: Mysql growing pains, 4 days to create index on one table!
On Thursday, July 15, 2004, 9:10:43 AM, matt wrote: mr Justin Swanhart wrote: Indexes can generate vast amounts of random i/o. Because of rotational latency, random i/o can really slow you down, especially if you are using IDE or SATA disks because they can't do tagged queueing like SCSI disks can. If you have the budget for it, I would consider getting some solid state disks. Because they have extremely low latency you will be able to get full i/o bandwidth on your reads. If you can't afford those, consider adding more disks to your RAID array so that you can spread the reads over more spindles, which will help performance. mr Using 8x72 gig 15,000 rpm U320 scsi drives in raid 5 now, that should be mr a fast read raid config mr no more will fit in the server, and solid state are 70,000 $ it's out of mr our budget mr I optimize the tables every weekened mr any other sugestions? Consider moving to a raid 10 configuration + adding additional drives externally on alternate scsi chains. Consider additional ram. Consider replicating to some slave servers and dividing reads among them. Hope this helps, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing text
On Friday, June 11, 2004, 7:00:39 AM, Csongor wrote: FC Hi, FC I have a table that has a few short text fields [text(4000), text(1000)] FC I would like to index. Do you think it is a good idea to index them FC simply, or is it better if I create auxilary fields which hold the MD5 FC for the text fields and index those? Would that be faster? The MD5 will be shorter - and so more of it will fit in RAM - so it will be faster. Better still if you can store it as a BIG INT because binary comparisons can be done (fewer cycles per comparison). Hope this helps, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 3, subquerie alternative
On Thursday, June 10, 2004, 1:20:03 PM, Mark wrote: MvB Hi, MvB For a project I'm creating a search function, the deal is this, a select MvB query must be submitted and in this query a check must be done to confirm a MvB previously found and accepted item is not shown anymore, a short version of MvB the query I need is this: snip/ MvB I don't know if the above query gives the result I want for starters, but if MvB someone gets the idea and know a way to implement a query like this in mysql MvB 3 instead of mysql 4.1+ I'd like to know. I cannot just upgrade to mysql I think what you are looking for is another table into which you can put your previous results. This might be a temp table, or an ordinary table that is keyed for your session data. Once this is done, you would join the previous_results table with your new query and select records where the components of the previous_results query are NULL. previously... INSERT INTO previous_result ... data1,... ... SELECT ... FROM data_table d LEFT JOIN previous_result p ON d.data1=p.data1 and WHERE p.data1 IS NULL AND (other criteria) The left join gives you everything you are asking for in your criteria. The WHERE p.data1 IS NULL restricts the results to those that do not yet exist in the previous data table (presuming data1 would never be NULL in your data). Hope this helps, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: My question is too idiot for nobody answer?
On Thursday, June 3, 2004, 8:14:00 AM, Victor wrote: VP If you choose to copy the files I would ensure that you are getting the VP innodb binary logs as well if they do not exists in the mysql data VP directory. The innodb backup tool or even mysqldump may provide a more VP consistent snapshot. If I might add $0.02 here... It has been my experience that mysqldump is the best solution. * The data is portable to alternate installs if needed (including changes in memory configurations which can improve performance...) * The output of mysqldump is typically much smaller when compressed than the raw data files. This invariably makes the operation faster and more efficient. Best, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Range query on datetime with index - any optimization?
Hello folks, I'm usinng MySQL 4.0.17. I have a table something like: RuleID int, GMTBase datetime, Credited bigint, ... I have an index built on GMTBase. I have rougly 8 million rows. GMTBase stores a datetime for the top of the hour on a given date. I want to build a summary of the last 2 days without scanning every record. It appears that there is no way to get MySQL to use the index on GMTBase to avoid scanning all 8 million rows. I estimate it should only scan about 267K rows. Explain mentions the GMTBase index but says it will examine about a million rows. That seems to roughly match my estimate of the number of distinct GMTBase values. The query I want to run is: select RuleID, GMTBase, sum(Credited) from RuleHistograms where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) group by GMTBase Have I done something wrong or is there simply no way to avoid scanning all of those records? Thanks, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range query on datetime with index - any optimization?
At 01:30 PM 5/5/2004, Daniel Clark wrote: I wonder if mysql isn't trying to process where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) What about doing this date subtracting in PHP and adding the result to the SQL statement. Nice try - but it's not the problem. Replacing the equation with a constant does not change the number of rows that will be reviewed. For example, explain SELECT * FROM `RuleHistogram` where GMTBase '2004-05-03' +---+---+---+-+-+++-+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---+---+---+-+-+++-+ | RuleHistogram | range | GMTBase | GMTBase | 8 | [NULL] | 954388 | Using where | +---+---+---+-+-+++-+ Anyway, I figured it out. Sorry of the confusion - I think my math is wrong and that's just the number of records it takes to go through 2 days. I'll look for another solution. Thanks, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL em bedded?
Hello, Personally, I think it's a matter of choosing the best tool for the job. For myself and my team, Java is the work horse particularly - suited for rapid application development and when there is a strong cross platform requirement. This means that Java tends to dominate our utilities and user-interface code. When heavy lifting is required we move to C++. Java's object model is simplistic (both a strength and a weakness). C++ is extremely flexible and efficient. To a greater extent, C/C++ will let you do a lot of things you really shouldn't. Java isn't immune to this. After all, bad engineering is bad engineering. In both cases it's up to the programmer to keep things where they should be. As for seeing a lot of bad Java programs and a lot of bad C/C++ programs... In my experience I've seen about the same of both... but a bad C/C++ program is less likely to survive deployment than a bad Java program. My $0.03. _M At 04:39 AM 2/21/2004, Franz, Fa. PostDirekt MA wrote: Hi, this discussion is useless, object or procedure is not realy the question. You need to know how to build a good programm, if you cannot create a good programm, no matter what language. The amount of realy bad java-programs (90% i have seen were realy bad) shows, that it is maybe not a good idea, to make programming to easy :o) . There are a lot of people, thinkink a complex task is better done with an oo-language. My boss is this opinion and had already 2 memleaks in C++, he searched for one 6 weeks. So the truth seems to be, that an oo-language (especially java) makes it easy to programm complex tasks, but what comes out in the end is worth. I prefer TCL because on my opinion it is the best of both worlds ( i never had a memleak except with a bad API written in C). Complex tasks should be done from skilled programmers - thats all. mfg Klaus -Ursprüngliche Nachricht- Von: Heikki Tuuri [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 21. Februar 2004 09:30 An: [EMAIL PROTECTED] Betreff: C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL embedded? Jochem, - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 21, 2004 2:10 AM Subject: Re: InnoDB Hot Backup + MySQL embedded? Sasha Pachev wrote: Heikki Tuuri wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) http://citeseer.nj.nec.com/shah01java.html here is a .pdf version of the paper: http://gist.cs.berkeley.edu/~mashah/java-paper/paper.pdf The authors used a 2 x Pentium III 667 MHz, Linux-2.2.16, Sun JDK 1.3, and Java HotSpot Server JVM 1.3.0. to implement a 'data-flow' query processor. Their conclusion is that the memory management and the garbage collection of Java is inefficient. The graph that they present shows an up to 2.5-fold performance degradation with the Java garbage collector, compared to their own tailored memory management system. I worked with Entity Systems Oy in the 1980s. We developed a Lisp interpreter and a compiler, and a Prolog interpreter. At that time, the inefficiency of the garbage collection in Lisp and Prolog was a serious problem. I am not familiar with more modern garbage collection algorithms, but the paper of Shah et al. suggests that there are still problems today. In the 1980s, the research group of Mike Stonebraker initially started implementing Postgres in a mixture of Lisp and C, but they later abandoned Lisp. Jochem Regards, Heikki -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection Lost Dumping Data Unexplained Pauses
Kind MySQL List, I need your help. I have an installation of replicated MySQL servers. I am migrating to new servers and upgraded MySQL software. My previous servers were RH8, MySQL 4.0.14. (A, B) My new servers are Fedora, MySQL 4.0.17. (C, D) Most of my critical data is built using INNODB tables. The newer servers are faster, have more memory (4GB), dual processors, and 3ware raid 10s. They should (and normally do) run circles around the older servers. The first step of my migration was to replicate my original servers to my new servers so all of the data is identical. (A-B-C-D). After replicating successfully for two weeks I've switched to the new servers. Here is my problem. I create a nightly backup from the slave of each pair using: myqldump -A -Q --opt ... | gzip backupfile.sql.gz On my older MySQL 4.0.14 boxes this works flawlessly. On my newer MySQL 4.0.17 boxes I consistently get the following error: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `RuleHistogram` at row: 189116 RuleHistogram is a large table - but the data is identical between the 4.0.14 boxes and the 4.0.17 boxes. In addition the application software is experiencing occasional freezes on some queries with the newer servers... these freezes eventually clear, but that should not happen. I have googled with no luck so far. Where should I look for a solution? Thanks! _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQLDump loses connection on large dump.
Thanks in advance for any help. I'm using mysqldump to create snapshots of slave database servers. I am upgrading from 4.0.14 on Redhat 8 to 4.0.17 Fedora Core 2. I have a pair of 4.0.14 boxes MNR6 master -- MNR7 slave. I have a pair of 4.0.17 boxes MNRC master -- MNRD slave. In moving to the new boxes I have set up this replication sequence: MNR6-MNR7-MNRC-MNRD I am using this command to make backups at the slaves: mysqldump -A -Q --opt --user=xx --password=xx | gzip /mnt/drive-u/MySQL-Backups/`date -I`.mnrd-backup.sql.gz This is done in a script once per day on the slave of a pair. /mnt/drive-u is a samba file link to a central server for backups. When I run this script on MNR7 it runs flawlessly. When I run this script on MNRD I get the following error: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `RuleHistogram` at row: 13154861 RuleHistogram is in the largest InnoDB table. The configuration of MNR6 and MNR7 is underpowered for the application, but it works. One 2.4GHz P4, 1 GB Ram, 2IDE HD in raid 1. MNRC and MNRD were built for the application and have much better hardware: Two 2.4GHz XEON, 4GB Ram, 4 SATA Drives Raid 10 via 3Ware Escalade. MNRC and MNRD respond nicely to queries and show no signs of trouble except for the above. My Question: Why would the more powerful servers with the more advanced software stall during a mysqldump operation when the less powerful survers successfully perform an identical operation on identical data without a hitch? What can I do to solve this problem with mysqldump? (BTW: I just sat through another failure - watching top, mysqld goes to idle along with mysqldump... they wait a few seconds in this condition, then the error occurs and the script stops. The mysql server remains responsive to queries before and after the operation. No errors are reported in the .err file.) Thanks again in advance! _M Pete McNeil (Madscientist) President, MicroNeil Research Corporation Chief SortMonster, www.SortMonster.com VOX: 703-406-2016 FAX: 703-406-2017 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on best table structure
At 03:30 PM 8/20/2003 -0700, Scott Haneda wrote: What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle snip I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. I'm gonna go out on a limb here. My intuition tells me to look for the opportunity to add some data to this process... For example, what if a record were created for each brochure that needed to be processed, and suppose that in some back-end application the person fulfilling these requests completed the data in the record... Then you might justify making a separate table for each brochure (or one with a two column key)... Then you would not waste space, you could gather statistics easily with joins, and you'd satisfy both processes at once... then again, if none of that back-end process exists, then it's probably simplest to just make an integer column and bit-map the check-boxes into it (if you want to save space)... or if you don't care about space just create a true/false column for each check box. The short answer is - more info is needed to pick the best approach. HTH, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to convert SQL Server 2000 database to MySQL?
At 05:43 PM 7/26/2003 -0500, kumar mcmillan wrote: Hi. First, you will be happy to know that I am migrating a client to PHP/MySQL from ASP/MSSQL. This is for reasons of future preservation, cost, security, efficiency, et cetera. The problem is, I don't currently have a PC and $$ to run Windows 2000 server on. I have been sent the MDF and LDF database files by the client... Does someone know of a way to convert those files to SQL without running Windows to do it? If not, is there an SQL-like format that SQL Server will save as that I can request from the client? Or is there an application (Windows or anything) that someone can recommend to make this conversion? I should point out that this is a simple database with no stored procedures or anything fancy. thanks for taking the time to read this, Kumar Have them export the tables as CSV files. You can then easily import them into MySQL. You will also want them to generate SQL scripts for each table so that you can see the MS-SQL that it takes to create those tables - this will give you the types and names of the columns... (CSV files can be generated to give you the names on the first line, but not the type). You _should_ be able to find compatible types for each column in order to import the data into MySQL. You _may_ need to do some scripting to convert things like date/time stamps from MS-SQL to MySQL... and you will need to pay attention to differences in functinality. This should get you started. Hope this helps, _M Pete McNeil (Madscientist) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's up with this GATOR crap?
I couldn't believe it so I looked. When I load the manual page I _do not_ see any such thing. Something else must be going on. _M At 11:43 PM 7/10/2003 -0400, C. Reeve wrote: Hi, I have noticed recently that every time I go to the MySQL manual page I get prompted to install Gator spyware. If MySQL condones or is going to use spyware, I may have to consider using another database. This may sound extreme, but I do not condone the use of spyware in any form and would hope that others feel the same way. Comments -- 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]
