HOWTO: Optimize MySQL 4.x

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2



ok, sometimes I am posting too fast ;-)
 
When I try to save the file in vi, I get this error:

Code:
"/etc/my.cnf" E212: Can't open file for writing

my.cnf does not yet exist. How do I get permission to create it in /etc/?

I am accessing the server via SSH using the admin user/pass.

Edit: nevermind, I used su (to get root) and it seems to work. Is there a better way?

Hopefully this helps to decrease the load MySQL causes...
 
Last edited:
hercules# /etc/rc.d/init.d/mysqld restart
/etc/rc.d/init.d/mysqld: Command not found.

And when i put any config in my my.cnf then mysql don't restart when i do a reboot . Also in the directadmin panel i can't use the start thing. Any help ?
 
Your using the cnf in this post and it won't start? Is there any output as to why it doesn't start or what happens? Is there anything that would give a hint to what it is in your error logs? It could be a typo or something in the cnf, might want to double check.
 
Code:
040810 20:36:00  mysqld started
040810 20:36:00  Can't start server : Bind on unix socket: No such file or directory
040810 20:36:00  Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
040810 20:36:00  Aborting

040810 20:36:00  /usr/local/mysql-4.0.17-unknown-freebsd5.1-i386/bin/mysqld: Shutdown Complete

040810 20:36:00  mysqld ended

I get that when i use the settings from the begin post and i found the config of John van directadmin somewhere got the same error. I just need to increase the max connection's because 100 is to little :p
 
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1

What write if my server is Intel Pentium IV 2.6/8 Ghz with RAM 1024 Mb?
 
On our servers with similar specifications we've not had to change the defaults.

Which doesn't mean you won't.

What it does mean is that there's no simple answer.

It depends on your server load, your MySQL load, the number of sites using MySQL, and lots of other variables.

You'll probably have to learn a bit about it and figure it out yourself over time, or hire someone who understands MySQL well to watch your server and adjust it for you as required.

Jeff
 
I need some serious help.

For 3 months i have tried everything and my Database keeps getting slower and slower.

I need a straight answer from someone!!!!! PLEASE!!!

I have this

Processor Name Intel(R) Celeron(R) CPU 2.00GHz
Vendor ID GenuineIntel
Processor Speed (MHz) 1990.289
Total Memory 1022812 kB
Free Memory 15444 kB
Total Swap Memory 2040244 kB
Free Swap Memory 2038176 kB
System Uptime 7 Days, 0 Hours and 5 Minutes
Apache 1.3.31 Running
DirectAdmin 1.22.4 Running
Exim 4.24 Running
MySQL 4.0.20 Running
Named 9.2.1 Running
ProFTPd 1.2.9 Running
sshd Running
vm-Pop3d 1.1.7e Running

basically a intel celeron 2GHz with 1 gig of ram

i know its a celeron but thats all i can afford and this is our starter server we will upgrade when we start getting money back!

Anyway. I need my SQL optimized for this and fast. the site is www.innerearaudio.com then go to catalog and you can see the parse time at the bottom of each page. see the problem. its got almost 750, 000 total records. the latest my.cnf i am using is the one on this site and i think it actually slowed it down. before i was using a slightly modded small.cnf and it took 11-13 secs.


HEEEELLLLLLPPPPPPPPPPP!!!!!! MySQL documentation sux or maybe i am not looking in the right place. most likely the first one!
 
Are you seeing what im seeing!!!!!!???????

15 secs is horrible !!!! and yes i optimize all tables after every day of major inserting and query testing.

So far this is what i have found and done and nothing is changing

Code:
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 47       |
| Qcache_inserts          | 56       |
| Qcache_hits             | 133      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 10       |
| Qcache_free_memory      | 16708008 |
| Qcache_free_blocks      | 4        |
| Qcache_total_blocks     | 110      |
+-------------------------+----------+

