Re: Query returns to many results
Peter Brawley wrote: /Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results / That's because your ... FROM ab_leader_board ablb, ab_members abm calls for a cross join--it asks for every logically possible combination of ablb and abm rows. From the rest of your query, it appears you need something like ... FROM ab_leader_board ablb INNER JOIN ab_members abm USING (name_of_joining_column) Also, do you really mean to sum all those ablb column values after having already called for all ablb column values with ablb.* ? PB - Schalk wrote: Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! Greetings Peter, Well, with regards to the ablb.*, I need access to each individual column as well as to the sum of all of those columns, so I think I need to do both, or don't I? -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Good stuff Peter, can you tell us your solution so folks can benefit from your efforts? (I expect the regex has to be POSIX style such as: mydb./'~(expirations|rtt)'/ right?) Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Hi, this is the solution I use: /usr/local/bin/mysqlhotcopy -p mypass --allowold 'mydb./~(expirations|rtt)$/' /var/backups/mysqlbackup/$1 I guess this one is also possible: /usr/local/bin/mysqlhotcopy -p mypass --allowold 'mydb./~^(expirations|rtt)$/' /var/backups/mysqlbackup/$1 Kind regards, Peter Imran Chaudhry wrote: Good stuff Peter, can you tell us your solution so folks can benefit from your efforts? (I expect the regex has to be POSIX style such as: mydb./'~(expirations|rtt)'/ right?) Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1016 : cant open ibd file even though it exists
Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY.
Wordpress MU
I am currently evaluating the multi-user version of the popular blogging software, Wordpress. One of the things I am not so sure about is its schema. To support multiple users, Wordpress creates about ten tables per user. I have, at worst case, 4000 users who may be using this software, and I assume roughly 100 posts per user. My question is whether or not it is good design to have each user have his or her own set of tables. Would it not make sense to have all user posts in a single table, referenced by a BlogID? This table could easily grow to be a few million rows, and I know MySQL won't choke on that, as long as the indexes are maintained. Aside from ease of backing up user data in the individual table scenario, is there any other benefits to doing it this way? I am considering modifying the product to support a single table, but want some insight before doing so. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wordpress MU
On Friday 24 February 2006 12:33, Erich Beyrent wrote: My question is whether or not it is good design to have each user have his or her own set of tables. Would it not make sense to have all user posts in a single table, referenced by a BlogID? This table could easily grow to be a few million rows, and I know MySQL won't choke on that, as long as the indexes are maintained. With split tables, you get the following: 1) A user can't accidentally delete another user's data (through code faults, easily) 2) Ease of backups - each user can do personal backups, and the logic is very simple to do so 3) Easier logic in the code? 4) An accidental command doesn't kill all data. The disadvantage to multiple tables (which WP as a vendor should handle for you) is that a change to one table schema must be propagated to all tables. The disadvantage of changing WP is you have to maintain the change across upgrades. Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysql connection... on Linux Redhat 9
I have MySQL 5 installed on My Machine, But it could handle only max about 350 concurrent mysql connection Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always got Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in Is it mysql bug, incorrect Linux setting or what? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysql connection... on Linux Redhat 9
Can you tell us about your config parameters? Probably you must set up your max_connections, max_user_connections http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html Ady Wicaksono escribió: I have MySQL 5 installed on My Machine, But it could handle only max about 350 concurrent mysql connection Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always got Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in Is it mysql bug, incorrect Linux setting or what? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysql connection... on Linux Redhat 9
It can be both Linux kernel setting and MySQL configuration issue. It can be a kernel issue since: - Although you have 4G on your machine your kernel may set to let only 2G to a user space process. You may need to upgrade your kernel to a 'hugemem' kernel which gives you either 3/1 or 4/4 (you need 8G mem for this) memory split - Number of open files (directly co related to number of open tables in MySQL) setting in kernel can be lower than what your MySQL instance needs. It can be a MySQL configuration issue since : - You may have incorrect settings in your my.cnf config file. Say if you assigned 2G to innodb buffer and other buffers and your kernel lets you to go upto 2G for user space process. For each connection coming MySQL dedicates at least 128K (thread size) memory, so for 350 connection, you end up using +43M memory which makes MySQL's total mem usage 2G + 43M. This may be the reason why you're seeing can't create new thread. Kayra Otaner On Fri, 2006-02-24 at 20:51 +0700, Ady Wicaksono wrote: I have MySQL 5 installed on My Machine, But it could handle only max about 350 concurrent mysql connection Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always got Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in Is it mysql bug, incorrect Linux setting or what? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysql connection... on Linux Redhat 9
[client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] #thread_stack=128KB #set-variable = table-type=innodb default-table-type = innodb port = 3306 socket = /var/lib/mysql/mysql.sock max_connections= 910 max_connect_errors = 999 skip-locking skip-innodb_doublewrite key_buffer = 384M max_allowed_packet = 4M table_cache= 2049 sort_buffer_size = 12M read_buffer_size = 12M myisam_sort_buffer_size = 64M thread_cache = 48 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # Master Replication # -- #log-bin #server-id=1 # My Inno DB tables # - # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = innodb_data_file_path = /data1/ibdata1:2000M;/data1/ibdata2:2000M;/data1/ibdata3:2000M;/data1/ibdata4:2000M;/data2/ibdata5:2000M;/data2/ibdata6:2000M;/data2/ibdata7:2000M;/data2/ibdata8:2000M;/data3/ibdata9:2000M;/data3/ibdata10:2000M;/data3/ibdata11:2000M;/data3/ibdata12:2000M;/data4/ibdata13:2000M;/data4/ibdata14:2000M;/data4/ibdata15:2000M;/data4/ibdata16:2000M;/data5/ibdata17:2000M;/data5/ibdata18:2000M;/data5/ibdata19:2000M;/data5/ibdata20:2000M; # -- OLD --- innodb_log_group_home_dir = /data1 innodb_log_arch_dir = /data1 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1300M innodb_additional_mem_pool_size = 80M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 20 innodb_table_locks=0 transaction-isolation = REPEATABLE-READ Javier Armendáriz wrote: Can you tell us about your config parameters? Probably you must set up your max_connections, max_user_connections http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html Ady Wicaksono escribió: I have MySQL 5 installed on My Machine, But it could handle only max about 350 concurrent mysql connection Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always got Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in Is it mysql bug, incorrect Linux setting or what? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysql connection... on Linux Redhat 9
Make sense,
Kayra, this is my.cnf for my mysql
cut here ---
[client]
port= 3306
socket = /var/lib/mysql/mysql.sock
# The MySQL server
[mysqld]
#thread_stack=128KB
#set-variable = table-type=innodb
default-table-type = innodb
port = 3306
socket = /var/lib/mysql/mysql.sock
max_connections= 910
max_connect_errors = 999
skip-locking
skip-innodb_doublewrite
key_buffer = 384M
max_allowed_packet = 4M
table_cache= 2049
sort_buffer_size = 12M
read_buffer_size = 12M
myisam_sort_buffer_size = 64M
thread_cache = 48
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Master Replication
# --
#log-bin
#server-id=1
# My Inno DB tables
# -
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir =
innodb_data_file_path =
/data1/ibdata1:2000M;/data1/ibdata2:2000M;/data1/ibdata3:2000M;/data1/ibdata4:2000M;/data2/ibdata5:2000M;/data2/ibdata6:2000M;/data2/ibdata7:2000M;/data2/ibdata8:2000M;/data3/ibdata9:2000M;/data3/ibdata10:2000M;/data3/ibdata11:2000M;/data3/ibdata12:2000M;/data4/ibdata13:2000M;/data4/ibdata14:2000M;/data4/ibdata15:2000M;/data4/ibdata16:2000M;/data5/ibdata17:2000M;/data5/ibdata18:2000M;/data5/ibdata19:2000M;/data5/ibdata20:2000M;
# -- OLD ---
innodb_log_group_home_dir = /data1
innodb_log_arch_dir = /data1
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1300M
innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_table_locks=0
transaction-isolation = REPEATABLE-READ
cut here ---
File open limit:
# cat /proc/sys/fs/file-max
512000
Kernel usage:
# uname -a
Linux db 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686
i386 GNU/Linux
Redhat Release :
# cat /etc/redhat-release
Red Hat Linux release 9 (Shrike)
However the bad news is when i try c program
(http://forums.mysql.com/read.php?21,71414,71667#msg-71667)
#include stdio.h
#include stdlib.h
int main ( void )
{
size_t siz = 100 * 1024 * 1024 ;
size_t idx = 1 ;
void *ptr ;
for (;;)
{
ptr = malloc ( siz * idx );
if ( ! ptr )
break ;
free ( ptr );
idx ++ ;
}
printf ( Max malloc %d * 100 MB \n, idx - 1 );
return ( 0 );
}
It return only 20*100Mbyte for MySQL user ~ 2 Gbytes
Please give me clue on this issue
Thx
Kayra Otaner wrote:
It can be both Linux kernel setting and MySQL configuration issue.
It can be a kernel issue since:
- Although you have 4G on your machine your kernel may set to let only
2G to a user space process. You may need to upgrade your kernel to a
'hugemem' kernel which gives you either 3/1 or 4/4 (you need 8G mem for
this) memory split
- Number of open files (directly co related to number of open tables in
MySQL) setting in kernel can be lower than what your MySQL instance
needs.
It can be a MySQL configuration issue since :
- You may have incorrect settings in your my.cnf config file. Say if you
assigned 2G to innodb buffer and other buffers and your kernel lets you
to go upto 2G for user space process. For each connection coming MySQL
dedicates at least 128K (thread size) memory, so for 350 connection, you
end up using +43M memory which makes MySQL's total mem usage 2G + 43M.
This may be the reason why you're seeing can't create new thread.
Kayra Otaner
On Fri, 2006-02-24 at 20:51 +0700, Ady Wicaksono wrote:
I have MySQL 5 installed on My Machine,
But it could handle only max about 350 concurrent mysql connection
Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always
got Can't create a new thread (errno 12); if you are not out of
available memory, you can consult the manual for
a possible OS-dependent bug in
Is it mysql bug, incorrect Linux setting or what?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrent mysqlconnection... on Linux Redhat 9
Yes it's 32 bit System (Double XEON - Intel(R) Xeon(TM) CPU 3.00GHz)
Any solutions for this Joce?
[EMAIL PROTECTED] wrote:
Hi,
If it's a 32 bits machine, I don't think you can adress more than 2 GB for
1 MySQL process.
Jocelyn
Make sense,
Kayra, this is my.cnf for my mysql
cut here ---
[client]
port= 3306
socket = /var/lib/mysql/mysql.sock
# The MySQL server
[mysqld]
#thread_stack=128KB
#set-variable = table-type=innodb
default-table-type = innodb
port = 3306
socket = /var/lib/mysql/mysql.sock
max_connections= 910
max_connect_errors = 999
skip-locking
skip-innodb_doublewrite
key_buffer = 384M
max_allowed_packet = 4M
table_cache= 2049
sort_buffer_size = 12M
read_buffer_size = 12M
myisam_sort_buffer_size = 64M
thread_cache = 48
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Master Replication
# --
#log-bin
#server-id=1
# My Inno DB tables
# -
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir =
innodb_data_file_path =
/data1/ibdata1:2000M;/data1/ibdata2:2000M;/data1/ibdata3:2000M;/data1/ibdata4:2000M;/data2/ibdata5:2000M;/data2/ibdata6:2000M;/data2/ibdata7:2000M;/data2/ibdata8:2000M;/data3/ibdata9:2000M;/data3/ibdata10:2000M;/data3/ibdata11:2000M;/data3/ibdata12:2000M;/data4/ibdata13:2000M;/data4/ibdata14:2000M;/data4/ibdata15:2000M;/data4/ibdata16:2000M;/data5/ibdata17:2000M;/data5/ibdata18:2000M;/data5/ibdata19:2000M;/data5/ibdata20:2000M;
# -- OLD ---
innodb_log_group_home_dir = /data1
innodb_log_arch_dir = /data1
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1300M
innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_table_locks=0
transaction-isolation = REPEATABLE-READ
cut here ---
File open limit:
# cat /proc/sys/fs/file-max
512000
Kernel usage:
# uname -a
Linux db 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686
i386 GNU/Linux
Redhat Release :
# cat /etc/redhat-release
Red Hat Linux release 9 (Shrike)
However the bad news is when i try c program
(http://forums.mysql.com/read.php?21,71414,71667#msg-71667)
#include stdio.h
#include stdlib.h
int main ( void )
{
size_t siz = 100 * 1024 * 1024 ;
size_t idx = 1 ;
void *ptr ;
for (;;)
{
ptr = malloc ( siz * idx );
if ( ! ptr )
break ;
free ( ptr );
idx ++ ;
}
printf ( Max malloc %d * 100 MB \n, idx - 1 );
return ( 0 );
}
It return only 20*100Mbyte for MySQL user ~ 2 Gbytes
Please give me clue on this issue
Thx
Kayra Otaner wrote:
It can be both Linux kernel setting and MySQL configuration issue.
It can be a kernel issue since:
- Although you have 4G on your machine your kernel may set to let only
2G to a user space process. You may need to upgrade your kernel to a
'hugemem' kernel which gives you either 3/1 or 4/4 (you need 8G mem for
this) memory split
- Number of open files (directly co related to number of open tables in
MySQL) setting in kernel can be lower than what your MySQL instance
needs.
It can be a MySQL configuration issue since :
- You may have incorrect settings in your my.cnf config file. Say if you
assigned 2G to innodb buffer and other buffers and your kernel lets you
to go upto 2G for user space process. For each connection coming MySQL
dedicates at least 128K (thread size) memory, so for 350 connection, you
end up using +43M memory which makes MySQL's total mem usage 2G + 43M.
This may be the reason why you're seeing can't create new thread.
Kayra Otaner
On Fri, 2006-02-24 at 20:51 +0700, Ady Wicaksono wrote:
I have MySQL 5 installed on My Machine,
But it could handle only max about 350 concurrent mysql connection
Unbelieveable, i have 4 Gbytes, but on the next 351 connection i always
got Can't create a new thread (errno 12); if you are not out of
available memory, you can consult the manual for
a possible OS-dependent bug in
Is it mysql bug, incorrect Linux setting or what?
--
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]
RE: Number Searches
On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. You might want to look into MySQL functions INET_ATON() and INET_NTOA(): http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. This message has been scanned for viruses by McAfee's Groupshield. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
system interrupts
Forgive me if this is a linux and/or system/os-specific question. I am supporting a MySQL 4.0.x replication setup (2 slaves) that handle ~10K qps and ~5000 persistent connections each. It's obvious to me that system interrupts and context-switching will be high with 2000+ mysqld procs, but generally performance is very good. Occassionally though, the load average of one slave surges up to 10-20 and stays there. My load average (each box has two CPUs w/ HT enabled) is typically 2. Two questions: 1. Is this an artifact of simply having too many active processes vying for CPU time? 2. Are the 2nd and 3rd mysqld processes dispatch processes? such that having too many mysqld threads, cause these to thrash? I always assumed these two were the slave and i/o threads. By 2nd and 3rd, i mean the first two threads that are spawned by the root/ parent mysqld proc. Only 50% of real memory is utilized, so no swapping. I tried to strace the first few procs, but I'm not sure what I'm looking at there. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
On Fri, 2006-02-24 at 10:02 -0600, John Trammell wrote: On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. You might want to look into MySQL functions INET_ATON() and INET_NTOA(): http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html WOW, thank you! learn something every day. This is why I love the IT field! :) Thanks again. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
It's party time!
And we're handing out the presents... A 5-year celebration of our developer tool Database Workbench. The MySQL module was added recently, so are the MS SQL Server and Oracle modules. From this Monday until next Friday, it's a 75% discount! Check here: http://www.upscene.com/index.htm?dbw_party_promo.htm Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It's party time!
On Friday 24 February 2006 09:14, Martijn Tonies wrote: And we're handing out the presents... A 5-year celebration of our developer tool Database Workbench. The MySQL module was added recently, so are the MS SQL Server and Oracle modules. From this Monday until next Friday, it's a 75% discount! Check here: http://www.upscene.com/index.htm?dbw_party_promo.htm Looks pretty nice. I'll look at buying a copy as soon as there is a version for Linux. j- k- -- Joshua Kugler PGP Key: http://pgp.mit.edu/ CDE System Administrator ID 0xDB26D7CE http://distance.uaf.edu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 25% slower
On Mon, 2005-12-26 at 09:27 +0800, Chenzhou Cui wrote: Hi, Sorry for long delay with reply. I guess it is similar to http://bugs.mysql.com/bug.php?id=17229 The problem is basically confirmed and we're now working to find solution Dear Peter, Thanks very much for your concern. Answers to your questions are listed below. Here, I am facing another serious problem: Should I interrupt the Indexing work, which has been running for about 19 days? I don't know how long it will take to finish the job. The table contains 1,045,175,762 rows and there is 3GB memory in my server. There are two important fields: `RAdeg` and `DEdeg` in the table. The source data is ordered by `DEdeg`. It costed me 22 hours 14 min 37.27 sec to add a index on `DEdeg`. The `RAdeg` values are random. I don't know how many days will it cost to create the index on that field. Some information about my database and server are provided below. Happy New Year, Chenzhou === show processlist; +--+--+---+---+-+-+---++ | Id | User | Host | db| Command | Time| State | Info | +--+--+---+---+-+-+---++ | 1524 | cb | localhost | USNOB | Query | 1630664 | copy to tmp table | alter table `main` add index (`RAdeg`) | | 4486 | cb | localhost | USNOB | Query | 0 | NULL | show processlist | +--+--+---+---+-+-+---++ 2 rows in set (0.00 sec) show table status from USNOB; +--++-++++--+---+--+---++-+-++---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++++--+---+--+---++-+-++---+--++-+ | main | MyISAM | 10 | Fixed | 1045175762 |157 | 164092594634 | 44191571343572991 | 6073899008 | 0 | NULL | 2005-12-06 08:31:40 | 2005-12-07 06:41:01 | NULL | latin1_swedish_ci | NULL || | +--++-++++--+---+--+---++-+-++---+--++-+ #free total used free sharedbuffers cached Mem: 31164243110228 6196 0 412922528564 -/+ buffers/cache: 5403722576052 Swap: 1020088 20548 999540 Peter Zaitsev wrote: Hi, I'm not on the MySQL list so let me write to you directly. Are you using MyISAM Tables ? Yes. I am using the default format. How does SHOW CREATE TABLE looks like ? CREATE TABLE `main` ( `USNO_B1_0` char(12) NOT NULL default '', `Tycho_2` char(12) default NULL, `RAdeg` double(10,6) default NULL, `DEdeg` double(10,6) default NULL, `e_RAdeg` smallint(3) default NULL, `e_DEdeg` smallint(3) default NULL, `Epoch` float(6,1) default NULL, `pmRA` mediumint(6) default NULL, `pmDE` mediumint(6) default NULL, `muPr` tinyint(1) default NULL, `e_pmRA` smallint(3) default NULL, `e_pmDE` smallint(3) default NULL, `fit_RA` tinyint(1) default NULL, `fit_DE` tinyint(1) default NULL, `Ndet` tinyint(1) default NULL, `Flags` char(3) default NULL, `B1mag` float(5,2) default NULL, `B1C` tinyint(1) default NULL, `B1S` tinyint(1) default NULL, `B1f` smallint(3) default NULL, `B1s_g` tinyint(2) default NULL, `B1xi` float(6,2) default NULL, `B1eta` float(6,2) default NULL, `R1mag` float(5,2) default NULL, `R1C` tinyint(1) default NULL, `R1S` tinyint(1) default NULL, `R1f` smallint(3) default NULL, `R1s_g` tinyint(2) default NULL, `R1xi` float(6,2) default NULL, `R1eta` float(6,2) default NULL, `B2mag` float(5,2) default NULL, `B2C` tinyint(1) default NULL, `B2S` tinyint(1) default NULL, `B2f` smallint(3) default NULL, `B2s_g` tinyint(2) default NULL, `B2xi` float(6,2) default NULL, `B2eta` float(6,2) default NULL, `R2mag`
Problem revoking all privileges
I'm using MySQL 4.1 (I think... a late 4 anyway) on a Gentoo box. It's only a semi-live situation and no need for security just yet. After messing with privileges a bit, I decided to revoke whatever privileges are already applied and specifically grant only the ones I need (using a shell script). To revoke, the obvious choice was to use (if I can remember it correctly): mysql revoke ALL on *.* from '%' However, I'm getting an error about that privilege not applied, and I've read it's because ALL isn't an alias for all privileges, it's a privilege in itself. I think this is the reason I can't remove it! So, the question is, how can I revoke all the permissions that have been applied to a database (recursing into table/columns etc.) ? Many thanks, Steve :) -- View this message in context: http://www.nabble.com/Problem-revoking-all-privileges-t1183423.html#a3115859 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem revoking all privileges
Twinkletoes [EMAIL PROTECTED] wrote on 02/24/2006 04:18:18 PM: I'm using MySQL 4.1 (I think... a late 4 anyway) on a Gentoo box. It's only a semi-live situation and no need for security just yet. After messing with privileges a bit, I decided to revoke whatever privileges are already applied and specifically grant only the ones I need (using a shell script). To revoke, the obvious choice was to use (if I can remember it correctly): mysql revoke ALL on *.* from '%' However, I'm getting an error about that privilege not applied, and I've read it's because ALL isn't an alias for all privileges, it's a privilege in itself. I think this is the reason I can't remove it! So, the question is, how can I revoke all the permissions that have been applied to a database (recursing into table/columns etc.) ? Many thanks, Steve :) -- View this message in context: http://www.nabble.com/Problem- revoking-all-privileges-t1183423.html#a3115859 Sent from the MySQL - General forum at Nabble.com. If you had checked the manual, you would have seen that your statement's syntax is missing the name of the user whose privileges you are trying to revoke. REVOKE ALL ON *.* FROM 'username'@'%'; is what you are looking for. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problem revoking all privileges
I appreciate your attempt to get me to RTM, but I think you'll find the [EMAIL PROTECTED] syntax is correct. It works if I list a specific privilege to revoke. The problem is that ALL is itself a specific privilege. You can grant/revoke using it but it doesn't replace all the other specific privileges - it's just another one and happens to mean everything. I can grant/revoke ALL, but it leaves all those other specific privileges intact. As far as I see it, the problem lies with how to remove all those specific privileges easily. I can blindly list all privs to remove, but this is not ideal. I suppose I could export host_privs, do some jigery-pokery with sed/awk and formulate all the possible privs to remove, but that just seems too long-winded. I hope this explains the problem better, Thanks, Steve :) -- View this message in context: http://www.nabble.com/Problem-revoking-all-privileges-t1183423.html#a3117095 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions
CodeHeads wrote: On Thu, 2006-02-23 at 21:46 -0600, Walter Johnson wrote: #chown mysql:mysql /var/mysql-data #chmod 770 /var/mysql-data Try this, assuming the files are in there for the database. $chown -R mysql:mysql /var/mysql-data/* $chmod -R 770 /var/mysql-data/* Hope that helps. CodeHeads, Thanks for the suggestion. But, I am still denied access to /var/mysql-data and/usr/local/mysql-standard-5.0.18-osx10.4-powerpc/data. I get 'Permission Denied' when I try to change to either directory. The owner of both is 'mysql'. For /var/mysql-data with PathFinder under Contents, there is a faint '9' in parentheses, but I cannot see the databases (or tables and views). thanks -walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware experiences
Hi, I'm just looking for any experiences that people might want to offer on this subject. My project is in the process of selecting hardware to build out our system, and we are considering getting a few of the new Sun Fire T1000's to run mysql on. We are expecting that the project will have a steady load of ~50-100 concurrent threads (usually sleeping) with occasionally huge spikes (it's for a project that will have a pretty high public profile with national press releases, etc). Has anyone had much experience with this hardware under loads? How does it compare with, for example, an opteron system? Thanks in advance!
DBNav errors on post
Greetings, I have a problem I hope someone can help with: A simple application consist of: ADOConnect, ADODataSet, ADOQuery, DataSource1 (on a data form) The Grid and DBNavigator are on Form1 Conected to MySQL table. When I add a record, by change of record (Next, New, whatever) or clicking 'post', the program errors as follows Multi-step operation generated errors. Check each status value. Sometimes you can proceed, other times the application hangs. The post is successful. I seem unable to trace the error as no address is given. Does anyone have experience of this sort of bahaviour or can anyone offer some advice. Thanks. John Barrington. System Profile: Centaur Hauls Pent 2 lookalike Win98 4.1 Delphi 6 MySQL 5.0.18 community edition -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
email to db
Can anyone tell me if it is possible to send an email from outlook to a DB so it updates a record. I have a php tipping script hosted with a mysql DB but would like users to email their tips instead of loggin on the site. Mark
Re: Inner join with left join
Scott,
I think Shawn nailed it with
SELECT
p.id, p.prod_name,
SUM(IF(o.id IS NULL,0,oi.quantity)) AS Qty
FROM products AS p
INNER JOIN order_items AS oi ON (p.id = oi.product_id)
INNER JOIN orders as o ON (oi.order_id = o.id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59"
GROUP BY p.id, p.prod_name
(you can often speed up a join by moving conditions from the Where
clause to the unnamed side of an Inner or Left Join).
PB
-
Scott Haneda wrote:
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count. This tells me those products have not been ordered ever, but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: email to db
Hi Mark, You will need to setup a processing script on your mailer that will do that for you. Your ISP maybe able to set this up for you. Most mailers use the aliases file for that. A quick google found this as an example, yes it is talking to Oracle but the principles are exactly the same. http://philip.greenspun.com/doc/email-handler Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: Saturday, 25 February 2006 1:19 PM To: mysql@lists.mysql.com Subject: email to db Can anyone tell me if it is possible to send an email from outlook to a DB so it updates a record. I have a php tipping script hosted with a mysql DB but would like users to email their tips instead of loggin on the site. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE and BIT columns
Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into tables that have BIT(8) columns. No matter what format I use, the result is not what I expect (see example below.) Anyone know how to properly format the data for loading into a BIT column? Thanks! $ cat /tmp/bit_test.txt 01010101 2 b'010' b\'010\' 0x2 02 mysql create table bit_test (b bit(8)); Query OK, 0 rows affected (0.01 sec) mysql load data infile '/tmp/bit_test.txt' into table bit_test; Query OK, 6 rows affected, 3 warnings (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 3 mysql select bin(b+0) from bit_test; +--+ | bin(b+0) | +--+ | | | 110010 | | | | | | | | | +--+ 6 rows in set (0.00 sec) Thanks!
Re: email to db
On Sat, 2006-02-25 at 10:49 +0800, Mark wrote: Can anyone tell me if it is possible to send an email from outlook to a DB so it updates a record. I have a php tipping script hosted with a mysql DB but would like users to email their tips instead of loggin on the site. Mark Wouldn't be easier using a PHP form, then send it to a database?? -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
