Re: There is a ram limit?
On Thu, Sep 23, 2010 at 12:39 AM, Camilo Uribe camilo.ur...@gmail.comwrote: On Fri, Jul 9, 2010 at 12:03 AM, Johan De Meersman vegiv...@tuxera.be wrote: This will mostly depend on your OS, really. Assuming you're running a 64-bit flavour of *nix on that box, I don't think you have to worry. Linux on 64-bits. Yes, but is the Linux (and your MySQL) itself also 64-bits ? :-p You *can* use all of your ram on a 32-bit linux with the Bigmem trick, but that introduces quite a bit of overhead, and doesn't remove the per-process limit. A 32-bit MySQL will simply not be able to address all that memory, even on a 64-bit OS. The default installation will use all the ram it needs or do I have to configure something else? The default setup will use all the RAM if it needs to - but it will do so by allocating per-connection buffers, the default shared pool sizes are rather conservative. For MyISAM you will need to tune query cache, key cache, read buffers, sort buffers et cetera; for InnoDB you'll make a good start by allocating a large chunk to the buffer pool. It may also (or may not, depending) be beneficial to leave some ram for the OS' buffer cache, which caches filesystem blocks and thus saves on raw reads. More exact tuning pointers are best had by observing your database's behaviour using a trending tool like Cacti or Munin. I've said it before and I'll say it again, my three favourite tuning resources are a) the online docs, b) Baron C°'s book High Performance MySQL and c) the Performance Tuning course. With the number of parameters you can tweak on a MySQL, it's hard to give more than basic tips without hand-on experience with the system. -- 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: Swap data in columns
ALTER TABLE tablename1 CHANGE column1 column2 VARCHAR(64), CHANGE column2 column1 VARCHAR(64) Assuming the columns are varchar(64) Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: nixofortune [mailto:nixofort...@googlemail.com] Sent: 22 September 2010 6:30 PM To: mysql@lists.mysql.com Subject: Swap data in columns Hi all. Sorry for very simple question, just can't figure out the solution. I need to swap data in column1 with data in column2. ++-+-+ | id | column1 | column2 | ++-+-+ | 1 | a | z | | 2 | b | y | | 3 | c | x | | 4 | d | w | | 5 | e | v | ++-+-+ Can you achieve this with a simple query? so for id 1 column1 = 'z' and column2 = 'a' and so on. Thanks guys, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query help please
Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
Re: Swap data in columns
Hi Kevin, It works in mysql but not exactly as I need. In my case it copied content of column2 into column1. So, not exactly what I intended to achieve. Thanks. Igor update mydata set column1 = column2, column2 = column1 On Thu, Sep 23, 2010 at 12:03 AM, Kevin (Gmail) kfoneil...@gmail.comwrote: update mydata set column1 = column2, column2 = column1 (works in sqlserver, can't try mysql at the moment) You can select which rows by adding a where clause obviously. I suppose that the field values are copied to a buffer which is the written to the table at the end of the update (or row by row?) - Original Message - From: nixofortune nixofort...@googlemail.com To: mysql@lists.mysql.com Sent: Wednesday, September 22, 2010 5:29 PM Subject: Swap data in columns Hi all. Sorry for very simple question, just can't figure out the solution. I need to swap data in column1 with data in column2. ++-+-+ | id | column1 | column2 | ++-+-+ | 1 | a | z | | 2 | b | y | | 3 | c | x | | 4 | d | w | | 5 | e | v | ++-+-+ Can you achieve this with a simple query? so for id 1 column1 = 'z' and column2 = 'a' and so on. Thanks guys, Igor
Fwd: Query help please
I wondered if anyone can help me ? Do you need any further information ? Cheers Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Thu, Sep 23, 2010 at 9:49 AM Subject: Query help please To: [MySQL] mysql@lists.mysql.com Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
Re: document for mysql performance improvement
If this is a dedicated MySQL box, i would increase the InnoDB buffer pool setting in my.cnf to at least 8GB, 12 would be even better (you did say you have 16GB of ram in the machine I believe). Also, what is the output of: show status like '%tmp%'; JW On Wed, Sep 22, 2010 at 8:01 PM, Vokern vok...@gmail.com wrote: 2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: There is a ram limit?
On Thu, Sep 23, 2010 at 1:21 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Thu, Sep 23, 2010 at 12:39 AM, Camilo Uribe camilo.ur...@gmail.com wrote: On Fri, Jul 9, 2010 at 12:03 AM, Johan De Meersman vegiv...@tuxera.be wrote: This will mostly depend on your OS, really. Assuming you're running a 64-bit flavour of *nix on that box, I don't think you have to worry. Linux on 64-bits. Yes, but is the Linux (and your MySQL) itself also 64-bits ? :-p You *can* use all of your ram on a 32-bit linux with the Bigmem trick, but that introduces quite a bit of overhead, and doesn't remove the per-process limit. A 32-bit MySQL will simply not be able to address all that memory, even on a 64-bit OS. Linux and MySQL of 64-bits: uname -a Linux blade2 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux file /usr/local/mysql/bin/mysqld /usr/local/mysql/bin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), for GNU/Linux 2.4.0, not stripped The default installation will use all the ram it needs or do I have to configure something else? The default setup will use all the RAM if it needs to - but it will do so by allocating per-connection buffers, the default shared pool sizes are rather conservative. For MyISAM you will need to tune query cache, key cache, read buffers, sort buffers et cetera; Thanks, I will look in High Performance MySQL and the mysql manual about them. By the way the server has all by default, so it use MyISAM. for InnoDB you'll make a good start by allocating a large chunk to the buffer pool. It may also (or may not, depending) be beneficial to leave some ram for the OS' buffer cache, which caches filesystem blocks and thus saves on raw reads. More exact tuning pointers are best had by observing your database's behaviour using a trending tool like Cacti or Munin. I'm going to install Better Cacti Templates http://code.google.com/p/mysql-cacti-templates/ I've said it before and I'll say it again, my three favourite tuning resources are a) the online docs, The mysql manual? b) Baron C°'s book High Performance MySQL I'm reading it and c) the Performance Tuning course. I didn't knew about this course, I will tell in my job about it and see if they want to send me. With the number of parameters you can tweak on a MySQL, it's hard to give more than basic tips without hand-on experience with the system. Thanks I'm going to look the things you said and come back later with a little more of experiencie and precise questions -- 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
Error on compile MySQL with version 5.5.6 rc
hi,everyone i try to compile and install mysql from source tarball,but i failed again again. this is my config string: [r...@powerpc mysql-5.5.6-rc]# ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local --sysconfdir=/etc/sysconfig/mysql --localstatedir=/var --enable-profiling --disable-largefile --disable-ipv6 --with-unix-socket-path=/tmp/mysql.socket --with-mysqld-user=mysql --with-zlib-dir=bundled --without-docs --without-man --with-low-memory --with-system-type --with-machine-type config-l.log the configure returns some warnings which i think can be simple ignored. /usr/bin/nm: old_atexit.o: no symbols /usr/bin/nm: udiv_qrnnd.o: no symbols /usr/bin/nm: mp_clz_tab.o: no symbols /usr/bin/nm: getopt_init.o: no symbols /usr/bin/nm: get_child_max.o: no symbols /usr/bin/nm: init-posix.o: no symbols /usr/bin/nm: lseek64.o: no symbols /usr/bin/nm: oldgetrlimit64.o: no symbols /usr/bin/nm: libc_multiple_threads.o: no symbols /usr/bin/nm: getutmpx.o: no symbols /bin/rm: cannot remove `libtoolT': No such file or directory then, make mysql with make command. and the ERROR appeared. mi_static.c:40: error: conflicting types for ‘myisam_max_temp_length’ ../../include/myisam.h:254: error: previous declaration of ‘myisam_max_temp_length’ was here make[2]: *** [mi_static.o] Error 1 make[2]: Leaving directory `/root/mysql-5.5.6-rc/storage/myisam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/root/mysql-5.5.6-rc/storage' make: *** [all-recursive] Error 1 i really do not know what is wrong,want your help!!! Many Thanks, Sharl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
