Re: Efficiency Query
At Montag, 14. Oktober 2002 14:57 Alberto Ruiz Cristina wrote: Hi all I am new to MySql and I am thinking about using it for managing a database, which would have a approximated length of 500 Mb. It is formed of vectors. Greetingz, from my experiences with several Databases since the 1970ies efficiency - especially effiency of relational Databases - is primarily dependant on the design of your data and programs. It depends first of all what you want to do with the data, e.g. in addition to what data you have, how many updates and what kind of updates you are doing, how many queries and what kind of them do you expect, how many clients are simultanously accessing the database and what they are doing... Any paper or book about relational database design and it's tuning will probably help you, MySQL is not special in that regard. Then the next important step will be the tuning of your database together your applications (or under simulated load which represents your environment). This may require some changes in the layout of your data and tables too, but it comes secondary, because the major faults are often done in the design of the application algorithms together with the design of the database. A design flaw can degrade your performance by several order of magnitudes (and can be difficult to fix), while the basic database tuning thingamagics (like adding or dropping an index) are relative cheap to do later. Trouble is that I am worried about the ability of MySql to handle that amount of data. The amount of data is not a problem, but how you access and update the data might be. For example: if you want to cut a hyperplane or space-slice (sorry, don't know the exact english phrase for that) through your vector-world, which orientation will these hyperplanes have? Will it be normal to one of your dimensions etc. Just storing 500 MB of vector data might even be done best in a binary file without using any database at all, which is read (with appropriate locking) into virtual memory as one large matrix, processed and perhaps written out again. If your problem is fit for a relational database, then MySQL can do the job. If not, then other RDBS have the same problems with it, probably. Get my idea? Greetings Michael -- Michael Zimmermann (http://vegaa.de) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Normalization sql
Hi John, your design is normalized, but incomplete and unconnected. Where do you put the information which actor was playing in what title, which title was done in what studio etc. ? Normalization is a representation technique to avoid storing *redundant* information. But first this information has to be there. So first make the design complete (in an unnormalized way, which fits the problem), then in a *second* step normalize it. Greetings Michael At Montag, 14. Oktober 2002 14:49 John Chang wrote: I've read a bunch about normalization in MySQL and still can't do it very well. What I want to normalize is videos (Title, Studios, Actors, Genre, bitrate). These are the tables and fields I think it needs. Is this normalized? Thank you. Table (Fields) Title (VideoTitle, details, id) Studio (Name, id) Actors (F_Name, L_Name, id) Genre (Name, id) Bitrate (rate, id) -- Michael Zimmermann (http://vegaa.de) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.4 replication slave hangs after master breakdown+reboot
Hi friends, has anybody had similiar experiences? Environment: Systems are x86 SuSE 8.0/7.3 Linux, MySQL-max version 4.0.4 is used on all machines (installed from the mysql.org-RPMs). The setup is a circular replication with 3 machines. Linux distro- or kernel- version don't seem to play a role, several combinations of SuSE-distro (8.0 - 8.0, 7.3 - 8.0, 8.0 - 7.3) or kernels (2.4.10, 2.4.19, 2.4.20-pre10) reproduce the same situation. Problem: After one server went down the hard way (which can be simulated with a rcnetwork stop) and comes up through a reboot the mysql master-process naturally opens a new bin-log. But the slave-process on the next machine in the replication-chain keeps 'hanging' on a position in the previous bin-log (Slave is running, Slave IO is also 'Yes') - probably the position when its master jumped down the cliff without prior notice. A 'slave stop;' plus 'slave start;' solves this problem, but the startup is not done automatically. As if the slave process is still listening on the socket of the dead connection and has not recognized that there is no longer somebody on the other side. No corrupted data on any machine or the like, just this inability to resume the slave- operations without that manual 'push'. Without that slave start+stop the hanging occurs 'forever' (much longer than the master-retry time, which is kept at the default of 60 seconds). If the reboot on the master is done normally (without cutting the connection first), then everything works fine. Michael -- Michael Zimmermann (http://vegaa.de) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Port 3306 restricted to IP addresses
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Donnerstag, 4. April 2002 12:23 Tshering Norbu wrote: For the inbound connection on port 3306 of MySQL Server, how do I restrict the connection to some IP addresses something like 1.2.3.* What do I need to do in my.cnf file? I let the firewall do that kind of restrictions. - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rFZH72vu22ltWBERAnojAKCFZMYbUGcp/0dQz3gJbsoHKc9xeACdFoAZ GGT4fn5G1hD+qmaEZx1+Mf4= =pmYD -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help with query, pelase
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Donnerstag, 4. April 2002 15:29 Hathaway, Scott L wrote: I have the following query: select *, max(event_date) as high, min(event_date) as low from schedule where event_date between '2002-03-01' and '2003-04-30' group by week_ending,meeting_id order by name, event_date, start_time If I order by event_date, start_time, name, I get the proper results. If I order by as above, the first week_ending group gets broken into two parts (the last part of the group gets placed at the end of the sql results). Probably the last group has different 'order by' values.(?) - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rGoN72vu22ltWBERAmjUAJ0bEv1CYkH+QhuDaUZAK7eRfX+TqwCePM5A VwFM2H5hSez7lwiFqP7GK1I= =0X4M -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Port 3306 restricted to IP addresses
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Donnerstag, 4. April 2002 21:24 adam nelson wrote: Firewall isn't good enough (who else is inside your firewall, likely the entire hosting company or internal corporate network). The user table has a host column that I use. Also, you can enable ipfw or some other local firewall on the host itself if you are very serious. Yes, I was sloppy in my language. I meant local packet filters to allow the mysql-port for certain IPs only, sure. Greetings - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rOI872vu22ltWBERAt09AJ9blFizh+Z2Vxz+DKcJjK+Flb3T/wCfdmGQ bqef47cdtlaw28l00iDflGc= =uxwr -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help with query, pelase
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Donnerstag, 4. April 2002 17:57 Hathaway, Scott L wrote: Yes, that should only cause a reordering within the group itself, but in my case, the group breaks into two groups! Your 'group by' clause needs to have the same fields (and in the same order) as your 'order by' clause or this is bound to happen - resp. as soon as the fields are no longer identical (counted from the beginning of the 'order by' and 'group by' clause) duplicate groups are possible. Or I misunderstood you completely .o) In that case please include examples of your query with actual values. Thanks - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rOL372vu22ltWBERAo+CAJ9hzmX+JP12rIcKD/dYnLVWUnC4nwCeKCKY OdQN5qkUrtOTmstXWfsUM34= =uPlX -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CPU - hog / hangup with replication
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi list, Peter, thanks for the tip with gdb. At the moment I'm not able to reproduce the cpu-hog, I only have the backtrace of one such event. Situation was: After one or two updates of the master (and perhaps some show slave status or show master status on both machines, the slave-machine hangs, i.e. the mysqld starts eating CPU and doing nothing else. A normal kill is of no use, only kill -9 stops the process. gdb showed after connectiong to mysqld: (gdb) bt #0 0x081488de in chunk_free () #1 0x08148663 in free () #2 0x0814109f in fclose () #3 0x08152847 in vsyslog () #4 0x081525ea in syslog () #5 0x0806ddd3 in handle_connections_sockets () #6 0x0806d64b in main () #7 0x0812f194 in __libc_start_main () (gdb) c Continuing. Then gdb is no longer interruptable with Ctrl+C. In addition to the cycling mysql-daemon, there is a defunct son-process of it (probably waiting for the father to accept it's return-code) mysql11245 11179 41 20:42 ?00:08:00 /usr/sbin/mysqld mysql11289 11245 0 20:42 ?00:00:00 [mysqld defunct] So that's the present state of my findings. To be able to debug into the source, I recompiled the mysqld from the SRPM, but was not able to reproduce the cpu-hog behaviour. I only get a blocking situation when I try to do crosswise replication. Hence I give up on this problem for the moment. Perhaps the above backtrace lets somebody have an AHA! moment? Have a happy day! Michael - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8qXIK72vu22ltWBERAqyOAJ9cVcw/cFYTZ2eQwUbZLQ99SKpRUgCcC877 4ItHMe+3nnY9y/rY+fs0y+0= =0Jjr -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PigeonRankĀ
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Montag, 1. April 2002 21:38 Colin Faber wrote: Is it true that mysql is planing to implement google's famous PigeonRank system in it's FTS word weighting schema??? I allready have a simple quick-n-dirty implementation running on our local mysql servers (using spades instead of pigeons), so it shouldn't be too difficult to phase this development into the main cvs-trunk later. The only problem I wasn't yet able to resolve reliably is to store the three possible values into one bit, hence I'm using two bits for the decision logic and call this three-valued boolean storage unit a 'peck'. But it's my feeling, that there must be better a solution; after all if a qbit can hold 4 values (as modern physics says), why shouldn't a peck be able to mark 3 possibilities (or even 4 if you count 'not-yet-pecked' as the fourth). Working on it. Michael - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8qL3g72vu22ltWBERAj4nAJ43ozoANVzaNc7sFhEi4TQSLikp0QCfYYa8 DxDIMKhmecG7gx6BmxKZxnE= =GEhN -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CPU - hog / hangup with replication
Hello all, sorry, if I nerve you with a problem, which is allready known - I didn't find appropr. info in the FAQs (or was too stupid to look at the right place?) Trying database replication with cross-wise update (A is master for B and B is master for A) situations occur after some sucessfule updates, where the system seems to 'hang' and one mysql process uses 99,9% CPU. Is my mysql or OS too outdated, or what would you do to debug the situation? mysql: 2.23.41 Kernel: 2.4.10-4GB Distro: SuSE 7.3 Thank you for any idea or hint where to look. Michael -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php