Code:
back_log    50 
basedir  / 
binlog_cache_size          32768 
bulk_insert_buffer_size          8388608 
character_set latin1 
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis... 
concurrent_insert ON 
connect_timeout              10 
convert_character_set   
datadir /var/lib/mysql/ 
default_week_format 0 
delay_key_write ON 
delayed_insert_limit                100 
delayed_insert_timeout          300 
delayed_queue_size               1000 
flush OFF 
flush_time 0 
ft_boolean_syntax + -><()~*:""&| 
ft_min_word_len 4 
ft_max_word_len 254 
ft_max_word_len_for_sort 20 
ft_stopword_file (built-in) 
have_bdb NO 
have_crypt YES 
have_innodb DISABLED 
have_isam YES 
have_raid NO 
have_symlink YES 
have_openssl NO 
have_query_cache YES 
init_file   
innodb_additional_mem_pool_size          1048576 
innodb_buffer_pool_size                          8388608 
innodb_data_file_path   
innodb_data_home_dir   
innodb_file_io_threads 4 
innodb_force_recovery 0 
innodb_thread_concurrency 8 
innodb_flush_log_at_trx_commit 1 
innodb_fast_shutdown ON 
innodb_flush_method   
innodb_lock_wait_timeout 50 
innodb_log_arch_dir   
innodb_log_archive OFF 
innodb_log_buffer_size                             1048576 
innodb_log_file_size 5242880 
innodb_log_files_in_group 2 
innodb_log_group_home_dir   
innodb_mirrored_log_groups 1 
innodb_max_dirty_pages_pct 90 
interactive_timeout 100 
join_buffer_size                                  1044480 
key_buffer_size                                   289406976 
language /usr/share/mysql/english/ 
large_files_support ON 
license GPL 
local_infile ON 
locked_in_memory OFF 
log OFF 
log_update OFF 
log_bin ON 
log_slave_updates OFF 
log_slow_queries OFF 
log_warnings ON 
long_query_time 10 
low_priority_updates OFF 
lower_case_file_system OFF 
lower_case_table_names 0 
max_allowed_packet                       16776192 
max_binlog_cache_size                   4294967295 
max_binlog_size                              1073741824 
max_connections 500 
max_connect_errors               10 
max_delayed_threads            20 
max_insert_delayed_threads           20 
max_heap_table_size                     16777216 
max_join_size                                 4294967295 
max_relay_log_size 0 
max_seeks_for_key                        4294967295 
max_sort_length                             1024 
max_user_connections 0 
max_tmp_tables 32 
max_write_lock_count                      4294967295 
myisam_max_extra_sort_file_size          268435456 
myisam_max_sort_file_size              2147483647 
myisam_repair_threads 1 
myisam_recover_options OFF 
myisam_sort_buffer_size                 75497472 
net_buffer_length                            16384 
net_read_timeout 30 
net_retry_count 10 
net_write_timeout 60 
new OFF 
open_files_limit 4096 
pid_file /var/lib/mysql/server.Innerearaudio.com.pid 
log_error   
port 3306 
protocol_version 10 
query_alloc_block_size             8192 
query_cache_limit                     1048576 
Variable_name  Value  
query_cache_size                   16777216 
query_cache_type ON 
query_prealloc_size               8192 
range_alloc_block_size               2048 
read_buffer_size                  10481664 
read_only OFF 
read_rnd_buffer_size             262144 
rpl_recovery_rank 0 
server_id 1 
slave_net_timeout 3600 
skip_external_locking ON 
skip_networking OFF 
skip_show_database OFF 
slow_launch_time 2 
socket /var/lib/mysql/mysql.sock 
sort_buffer_size                       1048568 
sql_mode 0 
table_cache                                1024 
table_type MYISAM 
thread_cache_size                     128 
thread_stack 126976 
tx_isolation REPEATABLE-READ 
timezone EDT 
tmp_table_size                             33554432 
tmpdir /tmp/ 
transaction_alloc_block_size           8192 
transaction_prealloc_size                    4096 
version 4.0.20-standard-log 
version_comment Official MySQL RPM 
version_compile_os pc-linux 
wait_timeout 100

and now what i did on my.cnf
Code:
max_connections = 500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
skip-innodb
skip-locking
query_cache_limit=1M
query_cache_size=16M
query_cache_type=1
key_buffer=276M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=1M
record_buffer=16M
read_buffer_size=10M
myisam_sort_buffer_size=72M

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=268M
sort_buffer_size=72M
read_buffer=1M
write_buffer=1M

[myisamchk]
key_buffer=268M
sort_buffer_size=72M
read_buffer=1M
write_buffer=1M

[mysqlhotcopy]
interactive-timeout

hows this look?!?!?!?!?!
 
ahhh you hit a page that was stored in cache in the database!!! lol

OS Commerce allows for page caching so if i go to it it loads in cache server/database side and then as long ass that query doesn't change it keeps it for the next user. or until it runs out of space!!!

lol I hate this crap sometimes!!!!!!!

Also if you go to the forum you will notice it loads fast as hell.
 
Last edited:
when trying to run this:

mysqlcheck -o -u root -p --all-databases


from that ev1 thread, my root password is not accepted, it says root@localhost has been denied.

Any ideas?
 
Hi all,

Is that the "maxconnection" limit for whole services?
So anyone have idea to limit maxconnection by each account?
Thanks
 
Thafusion said:
hercules# /etc/rc.d/init.d/mysqld restart
/etc/rc.d/init.d/mysqld: Command not found.

And when i put any config in my my.cnf then mysql don't restart when i do a reboot . Also in the directadmin panel i can't use the start thing. Any help ?
Try:
Code:
/etc/rc.d/init.d/mysql restart
 
lnguyen said:
when trying to run this:

mysqlcheck -o -u root -p --all-databases


from that ev1 thread, my root password is not accepted, it says root@localhost has been denied.

Any ideas?

You should try to login as "da_admin". The password can be found at:

/usr/local/directadmin/scripts/setup.txt
 
i afraid that i dont have my.cnf @@

[root@server root]# cat /etc/my.cnf
cat: /etc/my.cnf: No such file or directory
 
Back
Top