MySQL Performance: MySQL => My dedicated server is to slow... why?

Members

Help

Home

Home » Performance » MySQL » My dedicated server is to slow... why?

Show: Today's Messages :: Show Polls :: Message Navigator

My dedicated server is to slow... why? [message #2635]

Fri, 15 February 2008 05:55

turha
Messages: 3
Registered: February 2008

Junior Member

Hi,

I realy need some help from you. I know notting about configuration of mysql. I recently transfer my website from a share server to a dedicated server and the site its slower. The html pages open fast, but when is needed to get informations from the DB averything freezes. In the share server I could work ok with 50 user conected. In the dedicated server with 20 everything freezes. Is to slow. I don't know what to do.

This are the specifications of my server:

CPU AuthenticAMD, AMD Athlon(tm) XP 2600+
psa v8.0.1_build80060713.16 os_FedoraCore 4
SO Linux 2.6.17-1.2142_FC4smp
ram: 471.21 MB
free ram: 18Mb
buffer: 3.40Mb
Cached: 221Mb

I have a DB of 200Mb

the my.cnf is like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with
mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

I really need help... Thanks,

Pedro

Report message to a moderator

Re: My dedicated server is to slow... why? [message #2640 is a reply to message #2635 ]

Sat, 16 February 2008 09:39

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

Are you using MyISAM or InnoDB tables?

If you are using MyISAM then start by adding:
key_buffer_size=70M
To your [mysqld] section and restart.

_OR_ if you are using InnoDB then add:
innodb_buffer_pool_size=200M
to the [mysqld] section and restart.

Because the default values for these are very small.
Then come back and say if it made any difference.

Report message to a moderator

Re: My dedicated server is to slow... why? [message #2642 is a reply to message #2640 ]

Sat, 16 February 2008 18:30

turha
Messages: 3
Registered: February 2008

Junior Member

Hi Sterin,

thanks for your reply.

I had to point back the dns to the shared server because it was impossible to work in the dedicated one. So now I can't test it with real users.

Before I redirect again the Domain I will past here the "show variables" for you to see if there are other values that I should set up and configure, and after that I Will make the redirection of dns back to the dedicated server.

Thanks
----------------

show variables:

|
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | WET |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.20 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------- -------------------------------+
195 rows in set (0.00 sec)

Report message to a moderator

Re: My dedicated server is to slow... why? [message #2644 is a reply to message #2642 ]

Sun, 17 February 2008 16:03

bigrio
Messages: 5
Registered: January 2008
Location: Beijing China

Junior Member

also check the httpd.conf carefully.

Timeout setting in httpd.conf may impact your many db connections sleep too long.

Default timeout is 300. This is so long, so reduce it to 20 seconds or so.

I am solving the slow down of site with so many sleep mysql connections reaching to wait_timeout limit.

There were so many aborted_clients before changing timeout of apache. After changing the mysql connections with sleep state quickly disappears in show processlist.

The slow down issues looks like to be solved.

MaxRequestsperChild of httpd.conf should be checked to reduce memory waste and reduce the concurrent apache process in small server load.

Report message to a moderator

Previous Topic:	Having problem with queries
Next Topic:	Slow query (i think)

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 20 19:18:52 EST 2008

Total time taken to generate the page: 0.03181 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.