Re: splitting large tables vertically
kimky...@fhda.edu (Kyong Kim) writes: I was wondering about a scale out problem. Lets say you have a large table with 3 cols and 500+ million rows. Would there be much benefit in splitting the columns into different tables based on INT type primary keys across the tables? To answer your question properly requires more information: 1. Expected table structure. Can you show the current CREATE TABLE xxx\G output? 2. Expected use cases to extract data? 3. Do you expect to delete data frequently, or are you only inserting data, or is there a mix of inserts and deletes? If so provide more info. I've come across situations where a large table like this caused lots of problems. There were lots of concurrent delete batches (cleaning up) and at the same time lot of inserts. At the same time there were large groups of selects to collect certain sets of data for presentation. Perhaps you are doing something similar? If you do something similar you may find that it's extremely important to get the keys right especially the primary keys so that data retrieval (for SELECTs or DELETEs) is as fast as possible (using clustered indexes [PRIMARY KEY in innodb]). If not or if the queries overlap you may find performance degredation a big issue as Innobase manages the locks to ensure that the concurrent statements don't interfere. You can also use merge tables sitting on top of MyISAM per year or per whatever data in each table. That avoids you having to find data for 2009 as you look in table xxx_2009, so this can be a big win. MyISAM has the inconvenience that if the server ever crashes recovery of these tables can be very timeconsuming. Innodb has a larger footprint for the same data. So it's hard without more information on the structure and the use cases to answer your question. In fact if you have the time, try out and benchmark different approaches and see which is best for your requirements. Just remember that as the data grows the initial measurements may not be consistent with behaviour you see later. Also if you are looking at a large amount of data like this appropriate server tuning can influence performance significantly. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
compu...@videotron.ca (michel) writes: I set up MySQL and when I try to start it it fails telling me that I need to run 'mysql_upgrade'. Show us the full error output and provide information on the version of MySQL you are using. When I run 'mysql_upgrade' it runs 'mysqlcheck' which is supposed to only be run when the server works ... No, mysql_upgrade does call mysqlcheck to see if things need adjusting. Look at the documentation http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html Look at http://dev.mysql.com/doc/refman/5.1/en/upgrade.html which gives information on upgrading MySQL. However be careful as IMO this documentation can be a bit confusing and is incomplete. Nevertheless it's a good starting point. If however you don't think you are upgrading then you need to provide more information on how you are installing MySQL so we can determine why MySQL thinks that it needs to do an upgrade. My guess would be that you have multiple mysql binaries on your server and are not running the version you expect. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
Simon, I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution I would suggest that the developers team might change the error messages - Original Message - From: Simon J Mudd sjm...@pobox.com To: mysql@lists.mysql.com Sent: Sunday, May 10, 2009 5:20 AM Subject: Re: Still going in cicrles compu...@videotron.ca (michel) writes: I set up MySQL and when I try to start it it fails telling me that I need to run 'mysql_upgrade'. Show us the full error output and provide information on the version of MySQL you are using. When I run 'mysql_upgrade' it runs 'mysqlcheck' which is supposed to only be run when the server works ... No, mysql_upgrade does call mysqlcheck to see if things need adjusting. Look at the documentation http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html Look at http://dev.mysql.com/doc/refman/5.1/en/upgrade.html which gives information on upgrading MySQL. However be careful as IMO this documentation can be a bit confusing and is incomplete. Nevertheless it's a good starting point. If however you don't think you are upgrading then you need to provide more information on how you are installing MySQL so we can determine why MySQL thinks that it needs to do an upgrade. My guess would be that you have multiple mysql binaries on your server and are not running the version you expect. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=compu...@videotron.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
compu...@videotron.ca (michel) writes: I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So you had built the binaries by didn't have an initial mysql database created? From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution This looks correct. I would suggest that the developers team might change the error messages. Indeed, I'll create a bug report for this. http://bugs.mysql.com/44765 Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
Simon, I am totally new to MySQL and followed some instructions from the internet. Here is the install script I put together that seems to work. cd /home/qsys gzip -cd /home/qsys/mysql-5.1.32.tar.gz | tar xf - mv /home/qsys/mysql-5.1.32 /home/qsys/mysql-5.1.32.source cd /home/qsys/mysql-5.1.32.source ./configure --help --verbose ./configure --prefix=/home/qsys/mysql-5.1.32 --with-unix-socket-path=/home/qsys/mysql-5.1.32/mysql.sock --with-tcp-port=3305 make make install cd /home/qsys/mysql-5.1.32 mkdir /home/qsys/mysql-5.1.32/var # create the directory to generate test file alpha.lower.test /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld --general-log=Enable \ --log-output=FILE \ --general_log=/home/qsys/MySQL.log \ --log-error=/home/qsys/MySQL.log \ --bind-address=91.203.57.197 - Original Message - From: Simon J Mudd sjm...@pobox.com To: mysql@lists.mysql.com Sent: Sunday, May 10, 2009 8:03 AM Subject: Re: Still going in cicrles compu...@videotron.ca (michel) writes: I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So you had built the binaries by didn't have an initial mysql database created? From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution This looks correct. I would suggest that the developers team might change the error messages. Indeed, I'll create a bug report for this. http://bugs.mysql.com/44765 Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=compu...@videotron.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Still going in cicrles
starting MYSQL %MYSQL_HOME%\binmysqld --log-output=FILE --log-error=MySQL.log --result-file=results.log --perform some minor operation mysql -u username -p root DBNAME mysqlshow databases; mysqluse database; mysqlshow tables; please display the results of these operations of %MYSQL_HOME%\data\MySQL.log Martin __ Note de déni et de confidentialité 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: compu...@videotron.ca To: mysql@lists.mysql.com Subject: Re: Still going in cicrles Date: Sun, 10 May 2009 08:13:48 -0400 Simon, I am totally new to MySQL and followed some instructions from the internet. Here is the install script I put together that seems to work. cd /home/qsys gzip -cd /home/qsys/mysql-5.1.32.tar.gz | tar xf - mv /home/qsys/mysql-5.1.32 /home/qsys/mysql-5.1.32.source cd /home/qsys/mysql-5.1.32.source ./configure --help --verbose ./configure --prefix=/home/qsys/mysql-5.1.32 --with-unix-socket-path=/home/qsys/mysql-5.1.32/mysql.sock --with-tcp-port=3305 make make install cd /home/qsys/mysql-5.1.32 mkdir /home/qsys/mysql-5.1.32/var # create the directory to generate test file alpha.lower.test /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld --general-log=Enable \ --log-output=FILE \ --general_log=/home/qsys/MySQL.log \ --log-error=/home/qsys/MySQL.log \ --bind-address=91.203.57.197 - Original Message - From: Simon J Mudd sjm...@pobox.com To: mysql@lists.mysql.com Sent: Sunday, May 10, 2009 8:03 AM Subject: Re: Still going in cicrles compu...@videotron.ca (michel) writes: I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So you had built the binaries by didn't have an initial mysql database created? From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution This looks correct. I would suggest that the developers team might change the error messages. Indeed, I'll create a bug report for this. http://bugs.mysql.com/44765 Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=compu...@videotron.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® has a new way to see what's up with your friends. http://windowslive.com/Tutorial/Hotmail/WhatsNew?ocid=TXT_TAGLM_WL_HM_Tutorial_WhatsNew1_052009
RE: Still going in cicrles
providing accurate information would've helped the op in this situation i agree! thanks, Martin __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. 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. To: mysql@lists.mysql.com Subject: Re: Still going in cicrles From: sjm...@pobox.com Date: Sun, 10 May 2009 14:03:15 +0200 compu...@videotron.ca (michel) writes: I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So you had built the binaries by didn't have an initial mysql database created? From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution This looks correct. I would suggest that the developers team might change the error messages. Indeed, I'll create a bug report for this. http://bugs.mysql.com/44765 Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® goes with you. http://windowslive.com/Tutorial/Hotmail/Mobile?ocid=TXT_TAGLM_WL_HM_Tutorial_Mobile1_052009
Re: splitting large tables vertically
Simon, Thanks for the feedback. I don't have all the details of the schema and workload. Just an interesting idea that was presented to me. I think the idea is to split a lengthy secondary key lookup into 2 primary key lookups and reduce the cost of clustering secondary key with primary key data by using a shorter INT type surrogate key. Another downside is the possible need of foreign keys and added complexity of insertions and multi-column updates. Have you found primary key lookups to be at least twice as fast as secondary key lookups with VARCHAR type primary key in InnoDB? The whole idea is based on the assumption that it is. Also, MyISAM conversion is an option too. Have you found the table maintenance to be a significant overhead? I've experienced MyISAM table corruptions in production and I'm more inclined to go with InnoDB for its reliability. This is a fairly important table. Any insight would be much appreciated. Kyong kimky...@fhda.edu (Kyong Kim) writes: I was wondering about a scale out problem. Lets say you have a large table with 3 cols and 500+ million rows. Would there be much benefit in splitting the columns into different tables based on INT type primary keys across the tables? To answer your question properly requires more information: 1. Expected table structure. Can you show the current CREATE TABLE xxx\G output? 2. Expected use cases to extract data? 3. Do you expect to delete data frequently, or are you only inserting data, or is there a mix of inserts and deletes? If so provide more info. I've come across situations where a large table like this caused lots of problems. There were lots of concurrent delete batches (cleaning up) and at the same time lot of inserts. At the same time there were large groups of selects to collect certain sets of data for presentation. Perhaps you are doing something similar? If you do something similar you may find that it's extremely important to get the keys right especially the primary keys so that data retrieval (for SELECTs or DELETEs) is as fast as possible (using clustered indexes [PRIMARY KEY in innodb]). If not or if the queries overlap you may find performance degredation a big issue as Innobase manages the locks to ensure that the concurrent statements don't interfere. You can also use merge tables sitting on top of MyISAM per year or per whatever data in each table. That avoids you having to find data for 2009 as you look in table xxx_2009, so this can be a big win. MyISAM has the inconvenience that if the server ever crashes recovery of these tables can be very timeconsuming. Innodb has a larger footprint for the same data. So it's hard without more information on the structure and the use cases to answer your question. In fact if you have the time, try out and benchmark different approaches and see which is best for your requirements. Just remember that as the data grows the initial measurements may not be consistent with behaviour you see later. Also if you are looking at a large amount of data like this appropriate server tuning can influence performance significantly. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kimky...@fhda.edu Inst. Web Programmer CMDBA 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unix compress equivalent
hi Olaf, in unix you have small programms that do one thing and not more. What you want to archive is a compressed output files. the most easy way is: send to stdout | gzip -c outfile depending on your data replace gzip with zoo,lha,bzip2,compress,. re, wh Olaf Stein schrieb: Or even better, can I tell load data infile or somewhere in the table definition to compress whatever is written to the file? Thanks Olaf On 5/8/09 12:29 PM, Olaf Stein olaf.st...@nationwidechildrens.org wrote: Hi all What is the equivalent in unix (more specifically python) to the compress() function. I am trying to make csv file for use with load data infile and am wondering how to compress the strings that I would usually compress with compress() in a regular sql statement. The field I am writing this into is longblob and I need the compressed version here to be identical to what compress() would do Thanks olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
What about sub selects. As I see it you only care about the highest and lowest order of results in each list. Sorry, in am on a mobile so I can nit make a test case, and this will be pseudo SQL. Select * from table where start = (select foo) and ( select foo) ... Also look at the between and in keywords. Perhaps your list of timestamps is not in a database, can you put then in? Either way unless I am reading you wrong you only need the greatest and lowest time in your comparison, not a huge list. -- Scott Iphone says hello. On May 8, 2009, at 9:26 AM, Abhishek Pratap abhishek@gmail.com wrote: Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: splitting large tables vertically
kimky...@fhda.edu (Kyong Kim) writes: I don't have all the details of the schema and workload. Just an interesting idea that was presented to me. I think the idea is to split a lengthy secondary key lookup into 2 primary key lookups and reduce the cost of clustering secondary key with primary key data by using a shorter INT type surrogate key. Another downside is the possible need of foreign keys and added complexity of insertions and multi-column updates. Have you found primary key lookups to be at least twice as fast as secondary key lookups with VARCHAR type primary key in InnoDB? The whole idea is based on the assumption that it is. That's why you really need to be more precise in the data structures you are planning on using. This can change the results significantly. So no, I don't have any specific answers to your questions as you don't provide any specific information in what you ask. Also, MyISAM conversion is an option too. Have you found the table maintenance to be a significant overhead? I've experienced MyISAM table corruptions in production and I'm more inclined to go with InnoDB for its reliability. This is a fairly important table. Well disk (and memory) usage can also be important so as it seems InnoDB storage is less efficient this may actually degrade performance. Until you are more concrete it's hard to say what will work best for you. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: splitting large tables vertically
That's why you really need to be more precise in the data structures you are planning on using. This can change the results significantly. So no, I don't have any specific answers to your questions as you don't provide any specific information in what you ask. Yeah. Let me see if I can follow up with more concrete information sometime in future. I find performance tuning to be workload dependent and it is difficult to project without having all the details. Well disk (and memory) usage can also be important so as it seems InnoDB storage is less efficient this may actually degrade performance. Until you are more concrete it's hard to say what will work best for you. At this point I'm fairly convinced that this idea of vertical paritioning a table into column tables will degrade performance unless the workload is tailor-made for this. The cost of joins and index lookup/column data seems a bit too high for almost any scenario. Thanks for the prompt response. I'll follow up with you if I have more concrete details. Thanks Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kimky...@fhda.edu Inst. Web Programmer CMDBA 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
