Re: Is it a bug or my mistake in server configuration?
On Mon, Nov 10, 2008 at 08:23:24PM +0300, Alexey Vlasov wrote:
DBI connect('database,...)
failed: 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 at ...
I just thought, all this can be a result of dumping of one big base
(~6G). But it's still not clear what memory limit was reached.
I still continue observing the situation.
--
BRGDS. Alexey Vlasov.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it a bug or my mistake in server configuration?
Hi.
One client from my shared hosting periodically informs me about an
error:
DBI connect('database,...)
failed: 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 at ...
There's nothing suspicious in the MySQL error-log.
# free -m
total usedfree sharedbuffers cached
mem:16039 15794 245 0 2109 6935
-/+buffs/cache: 6748 9290
Swap:28615 502123594
my.cnf:
flush_time = 1800
set-variable = long_query_time=10
set-variable = back_log=1024
set-variable = max_connect_errors=1000
set-variable = max_connections=64
set-variable = connect_timeout=20
set-variable = wait_timeout=600
set-variable = interactive_timeout=600
set-variable = table_cache=1000
set-variable = thread_cache_size=16
set-variable = max_tmp_tables=8192
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=256M
set-variable = max_join_size=5000
set-variable = key_buffer_size=512M
set-variable = read_buffer_size=128K
set-variable = read_rnd_buffer_size=64K
set-variable = sort_buffer=128M
set-variable = join_buffer_size=64M
set-variable = net_buffer_length=64K
set-variable = query_cache_type=1
set-variable = query_cache_size=256M
set-variable = max_allowed_packet=16M
set-variable = ft_min_word_len=3
# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) 2097152
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 143360
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size(512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 143360
virtual memory (kbytes, -v) 4194304
file locks (-x) unlimited
# ps
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
4728 mysql 20 0 2045m 1.0g 5620 S0 6.3 2602:15 mysqld
# pstree | grep mysql
|-mysqld---29*[{mysqld}]
# mysql --version
mysql Ver 14.12 Distrib 5.0.54, for pc-linux-gnu (x86_64) using
readline 5.2
# uname -a
Linux 2.6.24 #4 SMP Fri Feb 29 20:10:01 MSK 2008
x86_64 Intel(R) Xeon(R) CPU E5345 @ 2.33GHz GenuineIntel GNU/Linux
I would like to know against what limit rests MySQL and whose mistake it
really is, of Perl mysql-client, mysqld or someone else?
--
BRGDS. Alexey Vlasov.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it a bug or my mistake in server configuration?
I've been having the same trouble in a Xen virtual machine. After about
an hour and a half, mysql will be consuming 100% of cpu. There is
nothing wrong with the tables. I'm assuming its a dynamic vs. fix
amount of memory available to mysql. I'm guaranteed x amount of ram,
but that might get reduced due to server load. I'm assuming mysql
doesn't like having ram taken away from it and get into a tizzy about it.
I've been forced to restart mysql hourly in order to get smooth operation.
--curtis
Alexey Vlasov wrote:
Hi.
One client from my shared hosting periodically informs me about an
error:
DBI connect('database,...)
failed: 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 at ...
There's nothing suspicious in the MySQL error-log.
# free -m
total usedfree sharedbuffers cached
mem:16039 15794 245 0 2109 6935
-/+buffs/cache: 6748 9290
Swap:28615 502123594
my.cnf:
flush_time = 1800
set-variable = long_query_time=10
set-variable = back_log=1024
set-variable = max_connect_errors=1000
set-variable = max_connections=64
set-variable = connect_timeout=20
set-variable = wait_timeout=600
set-variable = interactive_timeout=600
set-variable = table_cache=1000
set-variable = thread_cache_size=16
set-variable = max_tmp_tables=8192
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=256M
set-variable = max_join_size=5000
set-variable = key_buffer_size=512M
set-variable = read_buffer_size=128K
set-variable = read_rnd_buffer_size=64K
set-variable = sort_buffer=128M
set-variable = join_buffer_size=64M
set-variable = net_buffer_length=64K
set-variable = query_cache_type=1
set-variable = query_cache_size=256M
set-variable = max_allowed_packet=16M
set-variable = ft_min_word_len=3
# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) 2097152
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 143360
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size(512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 143360
virtual memory (kbytes, -v) 4194304
file locks (-x) unlimited
# ps
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
4728 mysql 20 0 2045m 1.0g 5620 S0 6.3 2602:15 mysqld
# pstree | grep mysql
|-mysqld---29*[{mysqld}]
# mysql --version
mysql Ver 14.12 Distrib 5.0.54, for pc-linux-gnu (x86_64) using
readline 5.2
# uname -a
Linux 2.6.24 #4 SMP Fri Feb 29 20:10:01 MSK 2008
x86_64 Intel(R) Xeon(R) CPU E5345 @ 2.33GHz GenuineIntel GNU/Linux
I would like to know against what limit rests MySQL and whose mistake it
really is, of Perl mysql-client, mysqld or someone else?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Is it a bug or my mistake in server configuration?]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( - Original-Nachricht Betreff: Re: Is it a bug or my mistake in server configuration? Datum: Mon, 10 Nov 2008 18:36:34 +0100 Von: Uwe Kiewel [EMAIL PROTECTED] An: Alexey Vlasov [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] Alexey Vlasov schrieb: open files (-n) 1024 just a shot to the blue: can you count your open files with lsof? HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHG8AAoJEEJXG7BUuynnycwP/1SB9O7u8toxRYcRbctfwbdV EnycPRk9k2meEEWEFNCqr/lQr3qzZL9BEj8HrMUnSqOSgkKKFS/XeqkIsUK+BZzp 2lV6zjD+wAtlM++O5BYYlQZ1JRgtk6rEkf+qWYQvRqbGOuQsSZ9UBqb7trUhbumF 7GsygytsUhT9aDmOlHoanlQ0VDQl+QvxKMVgtyu78s3CR7tMihSVxGdfC0A5Ws02 mlGdhxs/BgQsKuuSjLFar8Ok+Ad8g+lYhz5a2Tf8JTidYOBx1SAJoXC4R5MpRipp AUwfcMe3w7VaNZ47L+Sh20hYyQpYDCNiyBX371uMWE7Yizbm6ddDd1JZ3jkv8NdL N/9uLLLu14OIkYbC5TYJT84F9WlmvDtQiLTIl71LYjlPQdlEHsPs65+I5Pdmvt5V 0MfC2Weob0JUmzNBV/x03XfSUbEvXaScTdJa+DEV5+QNEnxxlRP45QVow+kfLMkQ gB7jvFfIkpjhUsfkaofH83f8wBGXT1AD257blb8X/M1lkLpWjh81xsBFDmfKacXt xu4St/VW59GESPbQaIkU5lcm/d/e93wY3xNIYtxnPsZ50pGv6PCszdI8N7Uu5UHQ KrYXO8G8Bi4unI9i1bfPSJUyfFErWGesiqYopydt5idvyfPJA9E2aKtqTXqHfDc9 m0XjbWeBv2JXC8R04byE =vQC5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it a bug or my mistake in server configuration?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Curtis Maurand schrieb: I've been having the same trouble in a Xen virtual machine. After about an hour and a half, mysql will be consuming 100% of cpu. There is nothing wrong with the tables. I'm assuming its a dynamic vs. fix amount of memory available to mysql. I'm guaranteed x amount of ram, but that might get reduced due to server load. I'm assuming mysql doesn't like having ram taken away from it and get into a tizzy about it. I've been forced to restart mysql hourly in order to get smooth operation. You are dealing with the impact, not with the reason. It can only be a workaround. Did you have a look at you machine regarding paging activity? Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHNwAAoJEEJXG7BUuynnZLwP/j2DaZ4ffqvQDXJm9xhpR8RR IX/X4eycsa42PAGCmvRkD10WgxTzTdIBA8euNX56sY8nS9GWanUzrBJ2fzmb6BKF Ype1HnyQfBjktTlAnb/WMZ0GfwTyL9+xPcD22lpaDSp4wlPvCq11TWMBDHC7HO3s 7VIMz44FTrgeiuNFOCv5ZPLRY1yIonJm7aJpUOdJE329zQmkkECOi7E0dQTGH9hi F5MWaymhIG4OrULiVbo+rBTzAhzHRYE7pv7y/q07vVLLpJ4mIM/9gBVS+p5ovrZE yskucJyvoMx9YUNx22iSUp5NhoA67RS2Ov5y1JdeVeKzFGIsxRziYx2BNXWJo+zl iY8XI5bcB2rx9wbmDqA413MyM/EdLlNmAUbwc4QnzIQLf7+cJ2u93FiZ0gtsNjWb d/r+thxO6EpOlnrkt9vn4pe9Rjp/7KYAOSTJz+FG2CP9fkpnrArMSoegDIZFxE6T s3NLmD5pPMdl1piwL5488F4f+AYuH5UNiZTOvbMr6NnQ9WL5LB1mDjRF+4/+BiW7 9nyfr7XcIdMWlED9dNR0fXU9rnbhi/ZOJhJbMFMKJaPPmZW6+cd7G5yqBe3UOGAd 3JD2IVPIz2NkpI1kGo4XnqxdAZS2/MXavDTVB+1tMOjMSkkJN5bkwRitZai9rb3Z Ca2df0HdK0T7tIC1Kxmq =5Xsj -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( Hint - use Reply to All - it's not specific to this mailing list. :-) Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
Hit 'Reply to All' instead of reply. On Mon, 2008-11-10 at 18:39 +0100, Uwe Kiewel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( - Original-Nachricht Betreff: Re: Is it a bug or my mistake in server configuration? Datum: Mon, 10 Nov 2008 18:36:34 +0100 Von: Uwe Kiewel [EMAIL PROTECTED] An: Alexey Vlasov [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] Alexey Vlasov schrieb: open files (-n) 1024 just a shot to the blue: can you count your open files with lsof? HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHG8AAoJEEJXG7BUuynnycwP/1SB9O7u8toxRYcRbctfwbdV EnycPRk9k2meEEWEFNCqr/lQr3qzZL9BEj8HrMUnSqOSgkKKFS/XeqkIsUK+BZzp 2lV6zjD+wAtlM++O5BYYlQZ1JRgtk6rEkf+qWYQvRqbGOuQsSZ9UBqb7trUhbumF 7GsygytsUhT9aDmOlHoanlQ0VDQl+QvxKMVgtyu78s3CR7tMihSVxGdfC0A5Ws02 mlGdhxs/BgQsKuuSjLFar8Ok+Ad8g+lYhz5a2Tf8JTidYOBx1SAJoXC4R5MpRipp AUwfcMe3w7VaNZ47L+Sh20hYyQpYDCNiyBX371uMWE7Yizbm6ddDd1JZ3jkv8NdL N/9uLLLu14OIkYbC5TYJT84F9WlmvDtQiLTIl71LYjlPQdlEHsPs65+I5Pdmvt5V 0MfC2Weob0JUmzNBV/x03XfSUbEvXaScTdJa+DEV5+QNEnxxlRP45QVow+kfLMkQ gB7jvFfIkpjhUsfkaofH83f8wBGXT1AD257blb8X/M1lkLpWjh81xsBFDmfKacXt xu4St/VW59GESPbQaIkU5lcm/d/e93wY3xNIYtxnPsZ50pGv6PCszdI8N7Uu5UHQ KrYXO8G8Bi4unI9i1bfPSJUyfFErWGesiqYopydt5idvyfPJA9E2aKtqTXqHfDc9 m0XjbWeBv2JXC8R04byE =vQC5 -END PGP SIGNATURE-
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andy Shellam schrieb: This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( Hint - use Reply to All - it's not specific to this mailing list. :-) It dosn't make sense to the a reply to the list and the sender. The described procedure works fine at other mailing lists... -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHZ5AAoJEEJXG7BUuynnwFEQAJYxmcj886VULbzqY11cIm27 gCBC1B5eJetycUbFBnJhJew7yes8dbgHXIm6xpY79A0pX0O4+Gf4cyaYI42y2MHV K3DDaWbZkWJRnpihtW3We6k9CX5u+tufx/eejx+o2Yq+uPpmU/BeG7pC/r/YDkMu kZ/MLMZSRswD/mmRNHWixu2TT6iYEdrreeSmV1LGCwUirqaG1fDx/jxc6HvkdcmC XofeipmjPLdOMZ+Mr2xTYwo5jWXekx1Ynp6vV8PJacFgl/Hw8/LJk0FU+Nt8J5Kv 1P2UGqQ3bxGaTyiGNyqK3ZQIDUw1d1tHWP7wTEkNYs18GlXaX7xtACtqlZUG3zxN qtfHtT5Ksvn//7WoiNG6t4wY56pkgIiitOhwVmBydqok5zw7uHaRgpiykc8jEV5N rh7CtuuPPpVtauU4gI5lLvDNF1MQ+53srNeLx63UNTIABsu0IPtaISNDuspg6o2V Fq5nxWi9K07GdwAPRjoarZ+wVNtG8xaO90tz8EaMUqLu1fLvcU3168j4oYfQ/oR7 ePGTUtzTP26uTMqZ/O5uPn+eirz0j2s4P28EvwSGflYZPKAyi3AfNlDeGwB4XWbP 2VUKPPLHVqvMpwFKUTlfCrrY2bPl6ESEycDSD3DROW5uZbZlc0hMJ1NAK5XQQVTR cDvOtAG4Ku1NO3tE+P22 =gf+z -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
Make sure the script or application querying the database is closing connections when it has finished with them. Theres an awful lot of badly coded scripts out there, especially php scripts, which keep the connections to the server open and eating away at server resources long after they could have released the thread back to the server. On Mon, 2008-11-10 at 18:39 +0100, Uwe Kiewel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( - Original-Nachricht Betreff: Re: Is it a bug or my mistake in server configuration? Datum: Mon, 10 Nov 2008 18:36:34 +0100 Von: Uwe Kiewel [EMAIL PROTECTED] An: Alexey Vlasov [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] Alexey Vlasov schrieb: open files (-n) 1024 just a shot to the blue: can you count your open files with lsof? HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHG8AAoJEEJXG7BUuynnycwP/1SB9O7u8toxRYcRbctfwbdV EnycPRk9k2meEEWEFNCqr/lQr3qzZL9BEj8HrMUnSqOSgkKKFS/XeqkIsUK+BZzp 2lV6zjD+wAtlM++O5BYYlQZ1JRgtk6rEkf+qWYQvRqbGOuQsSZ9UBqb7trUhbumF 7GsygytsUhT9aDmOlHoanlQ0VDQl+QvxKMVgtyu78s3CR7tMihSVxGdfC0A5Ws02 mlGdhxs/BgQsKuuSjLFar8Ok+Ad8g+lYhz5a2Tf8JTidYOBx1SAJoXC4R5MpRipp AUwfcMe3w7VaNZ47L+Sh20hYyQpYDCNiyBX371uMWE7Yizbm6ddDd1JZ3jkv8NdL N/9uLLLu14OIkYbC5TYJT84F9WlmvDtQiLTIl71LYjlPQdlEHsPs65+I5Pdmvt5V 0MfC2Weob0JUmzNBV/x03XfSUbEvXaScTdJa+DEV5+QNEnxxlRP45QVow+kfLMkQ gB7jvFfIkpjhUsfkaofH83f8wBGXT1AD257blb8X/M1lkLpWjh81xsBFDmfKacXt xu4St/VW59GESPbQaIkU5lcm/d/e93wY3xNIYtxnPsZ50pGv6PCszdI8N7Uu5UHQ KrYXO8G8Bi4unI9i1bfPSJUyfFErWGesiqYopydt5idvyfPJA9E2aKtqTXqHfDc9 m0XjbWeBv2JXC8R04byE =vQC5 -END PGP SIGNATURE-
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Daisley schrieb: Hit 'Reply to All' instead of reply. Is is okay for you the have your answers twice? If I post to a list, I read that list. So there is no need to have the ansers at the list and in my inbox Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJGHcCAAoJEEJXG7BUuynnKeIQAM0rb4PPOwIxwYClPNMUvTPN chqL8Lpo9FmRdWaJof5TqguruXDhPyK8xLeH+XBKPa7d952BNqRCFdtBLIHsmtQk GE7Z51E5puFWlKfZA9QC909juVNMPcKleTO0OCyHmhVX5NtazdVnrB4NR2RB0wFQ IIKiwBOH0id+YipyT1S3wOy8oo1DkA46u5NX4AIp66J3JDl642k+tvyQyGP05fzq 47PuCRI0FK7e6+xUHj3eNScJQXNkr4hJWkGi92suFgPGchKYBjWCBDbJUoNNl/rb QcpHOmC5TfUw1CCveb7CM6k8r10RNC02Q8BJTWKeYsc9PfR+lOdWLS7C/+nZjBr2 1rwZTYblAqOtzbokVvtQsPVol7b+hCiKZ5y8CGC+gmrr9GF0z/YHnspHnKiIDTS3 4IHMvVtZpjJu31S9Ywrsp7urQ947w32yM0vsCF2gxhCMV8gdZPoFBEwAyjx2mKDP JDEQ8eF5YQ18QEAEVoGgFHhQEsgup6OmbbQi415116pEiFCbJURkQLss9Qq7Cay4 2UHLuwkus6oEiC8Go1OPv67ZQ++0+lB9cd7GLZdBL1vwHLtzPRExBYLFp7ygP4yh +r6TqwmoUUNYwIRdFLcOcE8jMhqlAlwONmASWgkVsV8UzopSEKWjcI8EYoravqH1 3iA04LVzUbVnttppgbdB =RNS0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it a bug or my mistake in server configuration?
On Mon, Nov 10, 2008 at 06:36:34PM +0100, Uwe Kiewel wrote: Alexey Vlasov schrieb: open files (-n) 1024 just a shot to the blue: can you count your open files with lsof? # lsof -u mysql | wc -l 1719 I doubt that the problem is in that otherwise every second user would complain of some errors, and now only 1 from about 2000 users of this server complains. -- BRGDS. Alexey Vlasov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Windows Server Configuration
James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- 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: Re: Re: Windows Server Configuration
Sorry, I think I had James and David backwards there! On 8/25/06, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- 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: Windows Server Configuration
Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
Just noticed that you said partitions. I am assuming that you meat multiple drives in a raid array. Bill David Lazo said: Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- 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]
Windows Server Configuration
We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
I second what James recommends re: spindles and RAID 10. Better than RAID 5 for live data in my opinion; RAID 5 is decent for archival storage. You've got a pretty decent setup there otherwise - 4 CPU cores, 8 GB RAM - and you want to make sure your disks can keep things fed. As far as splitting things up: a general recommendation is to put logging (replication logging that is, not the error log necessarily) onto its own partition, ideally its own disks. Also consider putting MySQL's temp space on its own partition, ideally its own disks. Of course suddenly you're looking at a lot of disks if you really go whole-hog... The optimization section in the online manual is pretty decent, though some of the numbers are a bit dated (I saw one note this morning that said if you have at least 256 MB RAM...) Also Jeremy Zawodny's book High Performance MySQL is a good read, both in terms of optimizing your SQL/data strcuture and in choosing abnd setting up your hardware. (Third time today I've plugged that book - I don't own stock or anything, really) Dan On 8/22/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- 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: Windows Server Configuration
Thanks for all the recommendations. On 8/22/06 1:11 PM, Dan Buettner [EMAIL PROTECTED] wrote: I second what James recommends re: spindles and RAID 10. Better than RAID 5 for live data in my opinion; RAID 5 is decent for archival storage. You've got a pretty decent setup there otherwise - 4 CPU cores, 8 GB RAM - and you want to make sure your disks can keep things fed. As far as splitting things up: a general recommendation is to put logging (replication logging that is, not the error log necessarily) onto its own partition, ideally its own disks. Also consider putting MySQL's temp space on its own partition, ideally its own disks. Of course suddenly you're looking at a lot of disks if you really go whole-hog... The optimization section in the online manual is pretty decent, though some of the numbers are a bit dated (I saw one note this morning that said if you have at least 256 MB RAM...) Also Jeremy Zawodny's book High Performance MySQL is a good read, both in terms of optimizing your SQL/data strcuture and in choosing abnd setting up your hardware. (Third time today I've plugged that book - I don't own stock or anything, really) Dan On 8/22/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- 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: Suggestions on db server configuration - Replication load balancing or Clustering??
Ed Pauley II wrote: I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? - check out http://www.ultramonkey.org/3/ It's not the perfect solution to your problem(s), but it might help. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggestions on db server configuration - Replication load balancing or Clustering??
I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Configuration Help
Greetings, I am running MySQL (version 4.0.15 max) database on Linux (RH9) box. This linux box is a dedicated database server with following h/w configuration: CPU: 2 * 2.4 Ghz Xeon Processor, 512 K 533 FSB Ram :6GB Hdd:36GB * 5 raid config Typically, this database has less number of client connections but those who connect generally run highly analytical stuff off the database. Also the database size is pretty huge (around 40 gb). After reading though the manuals, specifically some of the performance enhancement tips, I build the my.cnf as show below. Now on to the real question, Do you MySQL gurus think that given all the details, Is there anyway to enhance the my.cnf file for better performance/speed ? Your kind help would be greatly appreciated. Best Regards Manoj --- my.cnf file - [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=512M table_cache=512 sort_buffer=2M read_buffer_size=4M read_rnd_buffer_size=4M max_connection=100 max_allowed_packet= 1M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format # innodb_options innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata/ibdata1:3G;ibdata/ibdata2:3G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_log_buffer_size=8M innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT --- End of my.cnf file - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server Configuration Help
In your my.cnf there is no: Query_cache_size - http://dev.mysql.com/doc/mysql/en/Query_Cache_Configuration.html Thread_cache_size - http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Marc. -Message d'origine- De : ManojSW [mailto:[EMAIL PROTECTED] Envoyé : lundi 6 décembre 2004 09:21 À : [EMAIL PROTECTED] Objet : Server Configuration Help Greetings, I am running MySQL (version 4.0.15 max) database on Linux (RH9) box. This linux box is a dedicated database server with following h/w configuration: CPU: 2 * 2.4 Ghz Xeon Processor, 512 K 533 FSB Ram :6GB Hdd:36GB * 5 raid config Typically, this database has less number of client connections but those who connect generally run highly analytical stuff off the database. Also the database size is pretty huge (around 40 gb). After reading though the manuals, specifically some of the performance enhancement tips, I build the my.cnf as show below. Now on to the real question, Do you MySQL gurus think that given all the details, Is there anyway to enhance the my.cnf file for better performance/speed ? Your kind help would be greatly appreciated. Best Regards Manoj --- my.cnf file - [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=512M table_cache=512 sort_buffer=2M read_buffer_size=4M read_rnd_buffer_size=4M max_connection=100 max_allowed_packet= 1M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format # innodb_options innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata/ibdata1:3G;ibdata/ibdata2:3G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_log_buffer_size=8M innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT --- End of my.cnf file - -- 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]
Server Configuration Help
Greetings, I am running MySQL (version 4.0.15 max) database on Linux (RH9) box. This linux box is a dedicated database server with following h/w configuration: CPU: 2 * 2.4 Ghz Xeon Processor, 512 K 533 FSB Ram :6GB Hdd:36GB * 5 raid config Typically, this database has less number of client connections but those who connect generally run highly analytical stuff off the database. Also the database size is pretty huge (around 40 gb). After reading though the manuals, specifically some of the performance enhancement tips, I build the my.cnf as show below. Now on to the real question, Do you MySQL gurus think that given all the details, Is there anyway to enhance the my.cnf file for better performance/speed ? Your kind help would be greatly appreciated. Best Regards Manoj --- my.cnf file - [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=512M table_cache=512 sort_buffer=2M read_buffer_size=4M read_rnd_buffer_size=4M max_connection=100 max_allowed_packet= 1M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format # innodb_options innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata/ibdata1:3G;ibdata/ibdata2:3G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_log_buffer_size=8M innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT --- End of my.cnf file -
Server Configuration
Hi, We are about to build some new database servers and I have some questions which I'd like some advice on. The machines we are building have 4 Xeon 2GHz CPU's, 4 x 32GB SCSI disk using RAID 1+0 (so thats 64GB of storage) and 4 Gig of RAM. The OS will be Redhat 7.3. Other than the mysql database all other tables will be InnoDB, there are only 8 tables with a few of them having millions of records. The data stored will be a cache of third party information for my application to use so there will be many read/writes. Now my questions are : Which file system would you recommend for this ? I've seen many recommendations for ReiserFS but have no experience of it. Should I use a pre-compiled binary or should I compile one myself ? Should the 2 disks for storage be split up into partitions or just 1 large partition per disk ? Is there anything else I should consider when configuring the machines that affect the performance ? I'm quite happy with configuration of the my.cnf for an InnoDB setup and also taking into account of the Linux GLIBC 2GB memory bug. Many Thanks. Marvin __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Configuration
On Wed, Jun 09, 2004 at 01:45:49PM +0100, Marvin Wright wrote: Hi, We are about to build some new database servers and I have some questions which I'd like some advice on. The machines we are building have 4 Xeon 2GHz CPU's, 4 x 32GB SCSI disk using RAID 1+0 (so thats 64GB of storage) and 4 Gig of RAM. Consider RAID10: http://www.acnc.com/04_01_10.html As opposed to 0+1: http://www.acnc.com/04_01_0_1.html You'd think they're the same but they're subtly different leading to very different characteristics. Note the Recommended Application for 10 is a database server. Which file system would you recommend for this ? I've seen many recommendations for ReiserFS but have no experience of it. I use xfs on my Debian MySQL server. Specs are pretty similar, two 2.8GHz Xeons, 4 36GB U320 drives (in RAID10, which is superb), and 4GB of memory. My /db has 418 inodes used, and 16G used out of the 30G on it; making for quite a large average filesize. To be honest, the filesystem isn't really my bottleneck - with 4GB, MySQL and the OS have tons of caching room, and the filesystem is doing maybe 40k/s of sustained activity with the odd burst of real work. You'll probably like to at least check xfs out. Should I use a pre-compiled binary or should I compile one myself ? I found it makes so little difference it's not worth worrying about. I use the apt package for ease of upgrade and dependencies. Should the 2 disks for storage be split up into partitions or just 1 large partition per disk ? Always partition. You get to choose which filesystem suits each partition best. My preference; ext3 for /, xfs for /db, ext2 for /dump. / does very little work but I want it consistant so ext3 is fine. /dump stores backups (which are mirrored elsewhere) and I don't care if its trashed, but I want it fast when I am using it. Is there anything else I should consider when configuring the machines that affect the performance ? Linux 2.6 probably isn't in RedHat 7.3 base, but you'll want to try it. It's faster than 2.4. My configuration was quite happy doing 35,000 selects per second (with super-smack, an arbitrary benchmarking tool); with 2.4 it was a few thousand lower. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tomcat+MySQL. Intermitent DbcpException: Server configuration - Now pool exhausted
Hello! Sorry not to have given any signs of life... Thank you for your answer. Changing the number of connections to 100 solved the problem of the Server configuration error, but I've gone back to getting java.sql.SQLException: DBCP could not obtain an idle db connection, pool exhausted (whole exception follows). I thought the configuration parameter namelogAbandoned/name valuetrue/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter would free and log abandoned connections, but it doesn't seem to do it. Anyway, I have checked over all my result sets, statements and connections and I think they are all closed properly... So I'm still a bit stuck here... Some other person in this list, suggested that there might be some problems with the DBCP, could it be so? Any other suggestions? Any help really appreciated. Monica 2003-08-20 04:04:57 ApplicationDispatcher[] Servlet.service() for servlet StructureServlet threw exception javax.servlet.ServletException: Error initialising boxes at com.ah.auk.core.BoxManager.configureBoxes(BoxManager.java:72) at com.ah.auk.structure.StructureServlet.doGet (StructureServlet.java:88) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at org.apache.catalina.core.ApplicationDispatcher.invoke(Unknown Source) at org.apache.catalina.core.ApplicationDispatcher.doForward(Unknown Source) at org.apache.catalina.core.ApplicationDispatcher.forward(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.custom(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.status(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.coyote.tomcat4.CoyoteAdapter.service (CoyoteAdapter.java:223) at org.apache.jk.server.JkCoyoteHandler.invoke (JkCoyoteHandler.java:261) at org.apache.jk.common.HandlerRequest.invoke (HandlerRequest.java:360) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:604) at org.apache.jk.common.ChannelSocket.processConnection (ChannelSocket.java:562) at org.apache.jk.common.SocketConnection.runIt (ChannelSocket.java:679) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run (ThreadPool.java:619) at java.lang.Thread.run(Thread.java:534) - Root Cause - com.ah.auk.core.BoxException: java.sql.SQLException: DBCP could not obtain an idle db connection, pool exhausted at com.ah.auk.box.CountyListBox.configure(CountyListBox.java:80) at com.ah.auk.core.BoxManager.configureBoxes(BoxManager.java:41) at com.ah.auk.structure.StructureServlet.doGet (StructureServlet.java:88) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at org.apache.catalina.core.ApplicationDispatcher.invoke(Unknown Source) at org.apache.catalina.core.ApplicationDispatcher.doForward(Unknown Source) at org.apache.catalina.core.ApplicationDispatcher.forward(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.custom(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.status(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.coyote.tomcat4.CoyoteAdapter.service (CoyoteAdapter.java:223) at org.apache.jk.server.JkCoyoteHandler.invoke (JkCoyoteHandler.java:261) at org.apache.jk.common.HandlerRequest.invoke (HandlerRequest.java:360
Tomcat+MySQL. Intermitent DbcpException: Server configuration denies access to data source
Hi! I'm not sure if this is the most adequate mySQL list for this post. If not, please indicat me which one I should use... I'm using Tomcat 4.1.24 with Apache 2 and MySQL 4.0.13. I have the mysql- connector-java-2.0.14-bin.jar in commons/lib. The application runs normally, and usually about once or twice a day I get this exception org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Server configuration denies access to data source. Once the exception occurs, it happens for every request and Tomcat needs restarting. Before getting this exception, I used to run out of connections, and therefore I added to the server.xml parameter namelogAbandoned/name valuetrue/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter I guess it could be related... I include the exception and my server.xml file. Any help appreciated. Monica 2003-08-07 15:55:02 StandardWrapperValve[StructureServlet]: Servlet.service () for servlet StructureServlet threw exception org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Server configuration denies access to data source at org.apache.commons.dbcp.DriverConnectionFactory.createConnection (DriverConnectionFactory.java:85) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject (PoolableConnectionFactory.java:184) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject (GenericObjectPool.java) at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject (AbandonedObjectPool.java:117) at org.apache.commons.dbcp.PoolingDataSource.getConnection (PoolingDataSource.java:110) at org.apache.commons.dbcp.BasicDataSource.getConnection (BasicDataSource.java:312) at com.ah.auk.db.DBUtil.getDBConnection(DBUtil.java:54) at com.ah.auk.db.DB.checkDBCon(DB.java:34) at com.ah.auk.db.HotelDBReader.getHotelsInGeoEntry (HotelDBReader.java:64) at com.ah.auk.delegates.CountyHelper.getHotelsPerCounty (CountyHelper.java:100) at com.ah.auk.box.CountyListBox.getCounties(CountyListBox.java:118) at com.ah.auk.box.CountyListBox.configure(CountyListBox.java:63) at com.ah.auk.core.BoxManager.configureBoxes(BoxManager.java:41) at com.ah.auk.structure.StructureServlet.doGet (StructureServlet.java:74) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at javax.servlet.http.HttpServlet.service(HttpServlet.java) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown Source) at com.ah.auk.context.ContextFilter.doFilter(ContextFilter.java:158) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown Source) at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardContextValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardContext.invoke(Unknown Source) at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok eNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.coyote.tomcat4.CoyoteAdapter.service (CoyoteAdapter.java:223) at org.apache.jk.server.JkCoyoteHandler.invoke (JkCoyoteHandler.java:261) at org.apache.jk.common.HandlerRequest.invoke (HandlerRequest.java:360
Re: Tomcat+MySQL. Intermitent DbcpException: Server configuration denies access to data source
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Monica Ferrero wrote: Hi! I'm not sure if this is the most adequate mySQL list for this post. If not, please indicat me which one I should use... I'm using Tomcat 4.1.24 with Apache 2 and MySQL 4.0.13. I have the mysql- connector-java-2.0.14-bin.jar in commons/lib. The application runs normally, and usually about once or twice a day I get this exception org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Server configuration denies access to data source. Once the exception occurs, it happens for every request and Tomcat needs restarting. Before getting this exception, I used to run out of connections, and therefore I added to the server.xml parameter namelogAbandoned/name valuetrue/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter I guess it could be related... I include the exception and my server.xml file. Any help appreciated. Monica [snip] ResourceParams name=jdbc/allukmasterDB parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namemaxActive/name value500/value /parameter parameter namemaxIdle/name value30/value /parameter [snip] Hi! Any reason you need to support _500_ active connections? MySQL will not let you do this out of the box (the limit is set to 100 'max_connections'), you'll need to re-configure MySQL to support more, see: http://www.mysql.com/doc/en/Too_many_connections.html If you cross the default limit, you'll get the 'access denied' exception you are getting. One of the main concepts behind connection pooling is to put a cap on resource usage, 500 connections is awfully high for a properly designed application...You should be able to get by with 25 or less in a well-constructed Java app. You might find my 'connection pooling with Connector/J' article helpful, see: http://www.mysql.com/articles/connection_pooling_with_connectorj.html Regards, Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/N8TJtvXNTca6JD8RAg0kAKC6R1MgttLGvo7gHfqUbD6Kyh4WRwCgjlwY P3dPqZbPkZ0ku98fN7pfpWk= =T3xw -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge Server configuration
X-Original-To: [EMAIL PROTECTED] X-Original-To: [EMAIL PROTECTED] Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm From: Dathan Vance Pattishall [EMAIL PROTECTED] Date: Thu, 24 Jul 2003 10:42:31 -0700 RAID-5 is cool, RAID-1+0 (10) is better for writes. Raid5 is slow and noisy compared to striped mirrors. But you need to have the cheap drives...and striped mirrors do not autostart (so some startup scripts need mods). -- email filters only allow mysql mailing list email, no direct replies or queries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge Server configuration
- Original Message - From: Mysql List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:15 AM Subject: Re: Huge Server configuration I have RAID 5 with 5 hardisks, so usuable number of spindle will only be 4. Unless you've designated one drive as a spare, you're using all 5. Maybe you meant that the array has the approximate storage capacity of 4 drives. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Huge Server configuration
Hello all, I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records. Could some one help me get an appropriate mysql configuration(my.conf) file for the machine. I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune thar up. Thanx in Advance -Chandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge Server configuration
NICE No matter how big your disks are, the number of spindles and throughput is your win. my.cnf 3.5x options skip-locking skip-name-resolve set-variable = tmp_table_size=4096 log-bin=binlog/something make sure binlog is a symlink to a separate partition / drive set-variable = key_buffer=4G set-variable = table_cache=2600 # make sure your OS can handle *2 this many file descriptors set-variable = sort_buffer=512M # this is not a common mem pool but a thread pool set-variable = record_buffer=512M set-variable = record_rnd_buffer=512M set-variable = myisam_sort_buffer_size=512M set-variable = max_allowed_packet=16M ---Original Message- --From: Mysql List [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 9:38 AM --To: [EMAIL PROTECTED] --Subject: Huge Server configuration -- --Hello all, -- --I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM --and 1TB harddisk space. --All the tables size are over 10GB and they have over 100mm records. -- --Could some one help me get an appropriate mysql configuration(my.conf) --file for the machine. -- --I understand ther are lots of factors depends on it to get a steady --working configuration. --All I need is some model configuration. I think later on I can tune thar --up. -- --Thanx in Advance ---Chandra -- -- -- -- --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: Huge Server configuration
what table types? Innodb.etc... about how many tables? do you do alot of sorting? are the exact same queries repeated alot? is the machine doing anything else or is mainly a DB server? can I borrow the machine for awhile? what version of mysql are you running? chris -Original Message- From: Mysql List [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:38 AM To: [EMAIL PROTECTED] Subject: Huge Server configuration Hello all, I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records. Could some one help me get an appropriate mysql configuration(my.conf) file for the machine. I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune thar up. Thanx in Advance -Chandra -- 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: Huge Server configuration
Christopher Knight wrote: what table types? Innodb.etc... It is mainly innodb. about how many tables? there are around 200 tables do you do alot of sorting? Lots of sorting and fltering is done are the exact same queries repeated alot? Not likely is the machine doing anything else or is mainly a DB server? Nope. Just DB. can I borrow the machine for awhile? Nope. It is our to be production box what version of mysql are you running? 4.0.14 Thanks for your reply -Chandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge Server configuration
Dathan Vance Pattishall wrote: NICE No matter how big your disks are, the number of spindles and throughput is your win. I have RAID 5 with 5 hardisks, so usuable number of spindle will only be 4. my.cnf 3.5x options skip-locking skip-name-resolve set-variable = tmp_table_size=4096 log-bin=binlog/something make sure binlog is a symlink to a separate partition / drive set-variable = key_buffer=4G set-variable = table_cache=2600 # make sure your OS can handle *2 this many file descriptors set-variable = sort_buffer=512M # this is not a common mem pool but a thread pool set-variable = record_buffer=512M set-variable = record_rnd_buffer=512M set-variable = myisam_sort_buffer_size=512M set-variable = max_allowed_packet=16M ---Original Message- --From: Mysql List [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 9:38 AM --To: [EMAIL PROTECTED] --Subject: Huge Server configuration -- --Hello all, -- --I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM --and 1TB harddisk space. --All the tables size are over 10GB and they have over 100mm records. -- --Could some one help me get an appropriate mysql configuration(my.conf) --file for the machine. -- --I understand ther are lots of factors depends on it to get a steady --working configuration. --All I need is some model configuration. I think later on I can tune thar --up. -- --Thanx in Advance ---Chandra -- -- -- -- --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: Huge Server configuration
RAID-5 is cool, RAID-1+0 (10) is better for writes. Your defiantly are going to be IO bound. I would go with many smaller disks = 20 disk, in multiple RAID-1+0 configurations on different channels or better yet different RAID controllers. Reason by example: Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most of the data is in memory. I'm transferring 15Mb a second of read traffic. ---Original Message- --From: Mysql List [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 10:16 AM --To: [EMAIL PROTECTED] --Subject: Re: Huge Server configuration -- --Dathan Vance Pattishall wrote: -- --NICE -- --No matter how big your disks are, the number of spindles and throughput --is your win. -- -- --I have RAID 5 with 5 hardisks, so usuable number of spindle will only be --4. -- --my.cnf 3.5x options -- --skip-locking --skip-name-resolve -- --set-variable = tmp_table_size=4096 --log-bin=binlog/something make sure binlog is a symlink to a separate --partition / drive -- --set-variable = key_buffer=4G --set-variable = table_cache=2600 # make sure your OS can handle *2 this --many file descriptors --set-variable = sort_buffer=512M # this is not a common mem pool but a --thread pool --set-variable = record_buffer=512M --set-variable = record_rnd_buffer=512M --set-variable = myisam_sort_buffer_size=512M --set-variable = max_allowed_packet=16M -- -- -- -- -Original Message- From: Mysql List [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 9:38 AM To: [EMAIL PROTECTED] Subject: Huge Server configuration Hello all, I have a server like 8way Intel Pentium 4 Xeon processor with 12GB --RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records. Could some one help me get an appropriate mysql --configuration(my.conf) file for the machine. I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune --thar up. Thanx in Advance -Chandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge Server configuration
BEGIN my.cnf [mysqld] port = 3306 socket= /tmp/mysql.sock basedir = /usr/local/mysql log = /var/log/mysql/mysql.log log-slow-queries = /var/log/mysql/mysql-slow.log log-err = /var/log/mysql/mysql.err log-bin = /var/log/mysql/mysql-bin.log log-long-format skip-locking ##Change depending on situation transaction-isolation = READ-COMMITTED ###Tweak Here## set-variable = sort_buffer=512M set-variable = record_buffer=512M set-variable = key_buffer=256M set-variable = log-warnings=1 set-variable = long_query_time=30 ###FOR CACHED QUERIES### set-variable = query_cache_size=128M ###TUNE BASED ON CONNECTIONS### set-variable = max_allowed_packet=32M set-variable = max_connections=50 set-variable = thread_stack=64K set-variable = thread_cache=16 set-variable = thread_concurrency=8 # InnoDB Config ###If you change these... make sure you have a backup !!! (before) ###These are just setup things... not really tweak #innodb_data_home_dir = /usr/local/mysql/data/innodb #innodb_data_file_path = innodb1:500M:autoextend #innodb_log_group_home_dir = /var/log/mysql/innodb #innodb_log_arch_dir = /var/log/mysql/innodb/ #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=10M #set-variable = innodb_log_buffer_size=10M set-variable = innodb_lock_wait_timeout=10 ###Look At These too!! set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=128M set-variable = innodb_flush_log_at_trx_commit=0 END my.cnf what table types? Innodb.etc... It is mainly innodb. about how many tables? there are around 200 tables do you do alot of sorting? Lots of sorting and fltering is done are the exact same queries repeated alot? Not likely is the machine doing anything else or is mainly a DB server? Nope. Just DB. can I borrow the machine for awhile? Nope. It is our to be production box what version of mysql are you running? 4.0.14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge Server configuration
Dathan Vance Pattishall wrote: RAID-5 is cool, RAID-1+0 (10) is better for writes. Your defiantly are going to be IO bound. I would go with many smaller disks = 20 disk, in multiple RAID-1+0 configurations on different channels or better yet different RAID controllers. Well I do not have the luxury. Money is already spent. Well there is no other way to change any of these things. This whole system will only act for readonly purpose. There will be not much of updates will be going on. Only for read with lots of filtering. Sorting is not a priority. Only for retreiving data with multiple conditions. some of the fields are indexed. some of them are not. but those will also be used in the where condition. Reason by example: Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most of the data is in memory. I'm transferring 15Mb a second of read traffic. Please explain. Thanks Again Chandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge Server configuration
---Original Message- --From: Mysql List [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 10:47 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: Huge Server configuration -- --Dathan Vance Pattishall wrote: -- --Only for read with lots of filtering. Sorting is not a priority. Only --for retreiving data with multiple conditions. --some of the fields are indexed. some of them are not. but those will --also be used in the where condition. -- --Reason by example: --Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most of --the data is in memory. I'm transferring 15MB a second of read traffic. I do a fair amount of writes and reads about 40/60 ratio something mysql is not very good at. Thus the need for many flushes to the disk and disk grabs. With this inevitability, this causes my RAID array to transfer a lot of data so much so I'm hitting the arrays max throughput which is about 15MB/sec or 140Mbits a second. With your primarily read box you still might be IO bound especially during table scans. Try using the query cache. As long as you don't write to the mysql cached data it should be a win for you. -- -- --Please explain. -- --Thanks Again --Chandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server configuration denies access to data source
I'm trying to use mysql with java, I create a short piece of code to make a conection but when the class run it sends an exception: Server configuration denies access to data source I can use the database using mysql client without problem. Could someone help me, or ask me where i find the listins from this mailing list? I guess this kind of error was reported before. thanks... Rodolfo Ricci ___ Yahoo! Mail Mais espaço, mais segurança e gratuito: caixa postal de 6MB, antivírus, proteção contra spam. http://br.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server configuration denies access to data source
You need to create the user and grant permissions try (username=teva):
INSERT INTO user (Host,User,Password) VALUES ('%', 'teva',
PASSWORD('your_password'));
and finally add access to the database
INSERT INTO db (Host,Db,User)
VALUES('%','NameOfDatabase','teva');
Hope this helps,
- Original Message -
From: Rodolfo Ricci [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, June 01, 2003 6:32 PM
Subject: Server configuration denies access to data source
I'm trying to use mysql with java, I create a short
piece of code to make a conection but when the class
run it sends an exception:
Server configuration denies access to data source
I can use the database using mysql client without
problem.
Could someone help me, or ask me where i find the
listins from this mailing list? I guess this kind of
error was reported before.
thanks...
Rodolfo Ricci
___
Yahoo! Mail
Mais espaço, mais segurança e gratuito: caixa postal de 6MB, antivírus,
proteção contra spam.
http://br.mail.yahoo.com/
--
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]
Server configuration denies access to data source
Hi, I am a new user of MYSQL, when I use JDBC to
connect MYSQL, I got exception:
SQLException: Server configuration denies access to
data source.
Could some one kindly help me ?
Thanks a lot.
Jeff Zhao
Here is my program, it is very simple
import java.sql.*;
public class LoadDriver
{
public static void main(String[] Args)
{
try {
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
}
catch (Exception E) {
System.err.println(Unable to load driver.);
E.printStackTrace();
}
try {
Connection Conn = DriverManager.getConnection(
jdbc:mysql://localhost/jzhaodb?user=jzhao);
}
catch (SQLException E) {
System.out.println(SQLException: +
E.getMessage());
System.out.println(SQLState: +
E.getSQLState());
System.out.println(VendorError: +
E.getErrorCode());
}
}
}
__
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
-
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
Server configuration denies access to data source
I got the same problem, and i have the impression that this is a MySQL bug... Sorry i can't help you at this moment... i have to help myself first ! :)) Good luck - 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
Server configuration denies access to data source
Ok, i think i've found the cause of my problem and maybe your too ! In fact my MySQL doesn't close opens conections and he doesn't let me open an another one...and so throw me an exception. If i'm waiting few minutes before opening new connections, it's working, so just check your 'connection timeout' value or find another way to close all those unclosed connections. Good luck again ! - 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: Server configuration denies access to data source
It is the code that closes the connections (pconnect) So it would be good to have a look at client first... MySQL should have no problme with upto 1000 connetions...more if you are not on Linx.. I hope this helps you or some one else... Simon -Original Message- From: Core Dumped [mailto:[EMAIL PROTECTED]] Sent: 17 January 2002 15:42 To: [EMAIL PROTECTED] Subject: Server configuration denies access to data source Ok, i think i've found the cause of my problem and maybe your too ! In fact my MySQL doesn't close opens conections and he doesn't let me open an another one...and so throw me an exception. If i'm waiting few minutes before opening new connections, it's working, so just check your 'connection timeout' value or find another way to close all those unclosed connections. Good luck again ! - 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
Server configuration denies access to data source
I installed MySQL in my local dir (~) (RH Linux7.2). Created database successfully. Can operate on this database at mysql prompt. I am trying to access the same database from my Java application THAT WAS working fine with the RMP installation of MySQL. When I run the program it gives SQLException Server configuration denies access to data source. I am running the MySQL server using following command ./bin/mysqld_safe . I created the database named TEST and given the log/pass as root/ (password nothing, as I do not have any password for root setup). What is that need to be done to allow permission to use this database via JDBC. - 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: Packet is Larger than max_allowed_packet from server configuration of 65536 bytes
Hi, Max allowed packet size of a SQL statement is 16MB. Its limit is from structure of MySQL packet. So, please set environment as below. --set-variable=max_allowed_packet=16M Thanks. Chris Stark wrote: Hi, I am having a problem with the configuration of the mysql server or client. I have a very large query that I need to send to MySQL from Java. The query has approximately 5000 individual items in the where clause. (i.e. WHERE i!='AP' AND i != 'J3' AND...to 5000). When I try to execute the Query I get the error: "Packet is Larger than max_allowed_packet from server configuration of 65536 bytes" I read the MySQL documentation and increased my max_allowed_packet to 24M on both the client and the Server in the my.cnf, but I still get the same errorIs there another variable I need to increase, because no matter what I change the value of max_allowed_packet to, I still get the 65535 bytes error?? Please help!! Thanks, Chris -- TAKAHASHI, Tomohiro - 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
Packet is Larger than max_allowed_packet from server configuration of 65536 bytes
Hi, I am having a problem with the configuration of the mysql server or client. I have a very large query that I need to send to MySQL from Java. The query has approximately 5000 individual items in the where clause. (i.e. WHERE i!='AP' AND i != 'J3' AND...to 5000). When I try to execute the Query I get the error: Packet is Larger than max_allowed_packet from server configuration of 65536 bytes I read the MySQL documentation and increased my max_allowed_packet to 24M on both the client and the Server in the my.cnf, but I still get the same errorIs there another variable I need to increase, because no matter what I change the value of max_allowed_packet to, I still get the 65535 bytes error?? Please help!! Thanks, Chris - 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
Server configuration denies access to data source
Hi all, I try to connect me to mySQL server from a java application. I create a new user called was with a password and with an host restricted to one host identified by his IP. I have made another user for localhost (with the same name). Local connection with mysql -u was -p works perfectly. But remote connection doesn't work. I can see in my application log file : Server configuration denies access to data source Do you have any idea about this problem ? Best regards, Nicolas Beaumont Atos Origin Activité Intégration Tour Les Miroirs 18 Av. d'Alsace 92926 Paris La Défense Cedex Tél : 01 70 92 40 33 mail : [EMAIL PROTECTED] - 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
Server configuration denies access to data source
I have a couple java servlets and WebObjects applications that fetch data from MySQL databases. I recently tried running them and received the following error: SQLException raised when connecting : java.sql.SQLException: Server configuration denies access to data source Since the last time I successfully ran these queries I have upgraded MacOS X 10.0.4 to 10.1 and upgraded WebObjects 5 to 5.3. I believe that I have not made any changes to MySQL privileges or databases since then. Can anyone give me some tips on where the problem might lay? Thank you, Craig Doran [EMAIL PROTECTED] - 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
Server configuration denies access to data source
I have a couple java servlets and WebObjects applications that fetch data from MySQL databases. I recently tried running them and received the following error: SQLException raised when connecting : java.sql.SQLException: Server configuration denies access to data source From a command line, I can access the databases and data just fine. Since the last time I successfully ran these queries I have upgraded MacOS X 10.0.4 to 10.1 and upgraded WebObjects 5 to 5.3. I believe that I have not made any changes to MySQL privileges or databases since then. Can anyone give me some tips on where the problem might lay? OS: Mac OS X 10.1 MySQL: 3.23.40 JDBC: mm.mysql.jar (version 1.3?) Apache: 1.3.20 Thank you, Craig Doran [EMAIL PROTECTED] - 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
Server configuration denies access to data source
hi. I had installed mysql on redhat-7.0 I have a java program that inserts, deletes from mysql. It used to work fine. I had to reinstall linux and hence mysql. Now the same program doesnt work. I get the error: Server configuration denies access to data source I can access the databases from the mysql prompt, but not from the java program. Also the db, columns_priv, tables_priv tables in mysql database are empty even though there are 3 users and 3 non empty databases. Is this normal? Please help. -sagar __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ - 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
MM JDBC driver: Server configuration denies access to data source
Hi,
I'm connecting to a MySQL server through the MM JDBC driver on under Red Hat
6.2 using the below code. I keep getting an error message returned that says
: Server configuration denies access to data .
I'm connecting as the root user and I am definitely passing the correct
password etc. in the url I send to the driver. The URL I'm using is :
jdbc:mysql://localhost:3306//test. Can anyone suggest a solution to this
problem please ?
I have another server which is running WIN2K that I can connect to just fine
and both servers were set up in similar ways.
I thought that perhaps I would need to setup the host table in mysql, but
that did not provide any useful results.
I just can't figure it out. Thanks you in advance for any information,
Regards,
Todd G. Nist
Email: [EMAIL PROTECTED]
code:
import java.sql.*;
public class mysqltest
{
Connection Conn = null;
ResultSet RS = null;
Statement Stmt = null;
static String DBUrl = null;
public static void main(String[] args) throws Exception {
DBUrl = args[0];
mysqltest T = new mysqltest();
}
public mysqltest() throws Exception {
try {
System.out.println(preparing to conntect to datasource: +
DBUrl);
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
Conn = DriverManager.getConnection(DBUrl);
Stmt = Conn.createStatement();
System.out.println(got connection);
System.out.println(Selecting Rows);
RS = Stmt.executeQuery(SELECT * FROM user);
System.out.println(Positioning before start of result set);
RS.beforeFirst();
System.out.print(going forward through results: );
while (RS.next()) {
String s = RS.getString(FirstName) + +
RS.getString(LastName);
System.out.println(s);
}
} catch (SQLException E) {
throw E;
}
finally {
if (RS != null) {
try {
RS.close();
} catch (SQLException SQLE) {}
}
if (Stmt != null) {
try {
Stmt.close();
} catch (SQLException SQLE) {}
}
if (Conn != null) {
try {
Conn.close();
} catch (SQLException SQLE) {}
}
}
}
}
The information in this electronic mail (e-mail) message may
be confidential and for use of only the named recipient. The
information may be protected by privilege, work product immunity
or other applicable law. If you are not the intended recipient
the retention, dissemination, distribution or copying of this
e-mail message is strictly prohibited. If you receive this e-mail
message in error please notify us immediately by telephone
at 770-723-1011 or [EMAIL PROTECTED] Thank you.
-
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
Server configuration denies access to the data source
Within the last few days we've started having serious problems with our database. We have no problems connecting to it using "mysql" and "mysqladmin". For instance, this command lets us into the database just fine: mysql -ucustomer -p customer_database The database seems to be working correctly. However, many of our customers are having troubles getting their Java servlets and/or JSP pages to connect properly. Note that these customers have existing applications that have been working correctly for some time. They have not made any changes, nor have we. Now, all of a sudden, they are getting these errors in their error logs: Cannot load connection class 'java.sql.SQLException: Server configuration denies access to data source'. This is happening to multiple customers at the same time. The only way we can fix the problem is to shut down the mysql database server and restart it. Any idea what might be happening? We are running on RedHat 6.2 with mysql 3.22.32 from RPM. Thanks for the help! Tauren - 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
HELP Server configuration denies access to data source
Hi Everyone,
I am in a very very
desperate state 'coz my whole site is down and if I
cannot connect to the database through my servlet my
@ss is grass.
Everything seems to be working fine except when I
execute the servlet I get this frustrating error:
" Server configuration denies access to data source "
And to connect my servlet code is:
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
con =
DriverManager.getConnection("jdbc:mysql://jumac.com:3306/DBNAME?user=ryanpassword=pw007");
Is the problem with my code or my hosting service?
What should I do?
Please give me detailed instructions as I am not too
familiar with MySql so if you are telling me to make
changes in the database please be specific.
Remember if you help me, when I become a LITTLE richer
than Bill Gates I won't forget you either!
Cheers,
Ryan.
([EMAIL PROTECTED])
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
-
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
