s9y and mysql

[ju]

Recently I noticed that my blog became somehow slower. It takes some time until pages are shown. So I looked into the server and found that mysql needed lots of time (and memory) - something like:

Code: Select all

top - 10:20:46 up  1:24,  1 user,  load average: 0.07, 0.04, 0.06
Tasks:  82 total,   2 running,  80 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3108624k total,   559628k used,  2548996k free,    19564k buffers
Swap:  5863640k total,        0k used,  5863640k free,   291764k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
 5185 mysql     20   0  131m  39m 5416 S  0.0  1.3   4:39.84 mysqld 

When I restart mysqld it only gets better for a few hours. Establishing a cron-job to restart mysqld is possible but not really a solution.

How can I tune mysql to cooperate better with s9y? (It is my own server, so I can do pretty everything, but I have very little knowledge of mysql.) What else can I do, to make s9y/mysql faster?

There are about 2200 articles online. I'm using s9y 1.5.5, mysql 5.0, PHP 5.26, Debian Lenny.

[garvinhicking]

Hi!

What other processes are running on your mysql? MySQL uses a query cache, so it's normal that after time the process gets bigger.

What you can do in mysql is to increase query cache and table cache sizes; you don't really need to tune it for s9y specifically, more generally. There should be many online articles and also a few books (by Jeremy Zawodny most notably) if you google for "mysql performance". This is a very distinct topic that we can't really handle here on our small forums, you should get much more valuable feedback on the MySQL forums or through those books, I'm afraid.

What you can do of course to reduce usage is configure the serendipity blog to i.e. not perform login lookups and category ACL permissions, remove unneeded event and sidebar plugins...and installing APC for PHP also helps a lot.

Regards,
Garvin

[ju]

@other processes: There are a mediawiki, a wikindnx (http://wikindx.sourceforge.net/) and a gallery2 on the same server. But they are not publicly available (at least not to search engines), so they should cause no/very few queries, unless I myself use them.

Thanks for the hints. Now I know at least what to google.

[LazyBadger]

Can't recall, is "persistent connections" configurable in Serendipity, but, anyway, from my admin-face I recommend do NOT use it.
For you memory usage ("Mem: 3108624k total, 559628k used") you can see at my-medium or even my-large conf examples and use these drafts as base, mostly in [mysqld] section.

Here my old changes for 256M->512M movement (had immediate effect in overall speed)
-key_buffer = 16K
+key_buffer = 16M
max_allowed_packet = 1M
-table_cache = 4
-sort_buffer_size = 64K
+table_cache = 64
+sort_buffer_size = 512K
+net_buffer_length = 8K
read_buffer_size = 256K
-read_rnd_buffer_size = 256K
-net_buffer_length = 2K
-thread_stack = 64K
+read_rnd_buffer_size = 512K
+myisam_sort_buffer_size = 8M